Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\WEB-INF\xsls\kuexttbl.xsl
<?xml version="1.0"?> <!-- Copyright (c) 2002, 2009, Oracle and/or its affiliates.All rights reserved. NAME kuexttbl.xsl DESCRIPTION XSLT stylesheet for conversion of table_t XML for external table unload/load or network load MODIFIED MM/DD/YY dgagne 11/14/07 - add network name transform ebatbout 10/05/07 - bug 5861640: support functional index on XMLType col lbarton 02/01/07 - IMPORT_CONVENTIONAL jkaloger 06/13/07 - Fix version comparison checks for 11.0 lbarton 09/18/06 - bug 5212908: PARALLEL_HINT lbarton 09/07/06 - lrg 2453260: version compatibility lbarton 03/15/06 - bug 4516042: use not xmltype hint cmlim 11/29/05 - support dml error logging lbarton 01/20/06 - bug 3328206: quote problems lbarton 12/29/05 - REMAP_COLUMN_NAME lbarton 11/02/05 - Bug 4715313: reformat files for use with XMLSpy lbarton 05/18/05 - add formatting lbarton 07/28/04 - add encryption support lbarton 05/11/04 - Bug 3617842: SAMPLE and PCTSPACE htseng 03/10/04 - bug 3369569: exclude hidden column through network htseng 02/26/04 - bug 3369240: not generate SYS_NC_OID$ if PK base on OID$. lbarton 01/06/04 - Bug 3352848: quote (sub)partition name lbarton 09/22/03 - Bug 3149477: SUBPARTITION_NAME param lbarton 09/02/03 - Bug 3111371: DoLongInsert: insert into cur schema lbarton 06/25/03 - Bug 3014162: create ext table in cur schema lbarton 06/13/03 - support QUERY for network insert lbarton 05/30/03 - Bug 2837515: Network INSERT SELECT syntax lbarton 03/20/03 - Bug 2856609: fix INSERT syntax lbarton 02/26/03 - INSERT SELECT of SYS_NC_OID$ col for network lbarton 02/19/03 - Add column list to RELATIONAL operator emagrath 12/23/02 - DROP TABLE FORCE-> PURGE jdavison 12/20/02 - Add append hint for XT Insert stmt lbarton 11/14/02 - add space after FORCE emagrath 11/04/02 - DROP TABLE [FORCE] lbarton 10/17/02 - Network load for typed tables lbarton 07/22/02 - Make DROP robust ebatbout 06/21/02 - lbarton 06/11/02 - lbarton_mdapi_exttbl lbarton 06/03/02 - Created --> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <!-- Import required scripts --> <xsl:import href="kucommon.xsl"/> <xsl:import href="kucolumn.xsl"/> <!-- Top-level parameters --> <xsl:param name="QUOTE_SYMBOL" select="''"/> <!-- Templates--> <xsl:template name="CreateExtTable"> <xsl:param name="DBlinkName" select="''"/> <!-- ******************************************************************* Template: CreateExtTable - Create External Table This template generates the syntax for external table unload/load. For unload, 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; CREATE TABLE "ET$43627" ( "C1", "C2" ) ORGANIZATION EXTERNAL <ext_table_clause> AS SELECT "C1", "C2" FROM RELATIONAL("SCOTT"."TAB1" NOT XMLTYPE) KU$ Note that we initially drop the ext table in case it's left around from a previous failed operation. For load, the output looks similar, except that we do a CREATE TABLE followed by INSERT (instead of CREATE TABLE ... AS SELECT): 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: 'EXPORT' = do external table unload 'IMPORT' = do external table load 'IMPORT_CONVENTIONAL' = do conventional path load SCN: SCN for operation SAMPLE: sample value QUERY: query clause DROP: drop the external table at the end 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 ******************************************************************** --> <!-- 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="COL_LIST/COL_LIST_ITEM[SEGCOL_NUM!=0 and TYPE_NUM!=112 and TYPE_NUM!=113 and TYPE_NUM!=123 and TYPE_NUM!=58 and TYPE_NUM!=8 and TYPE_NUM!=24 and (PROPERTY mod 65536)<32768 and ((INTCOL_NUM=BASE_INTCOL_NUM) or (2>BASE_COL_TYPE)) and ((PROPERTY mod 64)<32 or (NAME!='ACLOID' and NAME!='OWNERID'))]"/> <!-- Scalars for 10g --> <xsl:variable name="Scalars10g" select="COL_LIST/COL_LIST_ITEM[SEGCOL_NUM!=0 and TYPE_NUM!=112 and TYPE_NUM!=113 and TYPE_NUM!=123 and TYPE_NUM!=58 and TYPE_NUM!=8 and TYPE_NUM!=24 and (PROPERTY mod 65536)<32768]"/> <!-- (2) LOBs or anything that can be stored as a lob such as varrays or opaques, including schema-based and binary XMLType columns which are exported in self-describing binary as a BLOB. Note regarding XMLType columns - Include this type of column if any of the following conditions are met: 1. 0x0001 (1) - XMLType stored as object 2. 0x0004 (4) - XMLType stored as a lob 3. 0x0040 (64) - XMLType stored as binary If no storage is specified for the XMLType column in OPQMD/FLAGS, then this XMLType column is a virtual column associated w/ a functional index --> <xsl:variable name="Lobs" select="COL_LIST/COL_LIST_ITEM[SEGCOL_NUM!=0 and (TYPE_NUM=112 or TYPE_NUM=113 or TYPE_NUM=123 or TYPE_NUM=58) and (PROPERTY mod 65536)<32768 and ((INTCOL_NUM=BASE_INTCOL_NUM) or (2>BASE_COL_TYPE)) or (SEGCOL_NUM=0 and TYPE_NUM=58 and OPQMD and OPQMD/TYPE=1 and ((OPQMD/FLAGS mod 128) >= 64 or (OPQMD/FLAGS mod 8) >= 4 or (OPQMD/FLAGS mod 2) >= 1))]"/> <!-- Lobs for 10g --> <xsl:variable name="Lobs10g" select="COL_LIST/COL_LIST_ITEM[SEGCOL_NUM!=0 and (TYPE_NUM=112 or TYPE_NUM=113 or TYPE_NUM=123 or TYPE_NUM=58) and (PROPERTY mod 65536)<32768]"/> <!-- (3) Long and long raw --> <xsl:variable name="Longs" select="COL_LIST/COL_LIST_ITEM[SEGCOL_NUM!=0 and (TYPE_NUM=8 or TYPE_NUM=24) and (PROPERTY mod 65536)<32768]"/> <!-- call the work routine with the appropriate node sets --> <xsl:choose> <xsl:when test="$VERSION>=1100000000 and COL_LIST/COL_LIST_ITEM[BASE_INTCOL_NUM]"> <xsl:call-template name="CreateExtTableWrk"> <xsl:with-param name="DBlinkName" select="$DBlinkName"/> <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:when> <xsl:otherwise> <xsl:call-template name="CreateExtTableWrk"> <xsl:with-param name="DBlinkName" select="$DBlinkName"/> <xsl:with-param name="Scalars" select="$Scalars10g"/> <xsl:with-param name="Lobs" select="$Lobs10g"/> <xsl:with-param name="Longs" select="$Longs"/> </xsl:call-template> </xsl:otherwise> </xsl:choose> </xsl:template> <xsl:template name="CreateExtTableWrk"> <xsl:param name="DBlinkName" select="''"/> <xsl:param name="Scalars" select="''"/> <xsl:param name="Lobs" select="''"/> <xsl:param name="Longs" select="''"/> <!-- ******************************************************************* 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 ******************************************************************** --> <!-- 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:choose> <xsl:when test="$OPERATION_TYPE='EXPORT'"> <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="ExtTbl">1</xsl:with-param> <xsl:with-param name="ExtTblEncrypt">1</xsl:with-param> </xsl:call-template> </xsl:when> <xsl:when test="$OPERATION_TYPE='IMPORT' or $OPERATION_TYPE='IMPORT_CONVENTIONAL'"> <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="ExtTbl">2</xsl:with-param> <xsl:with-param name="ExtTblEncrypt">1</xsl:with-param> </xsl:call-template> </xsl:when> </xsl:choose> <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:choose> <xsl:when test="$OPERATION_TYPE='IMPORT'"> <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: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_OBJ/OWNER_NAME"/> </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="SCHEMA_OBJ/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="(PROPERTY mod 8)>=4">/*+NESTED_TABLE_SET_SETID+*/ </xsl:if> <xsl:text>INTO RELATIONAL(</xsl:text> <xsl:apply-templates select="SCHEMA_OBJ"/> <xsl:if test="$VERSION>=1100000000"> <xsl:text>NOT XMLTYPE</xsl:text> </xsl:if> <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="ExtTbl">2</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="ExtTbl">2</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: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="ExtTbl">3</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="SCHEMA_OBJ/OWNER_NAME"/> </xsl:call-template> <xsl:text>INSERT </xsl:text> <xsl:if test="($USE_DML_APPEND_HINT!= 0 and $OPERATION_TYPE!='IMPORT_CONVENTIONAL') or string-length($PARALLEL_HINT)!=0"> <xsl:text>/*+ </xsl:text> <xsl:if test="$USE_DML_APPEND_HINT!= 0 and $OPERATION_TYPE!='IMPORT_CONVENTIONAL'">APPEND </xsl:if> <xsl:if test="string-length($PARALLEL_HINT)!=0"> <xsl:text>PARALLEL("</xsl:text> <xsl:value-of select="SCHEMA_OBJ/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="(PROPERTY mod 8)>=4">/*+NESTED_TABLE_SET_SETID+*/ </xsl:if> <xsl:text>INTO RELATIONAL(</xsl:text> <xsl:apply-templates select="SCHEMA_OBJ"/> <xsl:if test="$VERSION>=1100000000"> <xsl:text>NOT XMLTYPE</xsl:text> </xsl:if> <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="ExtTbl">4</xsl:with-param> <xsl:with-param name="ExtTblOps">0</xsl:with-param> <xsl:with-param name="DoLF">0</xsl:with-param> <xsl:with-param name="DoREMAP">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="ExtTbl">5</xsl:with-param> <xsl:with-param name="ExtTblOps">1</xsl:with-param> <xsl:with-param name="DoLF">0</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:when test="$OPERATION_TYPE='EXPORT'"> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <xsl:text> AS </xsl:text> <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="ExtTbl">1</xsl:with-param> </xsl:call-template> <xsl:text>RELATIONAL(</xsl:text> <xsl:apply-templates select="SCHEMA_OBJ"/> <xsl:if test="string-length($DBlinkName)!=0"> <xsl:text>@</xsl:text> <xsl:value-of select="$DBlinkName"/> <xsl:text> </xsl:text> </xsl:if> <xsl:if test="$VERSION>=1100000000"> <xsl:text>NOT XMLTYPE</xsl:text> </xsl:if> <xsl:text>) </xsl:text> <xsl:call-template name="ExtTblPartition"/> <xsl:if test="string-length($DBlinkName)=0 and string-length($SAMPLE)!= 0 and $SAMPLE!=100"> <xsl:text>SAMPLE (</xsl:text> <xsl:value-of select="$SAMPLE"/> <xsl:text>) </xsl:text> </xsl:if> <xsl:if test="$SCN!=0"> <xsl:text>AS OF SCN </xsl:text> <xsl:value-of select="$SCN"/> <xsl:text> </xsl:text> </xsl:if> <xsl:text>KU$ </xsl:text> <xsl:value-of select="$QUERY"/> </xsl:when> <xsl:otherwise> <xsl:text>***ERROR: UNEXPECTED OPERATION_TYPE: </xsl:text> <xsl:value-of select="$OPERATION_TYPE"/> </xsl:otherwise> </xsl:choose> <xsl:if test="$OPERATION_TYPE='IMPORT'"> <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> <xsl:if test="$SQLTERMINATOR=1"> <xsl:text>;</xsl:text> </xsl:if> <!-- Drop the external table --> <xsl:if test="$DROP=1"> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <xsl:call-template name="DropExtTbl"/> </xsl:if> </xsl:template> <xsl:template name="SubstQuot"> <xsl:param name="String" select="''"/> <!-- ******************************************************************* Template: SubstQuot Parameters: String - string to output, possibly including QUOTE_SYMBOL Implicit Inputs: QUOTE_SYMBOL - string to be replaced by " ******************************************************************** --> <xsl:choose> <xsl:when test="string-length($QUOTE_SYMBOL)=0"> <xsl:value-of select="$String"/> </xsl:when> <xsl:when test="contains($String,$QUOTE_SYMBOL)"> <xsl:value-of select="substring-before($String,$QUOTE_SYMBOL)"/> <xsl:text>"</xsl:text> <xsl:call-template name="SubstQuot"> <xsl:with-param name="String" select="substring-after($String,$QUOTE_SYMBOL)"/> </xsl:call-template> </xsl:when> <xsl:otherwise> <xsl:value-of select="$String"/> </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_OBJ/OWNER_NAME"/> </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="ExtTblName"> <!-- ******************************************************************* Template: ExtTblName - Emit the external table name (in quotes) Use EXT_TABLE_SCHEMA if specified. ******************************************************************** --> <xsl:if test="string-length($EXT_TABLE_SCHEMA)!=0"> <xsl:text>"</xsl:text> <xsl:value-of select="$EXT_TABLE_SCHEMA"/> <xsl:text>".</xsl:text> </xsl:if> <xsl:text>"</xsl:text> <xsl:call-template name="ExtTblSimpleName"/> <xsl:text>" </xsl:text> </xsl:template> <xsl:template name="ExtTblSimpleName"> <!-- ******************************************************************* Template: ExtTblSimpleName - Emit the external table name either the global parameter EXT_TABLE_NAME or a name generated from obj# ******************************************************************** --> <xsl:choose> <xsl:when test="string-length($EXT_TABLE_NAME)!=0"> <xsl:value-of select="$EXT_TABLE_NAME"/> </xsl:when> <xsl:otherwise> <xsl:text>ET$</xsl:text> <xsl:value-of select="OBJ_NUM"/> </xsl:otherwise> </xsl:choose> </xsl:template> <xsl:template name="ExtTblSelect"> <xsl:param name="Scalars" select="''"/> <xsl:param name="Lobs" select="''"/> <xsl:param name="Longs" select="''"/> <xsl:param name="ExtTbl">1</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 ExtTbl - for ADT_type='TABLE', what kind of operation 1 = EXPORT, i.e., External Table unload 2 = IMPORT, i.e., External Table load 3 = IMPORT_CONVENTIONAL select statement 4 = IMPORT_CONVENTIONAL import statement column names 5 = IMPORT_CONVENTIONAL import statement values clause (3-5 for conventional path load) ******************************************************************** --> <xsl:text>SELECT </xsl:text> <!-- for export (ExtTbl=1) insert the parallel in on the SELECT --> <xsl:if test="string-length($PARALLEL_HINT)!=0 and $ExtTbl=1"> <xsl:text>/*+ PARALLEL(KU$,</xsl:text> <xsl:value-of select="$PARALLEL_HINT"/> <xsl:text>) */ </xsl:text> </xsl:if> <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="ExtTbl" select="$ExtTbl"/> <xsl:with-param name="ExtTblOps">1</xsl:with-param> <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="ExtTblPartition"> <!-- ******************************************************************* Template: ExtTblPartition - Emit [SUB]PARTITION name ******************************************************************** --> <xsl:choose> <xsl:when test="string-length($PARTITION_NAME)!=0"> <xsl:text> PARTITION ( "</xsl:text> <xsl:value-of select="$PARTITION_NAME"/> <xsl:text>" ) </xsl:text> </xsl:when> <xsl:when test="string-length($SUBPARTITION_NAME)!=0"> <xsl:text> SUBPARTITION ( "</xsl:text> <xsl:value-of select="$SUBPARTITION_NAME"/> <xsl:text>" ) </xsl:text> </xsl:when> </xsl:choose> </xsl:template> <xsl:template name="LoadTableFromNetwork"> <xsl:param name="DBlinkName" select="''"/> <xsl:param name="RemoteSchema" select="''"/> <xsl:param name="RemoteName" select="''"/> <!-- ******************************************************************* Template: LoadTableFromNetwork - This is used for network load, and is totally separate from external table load/unload. Parameters: DBlinkName - name of DBlink RemoteSchema - schema of table on remote node (defaults to OWNER_NAME) RemoteName - name of table on remote node (defaults to TABLE_NAME) ******************************************************************** --> <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_OBJ/OWNER_NAME"/> <xsl:with-param name="NameNode" select="SCHEMA_OBJ/NAME"/> </xsl:call-template> <!-- Do long insert if there exists a column of type RAW (8) or LONG RAW (24). Otherwise there are 4 cases (all handled by DoSimpleInsert): 1. top-level relational table 2. top-level object table 3. nested table of object type 4. nested table not of object type (e.g., nested table of number) All use INSERT /*+ NESTED_TABLE_SET_REFS */ INTO ... SELECT /*+ NESTED_TABLE_GET_REFS */ ... FROM ... but the column lists are different. --> <xsl:choose> <xsl:when test="COL_LIST/COL_LIST_ITEM[TYPE_NUM=8]"> <xsl:call-template name="DoLongInsert"> <xsl:with-param name="DBlinkName" select="$DBlinkName"/> <xsl:with-param name="RemoteSchema" select="$RemoteSchema"/> <xsl:with-param name="RemoteName" select="$RemoteName"/> </xsl:call-template> </xsl:when> <xsl:when test="COL_LIST/COL_LIST_ITEM[TYPE_NUM=24]"> <xsl:call-template name="DoLongInsert"> <xsl:with-param name="DBlinkName" select="$DBlinkName"/> <xsl:with-param name="RemoteSchema" select="$RemoteSchema"/> <xsl:with-param name="RemoteName" select="$RemoteName"/> </xsl:call-template> </xsl:when> <xsl:otherwise> <xsl:call-template name="DoSimpleInsert"> <xsl:with-param name="DBlinkName" select="$DBlinkName"/> <xsl:with-param name="RemoteSchema" select="$RemoteSchema"/> <xsl:with-param name="RemoteName" select="$RemoteName"/> </xsl:call-template> </xsl:otherwise> </xsl:choose> </xsl:template> <xsl:template name="DoLongInsert"> <xsl:param name="DBlinkName" select="''"/> <xsl:param name="RemoteSchema" select="''"/> <xsl:param name="RemoteName" select="''"/> <!-- ******************************************************************* Template: DoLongInsert - Used when table has a long or long raw column. Parameters: DBlinkName - name of DBlink RemoteSchema - schema of table on remote node (defaults to OWNER_NAME) RemoteName - name of table on remote node (defaults to TABLE_NAME) ******************************************************************** --> <xsl:text>DECLARE CURSOR C1 IS SELECT </xsl:text> <xsl:apply-templates select="COL_LIST"> <xsl:with-param name="ADT_type">INDEX</xsl:with-param> <xsl:with-param name="DoLF">0</xsl:with-param> <xsl:with-param name="DoREMAP">1</xsl:with-param> <xsl:with-param name="Network">1</xsl:with-param> </xsl:apply-templates> <xsl:text> FROM </xsl:text> <xsl:call-template name="RemoteSchemaObj"> <xsl:with-param name="RemoteSchema" select="$RemoteSchema"/> <xsl:with-param name="RemoteName" select="$RemoteName"/> </xsl:call-template> <xsl:text>@</xsl:text> <xsl:value-of select="$DBlinkName"/> <xsl:if test="$SCN!=0"> <xsl:text> AS OF SCN </xsl:text> <xsl:value-of select="$SCN"/> <xsl:text> </xsl:text> </xsl:if> <xsl:if test="string-length($QUERY)!=0"> <xsl:text> </xsl:text> <xsl:value-of select="$QUERY"/> </xsl:if> <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:text>; BEGIN FOR C1_REC IN C1 LOOP INSERT INTO </xsl:text> <xsl:apply-templates select="SCHEMA_OBJ"/> <xsl:text> VALUES (</xsl:text> <xsl:apply-templates select="COL_LIST"> <xsl:with-param name="ADT_type">INDEX</xsl:with-param> <xsl:with-param name="DoLF">0</xsl:with-param> <xsl:with-param name="DoREMAP">0</xsl:with-param> <xsl:with-param name="Prefix">C1_REC.</xsl:with-param> </xsl:apply-templates> <xsl:text>); END LOOP; END;</xsl:text> </xsl:template> <xsl:template name="DoSimpleInsert"> <xsl:param name="DBlinkName" select="''"/> <xsl:param name="RemoteSchema" select="''"/> <xsl:param name="RemoteName" select="''"/> <!-- ******************************************************************* Template DoSimpleInsert: Generate INSERT /*+ NESTED_TABLE_SET_REFS */ INTO schema.table(col-list) SELECT /*+ NESTED_TABLE_GET_REFS */ col-list FROM remote-schema.remote-name@DBlinkName ******************************************************************** --> <xsl:text>INSERT /*+ NESTED_TABLE_SET_REFS */ INTO </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <xsl:apply-templates select="SCHEMA_OBJ"/> <xsl:text>(</xsl:text> <xsl:call-template name="DoNetworkColList"/> <xsl:text>)</xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <xsl:text> SELECT /*+ NESTED_TABLE_GET_REFS */ </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <xsl:call-template name="DoNetworkColList"> <xsl:with-param name="DoREMAP">1</xsl:with-param> </xsl:call-template> <xsl:text> FROM </xsl:text> <xsl:call-template name="RemoteSchemaObj"> <xsl:with-param name="RemoteSchema" select="$RemoteSchema"/> <xsl:with-param name="RemoteName" select="$RemoteName"/> </xsl:call-template> <xsl:text>@</xsl:text> <xsl:value-of select="$DBlinkName"/> <!-- SAMPLE clause not valid for network query --> <xsl:if test="$SCN!=0"> <xsl:text> AS OF SCN </xsl:text> <xsl:value-of select="$SCN"/> <xsl:text> </xsl:text> </xsl:if> <xsl:text> KU$</xsl:text> <xsl:if test="string-length($QUERY)!=0"> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <xsl:text> </xsl:text> <xsl:value-of select="$QUERY"/> </xsl:if> <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> <!-- Terminate the SQL statement --> </xsl:if> </xsl:template> <xsl:template name="DoNetworkColList"> <xsl:param name="DoREMAP">0</xsl:param> <!-- ******************************************************************* Template DoNetworkColList: Generate the column list for DoSimpleInsert. 4 cases: 1. top-level relational table - use column list from table 2. top-level object table - SYS_NC_OID$, SYS_NC_ROWINFO$ 3. nested table of object type - NESTED_TABLE_ID, SYS_NC_ROWINFO$ 4. nested table not of object type (e.g., nested table of number) - - NESTED_TABLE_ID, COLUMN_VALUE ******************************************************************** --> <xsl:choose> <xsl:when test="(PROPERTY mod 2)>=1"> <!-- object table --> <xsl:choose> <xsl:when test="(PROPERTY mod 16384)>=8192"> <!-- nested table --> <xsl:text>NESTED_TABLE_ID, SYS_NC_ROWINFO$</xsl:text> </xsl:when> <xsl:when test="(PROPERTY mod 8192)>=4096"> <!-- 0x1000 = has primary key-based OID$ column --> <xsl:text>SYS_NC_ROWINFO$</xsl:text> </xsl:when> <xsl:otherwise> <!-- top-level obj table --> <xsl:text>SYS_NC_OID$, SYS_NC_ROWINFO$</xsl:text> </xsl:otherwise> </xsl:choose> </xsl:when> <xsl:otherwise> <xsl:choose> <xsl:when test="(PROPERTY mod 16384)>=8192"> <!-- nested table --> <xsl:text>NESTED_TABLE_ID, COLUMN_VALUE</xsl:text> </xsl:when> <xsl:otherwise> <!-- top-level rel. table --> <xsl:apply-templates select="COL_LIST"> <xsl:with-param name="ADT_type">INDEX</xsl:with-param> <xsl:with-param name="DoLF">0</xsl:with-param> <xsl:with-param name="DoREMAP" select="$DoREMAP"/> <xsl:with-param name="Network">1</xsl:with-param> </xsl:apply-templates> </xsl:otherwise> </xsl:choose> </xsl:otherwise> </xsl:choose> </xsl:template> <xsl:template name="RemoteSchemaObj"> <xsl:param name="RemoteSchema" select="''"/> <xsl:param name="RemoteName" select="''"/> <!-- ******************************************************************* Template: RemoteSchemaObj - emit schema.name for remote object Parameters: RemoteSchema - schema of table on remote node (defaults to OWNER_NAME) RemoteName - name of table on remote node (defaults to TABLE_NAME) ******************************************************************** --> <xsl:choose> <!-- If both RemoteSchema and RemoteName are null, then use the defaults --> <xsl:when test="string-length($RemoteSchema)=0 and string-length($RemoteName)=0"> <xsl:call-template name="QuoteObject"> <xsl:with-param name="Schema" select="SCHEMA_OBJ/OWNER_NAME"/> <xsl:with-param name="Object" select="SCHEMA_OBJ/NAME"/> </xsl:call-template> </xsl:when> <!-- At least 1 is not null, if both are not null, use supplied values --> <xsl:when test="string-length($RemoteSchema)!=0 and string-length($RemoteName)!=0"> <xsl:call-template name="QuoteObject"> <xsl:with-param name="Schema" select="$RemoteSchema"/> <xsl:with-param name="Object" select="$RemoteName"/> </xsl:call-template> </xsl:when> <!-- Only 1 is not null, if it is the schema, use supplied schemna and the defualt for table name --> <xsl:when test="string-length($RemoteSchema)!=0"> <xsl:call-template name="QuoteObject"> <xsl:with-param name="Schema" select="$RemoteSchema"/> <xsl:with-param name="Object" select="SCHEMA_OBJ/NAME"/> </xsl:call-template> </xsl:when> <!-- It must be the table name that is supplied, use the supplied table name and the default schema --> <xsl:otherwise> <xsl:call-template name="QuoteObject"> <xsl:with-param name="Schema" select="SCHEMA_OBJ/OWNER_NAME"/> <xsl:with-param name="Object" select="$RemoteName"/> </xsl:call-template> </xsl:otherwise> </xsl:choose> </xsl:template> </xsl:stylesheet>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de