Re: Procedure to Insert

I got the procedure to work (sort of) but apparently, no data is enetered:

SQL> create table test_table(col1 varchar2(5));

Table created.

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

PL/SQL procedure successfully completed.

SQL> select * from test_table;

no rows selected

The ''a'' has two sets of single quotes, not one set of double quotes.

The table was created by SYSTEM and I was logged on as SYSTEM to execute the procedure.

Alan Shein <alanshein_at_erols.com> wrote in message news:82p4ll$jjt$1_at_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 Fri Dec 10 1999 - 08:30:18 CST