Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2005
    Posts
    24

    Unanswered: PL/SQL - FETCH INTO from dynamic SQL

    Hi!

    I have a a problem using my Procedure with a dynamic SQL like the
    pseudo code:
    ...
    OPEN cursor FOR
    'select emp from emlpoyee where .......';

    LOOP
    FETCH cursor into temp;
    EXIT WHEN ret%NOTFOUND;
    END LOOP;

    I get an error like:
    ERROR:
    ORA-24338: statement handle not executed

    So, I want to FETCH into my variables, but with the dynamic SQL
    statement, Oracle says i should open a cursor before I fetch into.
    How is it possible? Close the cursor, reopen it and Fetch into? if so,
    how is this coded?

    The other way I thought is, that I use a function, returning the
    CURSOR, and Fetching it at another place.

    If so, how can i open a returned cursor for fetching into variables?
    Please give some code examples,

    Thanks,
    Klaus

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    As the STICKY post at top of forum states, many fine coding examples can be found at http://asktom.oracle.com
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2005
    Posts
    24
    sorry, tom has no solution for that kind of problem.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    http://asktom.oracle.com/pls/asktom/...:1288401763279

    You might want to consider remedial SEARCH training.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Try this:
    Code:
    SQL> set serverout on
    SQL> declare
      2    l_emp emp%rowtype;
      3    l_cur sys_refcursor;
      4    l_cond varchar2(100) := 'deptno = 10';
      5  begin
      6    open l_cur for 'select * from emp where '||l_cond;
      7    loop
      8      fetch l_cur into l_emp;
      9      dbms_output.put_line(l_emp.ename);
     10      exit when l_cur%notfound;
     11    end loop;
     12    close l_cur;
     13  end;
     14  /
    KING
    CLARK
    MILLER
    
    PL/SQL procedure successfully completed.

  6. #6
    Join Date
    Sep 2005
    Posts
    24
    The example works, but in my logic, it doesn't. i have to IF Conditions, with several OPEN requests:

    IF test=0 THEN

    OPEN cur FOR ......


    ELSIF test=1 then
    OPEN cur FOR...

    END IF;
    CLOSE CUR;

    Maybe this makes a problem? how can I resolve?
    thanks
    Klaus

    PL/SQL procedure successfully completed.

    ERROR:
    ORA-24338: statement handle not executed

  7. #7
    Join Date
    Sep 2005
    Posts
    24
    Here an example of the problem in a minimal example:

    if I execute the following steps, I have the error of fetch out of
    sequence:
    var temp refcursor;
    exec :temp:=ELBAMAIN.ELBI_5_4_0.testcursor2();

    OUTPUT:
    06-SEP-2007 12:12:09
    06-SEP-2007 12:12:09

    PL/SQL procedure successfully completed.

    ERROR:
    ORA-01002: fetch out of sequence

    no rows selected

    Elapsed: 00:00:00.01
    Elapsed: 00:00:00.06
    12:12:09 SQL>

    Here the CODE:

    PROCEDURE testcursor(nix IN VARCHAR2, cur1 IN OUT sys_refcursor)
    IS
    --l_cur sys_refcursor;
    --cur1 sys_refcursor;
    dual VARCHAR2(100):='sysdate';
    datum DATE;
    dSQL VARCHAR2(4000):=' ';
    BEGIN

    --OPEN l_cur FOR
    dSQL:='SELECT '||dual||' as datum from dual';
    OPEN cur1 for dSQL;
    EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line(sqlerrm);
    -- RETURN 0;
    --CLOSE l_cur;
    END testcursor;

    FUNCTION testcursor2(test IN VARCHAR2)
    RETURN sys_refcursor
    IS
    type a is record (datum DATE);
    b a;
    ret sys_refcursor;
    BEGIN
    testcursor('NIX',ret);
    loop
    FETCH ret into b;
    dbms_output.put_line(b.datum);
    exit when ret%notfound;
    end loop;
    RETURN ret;
    END testcursor2;

    Please, has anyone an idea, how I can resolve the problem?
    THANKS,
    Klaus
    Last edited by klaus1; 09-06-07 at 07:21.

Posting Permissions

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