Cursors and Execute Immediate
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Cursors and Execute Immediate

  1. #1
    Join Date
    Nov 2001
    Posts
    5
    Hello! Is there any way to use "execute immediate" with cursors? I would like to make my stored proc somewhat dynamic. Here is a scaled-down version of my procedure:

    CREATE OR REPLACE PACKAGE Publications_pkg AS
    TYPE char_array IS TABLE OF VARCHAR2(250)
    INDEX BY BINARY_INTEGER;
    PROCEDURE getPublicationInfo(
    o_PubNumber OUT char_array,
    o_Locations OUT char_array,
    o_Title OUT char_array,
    o_Organization OUT char_array,
    o_PublicationType OUT char_array,
    i_DisplayRows IN INTEGER);

    END Publications_pkg;
    /

    CREATE OR REPLACE PACKAGE BODY Publications_pkg AS
    PROCEDURE getPublicationInfo(
    o_PubNumber OUT char_array,
    o_Locations OUT char_array,
    o_Title OUT char_array,
    o_Organization OUT char_array,
    o_PublicationType OUT char_array,
    i_DisplayRows IN INTEGER)
    IS

    CURSOR crsPub IS
    SELECT P.PubNumber,
    L.Name AS Locations,
    SUBSTR(P.Title, 1, 50),
    OrgName(P.OrganizationID) AS Organization,
    PubTypeName(P.PublicationTypeID) AS PublicationType
    FROM (SELECT PublicationID, Name FROM Location) L, Publications P
    WHERE LOWER(P.Title) LIKE LOWER('%test%')
    AND L.PublicationID (+) = P.ID
    ORDER BY RevDate;

    BEGIN
    IF NOT crsPub%ISOPEN THEN -- open the cursor if
    OPEN crsPub; -- not already open
    END IF;

    FOR i IN 1..i_DisplayRows LOOP
    FETCH crsPub INTO o_PubNumber(i),
    o_Locations(i),
    o_Title(i),
    o_Organization(i),
    o_PublicationType(i);
    IF crsPub%NOTFOUND THEN
    CLOSE crsPub;
    END IF;
    END LOOP;
    END;
    /

    I have executed statements in the past by concatinating variables that have parts of code in them, and using many if statements, but because the cursor can't go after "BEGIN," (I think this is true of all cases), I haven't been able to make it work.

    Have I painted myself into a corner by the way I've structured this procedure, or is there another way to make the proc dynamic?

    Thanks in advance for any help!
    Shannon

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    well you can use cursor like this

    Code:
    declare
    .............
    begin
    .............
    for i in (select * from dba_users)
    loop
    execute immediate ...........
    end loop;
    end;
    /

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    i hope this may help u.
    this procedure will dynamically create a PL/SQL block.
    u try to give dbms_output.put_line() and do the check


    DECLARE
    lv_table_name VARCHAR(32) := 'emp' ;
    lv_dest_table_name VARCHAR(32) := 'testemp' ;
    col_name VARCHAR(32) ;
    datatype_name VARCHAR(32) ;
    stmt_col VARCHAR(4000) ;
    stmt_column VARCHAR(4000) ;
    sele_stmt VARCHAR(4000) ;
    stmt VARCHAR(32767) ;
    stmt_1 VARCHAR(10) := 'DECLARE '||CHR(10);
    stmt_2 VARCHAR(4000) := '

    TYPE c1 IS REF CURSOR;
    y1 c1;

    PROCEDURE oc (
    ct1 IN VARCHAR2,
    co1 IN OUT c1
    )
    IS
    BEGIN
    OPEN co1 FOR ct1;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
    WHEN OTHERS THEN
    NULL;
    END;';
    BEGIN
    FOR r IN (SELECT column_name,data_type,data_length FROM all_tab_columns
    WHERE table_name = lv_table_name)
    LOOP
    stmt_column := stmt_column || ' , ' || r.column_name ;
    sele_stmt := SUBSTR(stmt_column,3);

    stmt := stmt || 'p_' || r.column_name ||' ' ||lv_table_name||'.'||r.column_name||'%TYPE;'|| CHR(10);

    stmt_col := stmt_col || 'p_' || r.column_name || ',';

    END LOOP;

    stmt := stmt_1 || stmt || stmt_2 ||'

    BEGIN
    oc( ''SELECT' || sele_stmt || ' FROM ' || lv_table_name || ' '',y1);
    LOOP
    FETCH y1 INTO ' || SUBSTR(stmt_col,1,LENGTH(stmt_col)-1) || ';
    EXIT WHEN y1%notfound;
    INSERT INTO '|| lv_dest_table_name || ' VALUES( ' || SUBSTR(stmt_col,1,LENGTH(stmt_col)-1) || ' );
    COMMIT;
    END LOOP;

    END;';

    EXECUTE IMMEDIATE stmt ;

    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR'||SQLCODE||SQLERRM);
    NULL;

    END;
    Cheers!
    OraKid.

  4. #4
    Join Date
    Nov 2001
    Posts
    5
    Thanks for your help! Is it possible to make the select statement dynamic with what I've got?

    e.g.,

    IF myVar IS NOT NULL THEN
    Statement1 := 'Select myField1 FROM myTable1 WHERE myField2 = myVar';
    ELSE
    Statement1 := 'Select myField1 FROM myTable1';
    END IF; etc...

    Thanks again,
    Shannon

  5. #5
    Join Date
    Nov 2001
    Posts
    5
    I figured out how to get cursors to work w/ dynamic sql, without using "Execute Immediate." So...for those who are interested:

    CREATE OR REPLACE PACKAGE Publications_pkg AS
    TYPE PubInfo IS REF CURSOR;
    PROCEDURE getPublicationInfo( crsPub OUT PubInfo,
    i_DisplayRows IN INTEGER,
    i_whr IN VARCHAR2 := NULL);

    END Publications_pkg;
    /

    CREATE OR REPLACE PACKAGE BODY Publications_pkg AS
    PROCEDURE getPublicationInfo2( crsPub OUT PubInfo,
    i_DisplayRows IN INTEGER,
    i_whr IN VARCHAR2 := NULL)
    IS
    BEGIN
    OPEN crsPub FOR
    'SELECT P.PubNumber,
    P.ATANumber,
    P.RevNumber,
    P.Volume,
    P.RevDate,
    P.OrigDate,
    L.Name AS Locations,
    SUBSTR(P.Title, 1, 50),
    OrgName(P.OrganizationID) AS Organization,
    PubTypeName(P.PublicationTypeID) AS PublicationType
    FROM (SELECT PublicationID, Name FROM Location) L, Publications P
    WHERE ' || NVL (i_whr, '1=1') ||
    ' AND L.PublicationID (+) = P.ID
    ORDER BY RevDate';
    END;
    END;
    /

    Cheers!
    Shannon

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width