Re: Dynamice SQL in Oracle

From: Noel <tbal_at_go2.pll-l>
Date: Wed, 11 May 2005 09:20:09 +0200
Message-ID: <d5sc3p$jfa$1@nemesis.news.tpi.pl>

Użytkownik Madhivanan napisał:
> Is it possible to use Dynamic SQL in Oracle as in SQL Server2000?
> I used this
>
> declare t table(i integer, n varchar(10));
> insert into t values(2,'dfg');
> insert into t values(2,'dfg');
> insert into t values(2,'dfg');
> insert into t values(2,'dfg');
> insert into t values(2,'dfg');
>
> select * from t
>
> but I get the error
>
> insert into t values(2,'dfg');
> *
> ERROR at line 2:
> ORA-06550: line 2, column 1:
> PLS-00103: Encountered the symbol "INSERT" when expecting one of the
> following:

(Im sorry, for polish output from sqlpus)

I got the same error !!!!

SQL> declare t table(i integer, n varchar(10));

   2  insert into t values(2,'dfg');
   3  insert into t values(2,'dfg');
   4  insert into t values(2,'dfg');
   5  insert into t values(2,'dfg');
   6  insert into t values(2,'dfg');

   7 /
insert into t values(2,'dfg');
*
ERROR w linii 2:
ORA-06550: linia 2, kolumna 1:

SQL> begin

   2  execute immediate 'create table t(i number, n varchar(10))';
   3  execute immediate 'insert into t values(2,''dfg'')';
   4  execute immediate 'insert into t values(2,''dfg'')';
   5  execute immediate 'insert into t values(2,''dfg'')';
   6  execute immediate 'insert into t values(2,''dfg'')';
   7  execute immediate 'insert into t values(2,''dfg'')';

   8 end;
   9 /

Procedura PL/SQL została zakończona pomyślnie.

SQL> select * from t;

         I N
--------- ----------

         2 dfg
         2 dfg
         2 dfg
         2 dfg
         2 dfg

SQL> Now works.

-- 
Noel

Received on Wed May 11 2005 - 02:20:09 CDT