1 2 Previous Next 15 Replies Latest reply: Apr 10, 2009 4:36 AM by JustinCave RSS

    ref cursor result

    526603
      I have fucntion which retrurns REF CURSOR.

      I want to use data in the ref cursor in the query.

      So I have created a TEMP table where I am inserting data from ref cursor.

      I am using select * from temp_table .

      Is there any other way as I do not want to insert data in any temp table ?
      if I plan to create collection , is it possible to get the data as select * from collection.
        • 1. Re: ref cursor result
          Hoek
          Hi,

          Check this: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:246014735810
          • 2. Re: ref cursor result
            brkmhr
            I am not sure if I correctly understand, but do you want to iterate the rows in cursor?
            • 3. Re: ref cursor result
              6363
              Ref cursors are pointers to compiled SQL statements they cannot be selected from and are for passing results to a procedural language for processing.

              Is this ref cursor populated from a select statement? If you want to store a select statement to be used in other select statements you would use a view.
              • 4. Re: ref cursor result
                526603
                Thanks for link.
                as per that I have created below code

                declare
                type gcr_cursor_record is record (UICON varchar2(1000),COMPANYGID varchar2(1000),COMPANYNAME varchar2(1000),
                DOMICILE varchar2(1000), ULTIMATENAME varchar2(1000) ,ULTIMATEGID varchar2(1000) , ISO2CODE varchar2(1000), ENTITY varchar2(1000) ,
                SHORTNAME varchar2(1000));
                type gcrtableType as table of gcr_cursor_record;
                GCRTABLE gcrtableType;
                begin
                select * from TABLE ( cast ( GCRTABLE as gcrtableType) );
                end;

                I am getting error while executing. Please check
                • 5. Re: ref cursor result
                  6363
                  Thanks for link.
                  as per that I have created below code
                  declare
                  type gcr_cursor_record is record
                  Why? When the link clearly states.
                  and we said...

                  the proper way to do this is to NOT use a PLSQL table type but to use a SQL Object Type
                  instead. It would look like this:
                  tkyte@OSI1.WORLD> create or replace type myScalarType as object
                    2  (   x     int,
                    3      y    date,
                    4      z    varchar2(25)
                    5  )
                    6  /
                  Type created.
                  
                  tkyte@OSI1.WORLD> create or replace type myTableType as table of myScalarType;
                    2  /
                  Type created.
                  
                  <rest snipped>
                  What does your function code look like? Why are you putting it in a function and not a view if you want to select from it?
                  • 6. Re: ref cursor result
                    Hoek
                    Hi,

                    Glad the link is helpful, however, you might want to check it again, it states:
                    the proper way to do this is to NOT use a PLSQL table type but to use a SQL Object Type
                    instead.
                    So you need to change
                    type gcr_cursor_record is record...
                    into
                    create or replace type gcr_cursor_record as object....
                    (apart from your pl/sql block, also for your type)


                    Something like:
                    create or replace type gcr_cursor_record AS OBJECT
                    (UICON varchar2(1000),COMPANYGID varchar2(1000),COMPANYNAME varchar2(1000),DOMICILE varchar2(1000), ULTIMATENAME varchar2(1000) ,ULTIMATEGID varchar2(1000) , ISO2CODE varchar2(1000), ENTITY varchar2(1000) ,
                    SHORTNAME varchar2(1000));
                    
                    CREATE OR REPLACE type gcrtableType as table of gcr_cursor_record;
                    
                    declare
                    GCRTABLE gcrtableType;
                    begin
                    select * /*You need to select INTO here, in PL/SQL*/  from TABLE ( cast ( GCRTABLE as gcrtableType) );
                    end;
                    But, you need to select INTO something in your PL/SQL block.
                    • 7. Re: ref cursor result
                      526603
                      Thanks to ALL.
                      But my problem is I do not want to introduce any new TYPE onject in schema
                      Whatever type I will use that should be limited to the perticular session.
                      • 8. Re: ref cursor result
                        Hoek
                        Can you show how you use your ref cursor now (with your temp table) ?
                        • 9. Re: ref cursor result
                          BluShadow
                          user523600 wrote:
                          Thanks to ALL.
                          But my problem is I do not want to introduce any new TYPE onject in schema
                          Whatever type I will use that should be limited to the perticular session.
                          Not possible.
                          For SQL to be able to access a TYPE it must exist as a database object. If the TYPE is a PL/SQL type then it cannot be accessed by SQL.
                          • 10. Re: ref cursor result
                            526603
                            Thanks ..This is working fine.
                            But I am facing another issue ..

                            declare
                            gcr_cursor TYPES.ref_cursor;
                            o_gcr_cursor TYPES.ref_cursor;
                            GCRTABLE gcrtableType;
                            begin
                            gcr_cursor := gcr.get_master_client(null,null,'ab',null,null,null,null,null,null,null ,'AU',null,null,null,null,null,null,null);
                            FETCH gcr_cursor bulk collect INTO GCRTABLE;
                            end

                            here at line 7.
                            ORA-00932: inconsistent datatypes: expected - got -
                            ORA-06512: at line 7

                            Please let me know if any solution for this. I am using bulk collect as it is making my script efficient.

                            Please suggest.
                            • 11. Re: ref cursor result
                              Hoek
                              Hi,

                              Looking at your latest example, I'm wondering why you're using a ref cursor.
                              If you want it to be fast and efficient then why not just:
                              begin
                              
                              insert /*+ APPEND */ into GCRTABLE
                              select *
                              from  your_ref_cursor_query;
                              
                              end;
                              If you can do it in SQL, by all means, then do it in SQL.
                              • 12. Re: ref cursor result
                                526603
                                Thanks for reply.
                                I got your point.
                                But I am using ref cursor return by another function.
                                I do not want to skip that function. so I can not call directly SQL for ref cursor.
                                • 13. Re: ref cursor result
                                  Hoek
                                  ORA-00932: inconsistent datatypes: expected - got -
                                  ORA-06512: at line 7
                                  Have a look here:

                                  http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm#BABGAICI

                                  If you scroll down, past the example, you'll see the restrictions on ref cursors, one of them is:

                                  Cursor expressions can appear only:

                                  * In a SELECT statement that is not nested in any other query expression, except when it is a subquery of the cursor expression itself.
                                  * As arguments to table functions, in the FROM clause of a SELECT statement.


                                  You need to store the resultset of your ref cursor in arrays first.
                                  Then (bulk) insert the arrays into your table.
                                  Ref cursors are used to pass resultsets, I don't think you can do DML (= your insert) directly from them.
                                  • 14. Re: ref cursor result
                                    526603
                                    ok.
                                    But surprising thing is that if I do not create TYPES in scheme using Create or replace type.
                                    and just declare types in

                                    declare
                                    TYPE declartion
                                    begin
                                    end;

                                    then
                                    FETCH gcr_cursor bulk collect INTO GCRTABLE; it runs successfully.
                                    but I get error for CAST for table.                from TABLE ( cast ( GCRTABLE as gcrtableType) )

                                    if I create types in schema cAST is successful but bulk collect gives error..
                                    1 2 Previous Next