This discussion is archived
14 Replies Latest reply: Sep 16, 2013 8:11 AM by 3fca34d0-9ad2-464a-adcb-67111ce9c5a9 RSS

how do i do a select on results of a stored procedure?

3fca34d0-9ad2-464a-adcb-67111ce9c5a9 Newbie
Currently Being Moderated

Hello,

 

I'm new to Oracle. I'm working in SQL Developer.

 

I'm trying to do a simple select on the results of a stored procedure call. I'm not sure how to do it. Here's what I've got so far:

 

declare
type ref_cursor is ref cursor;
results ref_cursor;
begin
MyStoredProcedure(123, results);
select * from results;
end;
/

 

but it tells me:

 

Error starting at line 1 in command:
declare
type ref_cursor is ref cursor;
results ref_cursor;
begin
GET_TAX_TYPE_BY_TAX_ENTITY_KEY(60670100000000, results);
select * from results;
end;
Error report:
ORA-06550: line 6, column 15:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 6, column 1:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

 

Obviously, select * from results; is not the right way to do it. But what is?

 

Thanks for any forthcoming advice.

  • 1. Re: how do i do a select on results of a stored procedure?
    SomeoneElse Guru
    Currently Being Moderated

    Here's a simple SQL Plus demo:

     

    SQL> create or replace procedure

      2  cur_test (p_cur out sys_refcursor) is

      3  begin

      4     open p_cur for select * from emp;

      5  end;

      6  /

     

    Procedure created.

     

     


    SQL> var results refcursor

    SQL> exec cur_test(:results);

     

    PL/SQL procedure successfully completed.

     


    SQL> print results

     

                   EMPNO ENAME      JOB                        MGR

    -------------------- ---------- --------- --------------------

                    7369 SMITH      CLERK                     7902

                    7499 ALLEN      SALESMAN                  7698

                    7521 WARD       SALESMAN                  7698

                    7566 JONES      MANAGER                   7839

                    7654 MARTIN     SALESMAN                  7698

                    7698 BLAKE      MANAGER                   7839

                    7782 CLARK      MANAGER                   7839

                    7788 SCOTT      ANALYST                   7566

                    7839 KING       PRESIDENT

                    7844 TURNER     SALESMAN                  7698

                    7876 ADAMS      CLERK                     7788

                    7900 JAMES      CLERK                     7698

                    7902 FORD       ANALYST                   7566

                    7934 MILLER     CLERK                     7782

     

    14 rows selected.


    (note: I didn't paste all the columns from emp)

  • 2. Re: how do i do a select on results of a stored procedure?
    marcusafs Journeyer
    Currently Being Moderated

    You need to declare the RESULTS variable in MyStoredProcedure as an IN OUT parameter and after execution RESULTS will contain the results.  SInce we cannot see MyStoredProcedure we cannot help much more.

     

    Marcus Bacon

  • 3. Re: how do i do a select on results of a stored procedure?
    3fca34d0-9ad2-464a-adcb-67111ce9c5a9 Newbie
    Currently Being Moderated

    That was helpful (works for me too), but I still can't [I]select[/I] from results:

     

    var results refcursor

    exec GET_DETAILS_BY_TAX_ENTITY_KEY(60670100000000, :results)

    select * from results;

     

    This gives me:

     

    anonymous block completed

    Error starting at line 3 in command:

    select * from results

    Error at Command Line:3 Column:15

    Error report:

    SQL Error: ORA-00942: table or view does not exist

    00942. 00000 -  "table or view does not exist"

    *Cause:   

    *Action:

     


  • 4. Re: how do i do a select on results of a stored procedure?
    SomeoneElse Guru
    Currently Being Moderated

    > That was helpful (works for me too), but I still can't [I]select[/I] from results:

     

    Correct, you don't SELECT from an already opened cursor.

     

    The cursor is (or was) a select statement to begin with.  You now need to FETCH from it.

  • 5. Re: how do i do a select on results of a stored procedure?
    3fca34d0-9ad2-464a-adcb-67111ce9c5a9 Newbie
    Currently Being Moderated

    Okay, so I modified the script to look like this:

     

    var results refcursor

    var field_key number

    exec GET_DETAILS_BY_TAX_ENTITY_KEY(60670100000000, :results)

    fetch results into field_key;

     

    Now I get this error:

     

    Error starting at line 4 in command:

    fetch results into field_key

    Error report:

    Unknown Command

     

    My google research tells me this usually isn't how it's done. The fetching is done in the stored procedure itself (or in a function and then returned). Will it work outside/after the call to the stored procedure?

  • 6. Re: how do i do a select on results of a stored procedure?
    SomeoneElse Guru
    Currently Being Moderated

    > My google research tells me this usually isn't how it's done.

     

    No, it's not done that way at all.

     

    > Will it work outside/after the call to the stored procedure?

     

    Yes.  My example showed that.  After the procedure call, I used the SQL Plus command PRINT which simply iterates through the cursor and displays the results.

     

    What exactly are you trying to do with all this?  Is it just an academic exercise, or are you trying to build something for real?

     

    Normally, ref cursors are passed to outside code/client software.

  • 7. Re: how do i do a select on results of a stored procedure?
    SomeoneElse Guru
    Currently Being Moderated

    You might want to read the first message of this thread.

     

    PL/SQL 101 : Understanding Ref Cursors

  • 8. Re: how do i do a select on results of a stored procedure?
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    You come from a SQL-Server background?

     

    If so, please forget EVERYTHING you learned from SQL-Server when using Oracle. Oracle is not SQL-Server. It does an exceedingly poor job of imitating SQL-Server.

     

    One of the most important concepts in Oracle is that cursor are not result sets. Please read Re: PL Cursors for basic details as to what cursors are.

  • 9. Re: how do i do a select on results of a stored procedure?
    3fca34d0-9ad2-464a-adcb-67111ce9c5a9 Newbie
    Currently Being Moderated

    [quote]
    Yes.  My example showed that.  After the procedure call, I used the SQL Plus command PRINT which simply iterates through the cursor and displays the results.

    What exactly are you trying to do with all this?  Is it just an academic exercise, or are you trying to build something for real?

    Normally, ref cursors are passed to outside code/client software
    [/quote]

     

    Yes, at the end of the line, I will be wanting to use the contents of the cursor in a BIRT report (using Eclipse). I will be using either a dataset to get the content (which BIRT can handle automatically for me) or through a script (which I will have to learn how to do). But I'm not there yet. Right now I want to use the results of one stored procedure (which currently is being stored in the cursor) as input into another stored procedure. That other stored procedure will then be called by BIRT, which handles the results as I just described above.

     

    It's basically similar to having nested select queries (which I could do, I guess, if I had to abandon the use of my stored procedures), but we're trying out stored procedures right now and I have to work with those for now. If it [i]really[/i] doesn't work, then I'll try nested select queries.

     

    Thanks for the link. I'll read through it and see if it helps.

     

    Billy, thank you too for the link.

  • 10. Re: how do i do a select on results of a stored procedure?
    SomeoneElse Guru
    Currently Being Moderated

    Hmmm...maybe you're trying to do something like Pipelined Functions.

     

    http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/tuning.htm#i52932

     

    With Pipelined functions you kinda do a select from a stored function.

     

    i.e. select * from table(some_pipelined_function(arg1,arg2));

  • 11. Re: how do i do a select on results of a stored procedure?
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    3fca34d0-9ad2-464a-adcb-67111ce9c5a9 wrote:

     

    Right now I want to use the results of one stored procedure (which currently is being stored in the cursor) as input into another stored procedure.

    A procedure does not output data from a SQL statement. A cursor (program) does.

     

    A procedure can return a pointer (ref cursor) to the cursor for the caller to use. Keep in mind that PL/SQL is 2 DIFFERENT languages integrated at source code level. PL, or Programming Logic, is an implementation of the Ada language.

     

    In such a language (including C/C++, Java, C#, etc), you usually need to code SQL statements as text strings, and pass these via some kind of client language interface (classes, API, whatever) to the server.

     

    In PL/SQL, the SQL language is integrated with PL, where PL makes these (complex) SQL interface calls for you.

     

    See  Re: what is Main Difference between Sql and Plsql  for an example of how it looks without SQL integration (manually calling the SQL engine inside PL/SQL), versus full SQL integration (PL/SQL calls the SQL engine for you).

     

    So crafting another SQL select on top of a cursor raises serious questions about coherent and functional design, and about performance and scalability. This is not what the PL/SQL language is designed for.

     

     

    It's basically similar to having nested select queries (which I could do, I guess, if I had to abandon the use of my stored procedures), but we're trying out stored procedures right now and I have to work with those for now. If it [i]really[/i] doesn't work, then I'll try nested select queries.

    Whether it can be technically hacked or not, is totally irrelevant.

     

    What is very relevant, relevant ito Oracle concepts and fundamentals, relevant ito scalability and performance, relevant ito security, maintainability, robustness, and so on - is you using the correct Oracle features, appropriately, for addressing your requirements.

     

    And a select on a cursor pointer returned by a PL/SQL procedure is just plain wrong. It means a gross misunderstanding of Oracle concepts and fundamentals.

  • 12. Re: how do i do a select on results of a stored procedure?
    3fca34d0-9ad2-464a-adcb-67111ce9c5a9 Newbie
    Currently Being Moderated

    [quote]And a select on a cursor pointer returned by a PL/SQL procedure is just plain wrong. It means a gross misunderstanding of Oracle concepts and fundamentals.[/quote]

     

    It probably [I]does[/I] mean a gross misunderstanding. Like I said in my OP, I'm brand new to Oracle.

     

    Do you think an SQL function might serve my purposes instead?

     

    One thing to keep in mind is that the end of the line for the data I'm trying to get will be a dataset created in Eclipse/BIRT. It seems BIRT gives me two options for creating datasets: 1) one based on a straight SQL query, or 2) one based on a call to a stored procedure (I went with the latter, which is why I'm focusing on stored procedures). If I switch to 1), do you know if I could call a function stored in the database via a straight SQL query? If I stay with 2), I could probably create a stored procedure that calls the functions that I'd have to create to do what I'm trying to do (if it can be done at all with functions). Are either of these options viable with functions? Which would you recommend?

     

    Thanks again to everyone for your help.

  • 13. Re: how do i do a select on results of a stored procedure?
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    Whether a SQL cursor is constructed by a reporting system, a Java application, a C# program, or a PL/SQL call - the database does not care, as it does not know the difference. A SQL select cursor created in PL/SQL via the a ref cursor interface, is not going to be faster than the same SQL select from Eclipse. If the SQL source code is identical, the very same SQL cursor program will be used by both.

     

    What BIRT gives you is the flexibility of using server-side code (PL/SQL) to create the cursor - enabling that code to make business and technical decisions on how to craft that SQL select. Which is a good thing as it abstracts the data model and technical server complexities from BIRT.

     

    So my choice would be to use the procedural interface and returning ref cursors to the reporting system for rendering.

     

    The issue of running a cursor via cursor (selecting from a cursor), is questionable. Yes, there are a set of technical features that can be used to do this type of thing. But it needs very sound justification and careful design as it is very much an exception. I personally (wearing my DBA hat) would need such justification as it is that much of an exception. I have never seen this implemented in production in over a decade of DBA'ing, despite developers trying to use such a hack - as there always have been far better performing and scaling alternatives.

     

    The fundamental concept for robust and performant code for Oracle is: Maximise SQL. Minimise PL/SQL (or Java/C#/etc).

     

    This means when it comes to crunching database data, write a SQL program to do it. Stop thinking of SQL as a simplistic one statement data retrieval language. Think of it as a programming language. Extremely complex problems can be solved using a single SQL, that would easily require a 100+ lines code of PL/SQL, Java or C#. Write SQL programs. Not SQL one-liners for a data crunching PL/SQL or Java program.

     

    Why then PL/SQL? This is the best language for managing the creation of SQL, dealing with process flow, conditional processing, exception handling and so on. It is inferior to SQL (as is Java and C#) when it comes to data processing.

  • 14. Re: how do i do a select on results of a stored procedure?
    3fca34d0-9ad2-464a-adcb-67111ce9c5a9 Newbie
    Currently Being Moderated

    Thanks Bill, I guess that answers my question. Going with straight SQL was another option in the back of my mind but I didn't mention it above. Sounds like that's the route to take according to you.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points