Home » SQL & PL/SQL » SQL & PL/SQL » Not able to apply session tranforms (ORACLE 10G)
Not able to apply session tranforms [message #446182] Sat, 06 March 2010 00:12 Go to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
My function returns the DDL of an object by taking xml as input. I want to filter constranis, storage parameters and need sql terminator and pretty form. I'm using metadata api for that but unable to get.
I'm using 10.2.0.1.
Please look into my code.
CREATE OR REPLACE FUNCTION DDI.F_Metadata_Import_NEW (P_Type VARCHAR2,
                                            P_Name VARCHAR2,
                                            P_Owner VARCHAR2 DEFAULT 'SYS' )
   RETURN clob
   AUTHID CURRENT_USER
AS
   V_Xml       XMLTYPE;
   V_Xml_Ind   XMLTYPE;
   V_Node      VARCHAR2 (25);
   V_Cnt       NUMBER := 1;
   Handle      NUMBER;
   Th          NUMBER;
   Var1        sys.KU$_MULTI_DDLS;
   Var2        sys.KU$_DDLs;
   Var3        CLOB;
   V_Total     CLOB;
   V_Total2    CLOB;
   Obj_Type    VARCHAR2 (35);
   V_Part1     VARCHAR2 (35);
   V_Part2     VARCHAR2 (35);
   v_Length    NUMBER;
   V_Scmid     NUMBER;
   V_Objtpid   NUMBER;
   
BEGIN
    SELECT   Repos INTO   V_Xml  FROM   DDI.DDI_REPOS_T
                WHERE   Obj_Id = 13;
                DBMS_OUTPUT.PUT_LINE('get xml from ddi table');
                BEGIN
                    SELECT   '/ROWSET/ROW[' INTO V_Part1 FROM DUAL;
                    SELECT   ']' INTO V_Part2 FROM DUAL;
                    DBMS_OUTPUT.PUT_LINE('loop starts');
                    LOOP
                        -- Get the each dependent object metadata and node.
                        BEGIN
                            DBMS_OUTPUT.PUT_LINE('get the node');
                            SELECT   Xml, Nodes INTO   V_Xml_Ind, V_Node FROM   (SELECT   t2.COLUMN_VALUE.GETROOTELEMENT () Nodes, Xml
                                FROM   (SELECT   EXTRACT (V_XML,
                                               V_Part1 || V_Cnt || V_Part2) Xml FROM   DUAL) t,
                                TABLE (XMLSEQUENCE (t.xml.EXTRACT ('//node()'))) t2)
                                WHERE   ROWNUM = 1 AND Nodes NOT IN ('ROWSET', 'ROW');
                        EXCEPTION
                            WHEN NO_DATA_FOUND THEN EXIT;
                        END;
                        IF V_Xml_Ind IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE('NODE IS ' || V_Node);
                        END IF;
                        DBMS_OUTPUT.PUT_LINE('get the object type');
                         -- Get the Object_Type
                       IF V_Node = 'TABLE_T' THEN Obj_type := 'TABLE';
                         ELSIF V_Node IN ('TABLE_DATA_T','POST_TABLE_T' ) 
                            THEN Obj_type := 'TABLE_DATA';
                         ELSIF V_Node = 'OBJGRANT_T'
                            THEN Obj_type := 'OBJECT_GRANT';
                         ELSIF V_Node = 'INDEX_T'
                            THEN Obj_type := 'INDEX';
                         ELSIF V_Node = 'CONSTRAINT_T'
                            THEN Obj_type := 'CONSTRAINT';
                         ELSIF V_Node = 'I_STAT_T'
                            THEN Obj_type := 'INDEX_STATISTICS';
                         ELSIF V_Node = 'COMMENT_T'
                            THEN Obj_type := 'COMMENT';
                         ELSIF V_Node = 'REF_CONSTRAINT_T'
                            THEN Obj_type := 'REF_CONSTRAINT';
                         ELSIF V_Node = 'TRIGGER_T'
                            THEN Obj_type := 'TRIGGER';
                         ELSIF V_Node = 'T_STAT_T'
                            THEN Obj_type := 'TABLE_STATISTICS';
                         ELSIF V_Node = 'VIEW_T'
                            THEN Obj_type := 'VIEW';
                         ELSIF V_Node = 'M_VIEW_T'
                            THEN Obj_type := 'MATERIALIZED_VIEW';
                         ELSIF V_Node IN ('PROCEDURE_T','ALTER_PROCEDURE_T') 
                            THEN Obj_type := 'PROCEDURE';
                         ELSIF V_Node IN ('FUNCTION_T','ALTER_FUNCTION_T')
                            THEN Obj_type := 'FUNCTION';
                         ELSIF V_Node IN ('PACKAGE_T','ALTER_PACKAGE_SPEC_T','PACKAGE_BODY_T')
                            THEN Obj_type := 'PACKAGE';
                         ELSIF V_Node = 'SEQUENCE_T'
                            THEN Obj_type := 'SEQUENCE';
                         ELSIF V_Node = 'REFGROUP_T'
                            THEN Obj_type := 'REFRESH_GROUP';
                         ELSIF V_Node = 'LIBRARY_T'
                            THEN Obj_type := 'LIBRARY';
                         ELSIF V_Node = 'TYPE_T'
                            THEN Obj_type := 'TYPE';  
                         ELSIF V_Node = 'DIRECTORY_T'
                            THEN Obj_type := 'DIRECTORY';  
                         ELSIF V_Node = 'TABLESPACE_T'
                            THEN Obj_type := 'TABLESPACE';  
                         ELSIF V_Node = 'USER_T'
                            THEN Obj_type := 'USER';     
                       END IF;
      
                        DBMS_OUTPUT.PUT_LINE('Objetc type is ' || Obj_type);
                        DBMS_OUTPUT.PUT_LINE('append the rowset');
                        -- Append root node to xml data.
                        SELECT   XMLELEMENT ("ROWSET", V_Xml_Ind) INTO   V_Xml_Ind FROM DUAL;
                       DBMS_OUTPUT.PUT_LINE('madhavi1');
                       -- Convert the xml into DDL.
                        IF OBJ_TYPE NOT IN ('TABLE_DATA') 
                            THEN
                                DBMS_OUTPUT.PUT_LINE('start the process');
                                -- Open a handle for tables in the current schema.
                                Handle := DBMS_METADATA.OPENW (OBJ_TYPE);
                                DBMS_OUTPUT.PUT_LINE('open the handle');
                                -- Add the DDL transform so we get SQL creation DDL
                                th := DBMS_METADATA.ADD_TRANSFORM (HANDLE, 'DDL');
                                DBMS_OUTPUT.PUT_LINE ('Added the tranform');
                               
                                  -- Tell the XSL stylesheet we don't want physical storage information (storage,
                                  -- tablespace, etc), and that we want a SQL terminator on each DDL AND PRETTY FORMAT.
                                  DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',TRUE);
                                  DBMS_OUTPUT.PUT_LINE ('Segment attributes true');
                                  DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
                                  DBMS_OUTPUT.PUT_LINE ('STORAGE False');
                                  DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',FALSE);
                                  DBMS_OUTPUT.PUT_LINE ('Tablespace true');
                                  DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',FALSE);
                                  DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',FALSE);
                                  DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS_AS_ALTER',FALSE);
                                  DBMS_OUTPUT.PUT_LINE ('Constraints as alter true');
                                 
                                DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
                                DBMS_OUTPUT.PUT_LINE ('sqlterminator true');
                                DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
                                DBMS_OUTPUT.PUT_LINE ('Pretty true');
                                EXIT WHEN V_Xml_Ind is null;
                                -- Convert the xml to DDL
                                VAR1 := DBMS_METADATA.CONVERT (handle, V_Xml_Ind);
                                VAR2 := VAR1 (1).DDLS;
                                VAR3 := VAR2 (1).ddltext;

                                -- Get total DDL in one file/variable
                                IF V_Total IS NULL
                                THEN v_total := var3;
                                ELSE
                                 v_total := v_total || var3;
                                END IF;
                               END IF;  
     
                        V_CNT := V_CNT + 1;
                           END LOOP;

                    -- Free resources allocated for table stream
                    DBMS_METADATA.CLOSE (Handle);
                    DBMS_OUTPUT.PUT_LINE('After execution ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
                    RETURN V_Total;
                 END;
END;
/
SELECT F_Metadata_Import_NEW ('TABLE','PMN_ORG_T','EMACH') FROM DUAL;

Output is
Quote:

CREATE TABLE "EMACH"."PMN_ORG_T"
( "ORG_ID" VARCHAR2(6) NOT NULL ENABLE,
"ORG_TECH_ID" NUMBER(*,0) NOT NULL ENABLE,
"ID_BY" VARCHAR2(1) NOT NULL ENABLE,
"MAIN_TAP_CD" VARCHAR2(5) NOT NULL ENABLE,
"ALT_TAP_CD" VARCHAR2(5) NOT NULL ENABLE,
"TAPE_CD" VARCHAR2(3) NOT NULL ENABLE,
"ORG_NM" VARCHAR2(100) NOT NULL ENABLE,
"CNTRY_ID" NUMBER(*,0),
"CDP" VARCHAR2(10),
"LCL_CUR" VARCHAR2(3),
"OUTDAT" VARCHAR2(1) NOT NULL ENABLE,
"ENTRY_DATE" DATE DEFAULT sysdate NOT NULL ENABLE,
"MODIFY_DATE" DATE DEFAULT sysdate NOT NULL ENABLE,
"MODIFY_USER" VARCHAR2(20) DEFAULT nvl(sys_context('audit_context','db_user'),nvl(sys_context('userenv','current_user'),'Not available')) NOT NULL ENABLE,
"REC_VERSION" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE,
"RPT_CUR" VARCHAR2(3),
"GRP_CUR" VARCHAR2(3),
"ONL_USR_ID_PRFX" VARCHAR2(4),
CONSTRAINT "C_PMN_ORG_T_C_ID_BY" CHECK (ID_BY in ('-','G','M')) ENABLE,
CONSTRAINT "C_PMN_ORG_T_C_OUTDAT" CHECK (OUTDAT in ('Y','N')) ENABLE,
CHECK (org_tech_id<1000000) ENABLE,
CONSTRAINT "C_PMN_ORG_P" PRIMARY KEY ("ORG_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "S_CFG_I" ENABLE,
CONSTRAINT "C_PMN_ORG_A01" UNIQUE ("ORG_TECH_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "S_CFG_I" ENABLE,
CONSTRAINT "C_PMN_ORG_2_CUR_DEF_GRP_F" FOREIGN KEY ("GRP_CUR")
REFERENCES "EMACH"."CUR_DEF_T" ("CUR_CD") DISABLE,
CONSTRAINT "C_PMN_ORG_2_CUR_DEF_LOC_F" FOREIGN KEY ("LCL_CUR")
REFERENCES "EMACH"."CUR_DEF_T" ("CUR_CD") DISABLE,
CONSTRAINT "C_PMN_ORG_2_CUR_DEF_RPT_F" FOREIGN KEY ("RPT_CUR")
REFERENCES "EMACH"."CUR_DEF_T" ("CUR_CD") DISABLE,
CONSTRAINT "C_PMN_ORG_2_REF_CGSC_F" FOREIGN KEY ("CNTRY_ID")
REFERENCES "EMACH"."REF_CGSC_T" ("CGS_ID") DISABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "S_CFG_D"

Could you tell me why i'm unable to filter constraints and not getting sql terminator and pretty form?
Regards,
Madhavi.
Re: Not able to apply session tranforms [message #446183 is a reply to message #446182] Sat, 06 March 2010 01:01 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
Sorry for distubing. I got correct output for above function. Solution is just keeping a varaible (Th) in place of DBMS_METADATA.SESSION_TRANSFORM because we are not trying to get DDL from database session.
--Madhavi.
Re: Not able to apply session tranforms [message #446187 is a reply to message #446183] Sat, 06 March 2010 01:14 Go to previous message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post the fixed code.

Regards
Michel
Previous Topic: single select
Next Topic: Exception in proceedure
Goto Forum:
  


Current Time: Sun Sep 25 12:57:21 CDT 2016

Total time taken to generate the page: 0.16616 seconds