Re: Dynamice SQL in Oracle

Madhivanan wrote:

> 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:
> begin function package pragma procedure subtype type use
> <an identifier> <a double-quoted delimited-identifier> form
> current cursor
> The symbol "begin" was substituted for "INSERT" to continue.
> ORA-06550: line 12, column 0:
> PLS-00103: Encountered the symbol "end-of-file" when expecting one of
> the
> following:
> . , @ ; for <an identifier>
> <a double-quoted delimited-identifier> group having intersect
> minus order partition start subpartition union where
>
>
> Madhivanan

Trying to do almost anything in Oracle like you did it in SQL Server is a recipe for a bad experience.

1. Buy Tom Kyte's book "Expert one-on-one Oracle"
2. Go to http://tahiti.oracle.com and bookmark it
3. Go to http://www.psoug.org and click on Morgan's Library. Bookmark it.

The syntax, above, has nothing whatsoever to do with Oracle nor does it have anything to do with dynamic SQL. Heck if you had read even one page of Oracle documentation you'd know you should not be using the VARCHAR data type to create a table.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)