Home » SQL & PL/SQL » SQL & PL/SQL » unable to recreate an object (Oracle 10.2.0.1)
unable to recreate an object [message #447417] Mon, 15 March 2010 03:22 Go to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
I have the metadata of an object in one of my database table as xml. I failed to recreate the object in the other schema using metadata api. I developed a stored function to do the above job. My function doesn't throw any error as well as it doesn't create the object.
My code snippet is

CREATE OR REPLACE PROCEDURE DDI.move_table(
table_name in VARCHAR2,
from_schema in VARCHAR2,
to_schema in VARCHAR2 ) 
AUTHID CURRENT_USER
IS
-- Define local variables.
h2 NUMBER; -- handle returned by OPENW
th1 NUMBER; -- handle returned by ADD_TRANSFORM for MODIFY
th2 NUMBER; -- handle returned by ADD_TRANSFORM for DDL
xml XMLTYPE; -- XML document
errs sys.ku$_SubmitResults := sys.ku$_SubmitResults();
err sys.ku$_SubmitResult;
result BOOLEAN;
BEGIN

SELECT REPOS INTO xml from ddi.ddi_repos_t where obj_id = '1801';

-- Specify the object type using OPENW (instead of OPEN).
h2 := DBMS_METADATA.OPENW('TABLE');

-- First, add the MODIFY transform.
th1 := DBMS_METADATA.ADD_TRANSFORM(h2,'MODIFY');

-- Specify the desired modification: remap the schema name.
DBMS_METADATA.SET_REMAP_PARAM(th1,'REMAP_SCHEMA',from_schema,to_schema);

-- Now add the DDL transform so that the modified XML can be
-- transformed into creation DDL.
th2 := DBMS_METADATA.ADD_TRANSFORM(h2,'DDL');

-- Call PUT to re-create the object.
result := DBMS_METADATA.PUT(h2,xml,0,errs);

DBMS_METADATA.CLOSE(h2);
IF NOT result THEN

-- Process the error information.
FOR i IN errs.FIRST..errs.LAST LOOP
err := errs(i);
FOR j IN err.errorLines.FIRST..err.errorLines.LAST LOOP
dbms_output.put_line(err.errorLines(j).errorText);
END LOOP;
END LOOP;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20510,'No xml is available as metadata');
END;



Could you tell me where is the probelm?
The schema where i create and execute my function have dba privilege also.

Regards,
Madhavi.
Re: unable to recreate an object [message #447424 is a reply to message #447417] Mon, 15 March 2010 04:39 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Strange...If its really not telling you why this is not able to recreate...The error message should appear in case of any problem. Where are you running the script in SQL Prompt or in some utility?
Re: unable to recreate an object [message #447425 is a reply to message #447417] Mon, 15 March 2010 04:41 Go to previous messageGo to next message
Littlefoot
Messages: 20892
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If there was no error, perhaps objects were created, but not in schema you looked at.
Re: unable to recreate an object [message #447428 is a reply to message #447417] Mon, 15 March 2010 04:52 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
I ran my script on toad (I tried with SQL*PLUS also). The function should return the errors if any. When executing, i got null only. It means there was no error. I verified in the user (where i need to recreate the object). But it throws error like table or view doesn't exists when i select it. (I have checked properly).
Meanwhile i tried to creat an object like
CREATE TABLE USER2.EMP AS SELECT * FROM USER1.EMP;
The table created. (It shows no privilege problem).
Now i'm in a confusion that where i did wrong?
Regards,
madhavi.
Re: unable to recreate an object [message #447434 is a reply to message #447428] Mon, 15 March 2010 05:18 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The "Process the error information" part might not print anything if SERVEROUTPUT is not enabled.
Re: unable to recreate an object [message #447437 is a reply to message #447417] Mon, 15 March 2010 05:26 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
I used toad and also serveroutput is enabled only.
Re: unable to recreate an object [message #447438 is a reply to message #447437] Mon, 15 March 2010 05:34 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

The problem is with only this Procedure or any other also?
Try to crete the simplest one and test for msg's

anything like
CREATE OR REPLACE PROCEDURE Test is
BEGIN 
IF 1=1  THEN
dbms_output.put_line('Msg is working here..');
END IF;
END;

And then Execute

begin
test;
end;


Check if your getting any msg's?



Re: unable to recreate an object [message #447447 is a reply to message #447417] Mon, 15 March 2010 05:48 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
i got the message.
Msg is working here..


I dont think there is a problem in my environment. I feel that its a privilege issue( i don't know what is that). DBA Privilege useful when creating the object from sql. Here the case is the metadata api recreates the object.

Regards,
Madhavi.
Re: unable to recreate an object [message #447451 is a reply to message #447447] Mon, 15 March 2010 05:57 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
what is the output for this

Quote:
SELECT REPOS INTO xml from ddi.ddi_repos_t where obj_id = '1801';


sriram Smile
Re: unable to recreate an object [message #447454 is a reply to message #447417] Mon, 15 March 2010 06:08 Go to previous message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Output for above query is xml data (metadata in the form of xml) of a table which is passed as a parameter (table name passed as parameter).

In previous cases also, i'm unable to select the metadata of other schema objects. That is solved by change program by adding invoker rights. This case also same i think.
Regards,
madhavi.

[Updated on: Mon, 15 March 2010 06:22]

Report message to a moderator

Previous Topic: PL/SQL Performance issue
Next Topic: Hide declaration or declare it inside the body of a package
Goto Forum:
  


Current Time: Mon Dec 05 02:51:38 CST 2016

Total time taken to generate the page: 0.15101 seconds