Re: dynamic SQL question

A copy of this was sent to Curtis Holbrook <cholbroo_at_concentric.net> (if that email address didn't require changing) On 23 Oct 1999 12:30:40 PDT, you wrote:

>Hey all...
>
>I'm trying to use the procedure 'parse' in the Oracle package
>'dbms_sql'. There are two 'parse' procedures available: one takes a
>simple varchar2 argument to pass the SQL statement; the other takes a
>table of varchar2(256) argument to pass the SQL statement (for very
>large statements).
>
>My code looks something like this:
>
>declare
> type char_tbl as table of varchar2(256);
>
> my_tbl char_tbl;
>begin
> .
> .
> .
>
> dbms_sql.parse( cursor_id,
> my_tbl,
> my_tbl.first,
> my_tbl.last,
> false,
> dbms_sql.native );
> .
> .
> .
>end;
>
>The compiler returns the error 'Wrong number or type of arguments'. I
>know for certain that the first, fourth, and fifth arguements are fine.
>Am I passing the table incorrectly? I'm using PL/SQL version 2.3.
>

[Quoted] even though you and i know YOUR table type is the same as the dbms_sql.varchar2s type -- the compiler doesn't believe they are.

you should declare:

   my_tbl dbms_sql.varchar2s;

don't make up your own type -- use the SAME type as dbms_sql is using.

>Any help will be greatly appreciated.
>
>Thanks,
>Curtis

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation