Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: pl/sql error utilizing dynamic sql
as gocha stated, look for the DBMS_SQL package as user sys. if it is not
there something fundamentally has gone wrong while DB installation.
if it is there try
grant execute on dbms_sql to [user];
as user sys,
where [user] is the scheme you compile itable_proc. should not be necessary, but maybe it helps.
good luck,
--marcus
Stephen schrieb in Nachricht <79qdhs$gpa_at_tuna.eky.com>...
>am getting error on the below code.
>
>here are the errors:
>
>pls-00201 identifier 'dbms_sql.open_cursor' must be declared
>pls-00201 identifier 'dbms_sql.execute' must be declared
>
>code:
>
>procedure itable_proc
>AS
> begin
> declare
> vname varchar2(8);
> RESULT suser_t.mailerplanetcode%type;
> RESULT2 suser_t.mailerplanetcode%type;
> cursor c1 is
> select mailerplanetcode as plcde, mailerplanetcode from
> suser_t;
> procedure execute_immediate( sql_stmt in varchar2 )
> as
> exec_cursor integer default dbms_sql.open_cursor;
> rows_processed number default 0;
> begin
> dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
> rows_processed := dbms_sql.execute(exec_cursor);
> dbms_sql.close_cursor( exec_cursor );
> end;
> begin
> for i in c1 loop
> RESULT := i.plcde;
> vname := 'T'||RESULT||'_T';
> RESULT2 := i.mailerplanetcode;
> execute_immediate( 'insert into ' || vname || '
>
> scf,postal_oper_num,read_date,planetcode,postnet_zipcode,table_date)
> select
>scf,postal_oper_num,read_date,planetcode,postnet_zipcode,sysdate
> from ext_report_t
> where substr(planetcode,3,5) = ''' || RESULT2 ||
>''''
> );
> end loop;
> end;
> end itable_proc;
>
>
>
>--
>
>
>Stephen E. Poff
>poffs_at_tteam.com
>
>
>
>
Received on Wed Feb 10 1999 - 06:53:51 CST