| DBMS_METADATA.get_dll issue [message #164261] |
Wed, 22 March 2006 11:35  |
shsh_shah
Messages: 5
Registered: March 2006
Location: Dublin
|
Junior Member |
|
|
I am trying to copy constraints from remote database 'TEST' into local copy of those tables but get and error and cant solve the problem.
ERROR Description:
ERROR at line 1:
ORA-31603: object "ORDER_ITEMS" of type TABLE not found in schema "HAHMED2"
ORA-06512: at "SYS.DBMS_METADATA", line 1511
ORA-06512: at "SYS.DBMS_METADATA", line 1548
ORA-06512: at "SYS.DBMS_METADATA", line 1864
ORA-06512: at "SYS.DBMS_METADATA", line 2684
ORA-06512: at "SYS.DBMS_METADATA", line 4220
ORA-06512: at line 1
ORA-06512: at line 29
I am using script as below:
DECLARE
V_TABLENAME all_tables.table_name%TYPE;
l_ddl varchar2(32000);
V_COPYTABLENAME all_tables.table_name%TYPE;
v_tableExists boolean := false;
CURSOR c1 is
SELECT table_name FROM all_tables WHERE dropped = 'NO' and owner = 'TEST';
CURSOR c2 Is
Select table_name FROM user_tables where dropped = 'NO';
sql_stmt varchar2(200);
BEGIN
FOR rec1 IN c1 LOOP
v_tablename := rec1.table_name;
for rec2 in c2 loop
if rec2.table_name = (rec1.table_name || '_COPY') then
v_tableExists := true;
end if;
END LOOP;
if v_tableExists = false then
execute immediate 'create table ' || v_tablename || '_COPY as select * from brendan.' || v_tablename;
---CONSTRAINT COPY -----
SELECT DBMS_METADATA.GET_DDL ('TABLE', v_tablename)
INTO v_copytablename
FROM all_tables
where owner='TEST';
----TRIGGERS COPY --------
For trg in (select trigger_name from all_triggers where owner= 'BRENDAN' and table_name = v_tablename ) loop
l_ddl:= cast(replace(replace(dbms_metadata.get_ddl( 'TRIGGER', trg.trigger_name),v_tablename,v_copytablename),
trg.trigger_name,substr(v_copytablename||trg.trigger_name, 1 , 30)) as varchar2);
execute immediate substr(l_ddl, 1, instr(l_ddl,'ALTER TRIGGER')-1);
END LOOP;
END IF;
END LOOP;
END;
Any help will be really appreciated. ThankYou
NOTE:
Local schema: hahmed2
Remote schema : TEST
|
|
|
|
| Re: DBMS_METADATA.get_dll issue [message #164266 is a reply to message #164261] |
Wed, 22 March 2006 12:43  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
There are lots of problems with what you have listed..I'm not sure where to start.
Does the schema listed below have the table listed below?
ORA-31603: object "ORDER_ITEMS" of type TABLE not found in schema "HAHMED2"
Why are you selecting multiple calls/copies of get_ddl from all_tables, rather than just calling it once, especially since you are passing it the same table name each time?
Oh wait, it isn't the same table name, you haven't closed out of your outer loop. Please format your posts with appropriate code tags.
But regardless, multiple results can't be used with a select into construct.
And try to avoid nested cursor for loops looping line by line in plsql where possible, do things as single queries. In general, although this shouldn't be a performance issue but a functionality one.
I would suggest not trying to do this dynamically, at least to start. Instead try to write statements that generate sql statements through the get_ddl calls, have those written out to a file or the screen or something as a first step.
|
|
|
|
| Re: DBMS_METADATA.get_dll issue [message #164269 is a reply to message #164266] |
Wed, 22 March 2006 12:55 |
shsh_shah
Messages: 5
Registered: March 2006
Location: Dublin
|
Junior Member |
|
|
Regards Your question.
1. Does the schema listed below have the table listed below?
ORA-31603: object "ORDER_ITEMS" of type TABLE not found in schema "HAHMED2"
1. yes i am getting this table in hahmed2 schema but i have five tables in remote schema which should also be copied as well.
Can you give more clear idea i changed the loops but still same results.
-----------------------
DECLARE
V_TABLENAME all_tables.table_name%TYPE;
l_ddl varchar2(32000);
V_COPYTABLENAME all_tables.table_name%TYPE;
v_tableExists boolean := false;
CURSOR c1 is
SELECT table_name FROM all_tables WHERE dropped = 'NO' and owner = 'BRENDAN';
CURSOR c2 Is
Select table_name FROM user_tables where dropped = 'NO';
sql_stmt varchar2(200);
BEGIN
FOR rec1 IN c1 LOOP
v_tablename := rec1.table_name;
for rec2 in c2 loop
if rec2.table_name = (rec1.table_name || '_COPY') then
v_tableExists := true;
end if;
END LOOP;
if v_tableExists = false then
execute immediate 'create table ' || v_tablename || '_COPY as select * from brendan.' || v_tablename;
END IF;
END LOOP;
-----------------TRIGGERS WILL RUN IN LOOP TO CHECK IF ANY------
For trg in (select trigger_name from all_triggers where owner= 'BRENDAN' and table_name = v_tablename ) loop
l_ddl:= cast(replace(replace(dbms_metadata.get_ddl( 'TRIGGER', trg.trigger_name),v_tablename,v_copytablename),
trg.trigger_name,substr(v_copytablename||trg.trigger_name, 1 , 30)) as varchar2);
execute immediate substr(l_ddl, 1, instr(l_ddl,'ALTER TRIGGER')-1);
END LOOP;
------ CONSTRAINTS will run once thats why outside loop----
SELECT DBMS_METADATA.GET_DDL ('TABLE', v_tablename)
INTO v_copytablename
FROM all_tables
where owner='BRENDAN';
END;
IF possible can you help me out in correction.Thanx
|
|
|
|
| Re: DBMS_METADATA.get_dll issue [message #164272 is a reply to message #164261] |
Wed, 22 March 2006 13:02 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
No...I'm sorry...maybe someone else, but I would maintain my first suggestion of:
I would suggest not trying to do this dynamically, at least to start. Instead try to write statements that generate sql statements through the get_ddl calls, have those written out to a file or the screen or something as a first step.
And also add that it would help if you spent some time going through the plsql user guide...probably from towards the beginning, and basically did a walkthrough of it to become more familiar and practiced with the language. Or a good book.
And please, post your code in a properly formatted way.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
