包体unwrap后的结果
PACKAGE BODY dbms_aw AS
DEFAULT_SPSADVICE_TABLE CONSTANT VARCHAR2(18) := 'AW_SPARSITY_ADVICE';
CMDLOGCLOB;
PROPLOB CLOB;
ENDLN VARCHAR2(1) := '
';
PROCEDURE STARTUP IS
EXTERNAL NAME "xsinitdriver"
WITH CONTEXT
PARAMETERS(CONTEXT)
LIBRARY DBMS_AW_LIB;
PROCEDURE SHUTDOWN(FORCE IN BOOLEAN DEFAULT FALSE) IS
EXTERNAL NAME "xskilldriver"
WITH CONTEXT
PARAMETERS(CONTEXT, FORCE)
LIBRARY DBMS_AW_LIB;
PROCEDURE INITDRIVER IS
BEGIN
STARTUP();
END;
PROCEDURE TOGGLEDBCREATE IS
BEGIN
RETURN;
END;
PROCEDURE INIT_CMDLOG IS
BEGIN
IF CMDLOG IS NULL THEN
DBMS_LOB.CREATETEMPORARY(CMDLOG, TRUE);
ELSE
DBMS_LOB.TRIM(CMDLOG, 0);
END IF;
END;
PROCEDURE XSRUNKK( CMDIN
STRING,
OUTPUTOUTSTRING) IS
EXTERNAL NAME "xsRunKK"
WITH CONTEXT
PARAMETERS(CONTEXT,
CMDOCISTRING,
OUTPUT OCISTRING,
OUTPUT MAXLENUB4)
LIBRARY DBMS_AW_LIB;
PROCEDURE XSRUNLN( CMDIN
CLOB) IS
EXTERNAL NAME "xsRunLN"
WITH CONTEXT
PARAMETERS(CONTEXT,
CMD)
LIBRARY DBMS_AW_LIB;
PROCEDURE XSRUNKL( CMD
STRING,
OUTLOB IN OUT NOCOPY CLOB) IS
EXTERNAL NAME "xsRunKL"
WITH CONTEXT
PARAMETERS(CONTEXT,
CMDOCISTRING,
OUTLOB)
LIBRARY DBMS_AW_LIB;
PROCEDURE XSRUNKN( CMDIN
STRING) IS
EXTERNAL NAME "xsRunKN"
WITH CONTEXT
PARAMETERS(CONTEXT,
CMDOCISTRING)
LIBRARY DBMS_AW_LIB;
PROCEDURE XSRUNLL( CMDIN
CLOB,
OUTLOB IN OUT NOCOPY CLOB) IS
EXTERNAL NAME "xsRunLL"
WITH CONTEXT
PARAMETERS(CONTEXT,
CMD,
OUTLOB)
LIBRARY DBMS_AW_LIB;
PROCEDURE XSRUNLK( CMDIN
CLOB,
OUTPUTOUTSTRING) IS
EXTERNAL NAME "xsRunLK"
WITH CONTEXT
PARAMETERS(CONTEXT,
CMD,
OUTPUT OCISTRING,
OUTPUT MAXLENUB4)
LIBRARY DBMS_AW_LIB;
PROCEDURE XSCMDEXCKL(CMD
STRING,
OUTLOB IN OUT NOCOPY CLOB) IS
EXTERNAL NAME "xsCmdExcKL"
WITH CONTEXT
PARAMETERS(CONTEXT,
CMDOCISTRING,
OUTLOB)
LIBRARY DBMS_AW_LIB;
PROCEDURE XSCMDEXCKN(CMDIN
STRING) IS
EXTERNAL NAME "xsCmdExcKN"
WITH CONTEXT
PARAMETERS(CONTEXT,
CMDOCISTRING)
LIBRARY DBMS_AW_LIB;
PROCEDURE XSCMDEXCLL(CMDIN
CLOB,
OUTLOB IN OUT NOCOPY CLOB) IS
EXTERNAL NAME "xsCmdExcLL"
WITH CONTEXT
PARAMETERS(CONTEXT,
CMD,
OUTLOB)
LIBRARY DBMS_AW_LIB;
PROCEDURE EXECUTE(CMD IN STRING) IS
BEGIN
DBMS_OUTPUT.ENABLE(100000);
INIT_CMDLOG;
XSCMDEXCKL(CMD, CMDLOG);
PRINTLOG(CMDLOG);
END;
PROCEDURERUN(CMDIN
STRING,
SILENT IN
BOOLEAN DEFAULT FALSE) IS
BEGIN
IF SILENT THEN
XSRUNKN(CMD);
ELSE
INIT_CMDLOG;
XSRUNKL(CMD, CMDLOG);
PRINTLOG(CMDLOG);
END IF;
END;
PROCEDURERUN(CMDIN
CLOB,
SILENT IN
BOOLEAN DEFAULT FALSE) IS
BEGIN
IF SILENT THEN
XSRUNLN(CMD);
ELSE
INIT_CMDLOG;
XSRUNLL(CMD, CMDLOG);
PRINTLOG(CMDLOG);
END IF;
END;
PROCEDURERUN(CMDIN
STRING,
OUTPUTOUTSTRING) IS
BEGIN
XSRUNKK(CMD, OUTPUT);
END;
PROCEDURERUN(CMDIN
STRING,
OUTPUT IN OUT NOCOPY CLOB) IS
BEGIN
XSRUNKL(CMD, OUTPUT);
END;
PROCEDURERUN(CMDIN
CLOB,
OUTPUTOUTSTRING) IS
BEGIN
XSRUNLK(CMD, OUTPUT);
END;
PROCEDURERUN(CMDIN
CLOB,
OUTPUT IN OUT NOCOPY CLOB) IS
BEGIN
XSRUNLL(CMD, OUTPUT);
END;
FUNCTION INTERP(CMD IN STRING)
RETURN CLOB AS
BEGIN
INIT_CMDLOG;
XSCMDEXCKL(CMD, CMDLOG);
RETURN CMDLOG;
END;
PROCEDURE INTERP_SILENT(CMD IN STRING) IS
BEGIN
XSCMDEXCKN(CMD);
END;
FUNCTION INTERPCLOB(CMD_CLOB IN CLOB)
RETURN CLOB AS
BEGIN
INIT_CMDLOG;
XSCMDEXCLL(CMD_CLOB, CMDLOG);
RETURN CMDLOG;
END;
FUNCTION GETLOG
RETURN CLOB AS
BEGIN
RETURN CMDLOG;
END;
FUNCTIONXSEVALNUM(CMD IN STRING, RET OUT NUMBER) RETURN BOOLEAN IS
EXTERNAL NAME "xsevalnum"
WITH CONTEXT PARAMETERS(CONTEXT, CMD STRING, RET OCINUMBER)
LIBRARY DBMS_AW_LIB;
FUNCTIONEVAL_NUMBER(CMD IN STRING) RETURN NUMBER
IS
RET NUMBER;
BEGIN
IF XSEVALNUM(CMD, RET) THEN
RETURN RET;
ELSE
RETURN NULL;
END IF;
END EVAL_NUMBER;
FUNCTIONEVAL_TEXT(CMD IN STRING) RETURN VARCHAR2 IS
EXTERNAL NAME "xsevaltxt"
WITH CONTEXT PARAMETERS(CONTEXT, CMD STRING)
LIBRARY DBMS_AW_LIB;
PROCEDURE LOBLINEITER_INIT(CTX IN OUTLOBLINEITER_T,
MYLOB IN
CLOB,
LINEMAX IN
NUMBER) IS
BEGIN
CTX.MYLOB := MYLOB;
CTX.LOC := 1;
CTX.CLENGTH := DBMS_LOB.GETLENGTH(MYLOB);
IF CTX.CLENGTH IS NULL THEN
CTX.CLENGTH := 0;
END IF;
CTX.CMAX := CTX.CLENGTH;
CTX.LINEMAX := LINEMAX;
END LOBLINEITER_INIT;
FUNCTION LOBLINEITER_NEXT(CTXIN OUT LOBLINEITER_T,
LINEOUT VARCHAR2) RETURN BOOLEAN
IS
POS NUMBER;
WIDTH NUMBER;
BEGIN
IF CTX.LOC > CTX.CMAX THEN
RETURN FALSE;
END IF;
POS := DBMS_LOB.INSTR(CTX.MYLOB, ENDLN, CTX.LOC);
IF POS = 0 THEN
WIDTH := CTX.CMAX - CTX.LOC + 1;
ELSE
WIDTH := POS - CTX.LOC;
END IF;
IF WIDTH > CTX.LINEMAX THEN
WIDTH := CTX.LINEMAX;
END IF;
IF WIDTH > 0 THEN
DBMS_LOB.READ(CTX.MYLOB, WIDTH, CTX.LOC, LINE);
END IF;
CTX.CLENGTH := CTX.CLENGTH - WIDTH - 1;
CTX.LOC := CTX.LOC + WIDTH + 1;
RETURN TRUE;
END LOBLINEITER_NEXT;
PROCEDURE PRINTLOG(LOG_CLOB IN CLOB) AS
COUTPUT VARCHAR2(32767);
LOBCTXLOBLINEITER_T;
WIDTH NUMBER;
BEGIN
LOBLINEITER_INIT(LOBCTX, LOG_CLOB, 32768);
WHILE LOBLINEITER_NEXT(LOBCTX, COUTPUT) LOOP
WIDTH := LENGTH(COUTPUT);
IF WIDTH > 0 THEN
DBMS_OUTPUT.PUT_LINE(COUTPUT);
ELSE
DBMS_OUTPUT.PUT_LINE('');
END IF;
END LOOP;
END;
PROCEDURE INFILE(IFILENAME IN STRING) IS
BEGIN
EXECUTE('INFILE ''' || IFILENAME || ''' NOW');
END;
FUNCTION OLAP_ON RETURN BOOLEAN IS
OON NUMBER;
BEGIN
SELECT COUNT(*) INTO OON FROM V$OPTION
WHERE PARAMETER = 'OLAP' AND VALUE = 'TRUE';
RETURN (OON > 0);
END;
FUNCTION OLAP_RUNNING RETURN BOOLEAN
IS EXTERNAL NAME "xsrunning" WITH CONTEXT
PARAMETERS(CONTEXT)
LIBRARY DBMS_AW_LIB;
FUNCTION OLAP_ACTIVE RETURN BOOLEAN
IS EXTERNAL NAME "xsisactive" WITH CONTEXT
PARAMETERS(CONTEXT)
LIBRARY DBMS_AW_LIB;
PROCEDURE ADVISE_REL(RELNAMEIN VARCHAR2,
VALUESET IN VARCHAR2,
PCTIN BINARY_INTEGER DEFAULT 20,
COMPRESSED IN BOOLEANDEFAULT FALSE)
IS
EXTERNAL NAME "xsagAdvRel"
WITH CONTEXT
PARAMETERS(CONTEXT, RELNAME OCISTRING, VALUESET OCISTRING, PCT SB4,
COMPRESSED UB4)
LIBRARY DBMS_AW_LIB;
PROCEDURE ADVISE_CUBE( AGGMAP IN VARCHAR2,
PCTIN BINARY_INTEGER DEFAULT 20,
COMPRESSED IN BOOLEANDEFAULT FALSE)
IS
EXTERNAL NAME "xsagAdvCube"
WITH CONTEXT
PARAMETERS(CONTEXT, AGGMAP OCISTRING, PCT SB4, COMPRESSED UB4)
LIBRARY DBMS_AW_LIB;
PROCEDURE ENABLE_ACCESS_TRACKING(OBJNAME IN VARCHAR2)
IS
EXTERNAL NAME "xscatEnable"
WITH CONTEXT
PARAMETERS(CONTEXT, OBJNAME OCISTRING)
LIBRARY DBMS_AW_LIB;
PROCEDURE DISABLE_ACCESS_TRACKING(OBJNAME IN VARCHAR2)
IS
EXTERNAL NAME "xscatDisable"
WITH CONTEXT
PARAMETERS(CONTEXT, OBJNAME OCISTRING)
LIBRARY DBMS_AW_LIB;
PROCEDURE CLEAN_ACCESS_TRACKING(OBJNAME IN VARCHAR2)
IS
EXTERNAL NAME "xscatClean"
WITH CONTEXT
PARAMETERS(CONTEXT, OBJNAME OCISTRING)
LIBRARY DBMS_AW_LIB;
FUNCTION ADVISE_TABLE_NAME(ADVTABLE IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
IF ADVTABLE IS NULL THEN
RETURN DEFAULT_SPSADVICE_TABLE;
ELSE
RETURN DBMS_ASSERT.SIMPLE_SQL_NAME(ADVTABLE);
END IF;
END ADVISE_TABLE_NAME;
PROCEDURE SPARSITY_ADVICE_TABLE(TBLNAME IN VARCHAR2 DEFAULT NULL)
IS
TBLNAME_I VARCHAR2(100);
BEGIN
TBLNAME_I := ADVISE_TABLE_NAME(TBLNAME);
EXECUTE IMMEDIATE
'CREATE TABLE ' || TBLNAME_I || '(
cubename VARCHAR2(100) NOT NULL,
fact VARCHAR2(4000)NOT NULL,
dimensionVARCHAR2(100) NOT NULL,
dimcolumnVARCHAR2(100),
dimsourceVARCHAR2(4000),
membercountNUMBER(12,0),
leafcountNUMBER(12,0),
advice VARCHAR2(10)NOT NULL,
position NUMBER(4,0) NOT NULL,
densityNUMBER(11,8),
partnumNUMBER(6,0) NOT NULL,
partby CLOB,
parttops CLOB,
partlevelVARCHAR2(200))';
END;
PROCEDURE ADD_DIMENSION_SNOWFLAKE(DIMNAMEINVARCHAR2,
COLNAMEINVARCHAR2,
SOURCESIN OUTDBMS_AW$_DIMENSION_SOURCES_T,
ORGVAL INVARCHAR2,
DIMTYPEINNUMBER DEFAULT NO_HIER,
HIERCOLS INDBMS_AW$_COLUMNLIST_T
DEFAULT NULL,
PARTBY INNUMBER
DEFAULT PARTBY_DEFAULT)
IS
DIMSOURCE DBMS_AW$_DIMENSION_SOURCE_T;
SRCTABLES DBMS_AW$_COLUMNLIST_T;
NEWHIERCOLS DBMS_AW$_COLUMNLIST_T;
I NUMBER;
DOTPOINT NUMBER;
TABLENAME VARCHAR2(100);
OWNERNAME VARCHAR2(100);
COLUMN VARCHAR2(100);
V_TABLE NUMBER;
SRCVAL VARCHAR2(32767);
BEGIN
SRCTABLES := DBMS_AW$_COLUMNLIST_T();
SRCVAL := ORGVAL;
DOTPOINT := INSTR(SRCVAL, ',');
WHILE DOTPOINT > 0 LOOP
TABLENAME := TRIM(SUBSTR(SRCVAL, 0, DOTPOINT-1));
SRCVAL := TRIM(SUBSTR(SRCVAL, DOTPOINT+1));
SRCTABLES.EXTEND(1);
SRCTABLES(SRCTABLES.COUNT) := TABLENAME;
DOTPOINT := INSTR(SRCVAL, ',');
END LOOP;
SRCTABLES.EXTEND(1);
SRCTABLES(SRCTABLES.COUNT) := SRCVAL;
IF HIERCOLS.COUNTSRCTABLES.COUNT*2 THEN
RAISE_APPLICATION_ERROR(BAD_SNOWFLAKE_ERROR,
'The number of columns does not match ' ||
'the number of source tables.');
END IF;
FOR I IN 1..SRCTABLES.COUNT LOOP
DOTPOINT := INSTR(SRCTABLES(I), '.');
IF DOTPOINT > 0 THEN
OWNERNAME := TRIM(SUBSTR(SRCTABLES(I), 0, DOTPOINT-1));
TABLENAME := TRIM(SUBSTR(SRCTABLES(I), DOTPOINT+1));
SELECT COUNT(*) INTO V_TABLE FROM ALL_TABLES
WHERE TABLE_NAME = UPPER(TABLENAME)
AND OWNER = UPPER(OWNERNAME);
ELSE
SELECT COUNT(*) INTO V_TABLE FROM USER_TABLES
WHERE TABLE_NAME = UPPER(SRCTABLES(I));
END IF;
IF V_TABLE ';
SPARSEDFN := SPARSEDFN || '>';
END IF;
DIMLIST := '';
RETURN DIMLIST;
END ADVISE_DIMENSIONALITY;
PROCEDURE LOB_STRINGAPPEND(OUTPUT IN OUT NOCOPY CLOB,
STRIN
VARCHAR2) IS
BEGIN
DBMS_LOB.WRITEAPPEND(OUTPUT, LENGTH(STR), STR);
END LOB_STRINGAPPEND;
FUNCTION PARTNAME(BASEIN VARCHAR2,
PARTNUM IN NUMBER) RETURN VARCHAR2 IS
BEGIN
RETURN BASE || '_p' || PARTNUM;
END PARTNAME;
FUNCTION TEMPLATENAME(BASE IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN BASE || '_pt';
END TEMPLATENAME;
PROCEDURE ADVISE_DIMENSIONALITY(OUTPUTOUT NOCOPY CLOB,
CUBENAME IN VARCHAR2,
SPARSENAME IN VARCHAR2 DEFAULT NULL,
DTYPEIN VARCHAR2 DEFAULT 'NUMBER',
ADVTABLE IN VARCHAR2 DEFAULT NULL)
IS
LOBCTX LOBLINEITER_T;
NPARTS NUMBER;
CUBEDEFVARCHAR2(32767);
SPARSEDEFVARCHAR2(32767);
SPARSENAME_I VARCHAR2(200);
ADVTABLE_I VARCHAR2(350);
PTNAME VARCHAR2(200);
PARTSTRVARCHAR2(350);
FIRSTBOOLEAN;
DIMNAMEVARCHAR2(200);
PARTBY CLOB;
PARTNUMNUMBER;
LINENO NUMBER;
TYPE ADVCURTYP IS REF CURSOR;
ADV_CV ADVCURTYP;
BEGIN
ADVTABLE_I := ADVISE_TABLE_NAME(ADVTABLE);
DBMS_LOB.CREATETEMPORARY(OUTPUT, FALSE);
EXECUTE IMMEDIATE 'SELECT MAX(partnum) FROM ' || ADVTABLE_I ||
' WHERE cubename=:1'
INTO NPARTS USING CUBENAME;
IF NPARTS > 1 THEN
IF SPARSENAME IS NULL THEN
SPARSENAME_I := CUBENAME || '_dim';
ELSE
SPARSENAME_I := SPARSENAME;
END IF;
PTNAME := TEMPLATENAME(CUBENAME);
DBMS_LOB.OPEN(OUTPUT, DBMS_LOB.LOB_READWRITE);
FOR PARTNUM IN 1..NPARTS LOOP
IF PARTNUM > 1 THEN
LOB_STRINGAPPEND(OUTPUT, ENDLN);
END IF;
CUBEDEF := ADVISE_DIMENSIONALITY(CUBENAME, SPARSEDEF,
PARTNAME(SPARSENAME_I, PARTNUM),
PARTNUM, ADVTABLE);
IF SPARSEDEF IS NOT NULL THEN
LOB_STRINGAPPEND(OUTPUT, SPARSEDEF);
END IF;
END LOOP;
LOB_STRINGAPPEND(OUTPUT, ENDLN);
LOB_STRINGAPPEND(OUTPUT, 'DEFINE ' || PTNAME || ' PARTITION TEMPLATE-' || ENDLN);
LOB_STRINGAPPEND(OUTPUT, 'PARTITION BY LIST (');
FIRST := TRUE;
OPEN ADV_CV
FOR 'SELECT dimension, partnum FROM ' || ADVTABLE_I ||
' WHERE cubename=:1 AND partby IS NOT NULL' ||
' ORDER BY partnum'
USING CUBENAME;
LOOP
FETCH ADV_CV INTO DIMNAME, PARTNUM;
EXIT WHEN ADV_CV%NOTFOUND;
IF FIRST THEN
FIRST := FALSE;
LOB_STRINGAPPEND(OUTPUT, DIMNAME || ') -' || ENDLN);
LOB_STRINGAPPEND(OUTPUT, ' (');
ELSE
LOB_STRINGAPPEND(OUTPUT, ' -' || ENDLN || '');
END IF;
PARTSTR := PARTNAME(SPARSENAME_I, PARTNUM);
CUBEDEF := ADVISE_DIMENSIONALITY(CUBENAME, SPARSEDEF, PARTSTR,
PARTNUM, ADVTABLE);
CUBEDEF := REGEXP_REPLACE(CUBEDEF, PARTSTR || ']*>',
PARTSTR || '');
LOB_STRINGAPPEND(OUTPUT, 'PARTITION p' || PARTNUM ||
' VALUES () ' || CUBEDEF);
END LOOP;
LOB_STRINGAPPEND(OUTPUT, ')');
OPEN ADV_CV
FOR 'SELECT partnum, partby FROM ' || ADVTABLE_I ||
' WHERE cubename=:1 AND partby IS NOT NULL' ||
' ORDER BY partnum'
USING CUBENAME;
LOOP
FETCH ADV_CV INTO PARTNUM, PARTBY;
EXIT WHEN ADV_CV%NOTFOUND;
LOBLINEITER_INIT(LOBCTX, PARTBY, 32767);
LINENO := 0;
WHILE LOBLINEITER_NEXT(LOBCTX, CUBEDEF) LOOP
IF LENGTH(CUBEDEF) > 0 THEN
IF LINENO = 0 OR REMAINDER(LINENO, 16) = 0 THEN
LOB_STRINGAPPEND(OUTPUT, ENDLN || 'MAINTAIN ' || PTNAME ||
' MOVE TO PARTITION p' || PARTNUM ||
' -' || ENDLN || '');
ELSE
IF LINENO > 0 THEN
LOB_STRINGAPPEND(OUTPUT, ',');
END IF;
IF REMAINDER(LINENO, 4) = 0 THEN
LOB_STRINGAPPEND(OUTPUT, ' -' || ENDLN || '');
END IF;
END IF;
LOB_STRINGAPPEND(OUTPUT, ' ''' || CUBEDEF || '''');
LINENO := LINENO + 1;
END IF;
END LOOP;
END LOOP;
LOB_STRINGAPPEND(OUTPUT, ENDLN);
LOB_STRINGAPPEND(OUTPUT, 'DEFINE ' || CUBENAME || ' ' || DTYPE ||
' VARIABLE > -' || ENDLN);
FIRST := TRUE;
OPEN ADV_CV
FOR 'SELECT partnum FROM ' || ADVTABLE_I ||
' WHERE cubename=:1 AND partby IS NOT NULL' ||
' ORDER BY partnum'
USING CUBENAME;
LOOP
FETCH ADV_CV INTO PARTNUM;
EXIT WHEN ADV_CV%NOTFOUND;
IF FIRST THEN
FIRST := FALSE;
LOB_STRINGAPPEND(OUTPUT, '(');
ELSE
LOB_STRINGAPPEND(OUTPUT, ', -' || ENDLN || ' ');
END IF;
LOB_STRINGAPPEND(OUTPUT, 'PARTITION p' || PARTNUM || ' INTERNAL');
END LOOP;
LOB_STRINGAPPEND(OUTPUT, ')');
DBMS_LOB.CLOSE(OUTPUT);
ELSE
CUBEDEF := ADVISE_DIMENSIONALITY(CUBENAME, SPARSEDEF, SPARSENAME,
ADVTABLE => ADVTABLE);
LOB_STRINGAPPEND(OUTPUT, SPARSEDEF || ENDLN);
LOB_STRINGAPPEND(OUTPUT, 'DEFINE ' || CUBENAME || ' ' || DTYPE ||
' VARIABLE ' || CUBEDEF);
END IF;
END ADVISE_DIMENSIONALITY;
FUNCTION ADVISE_PARTITIONING_DIMENSION(CUBENAME IN VARCHAR2,
DIMSOURCES IN DBMS_AW$_DIMENSION_SOURCES_T,
ADVTABLE IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2
IS
ADVTABLE_I VARCHAR2(100);
PARTDIMVARCHAR2(100);
BEGIN
ADVTABLE_I := ADVISE_TABLE_NAME(ADVTABLE);
EXECUTE IMMEDIATE 'SELECT dimension FROM ' || ADVTABLE_I ||
' WHERE PARTBY IS NOT NULL AND partnum=1 and cubename=:1'
INTO PARTDIM USING CUBENAME;
RETURN PARTDIM;
END ADVISE_PARTITIONING_DIMENSION;
FUNCTION ADVISE_PARTITIONING_LEVEL(CUBENAME IN VARCHAR2,
DIMSOURCES IN DBMS_AW$_DIMENSION_SOURCES_T,
ADVTABLE IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2
IS
ADVTABLE_I VARCHAR2(100);
PARTLEVELVARCHAR2(200);
PARTDIMVARCHAR2(100);
PARTTOPS CLOB;
PARTTOPVARCHAR2(100);
THISCOLVARCHAR2(100);
SRCVAL VARCHAR2(100);
LINECTXLOBLINEITER_T;
NFOUND NUMBER(10,0);
DIMSOURCEDBMS_AW$_DIMENSION_SOURCE_T;
BEGIN
ADVTABLE_I := ADVISE_TABLE_NAME(ADVTABLE);
EXECUTE IMMEDIATE 'SELECT partlevel from (SELECT partlevel, ROWNUM as ' ||
' rown FROM ' || ADVTABLE_I ||
' WHERE cubename=:1 AND partby IS NOT NULL) where rownTRUE);
END;
PROCEDURE AW_ATTACH( NAMEINVARCHAR2,
FORWRITEINBOOLEANDEFAULT FALSE,
CREATEAWINBOOLEANDEFAULT FALSE,
ATTARGS INVARCHAR2 DEFAULT NULL,
TBSPACE INVARCHAR2 DEFAULT NULL) IS
BEGIN
AW_ATTACH('', NAME, FORWRITE, CREATEAW, ATTARGS);
END;
PROCEDURE AW_ATTACH( SCHEMAINVARCHAR2,
NAMEINVARCHAR2,
FORWRITEINBOOLEANDEFAULT FALSE,
CREATEAWINBOOLEANDEFAULT FALSE,
ATTARGS INVARCHAR2 DEFAULT NULL,
TBSPACE INVARCHAR2 DEFAULT NULL)
IS
DBERR01EXCEPTION;
PRAGMA EXCEPTION_INIT(DBERR01, -33262);
RORW VARCHAR(2);
DBNAME VARCHAR2(80);
IATTARGS VARCHAR2(500);
BEGIN
DBNAME := GEN_DBNAME(SCHEMA, NAME);
IF FORWRITE THEN
RORW := 'RW';
ELSE
RORW := 'RO';
END IF;
BEGIN
IATTARGS := ' ' || RORW;
RUN('AW ATTACH ' || DBNAME || IATTARGS, SILENT => TRUE);
EXCEPTION
WHEN DBERR01 THEN BEGIN
IF NOT CREATEAW THEN RAISE; END IF;
AW_CREATE(SCHEMA, NAME, TBSPACE);
END;
END;
END;
PROCEDURE AW_DETACH( NAMEINVARCHAR2) IS
BEGIN
AW_DETACH('', NAME);
END;
PROCEDURE AW_DETACH( SCHEMAINVARCHAR2,
NAMEINVARCHAR2)
IS
DBNAME VARCHAR2(80);
BEGIN
DBNAME := GEN_DBNAME(SCHEMA, NAME);
RUN('AW DETACH ' || DBNAME, SILENT => TRUE);
END;
PROCEDURE AW_CREATE( NAMEINVARCHAR2,
TBSPACE INVARCHAR2 DEFAULT NULL,
PARTNUM IN NUMBER DEFAULT 8) IS
TBARGVARCHAR2(100);
BEGIN
IF TBSPACE IS NULL THEN
TBARG := '';
ELSE
TBARG := ' TABLESPACE ' || TBSPACE;
END IF;
RUN('AW CREATE ' || NAME || ' FIRST ' || TBARG ||
' PARTITIONS ' || PARTNUM, SILENT => TRUE);
END;
PROCEDURE AW_CREATE( SCHEMAINVARCHAR2,
NAMEINVARCHAR2,
TBSPACE INVARCHAR2 DEFAULT NULL)
IS
DBNAME VARCHAR2(80);
TBARGVARCHAR2(100);
BEGIN
DBNAME := GEN_DBNAME(SCHEMA, NAME);
IF TBSPACE IS NULL THEN
TBARG := '';
ELSE
TBARG := ' TABLESPACE ' || TBSPACE;
END IF;
RUN('AW CREATE ' || DBNAME || ' FIRST ' || TBARG, SILENT => TRUE);
END;
PROCEDURE AW_COPY(OLDNAME IN VARCHAR2,
NEWNAME IN VARCHAR2,
NEWTABLESPACE IN VARCHAR2 DEFAULT NULL,
PARTNUM IN NUMBER DEFAULT 8)
IS
BEGIN
AW_COPY('', OLDNAME, NEWNAME, NEWTABLESPACE, PARTNUM);
END;
PROCEDURE AW_COPY(OLDSCHEMA IN VARCHAR2,
OLDNAME IN VARCHAR2,
NEWNAME IN VARCHAR2,
NEWTABLESPACE IN VARCHAR2 DEFAULT NULL,
PARTNUM IN NUMBER DEFAULT 8)
IS
COPYLOBS DBMS_AW.EIF_T;
BEGIN
IF NEWTABLESPACE IS NULL THEN
RUN('aw create '||NEWNAME || ' partitions ' || PARTNUM, SILENT => TRUE);
ELSE
RUN('aw create '||NEWNAME || ' partitions ' || PARTNUM
|| ' tablespace ' || NEWTABLESPACE, SILENT => TRUE);
END IF;
EIF_OUT(OLDSCHEMA, OLDNAME, COPYLOBS);
AW_DETACH(OLDSCHEMA, OLDNAME);
EIF_IN(NEWNAME, COPYLOBS);
EIF_DELETE(COPYLOBS);
RUN('update '||NEWNAME, SILENT => TRUE);
EXCEPTION
WHEN OTHERS THEN BEGIN
AW_DETACH(OLDSCHEMA, OLDNAME);
AW_DETACH(NEWNAME);
AW_DELETE(NEWNAME);
RAISE;
END;
END;
PROCEDURE AW_DELETE( NAMEINVARCHAR2) IS
BEGIN
AW_DELETE('', NAME);
END;
PROCEDURE AW_DELETE( SCHEMAINVARCHAR2,
NAMEINVARCHAR2)
IS
DBNAME VARCHAR2(80);
TBARGVARCHAR2(100);
BEGIN
DBNAME := GEN_DBNAME(SCHEMA, NAME);
RUN('AW DELETE ' || DBNAME, SILENT => TRUE);
END;
PROCEDURE INT_AW_RENAME(INNAMEIN VARCHAR2,
OUTNAME IN VARCHAR2) IS
EXTERNAL NAME "xsawRename"
WITH CONTEXT
PARAMETERS(CONTEXT, INNAME, OUTNAME)
LIBRARY DBMS_AW_LIB;
PROCEDURE AW_RENAME( INNAMEIN VARCHAR2,
OUTNAME IN VARCHAR2)
IS
NEW_IN VARCHAR2(400);
NEW_OUT VARCHAR2(400);
BEGIN
IF INNAME IS NULL OR (INNAME = '') OR OUTNAME IS NULL OR (OUTNAME = '') THEN
RAISE_APPLICATION_ERROR(AWNAME_NULL_ERROR,
'AW name cannot be NULL.');
END IF;
NEW_IN := DBMS_ASSERT.SIMPLE_SQL_NAME(UPPER(INNAME));
NEW_OUT := DBMS_ASSERT.SIMPLE_SQL_NAME(UPPER(OUTNAME));
INT_AW_RENAME(NEW_IN, NEW_OUT);
END;
FUNCTIONAW_TABLESPACE(SCHEMA IN VARCHAR2,
NAME IN VARCHAR2)
RETURN VARCHAR2 AS
TBLNAME VARCHAR2(100);
TBSPVARCHAR2(100);
PARTVARCHAR2(3);
BEGIN
TBLNAME := 'AW$'||UPPER(NAME);
SELECT TABLESPACE_NAME, PARTITIONED INTO TBSP, PART FROM ALL_TABLES
WHERE OWNER=SCHEMA AND TABLE_NAME=TBLNAME;
IF PART = 'YES' THEN
SELECT DEF_TABLESPACE_NAME INTO TBSP FROM ALL_PART_TABLES
WHERE OWNER=SCHEMA AND TABLE_NAME=TBLNAME;
END IF;
RETURN TBSP;
END AW_TABLESPACE;
FUNCTIONAW_TABLESPACE(NAME IN VARCHAR2)
RETURN VARCHAR2 AS
BEGIN
RETURN AW_TABLESPACE(USER, NAME);
END AW_TABLESPACE;
FUNCTIONINT_EIF_BLOB_OUT( SCHEMA IN VARCHAR2, NAME IN VARCHAR2,
OBJLISTIN EIF_OBJLIST_T DEFAULT NULL)
RETURN BLOB IS
EXTERNAL NAME "xseifExportBLOB"
WITH CONTEXT
PARAMETERS(CONTEXT, SCHEMA, NAME, OBJLIST)
LIBRARY DBMS_AW_LIB;
PROCEDURE INT_EIF_BLOB_IN(SCHEMA IN VARCHAR2,
NAME IN VARCHAR2,
IMPLOB IN BLOB,
DATADFNS IN BINARY_INTEGER DEFAULT EIFIMP_DATA,
OBJLISTIN EIF_OBJLIST_T DEFAULT NULL) IS
EXTERNAL NAME "xseifImportBLOB"
WITH CONTEXT
PARAMETERS(CONTEXT, SCHEMA, NAME, IMPLOB, DATADFNS SB4, OBJLIST)
LIBRARY DBMS_AW_LIB;
PROCEDURE INT_EIF_TABLE_OUT(SCHEMA IN VARCHAR2,
NAME IN VARCHAR2,
EXPTABLE OUT EIF_T,
OBJLISTIN EIF_OBJLIST_T DEFAULT NULL,
DFNS IN BOOLEAN DEFAULT FALSE) IS
EXTERNAL NAME "xseifExportTable"
WITH CONTEXT
PARAMETERS(CONTEXT, SCHEMA, NAME, EXPTABLE, OBJLIST, DFNS UB4)
LIBRARY DBMS_AW_LIB;
PROCEDURE INT_EIF_TABLE_IN( SCHEMA IN VARCHAR2,
NAME IN VARCHAR2,
IMPTABLE IN EIF_T,
DATADFNS IN BINARY_INTEGER DEFAULT EIFIMP_DATA,
OBJLISTIN EIF_OBJLIST_T DEFAULT NULL) IS
EXTERNAL NAME "xseifImportTable"
WITH CONTEXT
PARAMETERS(CONTEXT, SCHEMA, NAME, IMPTABLE, DATADFNS SB4, OBJLIST)
LIBRARY DBMS_AW_LIB;
PROCEDURE OCI_CLEAN IS
EXTERNAL NAME "xsociClean"
WITH CONTEXT
PARAMETERS(CONTEXT)
LIBRARY DBMS_AW_LIB;
FUNCTION PROP_VAL(RID IN ROWID)
RETURN VARCHAR2 IS
EXTERNAL NAME "xspgPValGet"
WITH CONTEXT
PARAMETERS(CONTEXT,
RID OCISTRING)
LIBRARY DBMS_AW_LIB;
FUNCTION XSPVALCLOB(RID IN ROWID, OUTLOB IN OUT NOCOPY CLOB)
RETURN BOOLEAN IS
EXTERNAL NAME "xspgPValClob"
WITH CONTEXT
PARAMETERS(CONTEXT,
RID OCISTRING,
OUTLOB)
LIBRARY DBMS_AW_LIB;
FUNCTION PROP_CLOB(RID IN ROWID)
RETURN CLOB AS
BEGIN
IF PROPLOB IS NULL THEN
DBMS_LOB.CREATETEMPORARY(PROPLOB, TRUE);
ELSE
DBMS_LOB.TRIM(PROPLOB, 0);
END IF;
IF XSPVALCLOB(RID, PROPLOB)
THEN
RETURN PROPLOB;
ELSE
RETURN NULL;
END IF;
END;
FUNCTION PROP_LEN(RID IN ROWID)
RETURN NUMBER IS
EXTERNAL NAME "xspgPValLen"
WITH CONTEXT
PARAMETERS(CONTEXT,
RID OCISTRING)
LIBRARY DBMS_AW_LIB;
FUNCTION OLAP_TYPE(OTYPE IN NUMBER)
RETURN VARCHAR2 IS
RETVAL VARCHAR2(20);
BEGIN
CASE OTYPE
WHEN1 THEN RETVAL := 'INTEGER';
WHEN2 THEN RETVAL := 'REAL';
WHEN3 THEN RETVAL := 'REALD';
WHEN4 THEN RETVAL := 'EXP_BOOL';
WHEN5 THEN RETVAL := 'IDN';
WHEN6 THEN RETVAL := 'EXP_CHAR';
WHEN7 THEN RETVAL := 'VSUB';
WHEN8 THEN RETVAL := 'INDXREC';
WHEN9 THEN RETVAL := 'SHINT';
WHEN 10 THEN RETVAL := 'VALUESET';
WHEN 11 THEN RETVAL := 'RAWTEXT';
WHEN 12 THEN RETVAL := 'WKSREC';
WHEN 13 THEN RETVAL := 'EXP_DATE';
WHEN 14 THEN RETVAL := 'EXP_NUMBER';
WHEN 15 THEN RETVAL := 'EXP_DATETIME';
WHEN 16 THEN RETVAL := 'EXP_LONGINT';
WHEN 17 THEN RETVAL := 'EXP_NTEXT';
WHEN 18 THEN RETVAL := 'DVPOSL';
WHEN -5 THEN RETVAL := 'NTEXTF';
WHEN -4 THEN RETVAL := 'BITBOOL';
WHEN -3 THEN RETVAL := 'TINYBOOL';
WHEN -2 THEN RETVAL := 'TINYINT';
WHEN -1 THEN RETVAL := 'CHARF';
ELSE RETVAL := 'NONE';
END CASE;
RETURN RETVAL;
END;
FUNCTION EIF_BLOB_OUT( NAMEINVARCHAR2,
OBJLIST INEIF_OBJLIST_T DEFAULT NULL)
RETURN BLOB IS
MYLOB BLOB;
BEGIN
RETURN EIF_BLOB_OUT('', NAME, OBJLIST);
END;
FUNCTION EIF_BLOB_OUT( SCHEMAINVARCHAR2,
NAMEINVARCHAR2,
OBJLIST INEIF_OBJLIST_T DEFAULT NULL)
RETURN BLOB IS
MYOBJS EIF_OBJLIST_T;
RETLOB BLOB;
BEGIN
IF OBJLIST IS NULL THEN
MYOBJS := EIF_OBJLIST_T();
ELSE
MYOBJS := OBJLIST;
END IF;
AW_ATTACH(SCHEMA, NAME);
RETLOB := INT_EIF_BLOB_OUT(SCHEMA, NAME, MYOBJS);
OCI_CLEAN();
RETURN RETLOB;
END;
PROCEDURE EIF_BLOB_IN( NAMEINVARCHAR2,
IMPLOBINBLOB,
DATADFNSINBINARY_INTEGER DEFAULT EIFIMP_DATA,
OBJLIST INEIF_OBJLIST_TDEFAULT NULL) IS
BEGIN
EIF_BLOB_IN('', NAME, IMPLOB, DATADFNS, OBJLIST);
END;
PROCEDURE EIF_BLOB_IN( SCHEMAINVARCHAR2,
NAMEINVARCHAR2,
IMPLOBINBLOB,
DATADFNSINBINARY_INTEGER DEFAULT EIFIMP_DATA,
OBJLIST INEIF_OBJLIST_TDEFAULT NULL)
IS
MYOBJS EIF_OBJLIST_T;
BEGIN
IF OBJLIST IS NULL THEN
MYOBJS := EIF_OBJLIST_T();
ELSE
MYOBJS := OBJLIST;
END IF;
AW_ATTACH(SCHEMA, NAME, TRUE, TRUE, 'noautogo');
INT_EIF_BLOB_IN(SCHEMA, NAME, IMPLOB, DATADFNS, MYOBJS);
END;
PROCEDURE EIF_OUT( NAMEINVARCHAR2,
EXPEIFOUT EIF_T,
OBJLIST INEIF_OBJLIST_T DEFAULT NULL,
DFNSINBOOLEAN DEFAULT FALSE) IS
BEGIN
EIF_OUT('', NAME, EXPEIF, OBJLIST, DFNS);
END;
PROCEDURE EIF_OUT( SCHEMAINVARCHAR2,
NAMEINVARCHAR2,
EXPEIFOUT EIF_T,
OBJLIST INEIF_OBJLIST_T DEFAULT NULL,
DFNSINBOOLEAN DEFAULT FALSE)
IS
MYOBJS EIF_OBJLIST_T;
BEGIN
IF OBJLIST IS NULL THEN
MYOBJS := EIF_OBJLIST_T();
ELSE
MYOBJS := OBJLIST;
END IF;
AW_ATTACH(SCHEMA, NAME);
INT_EIF_TABLE_OUT(SCHEMA, NAME, EXPEIF, MYOBJS, DFNS);
OCI_CLEAN();
END;
PROCEDURE EIF_IN(NAMEINVARCHAR2,
IMPEIFINEIF_T,
DATADFNSINBINARY_INTEGER DEFAULT EIFIMP_DATA,
OBJLIST INEIF_OBJLIST_TDEFAULT NULL) IS
BEGIN
EIF_IN('', NAME, IMPEIF, DATADFNS, OBJLIST);
END;
PROCEDURE EIF_IN(SCHEMAINVARCHAR2,
NAMEINVARCHAR2,
IMPEIFINEIF_T,
DATADFNSINBINARY_INTEGER DEFAULT EIFIMP_DATA,
OBJLIST INEIF_OBJLIST_TDEFAULT NULL)
IS
MYOBJS EIF_OBJLIST_T;
BEGIN
IF OBJLIST IS NULL THEN
MYOBJS := EIF_OBJLIST_T();
ELSE
MYOBJS := OBJLIST;
END IF;
AW_ATTACH(SCHEMA, NAME, TRUE, TRUE, 'noautogo');
INT_EIF_TABLE_IN(SCHEMA, NAME, IMPEIF, DATADFNS, MYOBJS);
END;
PROCEDURE EIF_DELETE(EIF IN OUT EIF_T) IS
BEGIN
IF EIF IS NULL THEN
RETURN;
END IF;
IF EIF.FIRST IS NULL OR EIF.LAST IS NULL THEN
EIF.DELETE();
RETURN;
END IF;
FOR I IN EIF.FIRST..EIF.LAST LOOP
DBMS_LOB.FREETEMPORARY(EIF(I));
END LOOP;
EIF.DELETE();
EIF.TRIM(EIF.COUNT);
END;
PROCEDURE MOVE_AWMETA(DEST_TBS IN VARCHAR2)
IS
NEW_TBS VARCHAR2(30);
TB_EXIST NUMBER;
CURSOR C1 IS SELECT AWNAME FROM AW$ WHERE AWSEQ#0 THEN
RAISE_APPLICATION_ERROR(HAS_SCHEMA_ERROR,
'Cannot specify schema when converting AW to same name.');
END IF;
IF EVAL_NUMBER(ATTSTR) = 1 AND EVAL_NUMBER(CHGSTR) = 1 THEN
RAISE_APPLICATION_ERROR(AW_CHANGED_ERROR,
'Cannot convert AW because it has been changed but not updated.');
END IF;
RUN('aw attach ' || UPNAME || ' rwx forcemode');
VERSTRING := EVAL_TEXT('aw(format)');
SAVEPOINT DBMS_AW_CONVERT_SP;
IF VERSTRING = '9' THEN
BEGIN
CONVERT(UPNAME, UPNAME);
EXCEPTION
WHEN OTHERS THEN
IF EVAL_NUMBER(ATTSTR) = 1 THEN
RUN('aw detach ' || UPNAME);
END IF;
RAISE;
END;
ELSE
BEGIN
XSAWUPGRADE(UPNAME);
RUN('aw detach ' || UPNAME);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO DBMS_AW_CONVERT_SP;
RUN('aw detach ' || UPNAME);
RAISE;
END;
END IF;
COMMIT;
END;
PROCEDURE CONVERT (OLDNAME IN VARCHAR2,
NEWNAME IN VARCHAR2,
NEWTABLESPACE IN VARCHAR2 DEFAULT NULL) IS
COMPAT VARCHAR2(30);
MAJORVER NUMBER;
DOTPOS NUMBER;
UPOLDNAMEVARCHAR2(30);
UPNEWNAMEVARCHAR2(30);
TEMPNAME VARCHAR2(30);
OLDATTSTRVARCHAR2(100);
OLDCHGSTRVARCHAR2(100);
NEWATTSTRVARCHAR2(100);
TEMPATTSTR VARCHAR2(100);
BEGIN
SELECT VALUE INTO COMPAT FROM V$PARAMETER WHERE NAME = 'compatible';
DOTPOS := INSTR(COMPAT, '.');
MAJORVER := TO_NUMBER(SUBSTR(COMPAT, 1, DOTPOS-1));
IF MAJORVER0 THEN
RAISE_APPLICATION_ERROR(HAS_SCHEMA_ERROR,
'Cannot specify schema when converting AW to same name.');
END IF;
TEMPNAME := RTRIM(SUBSTR(UPOLDNAME, 0, 29)) || '_';
TEMPATTSTR := 'if aw(attached ''' || TEMPNAME || ''') then 1 else 0';
AW_COPY(OLDNAME=>UPOLDNAME, NEWNAME=>TEMPNAME, NEWTABLESPACE=>NEWTABLESPACE);
IF EVAL_NUMBER(OLDATTSTR) = 1 THEN
RUN('aw detach ' || UPOLDNAME);
END IF;
IF EVAL_NUMBER(TEMPATTSTR) = 1 THEN
RUN('aw detach ' || TEMPNAME);
END IF;
AW_DELETE(UPOLDNAME);
AW_RENAME(TEMPNAME, UPOLDNAME);
ELSE
AW_COPY(OLDNAME=>UPOLDNAME, NEWNAME=>UPNEWNAME, NEWTABLESPACE=>NEWTABLESPACE);
END IF;
EXCEPTION
WHEN OTHERS THEN
IF EVAL_NUMBER(OLDATTSTR) = 1 THEN
RUN('aw detach ' || UPOLDNAME);
END IF;
RAISE;
END;
IF EVAL_NUMBER(OLDATTSTR) = 1 THEN
RUN('aw detach ' || UPOLDNAME);
END IF;
IF EVAL_NUMBER(NEWATTSTR) = 1 THEN
RUN('aw detach ' || UPNEWNAME);
END IF;
COMMIT;
END;
PROCEDURE GATHER_STATS IS
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('sys','aw_prop$',CASCADE=>TRUE);
DBMS_STATS.GATHER_TABLE_STATS('sys','aw$',CASCADE=>TRUE);
DBMS_STATS.GATHER_TABLE_STATS('sys','aw_obj$',CASCADE=>TRUE);
DBMS_STATS.GATHER_TABLE_STATS('sys','ps$',CASCADE=>TRUE);
END;
FUNCTION GET_OBJ_PROTECT
RETURN BOOLEAN IS
EXTERNAL NAME "xsSmplGetObjProtect"
LIBRARY DBMS_AW_LIB;
PROCEDURE SET_OBJ_PROTECT(VAL IN BOOLEAN) IS
EXTERNAL NAME "xsSmplSetObjProtect"
PARAMETERS(VAL)
LIBRARY DBMS_AW_LIB;
END DBMS_AW;
|
