Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2007
    Posts
    4

    Unanswered: Stored Procs & Ref Cursors

    To whom it may concern:

    I currently have a stored procedure that creates a SELECT statement on the fly. I would require for said SELECT statement to be executed from within the stored procedure and all results returned. Example:

    CREATE OR REPLACE PROCEDURE ME (myResults OUT CURSOR_TYPE) AS

    SQLToExecute VARCHAR2(3000);

    BEGIN

    SQLToExecute := 'begin SELECT COL1, COL3 FROM SOMETABLE; end;';

    OPEN myResults FOR
    myResults;
    CLOSE myResults;

    END;

    From the C# frontend, I am utilizing the OracleDataReader object to fill a DataTable. When I call the Fill() method, however, I get the following error:

    PLS--00428: an INTO clause is expected in this SELECT statement.

    Any insight is much appreciated. Thanks in advance.

    tdeve.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >PLS--00428: an INTO clause is expected in this SELECT statement.
    Which part of the above do you NOT understand?
    When doing a SELECT within PL/SQL you must use the INTO clause; which your code lacks.
    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
    Feb 2007
    Posts
    4
    my mistake...

    the last part of the code should read:

    OPEN myResults FOR
    SQLToExecute;
    CLOSE myResults;

    Regardless, it is my understanding that an INTO can only return one row of data while I am attempting to return potentially multiple rows. My question should have rather been "what am I missing from my code that would allow for all necessary rows to be returned" instead of sounding like "what does I'm missing an INTO statement mean?".

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I am not a mind reader, so I can only answer the question that is actually asked.
    I don't know/do .NOT, but I suspect that a REF CURSOR needs to be returned.
    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
    Feb 2007
    Posts
    4
    I was under the impression that by specifying myResults as an OUT parameter and performing the OPEN and CLOSE would allow for the storage of records within the ref cursor and be automatically returned to the calling application.

    I attempted to add a return statement followed by the cursor name after I close the ref cursor, however, my procedure no longer compiled stating that in a procedure, a return statement cannot contain an expression.

    I am not very familiar with Oracle, however, I'd assume that the same steps would need to be taken within the stored procedure to ensure a valid return of records regardless of whether or not the front end is in .NET.

    Any ideas as to what I'm missing? I'd rather not have to return the SQL statement that's created from within the stored procedure and then have to make an additional DB call. There should be a way that I can do everything from inside the stored procedure.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Both http://otn.oracle.com & http://asktom.oracle.com contain a wealth of knowledge.
    If you spend a little time searching & reading, you should find your answer(s).
    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.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's an example which *might* help you to understand how to do it. First, create a procedure:
    Code:
    SQL> CREATE OR REPLACE PROCEDURE me (myresults OUT sys_refcursor)
      2  IS
      3  BEGIN
      4    OPEN myresults FOR SELECT empno, ename FROM EMP WHERE deptno = 20;
      5  END;
      6  /
    
    Procedure created.
    This part of code shows how to call this procedure:
    Code:
    SQL> var result refcursor;
    SQL> exec me (:result);
    
    PL/SQL procedure successfully completed.
    
    SQL> -- Now, "result" contains output data set
    SQL> print result
    
         EMPNO ENAME
    ---------- ----------
          7369 SMITH
          7566 JONES
          7788 SCOTT
          7876 ADAMS
          7902 FORD
    
    SQL>
    I do not speak C, so I can't help about this part, but I hope that you'll be able to figure out how to do it.

  8. #8
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    As littlefoot showed, the important things on the PLSQL side are :

    - pass the ref cursor as OUT parameter
    - put only the SELECT query when you open the ref cursor (no BEGIN...END like you tried)
    - leave the cursor open since if you close it, the C# code won't be able to read anything from the cursor

    Then on the C# side call the procedure with BEGIN...END :

    'BEGIN ME(:cur); END;'

    and bind the DataReader to the :cur variable. I'm not a C# specialist so you'll have to read the documentation

    http://download-uk.oracle.com/docs/c....htm#sthref217

    And don't forget to CLOSE the cursor on the client side (I think the C# Fill() method does close the cursor, but you might want to check it out).

    There is an alternative to returning the cursor and letting the C# code fetch it : using associative arrays (you'll find how to bind them in the C# doc) and BULK COLLECT :
    Code:
    CREATE OR REPLACE PROCEDURE me (itblEmpno OUT NUMBER_IBT, strtblEname OUT VARCHAR2_IBT)
    IS
    BEGIN
        SELECT empno, ename 
        BULK COLLECT INTO itblEmpno, strtblEname
        FROM EMP WHERE deptno = 20;
    END;
    /
    NUMBER_IBT and VARCHAR2_IBT are INDEX-BY TABLE (or associative array) TYPES that would have been created in the package containing the procedure, for example.

    I personally prefer the cursor solution because when you have many results to get, fetching everything on the PLSQL side can use a HUGE amount of memory, whereas if the results are fetched little by little by the C# side, the memory consumption on the server will be much less.

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  9. #9
    Join Date
    Feb 2007
    Posts
    4
    Littlefoot and RBARAER,

    your help was much appreciated. I had seen an example that had the begin and end stored in the variable and that was causing all my problems.

Posting Permissions

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