Re: DBMS_SQL.PARSE does not work after Oracle8 upgrade

A copy of this was sent to "Frank Siegel" <NorthernSnow_at_worldnet.att.net> (if that email address didn't require changing) On Thu, 28 Oct 1999 10:19:52 -0400, you wrote:

>Thanks in advance for any help you may provide. Yesterday
>we upgraded from Oracle 7.3.4 to Oracle 8.0.5. Now our
>dynamic SQL function does not work.
>
>This is where I bomb:
>
>dbms_output.put_line('*** About to SQL parse for table: ');
>DBMS_SQL.PARSE (cursor_handle,
> 'SELECT ' || col_string ||
> ' FROM ' || in_table_name,
> DBMS_SQL.V7);
>dbms_output.put_line('*** Made it!');
>
>This is the result::
>*** About to SQL parse for table: CONNECTICARE_RX
>5ORA-00923: FROM keyword not found where expected
>

thats not the exact code you are running (eg: the dbms_output output you show does not match up with what the dbms_output.put_line statement would actually produce)...

Try this:

dbms_output.put_line( '*** about to sql parse for table: ' || in_table_name ); declare

    l_query varchar2(4000) default 'select ' || col_string ||

                                     ' from ' || in_table_name;

    dbms_output.put_line( substr( 'Parsing "' || l_query || '"', 1, 250 );     dbms_sql.parse( cursor_handle, l_query, dbms_sql.v7 ); end;
dbms_output.put_line( '*** Made it!' );

That way -- you'll see the query you are parsing. I'm guessing there is something wrong with your variable col_string and it might not be upgrade related at all.

>What should I do or try? Changing DBMS_SQL.V7 to DBMS_SQL.V8 did not work.
>It said we had to define a string for V8.
>
>Thanks Again,
>Frank S.
>New Hampshire
>
>

--
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 Received on Thu Oct 28 1999 - 10:06:54 CDT