Home » SQL & PL/SQL » SQL & PL/SQL » need procedure (oracle 10g)
need procedure [message #436511] Wed, 23 December 2009 22:00 Go to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
I need to develop a procedure for below requirement.
Develop a (PL SQL) procedure that takes as input a table (by name? by object ID? not sure..) and then exports the metadata in XML format for that table: This meta data should be stored in DDI. DDI_REPOS_T (which means that first the table must be defined as an object in DDI_OBJ_DEF_T).... The ORACLE DBMS_METADATA functionality should be used.
Regards,
Madhavi.
Re: need procedure [message #436512 is a reply to message #436511] Wed, 23 December 2009 22:09 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
DBMS_METADATA.GET_XML

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: need procedure [message #436514 is a reply to message #436511] Wed, 23 December 2009 22:41 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
In above case, i used DBMS_METADATA.GET_XML for retrieving the metadata in the form of XML. Can i export the metadata in XML using the same package? or use any other packages like DBMS_DATAPUMP ete...
Regards,
Madhavi.
Re: need procedure [message #436516 is a reply to message #436511] Wed, 23 December 2009 22:46 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
> This meta data should be stored in DDI. DDI_REPOS_T
I have no idea what this is & GOOGLE is clueless.

You need to provide more detailed explanation in order for me to assist.

I do not understand where or in what the XML needs to be deposited.
Re: need procedure [message #436517 is a reply to message #436514] Wed, 23 December 2009 22:56 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
You just copy and paste your SQL session, whatever you have done so far.
Also specify where have you stuck up like any syntactical or logical error you are getting.

regards,
Delna
Re: need procedure [message #436518 is a reply to message #436511] Wed, 23 December 2009 22:59 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
Thanks for reply. Actually i need to export the metadata of any object (Table) in XML format into one table (DDI_REPOS_T) in DDI schema on some other database. The table (DDI_REPOS_T) having two columns. One column is objectid_column (NUMBER Datatype) and second one is repository_column (XMLTYPE Datatype). I want to export into this second column.

Using DBMS_METADATA.GET_XML i able to retrieve the metadata in XML formt. But i don't know how to export it using the same package. Here my doubt is, Is dbms_metadata package used for export? Shall i use other methods?
Thanks,
Madhavi.
Re: need procedure [message #436520 is a reply to message #436518] Wed, 23 December 2009 23:17 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
PLEASE, Please, please read & follow Posting Guidelines.

>Actually i need to export the metadata of any object (Table) in XML format into one table (DDI_REPOS_T) in DDI schema on some other database.

EXPORT is an Oracle utility.
When used as a verb I am not sure what action needs to occur.
I doubt you intend to use "exp" as part of the solution.

>into one table (DDI_REPOS_T)
It would be MOST helpful if you actually posted DDL for this table, so we know what you are talking about to.

[Updated on: Wed, 23 December 2009 23:17]

Report message to a moderator

Re: need procedure [message #436530 is a reply to message #436511] Thu, 24 December 2009 00:35 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
My requirement is copy/move (i call it as export) the metadata of table into one col of remote table.
I wrote the procedure like this.
CREATE OR REPLACE PROCEDURE F_DELETE 
AS
V_CLOB1 clob;
V_CLOB2 clob;
ABC XMLTYPE;
BEGIN
-- Retrieve the metadata
V_CLOB1 := DBMS_METADATA.GET_XML('TABLE','MFP_HOST_GRP_CFG_T','MACHVAJA');
-- Retrieve the comments of the object
V_CLOB2 := DBMS_METADATA.GET_DEPENDENT_XML('COMMENT','MFP_HOST_GRP_CFG_T','MACHVAJA');
ABC := SYS.XMLTYPE.CREATEXML(V_CLOB1);
INSERT INTO TEST.DDI_REPOS_T@TEST (Object_id, repository) VALUES (1,ABC);
END;


I got the following error.
ORA-22804: remote operations not permitted on object tables or user-defined type columns
ORA-06512: at "MACHVAJA.F_DELETE", line 12
ORA-06512: at line 1


Description of my remote table is..
SQL> DESC DDI_REPOS_T;
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 OBJECT_ID                                          NUMBER(10)
 REPOSITORY                                         XMLTYPE


Is there any method?
Thanks,
Madhavi.
Re: need procedure [message #436569 is a reply to message #436530] Thu, 24 December 2009 04:27 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:


ORA-22804 remote operations not permitted on object tables or user defined type columns
PLS-453 remote operations not permitted on object tables or user defined type columns

Solution Description

To workaround this restriction, log into the remote database and create a procedure or function that accepts the pieces of the object table (breaking the object type down into it's components) and performs the DML operation for you. Then log into your local database and perform the desired DML operation using a remote procedure call to this new procedure or function.



Source:http://forums.oracle.com/forums/thread.jspa?threadID=210112&tstart=345

[Updated on: Thu, 24 December 2009 04:27]

Report message to a moderator

Re: need procedure [message #436631 is a reply to message #436530] Thu, 24 December 2009 12:28 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
>My requirement is copy/move (i call it as export) the metadata of table into one col of remote table.
One perspective of this situation is that you are trying to "push" the XMLTYPE into a remote database.
It might be possible to write the procedure such that it resides in/on the same system where DDI_REPOS_T exists locally & "pull" the CLOB containing the XML into this procedure.

[Updated on: Thu, 24 December 2009 12:30]

Report message to a moderator

Re: need procedure [message #436765 is a reply to message #436511] Sun, 27 December 2009 23:44 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
Thanks for reply. As above, i used one ananymous block to insert the returned value of function (get from remote database) in a table (local database) using dblink as below.
 
declare
  v_xml xmltype;
  begin
   select p_delete @DRDD1.REGRESS.RDBMS.DEV.US.ORACLE.COM into v_xml from dual;
   insert into ddi_repos_t (object_id, repository) values (1, v_xml);
 end;

I got the below error.
ORA-30626: function/procedure parameters of remote object types are not supported
ORA-06512: at line 4

Regards,
madhavi.
Re: need procedure [message #436767 is a reply to message #436511] Mon, 28 December 2009 01:17 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
I have one more problem before the above issue.
I want to export (push/move) the metadata of object (Table) into another database column of XMLTYPE in a XML Format. I want to filter the other parameters like segment attributes, storage parameters, constraints etc....

Finally i want to export the data in XML format.
I used the below function to get the required data in XML format.

CREATE OR REPLACE FUNCTION P_DELETE 
RETURN XMLTYPE
AS
Handle NUMBER;
Th NUMBER;
th1 number;
ABC XMLTYPE;
BEGIN
HANDLE := DBMS_METADATA.OPEN('TABLE');

DBMS_METADATA.SET_FILTER(HANDLE, 'SCHEMA','MACHRAJS');
DBMS_METADATA.SET_FILTER(HANDLE, 'NAME','EMPLOYEE');

TH := DBMS_METADATA.ADD_TRANSFORM(HANDLE,'DDL');

DBMS_METADATA.SET_TRANSFORM_PARAM(TH,'SEGMENT_ATTRIBUTES',FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM(TH,'STORAGE',FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM(TH,'CONSTRAINTS',FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM(TH,'REF_CONSTRAINTS',FALSE);

TH1 := DBMS_METADATA.ADD_TRANSFORM(HANDLE,'MODIFY');

ABC := DBMS_METADATA.FETCH_XML(HANDLE);

DBMS_METADATA.CLOSE(HANDLE);
 
RETURN ABC;
END;


At the time of creating i didn't get any error.
But when executing i got the below error.
SELECT P_DELETE FROM DUAL;

ORA-31607: function FETCH_XML is inconsistent with transform.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 3621
ORA-06512: at "MACHRAJS.P_DELETE", line 23



I thought iam unable to convert/transform data into XML after applying filter on DDL transform.

Please suggest me.

Regards,
Madhavi.
Re: need procedure [message #436993 is a reply to message #436511] Wed, 30 December 2009 04:21 Go to previous message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,

My requirement is exporting comments (table and columns) along with table metadata.
I developed 2 functions (one for metadata of table another for comments).

CREATE OR REPLACE function P_METADATA (V_DBLINK IN VARCHAR2,V_SCHEMA IN VARCHAR2, V_TABLE IN VARCHAR2)
return XMLTYPE
 AS
HANDLE1 NUMBER;
ABC1 XMLTYPE;
TH1 NUMBER;

HANDLE2 NUMBER;
ABC2 XMLTYPE;
TH2 NUMBER;

begin
    HANDLE1 := dbms_metadata.open('TABLE',NETWORK_LINK => V_DBLINK);
    DBMS_METADATA.SET_FILTER(HANDLE1, 'SCHEMA',V_SCHEMA);
    DBMS_METADATA.SET_FILTER(HANDLE1, 'NAME',V_TABLE);
    DBMS_METADATA.SET_COUNT(HANDLE1,1000000);
    ABC1 := DBMS_METADATA.FETCH_XML(HANDLE1);
   dbms_metadata.CLOSE(HANDLE1);
    RETURN ABC1;
END;


My first function returns xml format like below.

2nd function...
CREATE OR REPLACE function P_METADATA (V_DBLINK IN VARCHAR2,V_SCHEMA IN VARCHAR2, V_TABLE IN VARCHAR2)
return XMLTYPE
 AS
HANDLE1 NUMBER;
ABC1 XMLTYPE;
TH1 NUMBER;

HANDLE2 NUMBER;
ABC2 XMLTYPE;
TH2 NUMBER;

begin

    HANDLE2 := dbms_metadata.open('COMMENT',NETWORK_LINK => V_DBLINK);
  --  DBMS_METADATA.SET_FILTER(HANDLE, 'SCHEMA',V_SCHEMA);
    DBMS_METADATA.SET_FILTER(HANDLE2, 'BASE_OBJECT_NAME',V_TABLE);
    DBMS_METADATA.SET_COUNT(HANDLE2,100);
    ABC2 := DBMS_METADATA.FETCH_XML(HANDLE2);
    RETURN ABC2;
   dbms_metadata.CLOSE(HANDLE2);
    
END;

For my first function i have one root node and one child node. 2nd function returns one root node and many (for each column comment returnd one child node).

I want one variable (now 2 variables with diffrent format) containing total xml data then insert into one table. When i trying for append/ merge/ concat i got invalid xml format error.
I am not much familiar with XML. Please help me.
Regards,
Madhavi.
Previous Topic: Sum of debit and Credit
Next Topic: Oracle9i
Goto Forum:
  


Current Time: Wed Sep 28 05:45:05 CDT 2016

Total time taken to generate the page: 0.12626 seconds