Re: Challenge? dynamic query in store procedure

> This works, however it has some problems:
>
> 1. I do not understand why sqlplus complains when I tried to do
>
> p_cursor in out ref cursor
>
> in the procedure declaration. I have to create a type packages to work
> around this: create a type called cursortype, and use types.cursortype
> in the procedure.
>

Simply because Oracle PL/SQL designers designed it this way. :) In 9i, you have SYS_REFCURSOR type declared for you, so you don't have to declare REF CURSOR type in a package - you can use SYS_REFCURSOR instead.

> 2. I have to use print(c) to print out the data. I want more control
> of out put, such as
>
> DBMS_output.Put_line('This is filed one' || c.filed1 || ' This is
> filed 2' || c.filed2);

Well, if you *know* the %ROWTYPE for that cursor, there's no problem fetching from it into a record or a set of variables and then processing fetched data the way you want. However, you have no way to *describe* a weak cursor (at least, not in PL/SQL) so if you don't know the row layout it is pretty impossible to fetch from that cursor since you don't know which columns and of which types are there (unlike DBMS_SQL cursors, where you have DESCRIBE_COLUMNS() procedure, which can be used to describe cursor columns, and then fetch them with COLUMN_VALUE() procedures - but DBMS_SQL doesn't support ADTs so it can't be used with anything beyond basic SQL types.) With JDBC or OCI you can describe any cursor and that's what sqlplus does behind the scene when you PRINT cursor_variable.

Corrections and additions welcome.

-- 
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.