| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: Dynamic SQL
Add a raise statement in your exception block,
that will give the reason of the failure:
...
EXCEPTION
when others then
dbms_sql.close_cursor(cursor_name);
raise;
END exec;
/
-- Michel ykhlef Mourad <ykhlef_at_yahoo.fr> a écrit dans le message : 38F33148.4441C472_at_yahoo.fr...Received on Tue Apr 11 2000 - 00:00:00 CDT
> My procedure works fine when
> i do not pass a string that create a table.
>
> If i have a table T the
> following command
> exec('alter T add(b integer)')
> works fine
>
> But exec('create table myT(id integer)') faills
>
> That is all
>
> Michel Cadot a écrit :
>
> > So it doesn't work but what is the error?
> > Tell us more.
> >
> > --
> > Michel
> >
> > ykhlef Mourad <ykhlef_at_yahoo.fr> a écrit dans le message : 38F31E29.434D79B6_at_yahoo.fr...
> > > Hi,
> > >
> > > Here is my dynamic storage procedure exec.
> > >
> > > CREATE OR REPLACE PROCEDURE exec(theString in varchar2) as
> > > cursor_name INTEGER;
> > > rows_processed INTEGER;
> > > BEGIN
> > > cursor_name := dbms_sql.open_cursor;
> > > dbms_sql.parse(cursor_name, theString, dbms_sql.v7);
> > > rows_processed := dbms_sql.execute(cursor_name);
> > > dbms_sql.close_cursor(cursor_name);
> > > EXCEPTION
> > > when others then
> > > dbms_sql.close_cursor(cursor_name);
> > > END exec;
> > > /
> > >
> > > Probleme
> > > -------------
> > >
> > > The command
> > > execute exec('create table myT(a number(34))');
> > >
> > > works in SYS schema and does not in a user Schema :
> > >
> > > Note that the command
> > > execute exec('drop table client');
> > > works in a user schema
> > >
> > >
> > > Thank you in advance
> > > Mourad
> > >
> > >
>
 |
 |