Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Using REF CURSOR-like process against PL/SQL Table

    We're building packages for database updates, and during processing we're filling working variables (ie, # rows updated, # skipped, etc). Ideally, we'd like to fill a PL/SQL table with 'rows' of data, and then pass them back to the calling program via a REF CURSOR. This way, we could customize the rowtype for each package's output.

    I'm unable to tell if we're coding things improperly, or if we're attempting something that Oracle cannot do. We just keep getting errors each time we try it a different way. I have been reading that:

    1) You can't create a TYPE on-the-fly in the DECLARATION section of a procedure for use in a REF CURSOR
    2) a REF CURSOR can only return the results of a SQL statement

    Are both of these true, and if so are there any tricks to getting around them?

    If there's no other recourse we will create a table for holding this information, and then return results from there via a REF CURSOR, but I'd like to achieve the flexibility of this approach if it's possible.

    If anyone has a couple of hints it'd be great.
    Thanks,
    Chuck

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    1) You can't create a TYPE on-the-fly in the DECLARATION section of a procedure for use in a REF CURSOR
    According to the doc, it isn't so, "You can define REF CURSOR types in any PL/SQL block, subprogram, or package." : for 10g, see here. However, when I want to use REF CURSORS in a procedure, I prefer to declare it in the head of the package the procedure belongs to, like that :
    Code:
    create or replace package pckg_test as
    
    	type rc is REF CURSOR;
    	
    	PROCEDURE test1(
    		rcResult out rc
    		);
    
    end pckg_test;
    This way, I share the rc type among all procedures or functions within the package.

    2) a REF CURSOR can only return the results of a SQL statement
    AFAIK, it's true. If you want to use REF CURSORS to pass values from one procedure to another, you could work with a "normal" Table or a Global Temporay Table (GTT). But if you really want to work with PL/SQL tables, then you can still pass them as out parameters to another procedure. In that case, you should consider using the NOCOPY option in order to pass your PL/SQL table by reference, not by value (it just passes the pointer to the table, not the table itself). For more info on NOCOPY, look at the "PL/SQL User's Guide and Reference", for 10g, you'll find it here.

    HTH & Regards,

    RBARAER
    Last edited by RBARAER; 02-09-05 at 06:09.

  3. #3
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    type rc is REF CURSOR;
    Conveniently, SYS_REFCURSOR is already defined for us in 9i.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    1) You can't create a TYPE on-the-fly in the DECLARATION section of a procedure for use in a REF CURSOR
    I was referring to setting up a custom row-type which would be used to determine the structure of the PL/SQL table, which would then be used with a cursor in order to return data. The documentation seems to indicate (as do some failed attempts) that this is not possible, requiring that types referred to in the SQL statement of a REF CURSOR must be defined in SQL. Even though we're attempting something similar without SQL, we still would have to declare a rowtype for the cursor, I think.



    When you refer to passing PL/SQL tables between procedures, are you hinting at a way to return the results of a PL/SQL table out to the calling program (a reporting tool in this case)? Would you mind clarifying a bit more? I'm still learning, and sometimes the concept slips by me.

    -Chuck

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Sorry for your first question, seems like I didn't understand it as it was meant to be understood . I fear I can't help you more than the doc here.

    Concerning NOCOPY, the doc I pointed you to will certainly be more helpful than me , for I have never used it yet, just know that it exists, and for two procedures running both on the server, it asks the compiler to try to pass a parameter by reference instead of by value, which is very interesting for large structures such as some PL/SQL tables. As I said, it is only a hint, not an obligation for the compiler, and it will only work in certain conditions (see the doc). For example, if you try to pass a PL/SQL table by reference from a proc on the server to an app on a client, it will not work and pass it by value (copying all data and sending it on the network). I'm not sure this will help you, but I thought it might be interesting for you to know it exists.

    Regards,

    RBARAER

  6. #6
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66
    Quote Originally Posted by chuck_forbes
    we'd like to fill a PL/SQL table with 'rows' of data, and then pass them back to the calling program via a REF CURSOR.
    2) a REF CURSOR can only return the results of a SQL statement
    I'm contemplating conversion from Sybase to Oracle. In that platform, temp tables created on the fly could participate in subsequent joins. This wasn't possible with Oracle 8i. But now I'm reading about Table functions. If my interpretation is correct, you can do something like

    select co1, col2
    from my_db_table, TABLE( my_collection )
    where ...

    If you can Select, then you can also create a REF CURSOR.

    This way, the information stored in PL/SQL tables finds its way back into the world of result sets. The TABLE is a key word, but I really just started to read the Table Functions chapter in the PL/SQL manual. Please let me know if it works for you.

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    Right, this seems to work, but you have to create any TYPE classes via SQL statements outside the procedure, rather than using PL/SQL. This, to me, makes management of all the elements a bit more difficult, since you have to remember that whenever you move the package script, you also have to move the script which creates the TYPE classes.

    BTW, we are in the process of moving from Sybase to Oracle (and WANG to Oracle). I think you will find INLINE VIEWS capable of replacing Sybase temp tables in many cases:

    Code:
    select t1.employee_name, inline_view.last_payday
    from   employees t1,
             (select emp_id, max(payday)
              from pay_table
              group by emp_id) inline_view
    where t1.emp_id = inline_view.emp_id
    I do not know of any specific issues surrounding large PL/SQL tables being used in place of Sybase temp tables, but I ran across a couple of posts at asktom.com which warns against them. In our case, they were always going to be small (<100 records), and we wouldn't need to sort them, or do anything other than spit out the records one by one.

    -Chuck

  8. #8
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66
    Quote Originally Posted by chuck_forbes
    create any TYPE classes via SQL statements outside the procedure, rather than using PL/SQL. [...] remember that whenever you move the package script, you also have to move the script which creates the TYPE classes.
    AFAIK, the TYPE definitions can go into the package header ( the publicly visible portion of the package). This way, any caller who is using the procedure automatically gets access to the Types as well.

    Without being aware of the details, I suspect that the type declarations are only required if using a strongly typed cursor. In this case, the columns returned by the cursor are accurately defined. But there is always the less safe, but more flexible alternative of untyped REF CURSOR. I heard v9.2 has a system package which predefines the generic reference cursor type.


    It's interesting that few Oracle programmers felt the need to return a Ref Cursor based on Pl/Sql tables. Perhaps our way of thinking is still very much Sybase. Or, shall I say SQL Server. BTW, Ms-Sql-2000 also has inline views. But I still prefer temp tables, just to avoid having a single large Select statement.

    Oracle copes surprisingly well with huge statements, with inline views nested 3 or 4 levels deep. I had to write such monstruous statements when I was accessing a read-only database. But they are difficult to read, and a single syntax error, anywhere on a three-page statement, can ruin your day. A sequential approach, going from one temp table to the next, is easier to follow, even in a set-based architecture.

    Andrew

Posting Permissions

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