Procedure to Insert

From: Alan Shein <alanshein_at_erols.com>
Date: Thu, 9 Dec 1999 15:50:02 -0500
Message-ID: <82p4ll$jjt$1@autumn.news.rcn.net>

I have the following procedure, which can truncate tables for me, but I would like to use it to do an insert every now and then as well. What am I doing wrong?

CREATE OR REPLACE PROCEDURE special_sql (p_sql VARCHAR2) as

ch INTEGER;
begin

   ch := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(ch,p_sql,DBMS_SQL.NATIVE);    DBMS_SQL.CLOSE_CURSOR(ch);
   commit work;
end special_sql;

So,

execute special_sql ('truncate table test_table');

works just fine, but

execute special_sql('insert into test_table (col1) values ('a')');

returns an error:

ERROR at line 1:
ORA-06550: line 1, column 59:
PLS-00103: Encountered the symbol "A" when expecting one of the following:
. ( ) , * @ % & | = - + < / > in mod not range rem => ..
an exponent (**) <> or != or ~= >= <= <> and or like between is null is not ||
The symbol ". was inserted before "A" to continue.

I tried various permutations of single and double quotes (except the right one?), but nothing seemed to work. Received on Thu Dec 09 1999 - 14:50:02 CST