Edit D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\xml\xsl\kuetable.xsl
<?xml version="1.0"?> <!-- Copyright (c) 2000, 2009, Oracle and/or its affiliates. All rights reserved. --> <!-- NAME kuetable.xsl DESCRIPTION XSLT stylesheet for Diff Document to External Table DDL NOTES Do NOT modify this file under any circumstance. Copy the file if you wish to use this stylesheet with an external XML/XSL parser MODIFIED MM/DD/YY ebatbout 02/20/08 - Creation --> <xsl:stylesheet version="1.0" xmlns:sxml="http://xmlns.oracle.com/ku" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <!-- Import required scripts --> <xsl:import href="kuexttbl.xsl"/> <xsl:import href="kuacomm.xsl"/> <xsl:import href="kustablc.xsl"/> <xsl:import href="kuatcol.xsl"/> <xsl:import href="kuatpar.xsl"/> <xsl:import href="kuacnstr.xsl"/> <xsl:import href="kuatprop.xsl"/> <xsl:import href="kustbphd.xsl"/> <!-- Top-level parameters --> <xsl:param name="PRETTY">1</xsl:param> <xsl:param name="SQLTERMINATOR">0</xsl:param> <xsl:param name="SEGMENT_ATTRIBUTES">1</xsl:param> <xsl:param name="STORAGE">1</xsl:param> <xsl:param name="TABLESPACE">1</xsl:param> <xsl:param name="CONSTRAINTS">1</xsl:param> <xsl:param name="REF_CONSTRAINTS">1</xsl:param> <xsl:param name="CONSTRAINTS_AS_ALTER">0</xsl:param> <xsl:param name="SIZE_BYTE_KEYWORD">0</xsl:param> <!-- params for Logical Standby --> <xsl:param name="INSERT">0</xsl:param> <xsl:param name="DBLINK">0</xsl:param> <xsl:param name="OPERATION_TYPE">CREATE</xsl:param> <xsl:param name="INSERTNULLS">0</xsl:param> <xsl:param name="EXT_TABLE_NAME" select="''"/> <xsl:param name="EXT_TABLE_SCHEMA" select="''"/> <xsl:param name="EXT_TABLE_CLAUSE" select="''"/> <xsl:param name="TARGET_TABLE_NAME" select="''"/> <xsl:param name="TARGET_TABLE_SCHEMA" select="''"/> <xsl:param name="SCN">0</xsl:param> <xsl:param name="PARTITION_NAME" select="''"/> <xsl:param name="SUBPARTITION_NAME" select="''"/> <xsl:param name="QUERY" select="''"/> <xsl:param name="SAMPLE" select="''"/> <xsl:param name="ENCRYPTION_PASSWORD" select="''"/> <xsl:param name="DML_ERROR_LOGGING_CLAUSE" select="''"/> <xsl:param name="USE_DML_APPEND_HINT">1</xsl:param> <xsl:param name="XMLTYPE_STREAM_FORMAT" select="''"/> <xsl:param name="PARALLEL_HINT" select="''"/> <!-- params for parse --> <xsl:param name="PRS_DDL">0</xsl:param> <xsl:param name="PRS_DELIM">\{]`</xsl:param> <xsl:param name="PRS_VERB">0</xsl:param> <xsl:param name="PRS_OBJECT_TYPE">0</xsl:param> <xsl:param name="PRS_SCHEMA">0</xsl:param> <xsl:param name="PRS_NAME">0</xsl:param> <xsl:param name="PRS_TABLESPACE">0</xsl:param> <xsl:param name="PRS_GRANTEE">0</xsl:param> <xsl:param name="PRS_GRANTOR">0</xsl:param> <xsl:param name="PRS_BASE_OBJECT_SCHEMA">0</xsl:param> <xsl:param name="PRS_BASE_OBJECT_NAME">0</xsl:param> <xsl:param name="PRS_BASE_OBJECT_TYPE">0</xsl:param> <xsl:param name="PRS_SYSTEM_GENERATED">0</xsl:param> <xsl:param name="PRS_PATCHTABLEMETADATA">0</xsl:param> <xsl:param name="PRS_LONG_POSITION">0</xsl:param> <!-- Params for column name remapping --> <xsl:param name="REMAP_COLUMN_NAMEO1" select="''"/> <xsl:param name="REMAP_COLUMN_NAMEN1" select="''"/> <xsl:param name="REMAP_COLUMN_NAMEO2" select="''"/> <xsl:param name="REMAP_COLUMN_NAMEN2" select="''"/> <xsl:param name="REMAP_COLUMN_NAMEO3" select="''"/> <xsl:param name="REMAP_COLUMN_NAMEN3" select="''"/> <xsl:param name="REMAP_COLUMN_NAMEO4" select="''"/> <xsl:param name="REMAP_COLUMN_NAMEN4" select="''"/> <xsl:param name="REMAP_COLUMN_NAMEO5" select="''"/> <xsl:param name="REMAP_COLUMN_NAMEN5" select="''"/> <xsl:param name="REMAP_COLUMN_NAMEO6" select="''"/> <xsl:param name="REMAP_COLUMN_NAMEN6" select="''"/> <xsl:param name="REMAP_COLUMN_NAMEO7" select="''"/> <xsl:param name="REMAP_COLUMN_NAMEN7" select="''"/> <xsl:param name="REMAP_COLUMN_NAMEO8" select="''"/> <xsl:param name="REMAP_COLUMN_NAMEN8" select="''"/> <xsl:param name="REMAP_COLUMN_NAMEO9" select="''"/> <xsl:param name="REMAP_COLUMN_NAMEN9" select="''"/> <xsl:param name="REMAP_COLUMN_NAMEO10" select="''"/> <xsl:param name="REMAP_COLUMN_NAMEN10" select="''"/> <!-- Templates --> <xsl:template match="sxml:TABLE"> <!-- ******************************************************************* Template: TABLE - top-level template for Diff Document to External Table ******************************************************************** --> <xsl:if test="$PRS_ROW=1"> <xsl:value-of select="$PRS_DELIM"/> <xsl:text>D</xsl:text> <xsl:value-of select="position()"/> <xsl:value-of select="$PRS_DELIM"/> </xsl:if> <xsl:choose> <xsl:when test="$OPERATION_TYPE='CREATE' or $OPERATION_TYPE='IMPORT_CONVENTIONAL' or $INSERTNULLS=1"> <xsl:call-template name="CreateExtTable"/> </xsl:when> <xsl:otherwise> <xsl:text>***ERROR: UNEXPECTED OPERATION_TYPE: </xsl:text> <xsl:value-of select="$OPERATION_TYPE"/> </xsl:otherwise> </xsl:choose> </xsl:template> <xsl:template name="CreateExtTable"> <xsl:param name="DBlinkName" select="''"/> <!-- ******************************************************************* Template: CreateExtTable - Create External Table This template generates the syntax for external table load. The output looks like this: DECLARE stmt VARCHAR2(2000); TABLE_NOT_EXIST exception; pragma EXCEPTION_INIT(TABLE_NOT_EXIST, -942); BEGIN stmt := 'DROP TABLE "ET$43627" PURGE'; EXECUTE IMMEDIATE stmt; EXCEPTION WHEN TABLE_NOT_EXIST THEN NULL; END; Note that we initially drop the ext table in case it's left around from a previous failed operation. CREATE TABLE "ET$43627" ( "C1" NUMBER, "C2" VARCHAR2(30) ) ORGANIZATION EXTERNAL <ext_table_clause> ; INSERT /*+APPEND+*/ INTO RELATIONAL("SCOTT"."TAB1" NOT XMLTYPE) ("C1", "C2") SELECT "C1", "C2" FROM "ET$43627" KU$ The 'NOT XMLTYPE' syntax is new in 11g. Normally the RELATIONAL operator returns the underlying relational columns for UDTs. NOT XMLTYPE says to make an exception for XMLTYPE columns and instead return the top-level XMLTYPE column in a self-describing binary format. This format can be imported into an XMLTYPE column having any storage format: CLOB, object- relational or binary. Parameters: DBlinkName - name of DBlink Global parameters: PRETTY - 1 = pretty-print SQLTERMINATOR - 1 = emit SQL terminator at end of statement OPERATION_TYPE: 'IMPORT' = do external table load EXT_TABLE_NAME: name of external table (defaults to ET$ + obj#) EXT_TABLE_SCHEMA: schema of external table (defaults to null) EXT_TABLE_CLAUSE: everything following ORGANIZATION EXTERNAL ******************************************************************** --> <!-- call the work routine with the appropriate node sets --> <xsl:choose> <xsl:when test="sxml:OBJECT_TABLE"> <xsl:call-template name="CreateExtTableWrk"> <xsl:with-param name="DBlinkName" select="$DBlinkName"/> <xsl:with-param name="ParentNode" select="sxml:OBJECT_TABLE/sxml:OBJECT_COLUMN_PROPERTIES"/> </xsl:call-template> </xsl:when> <xsl:when test="sxml:XMLTYPE_TABLE"> <xsl:call-template name="CreateExtTableWrk"> <xsl:with-param name="DBlinkName" select="$DBlinkName"/> <xsl:with-param name="ParentNode" select="sxml:XMLTYPE_TABLE/sxml:OBJECT_COLUMN_PROPERTIES"/> </xsl:call-template> </xsl:when> <xsl:otherwise> <xsl:call-template name="CreateExtTableWrk"> <xsl:with-param name="DBlinkName" select="$DBlinkName"/> </xsl:call-template> </xsl:otherwise> </xsl:choose> </xsl:template> <xsl:template name="CreateExtTableWrk"> <xsl:param name="DBlinkName" select="''"/> <xsl:param name="ParentNode" select="sxml:RELATIONAL_TABLE"/> <!-- ******************************************************************* Template: CreateExtTableWrk - This template does the work. The caller has chosen the sets of scalars, lobs and long. Parameters: DBlinkName - name of DBlink Scalars - the node set of scalar COL_LIST_ITEM elements Lobs - the node set of lob COL_LIST_ITEM elements Longs - the node set of long COL_LIST_ITEM elements ******************************************************************** --> <!-- Create variables for sets of column: passed to kucolumn.xsl --> <!-- (1) Safe scalar types: not lobs, not columns that are part of an XMLType stored relationally. --> <xsl:variable name="Scalars" select=" $ParentNode/sxml:COL_LIST/sxml:COL_LIST_ITEM[not(@src='1') and not(@src='2') and sxml:SEGCOL_NUM!=0 and sxml:DATATYPE!='BLOB' and sxml:DATATYPE!='CLOB' and sxml:DATATYPE!='NCLOB' and sxml:DATATYPE!='VARRAY' and sxml:DATATYPE!='XMLTYPE' and sxml:DATATYPE!='OPAQUE' and sxml:DATATYPE!='LONG' and sxml:DATATYPE!='LONG_RAW']"/> <xsl:variable name="DroppedScalars" select=" $ParentNode/sxml:COL_LIST/sxml:COL_LIST_ITEM[(@src='1') and sxml:SEGCOL_NUM!=0 and ((sxml:DATATYPE/@value1 and sxml:DATATYPE/@value1 !='BLOB' and sxml:DATATYPE/@value1 !='CLOB' and sxml:DATATYPE/@value1 !='NCLOB' and sxml:DATATYPE/@value1 !='VARRAY' and sxml:DATATYPE/@value1 !='XMLTYPE' and sxml:DATATYPE/@value1 !='OPAQUE' and sxml:DATATYPE/@value1 !='LONG' and sxml:DATATYPE/@value1 !='LONG_RAW') or ( not(sxml:DATATYPE/@value1) and sxml:DATATYPE !='BLOB' and sxml:DATATYPE !='CLOB' and sxml:DATATYPE !='NCLOB' and sxml:DATATYPE !='VARRAY' and sxml:DATATYPE !='XMLTYPE' and sxml:DATATYPE !='OPAQUE' and sxml:DATATYPE !='LONG' and sxml:DATATYPE !='LONG_RAW'))]"/> <xsl:variable name="Lobs" select= "$ParentNode/sxml:COL_LIST/sxml:COL_LIST_ITEM[not(@src='1') and not(@src='2') and ((sxml:SEGCOL_NUM!=0 and (sxml:DATATYPE ='BLOB' or sxml:DATATYPE ='CLOB' or sxml:DATATYPE ='NCLOB' or sxml:DATATYPE ='VARRAY' or sxml:DATATYPE = 'OPAQUE' )) or (sxml:SEGCOL_NUM=0 and not(sxml:VIRTUAL) and sxml:DATATYPE='XMLTYPE'))]"/> <xsl:variable name="DroppedLobs" select= "$ParentNode/sxml:COL_LIST/sxml:COL_LIST_ITEM[(@src='1') and ((sxml:DATATYPE/@value1 and sxml:SEGCOL_NUM!=0 and (sxml:DATATYPE/@value1 ='BLOB' or sxml:DATATYPE/@value1 ='CLOB' or sxml:DATATYPE/@value1 ='NCLOB' or sxml:DATATYPE/@value1 ='VARRAY' or sxml:DATATYPE/@value1 ='OPAQUE') or (((sxml:SEGCOL_NUM/@value1 and sxml:SEGCOL_NUM/@value1=0) or sxml:SEGCOL_NUM=0) and not(sxml:VIRTUAL) and sxml:DATATYPE/@value1='XMLTYPE')) or (not(sxml:DATATYPE/@value1) and sxml:SEGCOL_NUM!=0 and (sxml:DATATYPE ='BLOB' or sxml:DATATYPE ='CLOB' or sxml:DATATYPE ='NCLOB' or sxml:DATATYPE ='VARRAY' or sxml:DATATYPE = 'OPAQUE' ) or (sxml:SEGCOL_NUM=0 and not(sxml:VIRTUAL) and sxml:DATATYPE='XMLTYPE')))]"/> <!-- (3) Long and long raw --> <xsl:variable name="Longs" select= "$ParentNode/sxml:COL_LIST/sxml:COL_LIST_ITEM[not(@src='1') and not(@src='2') and sxml:SEGCOL_NUM!=0 and (sxml:DATATYPE='LONG' or sxml:DATATYPE='LONG_RAW')]"/> <xsl:variable name="DroppedLongs" select= "$ParentNode/sxml:COL_LIST/sxml:COL_LIST_ITEM[(@src='1') and sxml:SEGCOL_NUM!=0 and (sxml:DATATYPE='LONG' or sxml:DATATYPE='LONG_RAW')]"/> <xsl:variable name="Nestedtab_columns" select=" $ParentNode/sxml:COL_LIST/sxml:COL_LIST_ITEM[not(@src='1') and not(@src='2') and sxml:DATATYPE='NESTED_TABLE']"/> <xsl:choose> <xsl:when test="$INSERTNULLS=1"> <xsl:call-template name="BuildInsertStmt"> <xsl:with-param name="ScalarCols" select="$Scalars"/> <xsl:with-param name="LobCols" select="$Lobs"/> <xsl:with-param name="LongCols" select="$Longs"/> <xsl:with-param name="DroppedScalars" select="$DroppedScalars"/> <xsl:with-param name="DroppedLobs" select="$DroppedLobs"/> <xsl:with-param name="DroppedLongs" select="$DroppedLongs"/> </xsl:call-template> </xsl:when> <xsl:otherwise> <!-- Drop the external table (in case it exists already) --> <xsl:call-template name="DropExtTbl"/> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <!-- Create the external table --> <xsl:if test="$PRS_NAME=1"> <xsl:value-of select="$PRS_DELIM"/> <xsl:text>ANAME</xsl:text> <xsl:value-of select="$PRS_DELIM"/> <xsl:text>B</xsl:text> <xsl:call-template name="ExtTblSimpleName"/> </xsl:if> <xsl:call-template name="DoParse"> <xsl:with-param name="Verb">CREATE</xsl:with-param> <xsl:with-param name="ObjectType">TABLE</xsl:with-param> <xsl:with-param name="SchemaNode" select="SCHEMA_OBJ/OWNER_NAME"/> </xsl:call-template> <xsl:text>CREATE TABLE </xsl:text> <xsl:call-template name="ExtTblName"/> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <xsl:text>(</xsl:text> <xsl:call-template name="DoExtTblCOL_LIST"> <xsl:with-param name="Scalars" select="$Scalars"/> <xsl:with-param name="Lobs" select="$Lobs"/> <xsl:with-param name="Longs" select="$Longs"/> <xsl:with-param name="ADT_type">TABLE</xsl:with-param> <xsl:with-param name="Source1">0</xsl:with-param> <xsl:with-param name="ExtTblEncrypt">1</xsl:with-param> </xsl:call-template> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <xsl:text>) ORGANIZATION EXTERNAL </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <xsl:call-template name="SubstQuot"> <xsl:with-param name="String" select="$EXT_TABLE_CLAUSE"/> </xsl:call-template> <xsl:if test="$SQLTERMINATOR=1"> <xsl:text>;</xsl:text> </xsl:if> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <xsl:if test="$PRS_NAME=1"> <xsl:value-of select="$PRS_DELIM"/> <xsl:text>ANAME</xsl:text> <xsl:value-of select="$PRS_DELIM"/> <xsl:text>B</xsl:text> <xsl:call-template name="ExtTblSimpleName"/> </xsl:if> <xsl:choose> <xsl:when test="$OPERATION_TYPE='CREATE'"> <xsl:call-template name="DoParse"> <xsl:with-param name="Verb">INSERT</xsl:with-param> <xsl:with-param name="ObjectType">TABLE</xsl:with-param> <xsl:with-param name="SchemaNode" select="$TARGET_TABLE_SCHEMA"/> </xsl:call-template> <xsl:text>INSERT </xsl:text> <xsl:if test="$USE_DML_APPEND_HINT!= 0 or string-length($PARALLEL_HINT)!=0"> <xsl:text>/*+ </xsl:text> <xsl:if test="$USE_DML_APPEND_HINT!= 0">APPEND </xsl:if> <xsl:if test="string-length($PARALLEL_HINT)!=0"> <xsl:text>PARALLEL("</xsl:text> <xsl:value-of select="$TARGET_TABLE_NAME"/> <xsl:text>",</xsl:text> <xsl:value-of select="$PARALLEL_HINT"/> <xsl:text>)</xsl:text> </xsl:if> <xsl:text>+*/ </xsl:text> </xsl:if> <xsl:if test="count($Nestedtab_columns)>0">/*+NESTED_TABLE_SET_SETID+*/ </xsl:if> <xsl:text>INTO RELATIONAL("</xsl:text> <xsl:value-of select="$TARGET_TABLE_SCHEMA"/> <xsl:text>"."</xsl:text> <xsl:value-of select="$TARGET_TABLE_NAME"/> <xsl:text>" NOT XMLTYPE</xsl:text> <xsl:text>) </xsl:text> <xsl:call-template name="ExtTblPartition"/> <xsl:text>(</xsl:text> <xsl:call-template name="DoExtTblCOL_LIST"> <xsl:with-param name="Scalars" select="$Scalars"/> <xsl:with-param name="Lobs" select="$Lobs"/> <xsl:with-param name="Longs" select="$Longs"/> <xsl:with-param name="ADT_type">INDEX</xsl:with-param> <xsl:with-param name="DoLF">0</xsl:with-param> </xsl:call-template> <xsl:text>) </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <xsl:call-template name="ExtTblSelect"> <xsl:with-param name="Scalars" select="$Scalars"/> <xsl:with-param name="Lobs" select="$Lobs"/> <xsl:with-param name="Longs" select="$Longs"/> <xsl:with-param name="ExtTblOps">1</xsl:with-param> </xsl:call-template> <xsl:text>"</xsl:text> <xsl:value-of select="$EXT_TABLE_NAME"/> <xsl:text>" </xsl:text> <xsl:if test="string-length($SAMPLE)!= 0 and $SAMPLE!=100"> <xsl:text>SAMPLE (</xsl:text> <xsl:value-of select="$SAMPLE"/> <xsl:text>) </xsl:text> </xsl:if> <xsl:text>KU$ </xsl:text> <xsl:value-of select="$QUERY"/> <xsl:if test="string-length($DML_ERROR_LOGGING_CLAUSE)!= 0"> <xsl:text> </xsl:text> <xsl:call-template name="SubstQuot"> <xsl:with-param name="String" select="$DML_ERROR_LOGGING_CLAUSE"/> </xsl:call-template> <xsl:text> </xsl:text> </xsl:if> <xsl:if test="$SQLTERMINATOR=1"> <xsl:text>;</xsl:text> </xsl:if> </xsl:when> <xsl:when test="$OPERATION_TYPE='IMPORT_CONVENTIONAL'"> <xsl:if test="$SQLTERMINATOR=1"> <xsl:text>;</xsl:text> </xsl:if> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <!-- return the SELECT followed by the INSERT as 2 separate statements --> <!-- first the parse items for the SELECT --> <xsl:if test="$PRS_NAME=1"> <xsl:value-of select="$PRS_DELIM"/> <xsl:text>ANAME</xsl:text> <xsl:value-of select="$PRS_DELIM"/> <xsl:text>B</xsl:text> <xsl:call-template name="ExtTblSimpleName"/> </xsl:if> <xsl:call-template name="DoParse"> <xsl:with-param name="Verb">SELECT</xsl:with-param> <xsl:with-param name="ObjectType">TABLE</xsl:with-param> <xsl:with-param name="SchemaNode" select="SCHEMA_OBJ/OWNER_NAME"/> </xsl:call-template> <!-- and the SELECT --> <xsl:call-template name="ExtTblSelect"> <xsl:with-param name="Scalars" select="$Scalars"/> <xsl:with-param name="Lobs" select="$Lobs"/> <xsl:with-param name="Longs" select="$Longs"/> <xsl:with-param name="ExtTblOps">0</xsl:with-param> </xsl:call-template> <xsl:call-template name="ExtTblName"/> <xsl:if test="string-length($SAMPLE)!= 0 and $SAMPLE!=100"> <xsl:text>SAMPLE (</xsl:text> <xsl:value-of select="$SAMPLE"/> <xsl:text>) </xsl:text> </xsl:if> <xsl:text>KU$ </xsl:text> <xsl:value-of select="$QUERY"/> <xsl:if test="$SQLTERMINATOR=1"> <xsl:text>;</xsl:text> </xsl:if> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <!-- now the INSERT --> <xsl:if test="$PRS_LONG_POSITION=1"> <xsl:value-of select="$PRS_DELIM"/> <xsl:text>ALONG_POSITION</xsl:text> <xsl:value-of select="$PRS_DELIM"/> <xsl:text>B</xsl:text> <xsl:call-template name="DoParseLongPosition"> <xsl:with-param name="Scalars" select="$Scalars"/> <xsl:with-param name="Lobs" select="$Lobs"/> <xsl:with-param name="Longs" select="$Longs"/> </xsl:call-template> </xsl:if> <xsl:if test="$PRS_NAME=1"> <xsl:value-of select="$PRS_DELIM"/> <xsl:text>ANAME</xsl:text> <xsl:value-of select="$PRS_DELIM"/> <xsl:text>B</xsl:text> <xsl:call-template name="ExtTblSimpleName"/> </xsl:if> <xsl:call-template name="DoParse"> <xsl:with-param name="Verb">INSERT</xsl:with-param> <xsl:with-param name="ObjectType">TABLE</xsl:with-param> <xsl:with-param name="SchemaNode" select="$TARGET_TABLE_SCHEMA"/> </xsl:call-template> <xsl:text>INSERT </xsl:text> <xsl:if test="(PROPERTY mod 8)>=4">/*+NESTED_TABLE_SET_SETID+*/ </xsl:if> <xsl:text>INTO RELATIONAL("</xsl:text> <xsl:value-of select="$TARGET_TABLE_SCHEMA"/> <xsl:text>"."</xsl:text> <xsl:value-of select="$TARGET_TABLE_NAME"/> <xsl:text>" NOT XMLTYPE</xsl:text> <xsl:text>) </xsl:text> <xsl:call-template name="ExtTblPartition"/> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <xsl:text>(</xsl:text> <xsl:call-template name="DoExtTblCOL_LIST"> <xsl:with-param name="Scalars" select="$Scalars"/> <xsl:with-param name="Lobs" select="$Lobs"/> <xsl:with-param name="Longs" select="$Longs"/> <xsl:with-param name="ADT_type">INDEX</xsl:with-param> <xsl:with-param name="DoLF">0</xsl:with-param> </xsl:call-template> <xsl:text>) </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <xsl:text>VALUES (</xsl:text> <xsl:call-template name="DoExtTblCOL_LIST"> <xsl:with-param name="Scalars" select="$Scalars"/> <xsl:with-param name="Lobs" select="$Lobs"/> <xsl:with-param name="Longs" select="$Longs"/> <xsl:with-param name="ADT_type">INDEX</xsl:with-param> <xsl:with-param name="DoLF">0</xsl:with-param> <xsl:with-param name="ExtTbl">5</xsl:with-param> <xsl:with-param name="ExtTblOps">1</xsl:with-param> </xsl:call-template> <xsl:text>) </xsl:text> <xsl:if test="$SQLTERMINATOR=1"> <xsl:text>;</xsl:text> </xsl:if> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> </xsl:when> </xsl:choose> <!-- Drop the external table --> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <xsl:call-template name="DropExtTbl"/> </xsl:otherwise> </xsl:choose> </xsl:template> <xsl:template name="DropExtTbl"> <!-- ******************************************************************* Template: DropExtTbl - Emit DROP TABLE for the external table. (For robustness we generate an exception handler in case the table doesn't exist.) ******************************************************************** --> <xsl:if test="$PRS_NAME=1"> <xsl:value-of select="$PRS_DELIM"/> <xsl:text>ANAME</xsl:text> <xsl:value-of select="$PRS_DELIM"/> <xsl:text>B</xsl:text> <xsl:call-template name="ExtTblSimpleName"/> </xsl:if> <xsl:call-template name="DoParse"> <xsl:with-param name="Verb">DROP</xsl:with-param> <xsl:with-param name="ObjectType">TABLE</xsl:with-param> <xsl:with-param name="SchemaNode" select="SCHEMA"/> </xsl:call-template> <xsl:text>DECLARE </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <xsl:text>stmt VARCHAR2(2000); </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <xsl:text>TABLE_NOT_EXIST exception; </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <xsl:text>pragma EXCEPTION_INIT(TABLE_NOT_EXIST, -942); </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <xsl:text>BEGIN </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <xsl:text> stmt := 'DROP TABLE </xsl:text> <xsl:call-template name="ExtTblName"/> <xsl:text>PURGE'; </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <xsl:text> EXECUTE IMMEDIATE stmt; </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <xsl:text> EXCEPTION </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <xsl:text> WHEN TABLE_NOT_EXIST THEN NULL; </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <xsl:text>END; </xsl:text> <xsl:if test="$SQLTERMINATOR=1"> <xsl:text> /</xsl:text> </xsl:if> </xsl:template> <xsl:template name="ExtTblSelect"> <xsl:param name="Scalars" select="''"/> <xsl:param name="Lobs" select="''"/> <xsl:param name="Longs" select="''"/> <xsl:param name="ExtTblOps">0</xsl:param> <!-- ******************************************************************* Template: ExtTblSelect - Emit SELECT col-list FROM Parameters: Scalars - the node set of scalar COL_LIST_ITEM elements Lobs - the node set of lob COL_LIST_ITEM elements Longs - the node set of long COL_LIST_ITEM elements ******************************************************************** --> <xsl:text>SELECT </xsl:text> <xsl:call-template name="DoExtTblCOL_LIST"> <xsl:with-param name="Scalars" select="$Scalars"/> <xsl:with-param name="Lobs" select="$Lobs"/> <xsl:with-param name="Longs" select="$Longs"/> <xsl:with-param name="ADT_type">INDEX</xsl:with-param> <xsl:with-param name="ExtTblOps" select="$ExtTblOps"/> <xsl:with-param name="DoLF">0</xsl:with-param> <xsl:with-param name="DoREMAP">1</xsl:with-param> </xsl:call-template> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <xsl:text> FROM </xsl:text> </xsl:template> <xsl:template name="DoExtTblCOL_LIST"> <xsl:param name="Scalars" select="''"/> <xsl:param name="Lobs" select="''"/> <xsl:param name="Longs" select="''"/> <xsl:param name="ADT_type">TABLE</xsl:param> <xsl:param name="DoLF">1</xsl:param> <xsl:param name="DoHidden">0</xsl:param> <xsl:param name="DoREMAP">0</xsl:param> <xsl:param name="Prefix" select="''"/> <xsl:param name="Source1">0</xsl:param> <xsl:param name="ExtTbl">0</xsl:param> <xsl:param name="ExtTblOps">0</xsl:param> <xsl:param name="ExtTblEncrypt">0</xsl:param> <!-- ******************************************************************* Template: DoExtTblCOL_LIST - process a COL_LIST and its members for external table import Parameters: Scalars - the node set of scalar COL_LIST_ITEM elements Lobs - the node set of lob COL_LIST_ITEM elements Longs - the node set of long COL_LIST_ITEM elements ADT_type - Object type 'TABLE' - table objects (full column attributes) 'INDEX' - index (and other) objects (col name only) DoLF - 1 = emit a line feed between columns (if PRETTY=1) DoHidden - 0 = exclude hidden columns non-0 = include hidden columns DoREMAP - 0 = don't remap column names non-0 = okay to remap column names Prefix - value of a prefix (e.g., "C1_REC.") to append to the column name ExtTbl - 5 = import_conventional, use bind variables ExtTblOps - do/don't use the special sql operators for external tables e.g., SYS_ET_BFILE_TO_RAW. 0 = don't use non-0 = do use ExtTblEncrypt - do/don't use the ENCRYPT keyword for encrypted columns when ExtTbl !=0 0 = don't use non-0 = do use Notes: Columns in segcol order EXCEPT columns that form the expansion of an XMLType stored relationally are omitted LOBs (or anything that can be stored as a lob such as varrays or opaques) are moved to the end but remain in segcol order Schema-based and binary XMLType columns are exported in self- describing binary as a BLOB, but they sort with all the other LOBs. LONGs come last no matter what in addition: exclude unused columns - prop bit 0x008000 type_num 112 = CLOB 113 = BLOB 123 = VARRAY 58 = OPAQUE 8 = LONG 24 = LONG RAW base_col_type 1 = UDT 2 = XMLType stored OR or CSX 3 = XMLType stored as CLOB 0 = other ******************************************************************** --> <!-- First emit safe scalar types: not lobs, not columns that are part of an XMLType stored relationally. --> <xsl:for-each select="$Scalars"> <xsl:sort select="sxml:SEGCOL_NUM" data-type="number"/> <xsl:choose> <xsl:when test="($Source1=1) and sxml:DATATYPE/@value1"> <xsl:call-template name="DoCOL_LIST_ITEM"> <xsl:with-param name="ADT_type" select="$ADT_type"/> <xsl:with-param name="DATATYPE" select="sxml:DATATYPE/@value1"/> <xsl:with-param name="DoLF" select="$DoLF"/> <xsl:with-param name="DoREMAP" select="$DoREMAP"/> <xsl:with-param name="Prefix" select="$Prefix"/> <xsl:with-param name="Source1" select="$Source1"/> <xsl:with-param name="ExtTbl" select="$ExtTbl"/> <xsl:with-param name="ExtTblOps" select="$ExtTblOps"/> <xsl:with-param name="ExtTblEncrypt" select="$ExtTblEncrypt"/> <xsl:with-param name="Pos" select="position()"/> </xsl:call-template> </xsl:when> <xsl:otherwise> <xsl:call-template name="DoCOL_LIST_ITEM"> <xsl:with-param name="ADT_type" select="$ADT_type"/> <xsl:with-param name="DATATYPE" select="sxml:DATATYPE"/> <xsl:with-param name="DoLF" select="$DoLF"/> <xsl:with-param name="DoREMAP" select="$DoREMAP"/> <xsl:with-param name="Prefix" select="$Prefix"/> <xsl:with-param name="Source1" select="$Source1"/> <xsl:with-param name="ExtTbl" select="$ExtTbl"/> <xsl:with-param name="ExtTblOps" select="$ExtTblOps"/> <xsl:with-param name="ExtTblEncrypt" select="$ExtTblEncrypt"/> <xsl:with-param name="Pos" select="position()"/> </xsl:call-template> </xsl:otherwise> </xsl:choose> </xsl:for-each> <!-- now a comma (maybe): if we did any non-lob cols... and now we have some lob cols... --> <xsl:if test="count($Scalars)>0 and count($Lobs)>0"> <!-- ... then insert a comma between the last non-lob col and the first lob col --> <xsl:call-template name="EmitCommaLF"> <xsl:with-param name="DoLF" select="$DoLF"/> </xsl:call-template> </xsl:if> <!-- LOBs (or anything that can be stored as a lob such as varrays or opaques, including schema-based and binary XMLType columns are exported in self-describing binary as a BLOB). We must sort by intcol_num instead of segcol_num because the XMLType columns always have segcol_num=0 and as a result sorting by segcol_num would produce the columns in the wrong order --> <xsl:for-each select="$Lobs"> <xsl:sort select="sxml:INTCOL_NUM" data-type="number"/> <xsl:choose> <xsl:when test="($Source1=1) and sxml:DATATYPE/@value1"> <xsl:call-template name="DoCOL_LIST_ITEM"> <xsl:with-param name="ADT_type" select="$ADT_type"/> <xsl:with-param name="DATATYPE" select="sxml:DATATYPE/@value1"/> <xsl:with-param name="DoLF" select="$DoLF"/> <xsl:with-param name="DoREMAP" select="$DoREMAP"/> <xsl:with-param name="Prefix" select="$Prefix"/> <xsl:with-param name="Source1" select="$Source1"/> <xsl:with-param name="ExtTbl" select="$ExtTbl"/> <xsl:with-param name="ExtTblOps" select="$ExtTblOps"/> <xsl:with-param name="ExtTblEncrypt" select="$ExtTblEncrypt"/> <xsl:with-param name="Pos" select="count($Scalars)+position()"/> </xsl:call-template> </xsl:when> <xsl:otherwise> <xsl:call-template name="DoCOL_LIST_ITEM"> <xsl:with-param name="ADT_type" select="$ADT_type"/> <xsl:with-param name="DATATYPE" select="sxml:DATATYPE"/> <xsl:with-param name="DoLF" select="$DoLF"/> <xsl:with-param name="DoREMAP" select="$DoREMAP"/> <xsl:with-param name="Prefix" select="$Prefix"/> <xsl:with-param name="Source1" select="$Source1"/> <xsl:with-param name="ExtTbl" select="$ExtTbl"/> <xsl:with-param name="ExtTblOps" select="$ExtTblOps"/> <xsl:with-param name="ExtTblEncrypt" select="$ExtTblEncrypt"/> <xsl:with-param name="Pos" select="count($Scalars)+position()"/> </xsl:call-template> </xsl:otherwise> </xsl:choose> </xsl:for-each> <!-- now a comma (maybe) --> <xsl:if test="(count($Scalars)+count($Lobs))>0 and count($Longs)>0"> <xsl:call-template name="EmitCommaLF"> <xsl:with-param name="DoLF" select="$DoLF"/> </xsl:call-template> </xsl:if> <!-- now LONGs (of which there can be at most 1 but, hey, whatever) --> <xsl:for-each select="$Longs"> <xsl:sort select="sxml:INTCOL_NUM" data-type="number"/> <xsl:call-template name="DoCOL_LIST_ITEM"> <xsl:with-param name="ADT_type" select="$ADT_type"/> <xsl:with-param name="DATATYPE" select="sxml:DATATYPE"/> <xsl:with-param name="DoLF" select="$DoLF"/> <xsl:with-param name="DoREMAP" select="$DoREMAP"/> <xsl:with-param name="Prefix" select="$Prefix"/> <xsl:with-param name="Source1" select="$Source1"/> <xsl:with-param name="ExtTbl" select="$ExtTbl"/> <xsl:with-param name="ExtTblOps" select="$ExtTblOps"/> <xsl:with-param name="ExtTblEncrypt" select="$ExtTblEncrypt"/> <xsl:with-param name="Pos" select="count($Scalars)+count($Lobs)+position()"/> </xsl:call-template> </xsl:for-each> </xsl:template> <xsl:template name="DoCOL_LIST_ITEM"> <xsl:param name="ADT_type">TABLE</xsl:param> <xsl:param name="DATATYPE" select="''"/> <xsl:param name="DoLF">1</xsl:param> <xsl:param name="DoREMAP">0</xsl:param> <xsl:param name="Network">0</xsl:param> <xsl:param name="Prefix" select="''"/> <xsl:param name="Source1">0</xsl:param> <xsl:param name="ExtTbl">0</xsl:param> <xsl:param name="ExtTblOps">0</xsl:param> <xsl:param name="ExtTblEncrypt">0</xsl:param> <xsl:param name="Pos">0</xsl:param> <!-- ******************************************************************* Template: DoCOL_LIST_ITEM - process a COL_LIST_ITEM Parameters: ADT_type - Object type 'TABLE' - table objects (full column attributes) 'INDEX' - index (and other) objects (col name only) DoLF - 1 = emit a line feed between columns (if PRETTY=1) DoREMAP - 0 = don't remap column names non-0 = okay to remap column names Prefix - value of a prefix (e.g., "C1_REC.") to append to the column name ExtTbl - for ADT_type='TABLE', what kind of operation 0 = normal CREATE table 5 = IMPORT_CONVENTIONAL ExtTblOps - do/don't use the special sql operators for external tables e.g., SYS_ET_BFILE_TO_RAW. 0 = don't use non-0 = do use ExtTblEncrypt - do/don't use the ENCRYPT keyword for encrypted columns when ExtTbl !=0 0 = don't use non-0 = do use ******************************************************************** --> <xsl:if test="string-length($Prefix)!=0"> <xsl:value-of select="$Prefix"/> </xsl:if> <!-- Type num Datatype Comment ======== ======== ======= 114 BFILE 8 LONG Import is direct path only 24 LONG RAW Import is direct path only 58 OPAQUE 123 VARRAY --> <xsl:if test="($ExtTblOps!=0 and ($DATATYPE='BFILE' or $DATATYPE='XMLTYPE' or $DATATYPE='OPAQUE' or $DATATYPE='VARRAY'))"> <xsl:choose> <xsl:when test="$DATATYPE='BFILE'">SYS_ET_RAW_TO_BFILE(</xsl:when> <xsl:when test="$DATATYPE='XMLTYPE'"> <xsl:choose> <xsl:when test="$XMLTYPE_STREAM_FORMAT='BLOB'">SYS_SC_2_XMLT(</xsl:when> <xsl:otherwise>SYS.XMLTYPE.CREATEXML(</xsl:otherwise> </xsl:choose> </xsl:when> <xsl:when test="$DATATYPE='VARRAY'">SYS_ET_BLOB_TO_IMAGE(</xsl:when> <!-- not XMLType --> <xsl:otherwise>SYS_OP_ITR(SYS_ET_BLOB_TO_IMAGE(</xsl:otherwise> </xsl:choose> </xsl:if> <xsl:choose> <xsl:when test="$ExtTbl=5"> <!-- build bind variable from position --> <xsl:text>:</xsl:text> <xsl:value-of select="$Pos"/> </xsl:when> <xsl:otherwise> <xsl:choose> <xsl:when test="sxml:INTERNAL_NAME"> <xsl:value-of select="sxml:INTERNAL_NAME"/> </xsl:when> <xsl:otherwise> <xsl:text>"</xsl:text> <xsl:value-of select="sxml:NAME"/> <xsl:text>"</xsl:text> </xsl:otherwise> </xsl:choose> </xsl:otherwise> </xsl:choose> <xsl:if test="($ExtTblOps!=0 and $ADT_type!='TABLE' and ($DATATYPE='BFILE' or $DATATYPE='XMLTYPE' or $DATATYPE='OPAQUE' or $DATATYPE='VARRAY'))"> <xsl:choose> <xsl:when test="$DATATYPE='BFILE'">)</xsl:when> <xsl:when test="$DATATYPE='LONG' or sxml:DATATYPE='LONG_RAW'"> <xsl:text>)</xsl:text> </xsl:when> <xsl:when test="$DATATYPE='XMLTYPE'">)</xsl:when> <xsl:when test="$DATATYPE='OPAQUE'"> <xsl:text>,'</xsl:text> <xsl:value-of select="sxml:TYPE_PROPERTIES/sxml:NAME"/> <xsl:text>','</xsl:text> <xsl:value-of select="sxml:TYPE_PROPERTIES/sxml:SCHEMA"/> <xsl:text>'),0)</xsl:text> </xsl:when> <xsl:when test="$DATATYPE='VARRAY'"> <xsl:text>,'</xsl:text> <xsl:value-of select="sxml:TYPE_PROPERTIES/sxml:NAME"/> <xsl:text>','</xsl:text> <xsl:value-of select="sxml:TYPE_PROPERTIES/sxml:SCHEMA"/> <xsl:text>')</xsl:text> </xsl:when> </xsl:choose> </xsl:if> <xsl:if test="$ADT_type='TABLE'"> <!-- emit datatype --> <xsl:choose> <xsl:when test="$DATATYPE='UDT'"> RAW(2)</xsl:when> <xsl:when test="$DATATYPE='NESTED_TABLE'"> RAW(16)</xsl:when> <xsl:when test="$DATATYPE='VARRAY'"> BLOB</xsl:when> <xsl:when test="$DATATYPE='OPAQUE'"> BLOB</xsl:when> <xsl:when test="$DATATYPE='LONG_RAW'"> BLOB</xsl:when> <xsl:when test="$DATATYPE='LONG'"> CLOB</xsl:when> <xsl:when test="$DATATYPE='XMLTYPE'"> <xsl:choose> <xsl:when test="$XMLTYPE_STREAM_FORMAT='BLOB'"> BLOB</xsl:when> <xsl:otherwise> CLOB</xsl:otherwise> </xsl:choose> </xsl:when> <xsl:when test="$DATATYPE='BFILE'"> RAW(530)</xsl:when> <xsl:when test="$DATATYPE='REF'"> RAW(36)</xsl:when> <xsl:otherwise> <xsl:text> </xsl:text> <xsl:choose> <xsl:when test="$Source1=0"> <xsl:call-template name="Datatype"/> </xsl:when> <xsl:otherwise> <xsl:call-template name="SourceDatatype"> <xsl:with-param name='Input_datatype' select="$DATATYPE"/> </xsl:call-template> </xsl:otherwise> </xsl:choose> </xsl:otherwise> </xsl:choose> <xsl:if test="sxml:SORT"> <xsl:text> SORT</xsl:text> </xsl:if> </xsl:if> <!-- Encryption --> <xsl:if test="$ExtTblEncrypt!=0 and sxml:ENCRYPT and not(sxml:ENCRYPT[@src='1'])"> <xsl:if test="string-length($ENCRYPTION_PASSWORD)>0"> <xsl:text> ENCRYPT USING 'AES128' IDENTIFIED BY "</xsl:text> <xsl:value-of select="$ENCRYPTION_PASSWORD"/> <xsl:text>" </xsl:text> </xsl:if> </xsl:if> <!-- Put out a comma / LF if not the last col --> <xsl:if test="not(position()=last())"> <xsl:text>,</xsl:text> </xsl:if> </xsl:template> <xsl:template name="SourceDatatype"> <xsl:param name="Input_datatype" select='sxml:DATATYPE'/> <!-- ******************************************************************* Template: SourceDatatype - emit column datatype Current node: COL_LIST/COL_LIST_ITEM ******************************************************************** --> <xsl:choose> <xsl:when test="$Input_datatype='CHAR' or $Input_datatype='VARCHAR2'"> <xsl:value-of select="$Input_datatype"/> <xsl:if test="sxml:LENGTH"> <xsl:text>(</xsl:text> <xsl:choose> <xsl:when test="sxml:LENGTH[@value1]"> <xsl:value-of select="sxml:LENGTH/@value1"/> </xsl:when> <xsl:otherwise> <xsl:value-of select="sxml:LENGTH"/> </xsl:otherwise> </xsl:choose> <xsl:if test="not(sxml:CHAR_SEMANTICS[@src='2'])"> <xsl:apply-templates select="sxml:CHAR_SEMANTICS"/> </xsl:if> <xsl:text>)</xsl:text> </xsl:if> </xsl:when> <xsl:when test="$Input_datatype='NCHAR' or $Input_datatype='NVARCHAR2' or $Input_datatype='RAW' or $Input_datatype='UROWID'"> <xsl:value-of select="$Input_datatype"/> <xsl:if test="sxml:LENGTH"> <xsl:text>(</xsl:text> <xsl:choose> <xsl:when test="sxml:LENGTH[@value1]"> <xsl:value-of select="sxml:LENGTH/@value1"/> </xsl:when> <xsl:otherwise> <xsl:value-of select="sxml:LENGTH"/> </xsl:otherwise> </xsl:choose> <xsl:text>)</xsl:text> </xsl:if> </xsl:when> <xsl:when test="$Input_datatype='NUMBER' or $Input_datatype='FLOAT'"> <xsl:value-of select="$Input_datatype"/> <xsl:if test="((sxml:PRECISION or sxml:SCALE) and ((not(sxml:SCALE/@src='2') or not(sxml:PRECISION/@src='2'))))"> <xsl:text>(</xsl:text> <xsl:choose> <xsl:when test="sxml:PRECISION"> <xsl:choose> <xsl:when test="sxml:PRECISION[@value1]"> <xsl:value-of select="sxml:PRECISION/@value1"/> </xsl:when> <xsl:otherwise> <xsl:value-of select="sxml:PRECISION"/> </xsl:otherwise> </xsl:choose> </xsl:when> <xsl:otherwise>*</xsl:otherwise> </xsl:choose> <xsl:if test="sxml:SCALE and $Input_datatype='NUMBER'"> <xsl:text>,</xsl:text> <xsl:choose> <xsl:when test="sxml:SCALE[@value1]"> <xsl:value-of select="sxml:SCALE/@value1"/> </xsl:when> <xsl:otherwise> <xsl:value-of select="sxml:SCALE"/> </xsl:otherwise> </xsl:choose> </xsl:if> <xsl:text>)</xsl:text> </xsl:if> </xsl:when> <xsl:when test="$Input_datatype='TIME' or $Input_datatype='TIME_WITH_TIMEZONE' or $Input_datatype='TIMESTAMP'"> <xsl:value-of select="$Input_datatype"/> <xsl:if test="sxml:SCALE"> <xsl:text>(</xsl:text> <xsl:choose> <xsl:when test="sxml:SCALE[@value1]"> <xsl:value-of select="sxml:SCALE/@value1"/> </xsl:when> <xsl:otherwise> <xsl:value-of select="sxml:SCALE"/> </xsl:otherwise> </xsl:choose> <xsl:text>)</xsl:text> </xsl:if> </xsl:when> <xsl:when test="$Input_datatype='TIMESTAMP_WITH_TIMEZONE'"> <xsl:text>TIMESTAMP </xsl:text> <xsl:if test="sxml:SCALE"> <xsl:text>(</xsl:text> <xsl:choose> <xsl:when test="sxml:SCALE[@value1]"> <xsl:value-of select="sxml:SCALE/@value1"/> </xsl:when> <xsl:otherwise> <xsl:value-of select="sxml:SCALE"/> </xsl:otherwise> </xsl:choose> <xsl:text>) </xsl:text> </xsl:if> <xsl:text>WITH TIME ZONE</xsl:text> </xsl:when> <xsl:when test="$Input_datatype='INTERVAL_YEAR_TO_MONTH'"> <xsl:text>INTERVAL YEAR </xsl:text> <xsl:if test="sxml:PRECISION"> <xsl:text>(</xsl:text> <xsl:choose> <xsl:when test="sxml:PRECISION[@value1]"> <xsl:value-of select="sxml:PRECISION/@value1"/> </xsl:when> <xsl:otherwise> <xsl:value-of select="sxml:PRECISION"/> </xsl:otherwise> </xsl:choose> <xsl:text>) </xsl:text> </xsl:if> <xsl:text>TO MONTH</xsl:text> </xsl:when> <xsl:when test="$Input_datatype='INTERVAL_DAY_TO_SECOND'"> <xsl:text>INTERVAL DAY </xsl:text> <xsl:if test="sxml:PRECISION"> <xsl:text>(</xsl:text> <xsl:choose> <xsl:when test="sxml:PRECISION[@value1]"> <xsl:value-of select="sxml:PRECISION/@value1"/> </xsl:when> <xsl:otherwise> <xsl:value-of select="sxml:PRECISION"/> </xsl:otherwise> </xsl:choose> <xsl:text>) </xsl:text> </xsl:if> <xsl:text>TO SECOND</xsl:text> <xsl:if test="sxml:SCALE"> <xsl:text> (</xsl:text> <xsl:choose> <xsl:when test="sxml:SCALE[@value1]"> <xsl:value-of select="sxml:SCALE/@value1"/> </xsl:when> <xsl:otherwise> <xsl:value-of select="sxml:SCALE"/> </xsl:otherwise> </xsl:choose> <xsl:text>) </xsl:text> </xsl:if> </xsl:when> <xsl:when test="$Input_datatype='TIMESTAMP_WITH_LOCAL_TIMEZONE'"> <xsl:text>TIMESTAMP </xsl:text> <xsl:if test="sxml:SCALE"> <xsl:text>(</xsl:text> <xsl:choose> <xsl:when test="sxml:SCALE[@value1]"> <xsl:value-of select="sxml:SCALE/@value1"/> </xsl:when> <xsl:otherwise> <xsl:value-of select="sxml:SCALE"/> </xsl:otherwise> </xsl:choose> <xsl:text>) </xsl:text> </xsl:if> <xsl:text>WITH LOCAL TIME ZONE</xsl:text> </xsl:when> <xsl:when test="$Input_datatype='UDT' or $Input_datatype='NESTED_TABLE' or $Input_datatype='VARRAY' or $Input_datatype='OPAQUE' or $Input_datatype='XMLTYPE' or $Input_datatype='REF'"> <xsl:if test="$Input_datatype='REF'">REF </xsl:if> <xsl:call-template name="SchemaName"> <xsl:with-param name="ParentNode" select="sxml:TYPE_PROPERTIES"/> </xsl:call-template> </xsl:when> <xsl:when test="$Input_datatype='LONG_RAW'">LONG RAW</xsl:when> <xsl:otherwise> <xsl:value-of select="$Input_datatype"/> </xsl:otherwise> </xsl:choose> </xsl:template> <xsl:template name="BuildInsertStmt"> <xsl:param name="ScalarCols" select="''"/> <xsl:param name="LobCols" select="''"/> <xsl:param name="LongCols" select="''"/> <xsl:param name="DroppedScalars" select="''"/> <xsl:param name="DroppedLobs" select="''"/> <xsl:param name="DroppedLongs" select="''"/> <!-- ******************************************************************* Template: BuildInsertStmt - This routine will build an Insert statement whose VALUES clause contains all NULL values. This routine is called by the Data Pump's data layer routine, kupd$data.fetch_insert_stmt, in file prvtbpd.sql. Parameters: ScalarCols - the node set of scalar COL_LIST_ITEM elements LobCols - the node set of lob COL_LIST_ITEM elements LongCols - the node set of long COL_LIST_ITEM elements DroppedScalars- the node set of scalar COL_LIST_ITEM elements that are not present in the target(import) database DroppedLobs - the node set of lob COL_LIST_ITEM elements that are not present in the target(import) database DroppedLongs - the node set of long COL_LIST_ITEM elements that are not present in the target(import) database The output looks like this: Insert into ******************************************************************** --> <xsl:call-template name="DoParse"> <xsl:with-param name="Verb">INSERT</xsl:with-param> <xsl:with-param name="ObjectType">TABLE</xsl:with-param> <xsl:with-param name="SchemaNode" select="SCHEMA"/> </xsl:call-template> <xsl:text>INSERT /*+ SYS_DL_CURSOR */ INTO RELATIONAL("</xsl:text> <xsl:value-of select="$TARGET_TABLE_SCHEMA"/> <xsl:text>"."</xsl:text> <xsl:value-of select="$TARGET_TABLE_NAME"/> <xsl:text>" NOT XMLTYPE)(</xsl:text> <xsl:call-template name="DoExtTblCOL_LIST"> <xsl:with-param name="Scalars" select="$ScalarCols"/> <xsl:with-param name="Lobs" select="$LobCols"/> <xsl:with-param name="Longs" select="$LongCols"/> <xsl:with-param name="ADT_type">INDEX</xsl:with-param> <xsl:with-param name="DoLF">0</xsl:with-param> </xsl:call-template> <xsl:text>)</xsl:text> <xsl:text> VALUES(</xsl:text> <!-- Write out as many nulls as there are columns --> <xsl:for-each select="$ScalarCols"> <xsl:text>NULL</xsl:text> <xsl:if test="not(position()=last())"> <xsl:text>,</xsl:text> </xsl:if> </xsl:for-each> <!-- If there is at least 1 ScalarCol and 1 LobCol, output a comma to separate the last non-lob col from the first lob col. --> <xsl:if test="count($ScalarCols)>0 and count($LobCols)>0"> <xsl:text>,</xsl:text> </xsl:if> <xsl:for-each select="$LobCols"> <xsl:text>NULL</xsl:text> <xsl:if test="not(position()=last())"> <xsl:text>,</xsl:text> </xsl:if> </xsl:for-each> <!-- If there is at least 1 ScalarCol and/or 1 LobCol plus a long column, output a comma to separate last non-long col from long col. --> <xsl:if test="(count($ScalarCols)>0 or count($LobCols)>0) and count($LongCols)>0"> <xsl:text>,</xsl:text> </xsl:if> <xsl:for-each select="$LongCols"> <xsl:text>NULL</xsl:text> <xsl:if test="not(position()=last())"> <xsl:text>,</xsl:text> </xsl:if> </xsl:for-each> <xsl:text>)</xsl:text> <xsl:if test="$SQLTERMINATOR=1"> <xsl:text>;</xsl:text> <!-- Terminate the SQL statement --> </xsl:if> <!-- Now output the dropped columns if any --> <xsl:if test="count($DroppedScalars)>0 or count($DroppedLobs)>0 or count($DroppedLongs)>0"> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <!-- There is at least 1 dropped column. Output the column names --> <xsl:call-template name="DoParse"> <xsl:with-param name="Verb">INSERT</xsl:with-param> <xsl:with-param name="ObjectType">TABLE</xsl:with-param> <xsl:with-param name="SchemaNode" select="SCHEMA"/> </xsl:call-template> <xsl:value-of select="count($DroppedScalars) + count($DroppedLobs) + count($DroppedLongs)"/> <xsl:call-template name="DoParse"> <xsl:with-param name="Verb">INSERT</xsl:with-param> <xsl:with-param name="ObjectType">TABLE</xsl:with-param> <xsl:with-param name="SchemaNode" select="SCHEMA"/> </xsl:call-template> <!-- If any dropped scalar columns, output the name>--> <xsl:for-each select="$DroppedScalars"> <xsl:choose> <xsl:when test="sxml:INTERNAL_NAME"> <xsl:value-of select="sxml:INTERNAL_NAME"/> </xsl:when> <xsl:otherwise> <xsl:value-of select="sxml:NAME"/> </xsl:otherwise> </xsl:choose> <xsl:if test="not(position()=last())"> <xsl:text>,</xsl:text> </xsl:if> </xsl:for-each> <!-- If there is at least 1 Dropped ScalarCol and 1 Dropped LobCol, output a comma to separate the last non-lob col from the first lob col. --> <xsl:if test="count($DroppedScalars)>0 and count($DroppedLobs)>0"> <xsl:text>,</xsl:text> </xsl:if> <xsl:for-each select="$DroppedLobs"> <xsl:choose> <xsl:when test="sxml:INTERNAL_NAME"> <xsl:value-of select="sxml:INTERNAL_NAME"/> </xsl:when> <xsl:otherwise> <xsl:value-of select="sxml:NAME"/> </xsl:otherwise> </xsl:choose> <xsl:if test="not(position()=last())"> <xsl:text>,</xsl:text> </xsl:if> </xsl:for-each> <!-- If there is at least 1 dropped scalar and/or lob column and a long column, output comma to separate the last non-long col from the long column --> <xsl:if test="(count($DroppedScalars)>0 or count($DroppedLobs)> 0) and count($DroppedLongs)>0"> <xsl:text>,</xsl:text> </xsl:if> <xsl:for-each select="$DroppedLongs"> <xsl:value-of select="sxml:NAME"/> <xsl:if test="not(position()=last())"> <xsl:text>,</xsl:text> </xsl:if> </xsl:for-each> </xsl:if> </xsl:template> </xsl:stylesheet>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de