Explanation needed

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • nahom
    New Member
    • Feb 2008
    • 3

    Explanation needed

    Hi, can any one explain me what function of this for loop and the 'execute immediate command line' . C_ALTER_INDEXES is a cursor

    FOR L_ALTER_INDEXES IN C_ALTER_INDEXES
    LOOP
    DBMS_OUTPUT.PUT _LINE(L_ALTER_I NDEXES.INDEX_AL TER);
    EXECUTE IMMEDIATE L_ALTER_INDEXES .INDEX_ALTER;
    END LOOP;
  • debasisdas
    Recognized Expert Expert
    • Dec 2006
    • 8119

    #2
    What Is The Problem With The Code ?

    Is That Your Code ?

    Comment

    • nahom
      New Member
      • Feb 2008
      • 3

      #3
      Originally posted by debasisdas
      What Is The Problem With The Code ?

      Is That Your Code ?

      no it is by some one else and i am unable to understand the usage of the Execute Immediate function.

      Comment

      • amitpatel66
        Recognized Expert Top Contributor
        • Mar 2007
        • 2358

        #4
        Originally posted by nahom
        no it is by some one else and i am unable to understand the usage of the Execute Immediate function.
        Let me brief you on what is the use of EXECUTE IMMEDIATE:

        EXECUTE IMMEDIATE is used to execute dynamic sql statements (queries). The sql statements which are not known till runtime are dynamic sql statements which are constructed dynamically and executed using EXECUTE IMMEDIATE statement.

        The piece of code that you have provided is not enough for our experts to explain you in detail.

        Please provide full source snippet, what is the cursor all about, and what data the cursor handle holds etc.

        Comment

        • nahom
          New Member
          • Feb 2008
          • 3

          #5
          Thanks very much for this explanation I am able to make sense of the remaining code.

          Comment

          • amitpatel66
            Recognized Expert Top Contributor
            • Mar 2007
            • 2358

            #6
            Originally posted by nahom
            Thanks very much for this explanation I am able to make sense of the remaining code.
            You are welcome :) .

            Comment

            • subashsavji
              New Member
              • Jan 2008
              • 93

              #7
              Originally posted by nahom
              Hi, can any one explain me what function of this for loop and the 'execute immediate command line' . C_ALTER_INDEXES is a cursor

              FOR L_ALTER_INDEXES IN C_ALTER_INDEXES
              LOOP
              DBMS_OUTPUT.PUT _LINE(L_ALTER_I NDEXES.INDEX_AL TER);
              EXECUTE IMMEDIATE L_ALTER_INDEXES .INDEX_ALTER;
              END LOOP;
              some of example of execute immediate and cursor.
              Code:
              DECLARE
              2 MY_CURSOR NUMBER;
              3 RESULT NUMBER;
              4 BEGIN
              5 MY_CURSOR := DBMS_SQL.OPEN_CURSOR;
              6 DBMS_SQL.PARSE(MY_CURSOR,'SELECT 1 FROM DUAL',0);
              7 RESULT := DBMS_SQL.EXECUTE(MY_CURSOR);
              8 DBMS_SQL.CLOSE_CURSOR(MY_CURSOR);
              9 END;
              10 /
              
              both compilation(parsing) and execution happens at same time.
              //
              create table Test
              (
              Id integer,
              SQLString varchar2(4000)
              );
              
              insert into Test values (1, 'select count(*) from Emp');
              insert into Test values (2, 'select count(*) from Dept');
              insert into Test values (3, 'select count(*) from User_Tables');
              
              commit;
              
              Now, actual PL/SQL block:
              
              declare
              
              vCount integer;
              
              cursor csrSQLToExecute (pId Test.Id%type)
              is
              select *
              from Test
              where Id > pId;
              
              begin
              
              for rec in csrSQLToExecute(0)
              loop
              execute immediate rec.SQLString into vCount;
              dbms_output.put_line('String: ' || rec.SQLString ||
              '. Result: ' || to_char(vCount));
              end loop;
              
              end;
              //
              Last edited by amitpatel66; Feb 20 '08, 09:59 AM. Reason: code tags

              Comment

              • amitpatel66
                Recognized Expert Top Contributor
                • Mar 2007
                • 2358

                #8
                Duplicate posts deleted for better management of the forum

                MODERATOR

                Comment

                Working...