Re: Problem with DBMS_SQL

I have the answer. Finally I remove the ; from the parse. Also I put en DBMS_SQL.execute before the close_cursor and .....IT WORKS !!!

PRAB ATHILAT wrote:

> I got the same error when my procedure with Dynamic Sql tries to truncate
> tables in another schema. IN spite of granting privileges the problem still
> occured. I know this much that it can be avoided by putting the porcedure in
> the same schema as tables OR putting in SYS schema (which is not the right
> solution).
> Diego Pafumi wrote in message <3654572D.2F8DA7F9_at_us.oracle.com>...
> >Hi everybody, I'm trying to run a Script to populate a table, but I
> >receive an error message in my function :
> >
> >ORA-06512: at "SYS.DBMS_SYS_SQL", line 491
> >ORA-06512: at "SYS.DBMS_SQL", line 32
> >ORA-06512: at "SYSTEM.CHARGE_Y2K_TABLES", line 56
> >ORA-06512: at line 67
> >
> >I call the Package/Function with:
> > Charge_Y2k_Tables.Insert_Data(tab.OWNER,tab.TABLE_NAME, col_names,
> >my_values);
> >
> >And my Package/Function is:
> >
> >CREATE OR REPLACE PACKAGE Charge_Y2k_Tables
> >IS
> > PROCEDURE Insert_Data (own in varchar2, tab in varchar2, col in
> >varchar2, data in varchar2);
> >END Charge_Y2k_Tables;
> >
> >CREATE OR REPLACE PACKAGE BODY Charge_Y2k_Tables
> >AS
> >PROCEDURE Insert_Data (own in varchar2, tab in varchar2, col in
> >varchar2, data in varchar2)
> >IS
> > my_cur integer;
> > rows_processed number;
> >BEGIN
> > my_cur := DBMS_SQL.OPEN_CURSOR;
> > DBMS_SQL.PARSE(my_cur, 'Insert into ' || own || '.' || tab || ' (' ||
> >col || ') values(' || data || ');', DBMS_SQL.V7);
> > DBMS_SQL.CLOSE_CURSOR(my_cur);
> >END Insert_Data;
> >
> >END Charge_Y2k_Tables;
> >
> >What is wrong with this???
> >Any comment will be appreciate !!!!!
> >Thanks !!!
> >
Received on Fri Nov 20 1998 - 00:00:00 CET