Re: dbms_sql - Cretate table problem

Try to "GRANT CREATE TABLE TO your_user;" directly ( NOT via role ).

 HTH. Michael.

In article <9274b0$j01$1_at_storm.comstar.ru>,   <gene_at_beep.ru> wrote:
> There is problem.
>
> I am trying to execute stored procedure ( created without problem).
>
> I am triying to copy table from one scheme to another...
> ****************************************************************************
> *********************
> (table_name IN VARCHAR2,sc_to IN VARCHAR2,sc_from IN VARCHAR2)
> AS
> cid INTEGER;
> strToCreate VARCHAR2(128);
> strToDrop VARCHAR2(128);
> BEGIN
> cid := DBMS_SQL.OPEN_CURSOR;
> strToDrop := 'DROP TABLE ' || sc_to || '.' || table_name;
> strToCreate := 'CREATE TABLE ' || sc_to || '.' || table_name || ' AS
> (SELECT * FROM ' || sc_from || '.' || table_name || ')';
> DBMS_SQL.PARSE(cid,strToDrop ,0);
> DBMS_SQL.PARSE(cid,strToCreate , 0);
> DBMS_SQL.CLOSE_CURSOR(cid);
> commit;
> EXCEPTION
> WHEN OTHERS THEN
> DBMS_SQL.PARSE(cid,strToCreate , 0);
> DBMS_SQL.CLOSE_CURSOR(cid);
> commit;
> END;
> ****************************************************************************
> *************************
>
> But I am getting this result ...
>
> ****************************************************************************
> *************************
> SQLWKS> execute refillTable('banks','frontoffice','sgtst');
> ORA-00942: table or view does not exist
> ORA-06512: at "SYS.DBMS_SYS_SQL", line 491
> ORA-06512: at "SYS.DBMS_SQL", line 32
> ORA-06512: at "FRONTOFFICE.REFILLTABLE", line 16
> ORA-00942: table or view does not exist
> ORA-06512: at line 2
> ****************************************************************************
> *************************
>
> From other hand, I can execute this code from Oracle Froms without any
> problem.
> ****************************************************************************
> *************************
> 00001 PROCEDURE refillTable(table_name IN VARCHAR2,sc_to IN
> VARCHAR2,sc_from IN VARCHAR2) IS
> 00002 cid INTEGER;
> 00003 strToCreate VARCHAR2(128);
> 00004 strToDrop VARCHAR2(128);
> 00005 BEGIN
> B(01) cid := DBMS_SQL.OPEN_CURSOR;
> 00007 strToDrop := 'DROP TABLE ' || sc_to || '.' || table_name;
> 00008 strToCreate := 'CREATE TABLE ' || sc_to || '.' || table_name || ' AS
> (SELECT * FROM ' || sc_from || '.' || table_name || ')';
> 00009 DBMS_SQL.PARSE(cid,strToDrop ,0);
> 00010 DBMS_SQL.PARSE(cid,'COMMIT',0);
> 00011 DBMS_SQL.PARSE(cid,strToCreate , 0);
> 00012 DBMS_SQL.CLOSE_CURSOR(cid);
> 00013 commit;
> 00014 EXCEPTION
> 00015 WHEN OTHERS THEN
> 00016 DBMS_SQL.PARSE(cid,strToCreate , 0);
> 00017 DBMS_SQL.CLOSE_CURSOR(cid);
> 00018 commit;
> 00019=>END;
> ****************************************************************************
> ***************************
>
> I am use connection parameters the same in both cases.
>
> What can be wrong ?!!!
>
> Evgeny.
>
>

Sent via Deja.com
http://www.deja.com/ Received on Tue Dec 26 2000 - 16:08:30 CST