dbms_sql - Cretate table problem

From: <gene_at_beep.ru>
Date: Mon, 25 Dec 2000 12:38:29 +0300
Message-ID: <9274b0$j01$1@storm.comstar.ru>

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. Received on Mon Dec 25 2000 - 03:38:29 CST