Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: pl/sql error utlizing dynamic sql

Re: pl/sql error utlizing dynamic sql

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 10 Feb 1999 21:50:28 GMT
Message-ID: <36c1fe96.33571232@192.86.155.100>


A copy of this was sent to "Stephen" <poffs_at_tteam.com> (if that email address didn't require changing) On Tue, 9 Feb 1999 17:42:56 -0500, you wrote:

>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
>

are you running this in sqlplus or from what environment? can you describe dbms_sql.open_cursor in SQL*Plus?

>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;
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Feb 10 1999 - 15:50:28 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US