DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: DBMS_SQL and REF CURSOR (Oracle 7)

Hybrid View

  1. #1
    Join Date
    Dec 2001
    Posts
    5

    Question

    Hello,

    What I’m missing in the following:

    PROCEDURE proc(
    p_var1 IN varchar2,

    p_cur In OUT cur_type)

    IS
    lnCursor INTEGER;
    lnDummy INTEGER;
    lvcSql VARCHAR2(2500);
    v_where varchar2(1000);
    BEGIN
    --v_where defined here from p_var1
    lnCursor := DBMS_SQL.OPEN_CURSOR;
    lvcSql :='OPEN p_cur FOR '||
    'SELECT col1, col2 '||
    'FROM table '||v_where ;
    dbms_output.put_line(lvcSql); --looks fine
    DBMS_SQL.PARSE( lnCursor, lvcSql, DBMS_SQL.V7 );
    lnDummy := DBMS_SQL.EXECUTE( lnCursor );
    DBMS_SQL.CLOSE_CURSOR( lnCursor );


    I get here ORA-00900: invalid SQL statement when parsing.

    Please help!

  2. #2
    Join Date
    Jan 2002
    Posts
    33
    append CREATE OR REPLACE to PROCEDURE proc

  3. #3
    Join Date
    Jan 2002
    Posts
    33
    by the way, i am assuming that you are using sql*plus or something other than forms or procedure builder.

  4. #4
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Opening a PL/SQL cursor as you tried is not allowed.
    Native dynamic SQL (NDS) could be good choice for that thing:
    Code:
    PROCEDURE proc( 
      p_var1 IN varchar2, 
      … 
      p_cur In OUT cur_type) 
    IS 
      lvcSql VARCHAR2(2500); 
      v_where varchar2(1000); 
    BEGIN 
      lvcSql :='OPEN p_cur FOR '|| 
        'SELECT col1, col2 '|| 
        'FROM table '||v_where ; 
      OPEN p_cur FOR lvcSql;
    end;

  5. #5
    Join Date
    Dec 2001
    Posts
    5
    Thank you for replies.
    To simflex: the procedure is the part of the packege, that's why it's written this way. cur_type is defined in package spec.
    To ales: It's not compiled this way, we don't have NDS, it's Oracle 7.
    Problem still exists...

  6. #6
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Oh, I'm sorry I overlooked the version in title ...

    In 7.3 is not possible to open PL/SQL cursor for dynamically built query. Try to redesign the proc, perhaps you could write more statically defined statements like "OPEN p_cur FOR SELECT ..." and put them into "IF-THEN-ELSIF-END IF".
    Or, if you need create you query dynamically, just use ordinary dbms_sql cursor (lnCursor) instead of PL/SQL cursor. You can also pass it out of the proc and fetch from it.
    Of course, it means more coding.
    HTH,
    Ales

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