Hey,
You can't use the INTO clause in Dynamic PL/SQL.
You have to use DBMS_SQL.DEFINE_COLUMN and
DBMS_SQL.COLUMN_VALUE.
Try this:
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
FROM user_tables
where table_name = UPPER('''||source ||''')'
,DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, name,
30);
DBMS_SQL.COLUMN_VALUE(source_cursor, 1, name);
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;
/
- Skurský_Michal <skursky_at_brn.pvt.cz> 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).
Do You Yahoo!?
Received on Wed Jan 17 2001 - 16:15:20 CST
