Re: Dynamic SQL

Michal,

    You cannot SELECT INTO in dynamic SQL. Look at the examples in $ORACLE_HOME/rdbms/admin/dbmssql.sql.
You must call two functions to attach your variable to the result of the cursor, then actually move the result into it (since you just want to test existence, I presume that calling the first one must be enough).

Regards,

Stephane Faroult
Oriole Corporation

Skurský Michal wrote:

> Hello,
> can anybody help me to understand my mistake?
> I tried to create procedure with 'table_name' parameter, which writes result
> of select -> Table exists or not. Procedure doesn't work properly - existing
> table is not found.
> Thanks
>
> CREATE OR REPLACE PROCEDURE cc1(source IN VARCHAR2) is
> name VARCHAR2(30);
> source_cursor INTEGER;
> ignore INTEGER;
> BEGIN
> source_cursor := dbms_sql.open_cursor;
> DBMS_SQL.PARSE(source_cursor,
> 'SELECT table_name INTO name FROM user_tables where table_name =
> source' ,DBMS_SQL.native);
> ignore := DBMS_SQL.EXECUTE(source_cursor);
> DBMS_OUTPUT.PUT_LINE('Table exists ');
> DBMS_SQL.CLOSE_CURSOR(source_cursor);
>
> EXCEPTION
> WHEN OTHERS THEN
> DBMS_OUTPUT.PUT_LINE('Table does not exist ');
> IF DBMS_SQL.IS_OPEN(source_cursor) THEN
> DBMS_SQL.CLOSE_CURSOR(source_cursor);
> END IF;
> END;
> /
>
> Execute cc1('xxxx')
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: =?iso-8859-2?Q?Skursk=FD_Michal?=
> INET: skursky_at_brn.pvt.cz
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Wed Jan 17 2001 - 15:44:47 CST