/*
--建立Directory,作为测试使用
CREATE USER A4 IDENTIFIED BY A4;
CREATE DIRECTORY MYDIR ON 'E:/ORACLE';
GRANT READ, WRITE ON DIRECTORY MYDIR TO A4;
CONN A4/A4@ORCL
--建立Log表
DROP TABLE LOG;
CREATE TABLE LOG(DT DATE DEFAULT SYSDATE,
USR VARCHAR2(30) DEFAULT USER,
MODULE_NAME VARCHAR2(30),
ACTION_NAME VARCHAR2(32),
SUB_ACTION VARCHAR2(60),
LOCATOR NUMBER(4,1),
TYPE VARCHAR2(30),--TYPE :DEBUG,ERROR,WARNING,INFO
INFO VARCHAR2(1024),
ERRCODE NUMBER,
ERRMSG VARCHAR2(512),
STACK VARCHAR2(1024)
);
--建立EXCEPTION_TABLES表
DROP TABLE EXCEPTION_TABLES;
CREATE TABLE EXCEPTION_TABLES(
TABLE_NAME VARCHAR2(30),
TYPE CHAR(1),--TYPE :P-PK USE,F-FK USE,A-ALL USE
USR VARCHAR2(30),
DT DATE,
STATUS NUMBER(2),--STATUS:0-IDLE,1-ENGAGE,2-LOCKING
CONSTRAINT PK_EXCEPTION_TABLES PRIMARY KEY(TABLE_NAME)
);
CREATE TABLE EXCEPTIONS(
ROW_ID ROWID,
OWNER VARCHAR2(30),
TABLE_NAME VARCHAR2(30),
CONSTRAINT VARCHAR2(30)
);
CREATE TABLE EXCEPTIONS2(
ROW_ID ROWID,
OWNER VARCHAR2(30),
TABLE_NAME VARCHAR2(30),
CONSTRAINT VARCHAR2(30)
);
CREATE TABLE EXCEPTIONS3(
ROW_ID ROWID,
OWNER VARCHAR2(30),
TABLE_NAME VARCHAR2(30),
CONSTRAINT VARCHAR2(30)
);
CREATE TABLE EXCEPTIONS4(
ROW_ID ROWID,
OWNER VARCHAR2(30),
TABLE_NAME VARCHAR2(30),
CONSTRAINT VARCHAR2(30)
);
CREATE TABLE EXCEPTIONS5(
ROW_ID ROWID,
OWNER VARCHAR2(30),
TABLE_NAME VARCHAR2(30),
CONSTRAINT VARCHAR2(30)
);
CREATE TABLE PK_EXCEPTIONS(
ROW_ID ROWID,
OWNER VARCHAR2(30),
TABLE_NAME VARCHAR2(30),
CONSTRAINT VARCHAR2(30)
);
CREATE TABLE PK_EXCEPTIONS2(
ROW_ID ROWID,
OWNER VARCHAR2(30),
TABLE_NAME VARCHAR2(30),
CONSTRAINT VARCHAR2(30)
);
CREATE TABLE FK_EXCEPTIONS(
ROW_ID ROWID,
OWNER VARCHAR2(30),
TABLE_NAME VARCHAR2(30),
CONSTRAINT VARCHAR2(30)
);
CREATE TABLE FK_EXCEPTIONS2(
ROW_ID ROWID,
OWNER VARCHAR2(30),
TABLE_NAME VARCHAR2(30),
CONSTRAINT VARCHAR2(30)
);
INSERT INTO EXCEPTION_TABLES VALUES('EXCEPTIONS','A',NULL,NULL,0);
INSERT INTO EXCEPTION_TABLES VALUES('EXCEPTIONS2','A',NULL,NULL,0);
INSERT INTO EXCEPTION_TABLES VALUES('EXCEPTIONS3','A',NULL,NULL,0);
INSERT INTO EXCEPTION_TABLES VALUES('EXCEPTIONS4','A',NULL,NULL,0);
INSERT INTO EXCEPTION_TABLES VALUES('EXCEPTIONS5','A',NULL,NULL,0);
INSERT INTO EXCEPTION_TABLES VALUES('PK_EXCEPTIONS','P',NULL,NULL,0);
INSERT INTO EXCEPTION_TABLES VALUES('PK_EXCEPTIONS2','P',NULL,NULL,0);
INSERT INTO EXCEPTION_TABLES VALUES('FK_EXCEPTIONS','F',NULL,NULL,0);
INSERT INTO EXCEPTION_TABLES VALUES('FK_EXCEPTIONS2','F',NULL,NULL,0);
--建立字符转化成表的基础类型
DROP FUNCTION CHAR_TO_TABLE;
DROP TYPE CHR2TAB_Tab_Type;
DROP TYPE CHR2TAB_Row_Type;
CREATE TYPE CHR2TAB_Row_Type AS OBJECT (
ITEM VARCHAR2(120)
);
/
CREATE TYPE CHR2TAB_Tab_Type AS TABLE OF CHR2TAB_Row_Type;
/
--建立系统参数表
DROP TABLE SYSPARAM;
CREATE TABLE SYSPARAM(DOMAIN VARCHAR2(32),
NAME VARCHAR2(64),
DEFAULT_VALUE VARCHAR2(512),
VALUE VARCHAR2(512),
ISDYNAMIC CHAR(1),--T-DYNAMIC ,F-NOT DYNAMIC
FORMAT VARCHAR2(128),
TYPE VARCHAR2(12),
DESCRIPTION VARCHAR2(128),
CONSTRAINT PK_SYSPARAM PRIMARY KEY(DOMAIN,NAME)
);
--若需要
conn sys/bing@orcl as sydba
grant select on v_$locked_object to system;
--建立汉字表
DROP TABLE BASE$CHINESE
/
CREATE TABLE BASE$CHINESE
(
Word_ID NUMBER(10,0) NOT NULL,
WORD CHAR(2) NOT NULL,
Word_Spell VARCHAR2(8) NOT NULL,
Word_Initial CHAR(1) NOT NULL,
Word_AscII NUMBER NOT NULL,
CONSTRAINT PK_BASE$CHINESE PRIMARY KEY(Word_ID)
)
/
CREATE UNIQUE INDEX IDX_BASE$CHINESE_WORD ON BASE$CHINESE(WORD,Word_Spell,Word_Initial)
/
COMMENT ON TABLE BASE$CHINESE IS'汉字拼音表（基表，不包含在系统内！）'
/
COMMENT ON COLUMN BASE$CHINESE.Word_ID IS'序列号'
/
COMMENT ON COLUMN BASE$CHINESE.WORD IS'汉字'
/
COMMENT ON COLUMN BASE$CHINESE.Word_Spell IS'拼音'
/
COMMENT ON COLUMN BASE$CHINESE.Word_Initial IS'首字母'
/
--建立返回文本文件整行的基础类型
DROP TYPE TEXT_TAB_Type;
DROP TYPE TEXT_Row_Type;
CREATE TYPE TEXT_Row_Type AS OBJECT (
ID NUMBER,
LINE VARCHAR2(4000),
LINE2 VARCHAR2(4000)
);
/
CREATE TYPE TEXT_TAB_Type AS TABLE OF TEXT_Row_Type;
/
Useage:
CREATE OR REPLACE DIRECTORY DOCUMENTS AS 'C:/';
SELECT * FROM Table(Get_Lines('DOCUMENTS','D.TXT'));
--建立从文本文件读取返回表的基础类型
DROP TYPE FromFile_Tab_Type;
DROP TYPE FromFile_Row_Type;
CREATE TYPE FromFile_Row_Type AS OBJECT (
ID NUMBER,
TEXT01 VARCHAR2(4000),
TEXT02 VARCHAR2(4000),
TEXT03 VARCHAR2(4000),
TEXT04 VARCHAR2(4000),
TEXT05 VARCHAR2(4000),
TEXT06 VARCHAR2(4000),
TEXT07 VARCHAR2(4000),
TEXT08 VARCHAR2(4000),
TEXT09 VARCHAR2(4000),
TEXT10 VARCHAR2(4000),
TEXT11 VARCHAR2(4000),
TEXT12 VARCHAR2(4000),
TEXT13 VARCHAR2(4000),
TEXT14 VARCHAR2(4000),
TEXT15 VARCHAR2(4000),
TEXT16 VARCHAR2(4000),
TEXT17 VARCHAR2(4000),
TEXT18 VARCHAR2(4000)
);
/
CREATE TYPE FromFile_TAB_Type AS TABLE OF FromFile_Row_Type;
/
*/
CREATE OR REPLACE PACKAGE Base_Func AS
--1.DECLARE CONSTANT**************************************************************************
INFO_LENGTH CONSTANT NUMBER(4) :=1024;
SQLERRM_LENGTH CONSTANT NUMBER(3) :=512;
LOG_TYPE_DEBUG CONSTANT VARCHAR2(30) :='DEBUG';
LOG_TYPE_ERROR CONSTANT VARCHAR2(30) :='ERROR';
LOG_TYPE_WARNING CONSTANT VARCHAR2(30) :='WARNING';
LOG_TYPE_INFO CONSTANT VARCHAR2(30) :='INFO';
LOG_STYLE_FORM CONSTANT VARCHAR2(32) :='FORM';
LOG_STYLE_GRID CONSTANT VARCHAR2(32) :='GRID';
BS CONSTANT VARCHAR2(1) :=' ';
RT VARCHAR2(1) :=CHR(13);
LF VARCHAR2(1) :=CHR(10);
TB VARCHAR2(1) :=CHR(9);
EXC_IDLE CONSTANT NUMBER(2) :=0;
EXC_ENGAGE CONSTANT NUMBER(2) :=1;
EXC_LOCKING CONSTANT NUMBER(2) :=2;
LOGTO_FILE CONSTANT VARCHAR2(12) :='FILE';
LOGTO_DB CONSTANT VARCHAR2(12) :='DB';
LOGTO_SCREEN CONSTANT VARCHAR2(12) :='SCREEN';
DATE_FORMAT CONSTANT VARCHAR2(32) :='YYYY-MM-DD HH24:MI:SS';
OUTPUT_MAX_LEN CONSTANT NUMBER :=255;
PUNC_COMMA CONSTANT CHAR(1) :=',';
PUNC_SEMICOLON CONSTANT CHAR(1) :=';';
PUNC_FULLSTOP CONSTANT CHAR(1) :='.';
PUNC_EXCALPOINT CONSTANT CHAR(1) :='!';
PUNC_ZCOMMA CONSTANT NCHAR(1) :='，';
PUNC_ZSEMICOLON CONSTANT NCHAR(1) :='；';
PUNC_ZFULLSTOP CONSTANT NCHAR(1) :='。';
PUNC_ZEXCALPOINT CONSTANT NCHAR(1) :='！';
PUNC_SQUOTION CONSTANT NCHAR(1) :=CHR(39);
PUNC_DQUOTION CONSTANT NCHAR(1) :='"';
DBCHAR_LONG CONSTANT INT :=4000;
--DECLARE SUBTYPE
SUBTYPE SQLSTR IS VARCHAR2(8192);
SUBTYPE SQLWHERE IS VARCHAR2(2048);
SUBTYPE SYSNAME IS VARCHAR2(30);
SUBTYPE INFO IS VARCHAR2(1024);
SUBTYPE TSQLERRM IS VARCHAR2(512);
SUBTYPE TINYCHAR IS VARCHAR2(32);
SUBTYPE MINICHAR IS VARCHAR2(64);
SUBTYPE SHORTCHAR IS VARCHAR2(255);
SUBTYPE LONGCHAR IS VARCHAR2(1024);
SUBTYPE MLONGCHAR IS VARCHAR2(4096);
SUBTYPE BLONGCHAR IS VARCHAR2(32767);
SUBTYPE TEXCMSG IS VARCHAR2(512);
SUBTYPE DBLONGCHAR IS VARCHAR2(4000);
TYPE tExc_Row IS RECORD (
ExcCode NUMBER,
ExcMsg TSQLERRM
);
-- TYPE tExp_Tab IS ARRAY(100) OF VARCHAR2(20);--tExp_Row;
TYPE tExc_Tab IS TABLE OF TSQLERRM INDEX BY BINARY_INTEGER;--tExp_Row;
--DECLARE GLOBAL VARIABLES
TYPE TRefCursor IS REF CURSOR;
LogFile SHORTCHAR;
LogLocation SHORTCHAR;
LogStyle TINYCHAR :=LOG_STYLE_FORM;
Exc_Tab tExc_Tab;
Start_Date Date;
Start_Time NUMBER;
ToFile_Flush_Rows NUMBER;
--*******************************************************************************************
--2.DECLARE PROCEDURE OR FUNCTION****************************************************************
PROCEDURE SetAutoCommit(b BOOLEAN);
/************************************************************************************************
函数描述：调用动态SQL语句包括（DML和DDL语句），但是限制为仅仅有一行的语句。
on_ExitCode返回执行的成功与否。0-没有操作行语句的成功返回,>0-有行操作的语句返回操作的行数,<0-失败的错误码
iv_Dynamic_SQL:传递要动态执行的语句，但是一定是一条语句，而且不带";"的。否则出错。举例："TRUNCATE TABLE MYTABLE1"
Useage:
SET SERVEROUTPUT ON SIZE 10000
Declare
x NUMBER;
BEGIN
Base_Func.Dynamic_Exec(x,'DELETE FROM TX WHERE ID=1');
DBMS_OUTPUT.Put_Line(x);
END;
************************************************************************************************/
PROCEDURE Dynamic_Exec(on_ExitCode OUT NUMBER,iv_Dynamic_SQL IN VARCHAR2);
/***********************************************************************************************
函数描述：调用动态SQL语句包括（DML和DDL语句），但是限制为仅仅有一行的语句。
on_ExitCode返回执行的成功与否。0-没有操作行语句的成功返回,>0-有行操作的语句返回操作的行数,<0-失败的错误码
iv_Dynamic_SQL:传递要动态执行的语句，具体的格式根据iv_DM_Type来确定。iv_DM_Type决定执行动态语句的格式，有两个参数"R"和"X"。若参数是"R"，表明是规范化的动态语句执行，注意不能带"BEGIN"和"END"符号，用户只要书写要执行的语句就行，不同语句之间要用分号分隔。举例：
"TRUNCATE TABLE MD_INV_STYLE;TRUNCATE TABLE MD_CAL;INSERT INTO MD_CAL VALUES(100,'CAL_NAME',6)"
若参数是"X"或者为其他值，表明是用户自定义的的动态语句执行，完全由用户控制结构和语法，系统不做任何改动直接执行，不同语句之间要用分号分隔。注意对于DDL操作（比如"TRUNCATE TABLE" 等操作），要用动态处理。举例：
"DECLARE X NUMBER;BEGIN DELETE FROM MYTABLE1;MYPACKAGE.MAINPROC(X);END;"
"DECLARE X NUMBER;BEGIN DELETE FROM MYTABLE1;MYPROCEDURE1(X);EXECUTE IMMDIATE ''TRUNCA TABLE MYTABLE2'';END;"
Useage:
SET SERVEROUTPUT ON SIZE 10000
Declare
x NUMBER;
BEGIN
Base_Func.Dynamic_Exec(x,'DECLARE X NUMBER;BEGIN DELETE FROM TX WHERE ID=3;MYPACKAGE1.MAINPROC(X);END;','X');
DBMS_OUTPUT.Put_Line(x);
END;
************************************************************************************************/
PROCEDURE Dynamic_Exec(on_ExitCode OUT NUMBER,iv_Dynamic_SQL IN VARCHAR2,iv_DM_Type IN VARCHAR2);
FUNCTION Dynamic_Exec(iv_Dynamic_SQL VARCHAR2) RETURN NUMBER;
FUNCTION Dynamic_Exec(iv_Dynamic_SQL IN VARCHAR2,iv_DM_Type IN VARCHAR2) RETURN NUMBER;
--***********************************************************************************************
/***********************************************************************************************
函数描述：执行动态语句，并且返回带有OUT参数（仅仅有一个参数，若多个过程，可以多次调用，多一个过程中多个OUT返回参数，不能用此函数）调用的返回值，这个函数包括三个重载函数
调用动态SQL语句包括（DML语句）返回结果，但是不限制语句的行数（执行多行动态SQL语句使用此函数）。
iv_Dynamic_SQL:传递要动态执行的语句，格式要正确，按照标准规范传递此语句，注意一定带"BEGIN"和"END"符号，用户只要书写要执行的语句就行，不同语句之间要用分号分隔。
iv_Init_Val:用于表明返回参数的类型和参数的初始化数值，举例：
另外返回值要只能是一个，并且带有邦定变量的形式，带有冒号":"，比如:
"BEGIN DELETE FROM STUDENT_TABLE;MYPACKAGE1.MAINPROC2(:X);END;"
调用之后，函数返回值返回过程调用返回值"X"
Useage:
SET SERVEROUTPUT ON SIZE 10000
Declare
x NUMBER;
BEGIN
x :=Base_Func.Dynamic_Returning('DECLARE X NUMBER;BEGIN select count(*) into :x from user_tables;END;',100);
DBMS_OUTPUT.Put_Line(x);
END;
************************************************************************************************/
FUNCTION Dynamic_Returning(iv_Dynamic_SQL IN VARCHAR2,iv_Init_Val IN VARCHAR2) RETURN VARCHAR2;
FUNCTION Dynamic_Returning(iv_Dynamic_SQL IN VARCHAR2,in_Init_Val IN NUMBER) RETURN NUMBER;
FUNCTION Dynamic_Returning(iv_Dynamic_SQL IN VARCHAR2,id_Init_Val IN DATE) RETURN DATE;
--***********************************************************************************************
/***********************************************************************************************
函数描述：执行动态语句，并且返回结果集的语句（只能返回一行数据，只能有一列，就是一个结果）调用的返回值，这个函数包括三个重载函数
调用动态SQL语句包括（DML和DDL语句），只能执行一条动态SQL语句。
iv_Dynamic_SQL:传递要动态执行的语句，格式要正确，按照标准规范传递此语句，注意一定不能带"BEGIN"和"END"符号，也不能带";"。
iv_Init_Val:用于表明返回参数的类型和参数的初始化数值，举例：
"SELECT SYSDATE FROM DUAL"或者"SELECT COUNT(*) FROM USER_TABLES"
Useage:
SET SERVEROUTPUT ON SIZE 10000
Declare
x DATE;
BEGIN
x :=Base_Func.Dynamic_Result('SELECT SYSDATE+2 FROM DUAL',SYSDATE);
DBMS_OUTPUT.Put_Line(x);
END;
************************************************************************************************/
FUNCTION Dynamic_Result(iv_Dynamic_SQL IN VARCHAR2,iv_Init_Val IN VARCHAR2) RETURN VARCHAR2;
FUNCTION Dynamic_Result(iv_Dynamic_SQL IN VARCHAR2,in_Init_Val IN NUMBER) RETURN NUMBER;
FUNCTION Dynamic_Result(iv_Dynamic_SQL IN VARCHAR2,id_Init_Val IN DATE) RETURN DATE;
--***********************************************************************************************
/************************************************************************************************
函数描述：对于由于某些原因处于无效(invalidated)状态的对象进行编译
iv_Object_Name:传递对象名称
iv_Owner:传递对象的Owner
Usage:
BEGIN
Base_Func.Compile_Object('MYPROCEDURE1');
END;
-------------------------------------------
BEGIN
Base_Func.Compile_Object('MYOWNER','MYPROCEDURE1');
END;
************************************************************************************************/
PROCEDURE Compile_Object(iv_Object_Name IN VARCHAR2);
PROCEDURE Compile_Object(iv_Owner VARCHAR2,iv_Object_Name IN VARCHAR2);
--***********************************************************************************************
/************************************************************************************************
函数描述：截断表
on_ExitCode:返回值,0-成功,<0-错误码
iv_Table_Name:要截断的表明成
iv_Owner:对象的Owner
Usage:
DECLARE
X NUMBER;
BEGIN
Base_Func.Truncate_Table(X,'MYTABLE1');
Base_Func.Truncate_Table(X,'MYOWNER','MYTABLE1');
END;
************************************************************************************************/
PROCEDURE Truncate_Table(on_ExitCode OUT NUMBER,iv_Table_Name VARCHAR2);
PROCEDURE Truncate_Table(on_ExitCode OUT NUMBER,iv_Owner VARCHAR2,iv_Table_Name VARCHAR2);
/************************************************************************************************
函数描述：删除表中某些记录
on_ExitCode:返回值,>=0-删除成功,记录删除的行数,<0-错误码
iv_Table_Name:要删除的表名称
iv_Owner:对象的Owner
iv_Del_Where:删除条件
Usage:
DECLARE
X NUMBER;
BEGIN
Base_Func.Delete_Table(X,'MYTABLE1','ID>1');
Base_Func.Delete_Table(X,'MYOWNER','MYTABLE1');
END;
************************************************************************************************/
PROCEDURE Delete_Table(on_ExitCode OUT NUMBER,iv_Table_Name VARCHAR2,iv_Del_Where VARCHAR2);
PROCEDURE Delete_Table(on_ExitCode OUT NUMBER,iv_Owner VARCHAR2,iv_Table_Name VARCHAR2,iv_Del_Where VARCHAR2);
/************************************************************************************************
函数描述：获得Exception Table名称
Usage:
Base_Func.Get_Exception_Tab;
************************************************************************************************/
FUNCTION Get_Exception_Tab RETURN VARCHAR2;
/************************************************************************************************
函数描述：获得Exception Table名称
iv_Type:传递Exception Table类型
Usage:
Base_Func.Get_Exception_Tab('P');
Base_Func.Get_Exception_Tab('F');
************************************************************************************************/
FUNCTION Get_Exception_Tab(iv_Type VARCHAR2) RETURN VARCHAR2;
/************************************************************************************************
函数描述：重置Exception Table的状态
Usage:
Base_Func.Reset_Exception_Tab;
************************************************************************************************/
PROCEDURE Reset_Exception_Tab;
/************************************************************************************************
函数描述：重置Exception Table的状态
iv_Exeception_Table:传递Exception Table名称
Base_Func.Reset_Exception_Tab('EXCEPTIONS');
************************************************************************************************/
PROCEDURE Reset_Exception_Tab(iv_Exeception_Table VARCHAR2);
/************************************************************************************************
函数描述：更改主键的状态为ENABLED或者DISABLED
on_ExitCode:返回执行结果，0-正确，<0错误码
iv_Owner:所有者
iv_Table_Name:表名称
iv_Status:要修改的状态
iv_ExceptionTable:重复记录处理所使用的Exception Table
Usage:
DECLARE
X NUMBER;
BEGIN
Base_Func.Alter_PKey(X,'MYOWNER','MYTABLE1','ENABLE');
Base_Func.Alter_PKey(X,'MYOWNER','MYTABLE2','DISABLE');
END;
------------------------------------------------------------------------------------------------
DECLARE
X NUMBER;
BEGIN
Base_Func.Alter_PKey(X,'MYOWNER','MYTABLE1','ENABLE','PK_EXCEPTIONS');
Base_Func.Alter_PKey(X,'MYOWNER','MYTABLE2','DISABLE','PK_EXCEPTIONS2');
END;
************************************************************************************************/
PROCEDURE Alter_PKey(on_ExitCode OUT NUMBER,iv_Owner VARCHAR2,iv_Table_Name VARCHAR2,iv_Status VARCHAR2);
PROCEDURE Alter_PKey(on_ExitCode OUT NUMBER,iv_Owner VARCHAR2,iv_Table_Name VARCHAR2,iv_Status VARCHAR2,iv_ExceptionTable VARCHAR2);
/************************************************************************************************
函数描述：设置系统Session的执行状态
iv_Module_Name:模块名称
iv_Action_Name:行为名称
Usage:
BEGIN
Base_Func.Application_Info('BASE_FUNC','ALTER_PKEY');
END;
************************************************************************************************/
PROCEDURE Application_Info(iv_Module_Name VARCHAR2,iv_Action_Name VARCHAR2);
/************************************************************************************************
函数描述：设置系统Session的客户端系统名称
iv_Module_Name:模块名称
Usage:
BEGIN
Base_Func.Client_Info('MySystemClient');
END;
************************************************************************************************/
PROCEDURE Client_Info(iv_Module_Name VARCHAR2);
/************************************************************************************************
设置系统日志处理标志
b:处理标志 TRUE-写日志,FALSE-不写日志
Usage:
BEGIN
Base_Func.Set_LogFlag(TRUE);
END;
************************************************************************************************/
PROCEDURE Set_LogFlag(b BOOLEAN);
/************************************************************************************************
日志记录主函数
iv_Module_Name:模块名称
iv_Action_Name:动作名称
iv_Sub_Action:子动作名称
in_SQLCode:
in_Locator:位置
iv_Type:类型,DEBUG-调试,ERROR-错误,WARNING-警告,INFO-通知
iv_Info:错误的详细信息
Usage:
BEGIN
Base_Func.Log('BASE_FUNC','ALTER_PKEY','DELETE DUP ROWS',-22,20,'ERROR','table isn't exists');
END;
************************************************************************************************/
PROCEDURE Log(iv_Module_Name VARCHAR2,iv_Action_Name VARCHAR2,iv_Sub_Action VARCHAR2,in_SQLCode NUMBER,in_Locator NUMBER,iv_Type VARCHAR2,iv_Info INFO);
/************************************************************************************************
设置系统日志输出的目标
iv_LogTo:输出目标参数,FILE-输出到文件,DB-输出到数据库,SCREEN-输出到屏幕
Usage:
BEGIN
Base_Func.Set_LogTo('SCREEN');
END;
************************************************************************************************/
PROCEDURE Set_LogTo(iv_LogTo VARCHAR2);
/************************************************************************************************
设置系统日志文件名称
iv_LogFile:输出文件名称参数
Usage:
BEGIN
Set_LogFile('mylog20050322.txt');
END;
************************************************************************************************/
PROCEDURE Set_LogFile(iv_LogFile VARCHAR2);
/************************************************************************************************
设置系统日志文件位置
iv_LogFile:输出文件位置参数
Usage:
BEGIN
Base_Func.Set_LogLocation('E:/Oracle');
END;
----------------------------------------
BEGIN
Set_LogLocation('TEXTOUTPUT');
END;
************************************************************************************************/
PROCEDURE Set_LogLocation(iv_LogLocation VARCHAR2);
/************************************************************************************************
设置系统日志屏幕输出的风格
iv_LogStyle:输出文件风格参数,GRID-表格样式,FORM-表单样式
Usage:
BEGIN
Base_Func.Set_LogStyle('GRID');
END;
----------------------------------------
BEGIN
Base_Func.Set_LogStyle('FORM');
END;
************************************************************************************************/
PROCEDURE Set_LogStyle(iv_LogStyle VARCHAR2);
/************************************************************************************************
设置系统日志屏幕输出的风格
iv_LogStyle:输出文件风格参数,GRID-表格样式,FORM-表单样式
Usage:
BEGIN
Base_Func.Set_LogStyle('GRID');
END;
----------------------------------------
BEGIN
Base_Func.Set_LogStyle('FORM');
END;
************************************************************************************************/
PROCEDURE Screen_Output(iv_Output VARCHAR2,Fmt_Flag NUMBER DEFAULT 0);
/************************************************************************************************
函数描述：取得某一时间所在周的周一的日期
id_Dttm:输入日期
iv_ISO_Flg:输出标志，默认为TRUE,TRUE-按照ISO模式返回,FALSE-按照普通模式返回.
Usage:
DECLARE
X DATE;
BEGIN
X :=Base_Func.Monday_Of_Week(SYSDATE,TRUE);
END;
************************************************************************************************/
FUNCTION Monday_Of_Week(id_Dttm DATE,iv_ISO_Flg VARCHAR2 DEFAULT 'TRUE') RETURN DATE;
/************************************************************************************************
函数描述：取得某一时间所在月的第一天的日期
id_Dttm:输入日期
Usage:
DECLARE
X DATE;
BEGIN
X :=Base_Func.FirstDay_Of_Month(SYSDATE,TRUE);
END;
************************************************************************************************/
FUNCTION FirstDay_Of_Month(id_Dttm DATE) RETURN DATE;
/************************************************************************************************
函数描述：取得系统配置参数
iv_Domain:参数所属范围
iv_Name:参数名称
ov_Value:参数的默认值
Usage:
DECLARE
X NUMBER;
BEGIN
X :=Base_Func.Get_SysParam('DOMAIN1','MAX_ID',10000);
END;
************************************************************************************************/
PROCEDURE Get_SysParam(iv_Domain VARCHAR2,iv_Name VARCHAR2,ov_Value IN OUT VARCHAR2);
PROCEDURE Get_SysParam(iv_Domain VARCHAR2,iv_Name VARCHAR2,on_Value IN OUT NUMBER);
PROCEDURE Get_SysParam(iv_Domain VARCHAR2,iv_Name VARCHAR2,od_Value IN OUT DATE);
/************************************************************************************************
函数描述：取得系统基本信息,包括版本、作者、描述
Usage:
SELECT Base_Func.Get_Version FROM DUAL;
SELECT Base_Func.Get_Author FROM DUAL;
SELECT Base_Func.Get_Descr FROM DUAL;
************************************************************************************************/
FUNCTION Get_Version RETURN VARCHAR2;
FUNCTION Get_Author RETURN VARCHAR2;
FUNCTION Get_Descr RETURN VARCHAR2;
--***********************************************************************************************
/************************************************************************************************
函数描述：输出数据库中的CLOB和BLOB数据到文件
iv_FileName:文件名称
iv_Directory:目录名称
ic_Clob:CLOB数据
Usage:
BEGIN
Base_Func.ExportBlog('MyClob1.txt','MyDirectory1',[CLOB TYPE]);
END;
************************************************************************************************/
PROCEDURE ExportBlob(iv_FileName VARCHAR2,iv_Directory VARCHAR2,ic_Clob CLOB);
/************************************************************************************************
函数描述：返回所有的字段构造的字符串列表
iv_Table_Name:表名称
Usage:
SELECT Base_Func.Get_All_Cols('MYTABLE1') FROM DUAL;
************************************************************************************************/
FUNCTION Get_All_Cols(iv_Table_Name VARCHAR2) RETURN VARCHAR2;
/************************************************************************************************
函数描述：返回访问某表的SQL语句
iv_Table_Name:表名称
Flag:标识 默认为0，0-使用"*"，<>0-使用所有的字段字符串列表
iv_Column_List:字符串字段列表
iv_Where:限制条件
************************************************************************************************/
FUNCTION GetSQL(iv_Table_Name VARCHAR2,Flag NUMBER DEFAULT 0) RETURN VARCHAR2;
FUNCTION GetSQL(iv_Table_Name VARCHAR2,iv_Column_List VARCHAR2) RETURN VARCHAR2;
FUNCTION GetSQL(iv_Table_Name VARCHAR2,iv_Column_List VARCHAR2,iv_Where VARCHAR2) RETURN VARCHAR2;
/************************************************************************************************
函数描述：返回所有的字段构造的逗号分隔的字符串列表
iv_Owner:拥有者
iv_Table_Name:表名称
Usage:
SELECT Base_Func.get_all_cols('MYOWNER','MYTABLE1') FROM DUAL;
************************************************************************************************/
FUNCTION Get_All_Cols(iv_Owner VARCHAR2,iv_Table_Name VARCHAR2) RETURN VARCHAR2;
/************************************************************************************************
函数描述：返回访问某表的SQL语句
iv_Table_Name:表名称
Flag:标识 默认为0，0-使用"*"，<>0-使用所有的字段字符串列表
iv_Column_List:字符串字段列表
iv_Where:限制条件
Usage:
SELECT Base_Func.GetSQL2('MYOWNER','MYTABLE1',0) FROM DUAL;
SELECT Base_Func.GetSQL2('MYOWNER','MYTABLE1',0,'ID,NAME','ID>8') FROM DUAL;
************************************************************************************************/
FUNCTION GetSQL2(iv_Owner VARCHAR2,iv_Table_Name VARCHAR2,Flag NUMBER DEFAULT 0) RETURN VARCHAR2;
FUNCTION GetSQL2(iv_Owner VARCHAR2,iv_Table_Name VARCHAR2,iv_Column_List VARCHAR2) RETURN VARCHAR2;
FUNCTION GetSQL2(iv_Owner VARCHAR2,iv_Table_Name VARCHAR2,iv_Column_List VARCHAR2,iv_Where VARCHAR2) RETURN VARCHAR2;
/************************************************************************************************
函数描述：将确定分隔符的字符串转化成表返回
i_Char:字符串
i_Split:分隔符
Useage:
SELECT * FROM TABLE(Base_Func.CHAR_TO_TABLE('JIANGBING,ZHANGLIRONG,CHENYANXIA',','));
************************************************************************************************/
FUNCTION Char_To_Table(i_Char VARCHAR2,i_Split VARCHAR2) RETURN CHR2TAB_Tab_Type;
/************************************************************************************************
函数描述：将表中某个字段的值转化成指定分隔符分隔的字符串返回
iv_Owner:所有者
iv_Table_Name:表
iv_Column_List:字段列表,不论几个字段的返回，必须转化成字符串，并且格式化好.举例:to_char(id)||','||Rtrim(name)
iv_Where:限制条件
i_Split:分隔符
Useage:
SELECT Base_Func.Table_To_Char('MYTABLE1','TO_CHAR(ID)||'',''||TO_CHAR(DT,''YYYY-MM-DD HH24:MI:SS'')) FROM DUAL;
************************************************************************************************/
FUNCTION Table_To_Char(iv_Table_Name VARCHAR2,iv_Column_List VARCHAR2,iv_Where VARCHAR2,iv_Split VARCHAR2) RETURN VARCHAR2;
FUNCTION Table_To_Char(iv_Owner VARCHAR2,iv_Table_Name VARCHAR2,iv_Column_List VARCHAR2,iv_Where VARCHAR2,iv_Split VARCHAR2) RETURN VARCHAR2;
FUNCTION Table_To_Char(iv_SQL VARCHAR2,iv_Split VARCHAR2) RETURN VARCHAR2;
/************************************************************************************************
函数描述：将表中返回的结果集(字符格式)输出到文件
iv_Location:输出位置
iv_File_Name:输出文件名称
iv_SQL:查询的语句
iv_Split:分隔符
Useage:
BEGIN
Base_Func.Table_To_File('MYDIR','XX.TXT','SELECT ID||CHR(9)||NAME FROM TX',NULL);
END;
************************************************************************************************/
PROCEDURE Table_To_File(iv_Location VARCHAR2,iv_File_Name VARCHAR2,iv_SQL VARCHAR2,iv_Split VARCHAR2);
/************************************************************************************************
函数描述：从指定分隔符的字符串中得到某个位置的字符串
i_Char:字符串
ii_ID:位置
i_Split:分隔符
Useage:
SELECT Base_Func.Get_Char_Text('JIANGBING;CHENYANXIA;ZHANGLIRONG;LIWEN;',2,';') FROM DUAL;
************************************************************************************************/
FUNCTION Get_Char_Text(i_Char VARCHAR2,ii_ID INT,i_Split VARCHAR2) RETURN VARCHAR2;
/************************************************************************************************
函数描述：从指定分隔符的字符串中得到某个位置的字符串
i_Char:字符串
ii_ID:位置
i_Split:分隔符
Useage:
SELECT * FROM TABLE(Base_Func.Get_Lines('MYDIR','XX.TXT'));
************************************************************************************************/
FUNCTION Get_Lines(iv_Location VARCHAR2,iv_File_Name VARCHAR2) RETURN TEXT_TAB_Type;
/************************************************************************************************
函数描述：从指定分隔符的字符串中得到某个位置的字符串
iv_Location:字符串
iv_File_Name:位置
ii_Col_Num:分隔符
iv_Col_Split
iv_FirstRow_IsData
Useage:
SELECT * FROM Table(Get_Text('MYDIR','D.TXT',2,CHR(9),'N'));
************************************************************************************************/
FUNCTION Get_Text(iv_Location VARCHAR2,iv_File_Name VARCHAR2,ii_Col_Num INT,iv_Col_Split VARCHAR2,iv_FirstRow_IsData CHAR) RETURN FromFile_Tab_Type;
/************************************************************************************************
函数描述：MD5加密
iv_UserName:登录名称
iv_Password:登录密码
Useage:
SET SERVEROUTPUT ON SIZE 100000
DECLARE
X VARCHAR2(60);
BEGIN
X :=Base_Func.MD5('JIANGBING','123456');
DBMS_OUTPUT.PUT_LINE(X);
END;
-------------------------------------------
SET SERVEROUTPUT ON SIZE 100000
DECLARE
X VARCHAR2(60);
BEGIN
X :=Base_Func.MD5('123456');
DBMS_OUTPUT.PUT_LINE(X);
END;
************************************************************************************************/
FUNCTION MD5(iv_UserName VARCHAR2,iv_Password VARCHAR2) RETURN VARCHAR2;
FUNCTION MD5(iv_Password VARCHAR2) RETURN VARCHAR2;
/************************************************************************************************
函数描述：加密
iv_Text:要加密的文本
Useage:
SET SERVEROUTPUT ON SIZE 100000
DECLARE
X RAW(20);
Y VARCHAR2(20);
BEGIN
X :=Base_Func.Encrypt('123456');
DBMS_OUTPUT.PUT('X is:');
DBMS_OUTPUT.PUT_LINE(X);
Y :=Base_Func.Decrypt(X);
DBMS_OUTPUT.PUT('Y is:');
DBMS_OUTPUT.PUT_LINE(Y);
END;
************************************************************************************************/
FUNCTION Encrypt (iv_Text IN VARCHAR2) RETURN RAW;
FUNCTION Decrypt (ir_Raw IN RAW) RETURN VARCHAR2;
/************************************************************************************************
函数描述：位运算
ii_Int1:位运算数值1
ii_Int2:位运算数值2
Useage:
DECLARE
X NUMBER;
BEGIN
X :=Base_Func.BAnd(12,2);
END;
-------------------------------------------
DECLARE
X NUMBER;
BEGIN
X :=Base_Func.BOr(12,2);
END;
-------------------------------------------
DECLARE
X NUMBER;
BEGIN
X :=Base_Func.BXor(12,2);
END;
************************************************************************************************/
FUNCTION BAnd(ii_Int1 INT,ii_Int2 INT) RETURN INT;
FUNCTION BOr(ii_Int1 INT,ii_Int2 INT) RETURN INT;
FUNCTION BXor(ii_Int1 INT,ii_Int2 INT) RETURN INT;
/************************************************************************************************
函数描述：根据自定义消息码，返回自定义异常的消息内容
ExcCode:自定义消息码
Useage:
DECLARE
X VARCHAR2(1024);
BEGIN
X :=Base_Func.ExcMsg(-20001);
END;
************************************************************************************************/
FUNCTION ExcMsg(ExcCode NUMBER) RETURN VARCHAR2;
/************************************************************************************************
函数描述：删除表中的特定条件下的重复行
iv_Table_Name:表名称
iv_Unique_Fld_List:唯一条件的字段列表
iv_Join_Cond:联结条件,举例:A.Table_ID=B.Table_ID 或者A.ID1=B.ID1 and A.ID2=B.ID2
Useage:
DECLARE
X VARCHAR2(1024);
BEGIN
X :=Base_Func.ExcMsg(-20001);
END;
************************************************************************************************/
PROCEDURE Delete_Dup_Rows(iv_Table_Name VARCHAR2,iv_Unique_Fld_List VARCHAR2);
PROCEDURE Delete_Dup_Rows2(iv_Table_Name VARCHAR2,iv_Join_Cond VARCHAR2);
PROCEDURE Delete_Dup_Rows3(iv_Table_Name VARCHAR2,iv_Join_Cond VARCHAR2);
/************************************************************************************************
函数描述：将字符串倒排
x:字符串
Useage:
SELECT Base_Func.RevString('JIANGBING') FROM DUAL;
************************************************************************************************/
FUNCTION RevString(x VARCHAR2) RETURN VARCHAR2;
/************************************************************************************************
函数描述：将字段的内容以一定格式的字符串输出
iv_SQL:输出SQL语句
iv_Split:分隔符,默认为逗号
Useage:
SELECT Base_Func.ROWTOCOL('SELECT NAME FROM TX',',') FROM DUAL;
************************************************************************************************/
FUNCTION RowToCol( iv_SQL In VARCHAR2,iv_Split In VARCHAR2 DEFAULT ',' ) RETURN VARCHAR2;
/************************************************************************************************
函数描述：生成表复制脚本
iv_LogonString:执行输出的脚本所使用登陆串，模式为:user/password@orcl
iv_Src_Owner:源用户
iv_Dest_Owner:目标用户
iv_Filter:过滤条件
iv_How_Filter:如何过滤,就是说"%"加到iv_Filter的哪个部分,前面？后面？还是前后都加.BEFORE-加到前面,AFTER-加到后面,ALL-前后都加
iv_Out_Target:输出目标,SCREEN-输出到屏幕,FILE-输出到文件
iv_Copy_Type:复制类型,APPEND-增加,CREATE-创建,INSERT-插入,REPLACE-替换
iv_Table_Suffix:复制生成的表的后缀
iv_File_Name:输出的文件名称
Useage:
BEGIN
Base_Func.Copy_Tab_Srpt_Gen('system/bing@orcl','A4','%');
Base_Func.Copy_Tab_Srpt_Gen('system/bing@orcl','A4','MYOWNER','EXCEPTIONS','BEFORE','SCREEN','CREATE','XX',NULL,NULL);
END;
************************************************************************************************/
PROCEDURE Copy_Tab_Srpt_Gen(iv_LogonString VARCHAR2,iv_Src_Owner VARCHAR2,iv_Dest_Owner VARCHAR2,iv_Filter VARCHAR2,iv_How_Filter VARCHAR2,iv_Out_Target VARCHAR2,iv_Copy_Type VARCHAR2,iv_Table_Suffix VARCHAR2,iv_Location VARCHAR2,iv_File_Name VARCHAR2);
PROCEDURE Copy_Tab_Srpt_Gen(iv_LogonString VARCHAR2,iv_Src_Owner VARCHAR2,iv_Filter VARCHAR2);
/************************************************************************************************
函数描述：获得服务器系统时间
Useage:
SELECT Base_Func..GetDate FROM DUAL;
************************************************************************************************/
FUNCTION GetDate RETURN DATE;
/************************************************************************************************
函数描述：获得服务器本地IP地址
Useage:
SELECT Base_Func..GetLocalIP FROM DUAL;
************************************************************************************************/
FUNCTION GetLocalIP RETURN VARCHAR2;
/************************************************************************************************
函数描述：根据IP地址获得服务器主机名称
IP:IP地址
Useage:
SELECT BASE_FUNC.GetHostName('192.168.100.2') FROM DUAL;
************************************************************************************************/
FUNCTION GetHostName(IP VARCHAR2) RETURN VARCHAR2;
/************************************************************************************************
函数描述：根据服务器主机名称获得IP地址
Host:服务器主机名称
Useage:
SELECT Base_Func.GetHostName('192.168.100.2') FROM DUAL;
************************************************************************************************/
FUNCTION GetIPAddr(Host VARCHAR2) RETURN VARCHAR2;
/************************************************************************************************
函数描述：发送邮件
sender:发送人
recipient:接收人
subject:主题
message:消息体内容
Useage:
BEGIN
Base_Func.Send_Mail('jiangbing@lenovoai.com','is-null@sohu.com','How do you do?','are you a good man?');
END;
************************************************************************************************/
PROCEDURE Send_Mail(sender IN VARCHAR2,recipient IN VARCHAR2,subject IN VARCHAR2,message IN VARCHAR2);
/************************************************************************************************
函数描述：取得汉字字符串的首字母组合
iv_Phrase:汉字字符串
iv_Split:首字母的分隔符
iv_First_Name_Split:
iv_Case:大小写标志,U-大写,L-小写,C-首字母大写
iv_Order:顺序 REVERSE-倒序
Useage:
select base_func.GetWordInitial('将饼') from dual;
select base_func.GetWordInitial('将饼历史',',','.','L',null) from dual;
************************************************************************************************/
FUNCTION GetWordInitial(iv_Phrase VARCHAR2) RETURN VARCHAR2;
FUNCTION GetWordInitial(iv_Phrase VARCHAR2,iv_Split VARCHAR2,iv_First_Name_Split VARCHAR2,iv_Case VARCHAR2,iv_Order VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
/************************************************************************************************
函数描述：取得汉字字符串的首字母组合
iv_Phrase:汉字字符串
Useage:
select base_func.GetWordSpell('将饼') from dual;
************************************************************************************************/
FUNCTION GetWordSpell(iv_Phrase VARCHAR2) RETURN VARCHAR2;
/************************************************************************************************
函数描述：取得汉字字符串的拼音组合
iv_Phrase:汉字字符串
iv_Split:首字母的分隔符
iv_First_Name_Split:
iv_Case:大小写标志,U-大写,L-小写,C-首字母大写
iv_Order:顺序 REVERSE-倒序
Useage:
select base_func.GetWordSpell('将饼历史',',','.','L',null) from dual;
************************************************************************************************/
FUNCTION GetWordSpell(iv_Phrase VARCHAR2,
iv_Split VARCHAR2,
iv_First_Name_Split VARCHAR2,
iv_Case VARCHAR2,
iv_Order VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
/************************************************************************************************
函数描述：得到汉字的在系统中定义的ID
iv_Word:汉字
Useage:
select base_func.GetWordID('将') from dual;
************************************************************************************************/
FUNCTION GetWordID(iv_Word VARCHAR2) RETURN NUMBER;
/************************************************************************************************
函数描述：得到汉字的ASCII码
iv_Word:汉字
Useage:
select base_func.GetWordAscii('将') from dual;
************************************************************************************************/
FUNCTION GetWordAscii(iv_Word VARCHAR2) RETURN NUMBER;
/************************************************************************************************
函数描述：根据汉字的ASCII码等到汉字
iv_Word:ASCII码
Useage:
select base_func.GetWord(23558) from dual;
************************************************************************************************/
FUNCTION GetWord(ii_Ascii INT) RETURN CHAR;
END Base_Func;
/
CREATE OR REPLACE PACKAGE BODY Base_Func AS
AutoCommitFlag BOOLEAN :=FALSE;
LogFlag BOOLEAN :=FALSE;
LogTo VARCHAR2(12) :=LOGTO_DB;
Current_Action VARCHAR2(32);
Module_Name SYSNAME :='BASE_FUNC';
VERSION VARCHAR2(18) :='1.1.0.001.20050322';
AUTHOR VARCHAR2(60) :='jiangbing@lenovoai.com';
DESCR VARCHAR2(120) :='ORACLE数据库开发基础函数库';
Crypt_Key RAW(32767) := UTL_RAW.Cast_To_Raw('12345678');
Pad_Char VARCHAR2(1) := '~';
Dup_PK EXCEPTION;
PRAGMA EXCEPTION_INIT(Dup_PK,-2437);
PROCEDURE SetAutoCommit(b BOOLEAN)
AS
BEGIN
AutoCommitFlag :=b;
END;
--*****************************************************************************************************************************
--内部函数，外部不能访问.
--Begin...
--*****************************************************************************************************************************
PROCEDURE Appl_Info(iv_Action_Name VARCHAR2)
AS
BEGIN
Dbms_Application_Info.Set_Module(Module_Name => Module_Name,Action_Name => iv_Action_Name);
Current_Action :=iv_Action_Name;
END;
PROCEDURE Client_Info(iv_Module_Name VARCHAR2)
IS
BEGIN
Dbms_Application_Info.Set_Client_Info(client_info => 'Orcl_Base_Func Client');
END;
/*
DROP TABLE LOG;
CREATE TABLE LOG(DT DATE DEFAULT SYSDATE,USR VARCHAR2(30) DEFAULT USER,MODULE_NAME VARCHAR2(30),ACTION_NAME VARCHAR2(32),SUB_ACTION VARCHAR2(60),LOCATOR NUMBER(4,1),TYPE VARCHAR2(30),INFO VARCHAR2(1024),ERRCODE NUMBER,ERRMSG VARCHAR2(512),STACK VARCHAR2(1024));
TYPE :DEBUG,ERROR,WARNING,INFO
*/
PROCEDURE Log(iv_Sub_Action VARCHAR2,in_SQLCode NUMBER,in_Locator NUMBER,iv_Type VARCHAR2,iv_Info INFO)
AS
v_SQLErrM TSQLERRM;
BEGIN
Log(Module_Name,Current_Action,iv_Sub_Action,in_SQLCode,in_Locator,iv_Type,iv_Info);
END;
PROCEDURE PadString (iv_Text IN OUT VARCHAR2);
--*****************************************************************************************************************************
--内部函数，外部不能访问.
--End.
--*****************************************************************************************************************************
-------------------------------------------------------------------------------------
/*--动态SQL调用重载函数
--on_ExitCode:返回值，提供给调用过程判断调用是否成功，成功返回0，否则返回小于零的错误码
--iv_Dynamic_SQL:提供动态执行的语句，只能使SQL语句，比如[INSERT INTO MYTABLE VALUES(...);]，不可以是
PL/SQL语句。
*/
PROCEDURE Dynamic_Exec(on_ExitCode OUT NUMBER,iv_Dynamic_SQL VARCHAR2)
IS
v_Dynamic_SQL SQLSTR;
v_Action_Name VARCHAR2(32) :='DYNAMIC_EXEC(N,V)';
v_Info INFO;
n_Locator NUMBER :=1;
BEGIN
Appl_Info(v_Action_Name);
n_Locator :=2;
IF iv_Dynamic_SQL IS NOT NULL THEN
v_Dynamic_SQL :=RTRIM(iv_Dynamic_SQL,';');
n_Locator :=3;
EXECUTE IMMEDIATE v_Dynamic_SQL;
IF AutoCommitFlag THEN
COMMIT;
END IF;
n_Locator :=4;
on_ExitCode :=SQL%ROWCOUNT;
ELSE
Raise_Application_Error(-20001,ExcMsg(-20001));
END IF;
n_Locator :=5;
EXCEPTION
WHEN OTHERS THEN
on_ExitCode :=SQLCODE;
v_Info :=SUBSTR('{iv_Dynamic_SQL:'||v_Dynamic_SQL||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,on_ExitCode,n_Locator,LOG_TYPE_ERROR,v_Info);
END IF;
END Dynamic_Exec;
PROCEDURE Dynamic_Exec(on_ExitCode OUT NUMBER,iv_Dynamic_SQL IN VARCHAR2,iv_DM_Type IN VARCHAR2)
IS
v_Dynamic_SQL SQLSTR;
v_Action_Name VARCHAR2(32) :='DYNAMIC_EXEC(N,V,V)';
v_Info INFO;
n_Locator NUMBER :=1;
BEGIN
Appl_Info(v_Action_Name);
IF iv_Dynamic_SQL IS NOT NULL THEN
IF iv_DM_Type='R' THEN--REGULATION ORACLE CALL
v_Dynamic_SQL :='BEGIN '||RTRIM(iv_Dynamic_SQL,';')||';END;';
ELSIF iv_DM_Type='X' THEN--EXTENTION
v_Dynamic_SQL :=iv_Dynamic_SQL;
ELSE
v_Dynamic_SQL :=iv_Dynamic_SQL;
END IF;
EXECUTE IMMEDIATE v_Dynamic_SQL;
IF AutoCommitFlag THEN
COMMIT;
END IF;
on_ExitCode :=SQL%ROWCOUNT;
ELSE
Raise_Application_Error(-20001,ExcMsg(-20001));
END IF;
EXCEPTION
WHEN OTHERS THEN
on_ExitCode :=SQLCODE;
v_Info :=SUBSTR('{iv_Dynamic_SQL:'||v_Dynamic_SQL||'}{iv_DM_Type:'||iv_DM_Type||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,on_ExitCode,n_Locator,LOG_TYPE_ERROR,v_Info);
END IF;
END Dynamic_Exec;
FUNCTION Dynamic_Exec(iv_Dynamic_SQL VARCHAR2) RETURN NUMBER
IS
v_Dynamic_SQL SQLSTR;
v_Action_Name VARCHAR2(32) :='DYNAMIC_EXEC(N,V)';
v_Info INFO;
n_Locator NUMBER :=1;
BEGIN
Appl_Info(v_Action_Name);
n_Locator :=2;
IF iv_Dynamic_SQL IS NOT NULL THEN
v_Dynamic_SQL :=RTRIM(iv_Dynamic_SQL,';');
n_Locator :=3;
EXECUTE IMMEDIATE v_Dynamic_SQL;
IF AutoCommitFlag THEN
COMMIT;
END IF;
n_Locator :=4;
RETURN SQL%ROWCOUNT;
ELSE
Raise_Application_Error(-20001,ExcMsg(-20001));
END IF;
n_Locator :=5;
EXCEPTION
WHEN OTHERS THEN
v_Info :=SUBSTR('{iv_Dynamic_SQL:'||v_Dynamic_SQL||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,SQLCODE,n_Locator,LOG_TYPE_ERROR,v_Info);
END IF;
RETURN SQLCODE;
END Dynamic_Exec;
FUNCTION Dynamic_Exec(iv_Dynamic_SQL IN VARCHAR2,iv_DM_Type IN VARCHAR2) RETURN NUMBER
IS
v_Dynamic_SQL SQLSTR;
v_Action_Name VARCHAR2(32) :='DYNAMIC_EXEC(N,V,V)';
v_Info INFO;
n_Locator NUMBER :=1;
BEGIN
Appl_Info(v_Action_Name);
IF iv_Dynamic_SQL IS NOT NULL THEN
IF iv_DM_Type='R' THEN--REGULATION ORACLE CALL
v_Dynamic_SQL :='BEGIN '||RTRIM(iv_Dynamic_SQL,';')||';END;';
ELSIF iv_DM_Type='X' THEN--EXTENTION
v_Dynamic_SQL :=iv_Dynamic_SQL;
ELSE
v_Dynamic_SQL :=iv_Dynamic_SQL;
END IF;
EXECUTE IMMEDIATE v_Dynamic_SQL;
IF AutoCommitFlag THEN
COMMIT;
END IF;
RETURN SQL%ROWCOUNT;
ELSE
Raise_Application_Error(-20001,ExcMsg(-20001));
END IF;
EXCEPTION
WHEN OTHERS THEN
v_Info :=SUBSTR('{iv_Dynamic_SQL:'||v_Dynamic_SQL||'}{iv_DM_Type:'||iv_DM_Type||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,SQLCODE,n_Locator,LOG_TYPE_ERROR,v_Info);
END IF;
RETURN SQLCODE;
END Dynamic_Exec;
FUNCTION Dynamic_Returning(iv_Dynamic_SQL IN VARCHAR2,iv_Init_Val IN VARCHAR2) RETURN VARCHAR2
IS
v_Dynamic_SQL SQLSTR;
v_Action_Name VARCHAR2(32) :='DYNAMIC_RETURNING(V,V)';
v_Info INFO;
n_Locator NUMBER :=1;
v_Return_Value VARCHAR2(1024);
BEGIN
Appl_Info(v_Action_Name);
IF iv_Dynamic_SQL IS NOT NULL THEN
v_Dynamic_SQL :=iv_Dynamic_SQL;
EXECUTE IMMEDIATE v_Dynamic_SQL USING OUT v_Return_Value;
ELSE
Raise_Application_Error(-20001,ExcMsg(-20001));
END IF;
RETURN v_Return_Value;
EXCEPTION
WHEN OTHERS THEN
v_Info :=SUBSTR('{iv_Dynamic_SQL:'||v_Dynamic_SQL||'}{iv_Init_Val:'||iv_Init_Val||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,SQLCODE,n_Locator,LOG_TYPE_ERROR,v_Info);
END IF;
RETURN iv_Init_Val;
END Dynamic_Returning;
FUNCTION Dynamic_Returning(iv_Dynamic_SQL IN VARCHAR2,in_Init_Val IN NUMBER) RETURN NUMBER
IS
v_Dynamic_SQL SQLSTR;
v_Action_Name VARCHAR2(32) :='DYNAMIC_RETURNING(V,N)';
v_Info INFO;
n_Locator NUMBER :=1;
n_Return_Value NUMBER;
BEGIN
Appl_Info(v_Action_Name);
IF iv_Dynamic_SQL IS NOT NULL THEN
v_Dynamic_SQL :=iv_Dynamic_SQL;
EXECUTE IMMEDIATE v_Dynamic_SQL USING OUT n_Return_Value;
ELSE
Raise_Application_Error(-20001,ExcMsg(-20001));
END IF;
RETURN n_Return_Value;
EXCEPTION
WHEN OTHERS THEN
v_Info :=SUBSTR('{iv_Dynamic_SQL:'||v_Dynamic_SQL||'}{iv_Init_Val:'||TO_CHAR(in_Init_Val)||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,SQLCODE,n_Locator,LOG_TYPE_ERROR,v_Info);
END IF;
RETURN in_Init_Val;
END Dynamic_Returning;
FUNCTION Dynamic_Returning(iv_Dynamic_SQL IN VARCHAR2,id_Init_Val IN DATE) RETURN DATE
IS
v_Dynamic_SQL SQLSTR;
v_Action_Name VARCHAR2(32) :='DYNAMIC_RETURNING(V,D)';
v_Info INFO;
n_Locator NUMBER :=1;
d_Return_Value DATE;
BEGIN
Appl_Info(v_Action_Name);
IF iv_Dynamic_SQL IS NOT NULL THEN
v_Dynamic_SQL :=iv_Dynamic_SQL;
EXECUTE IMMEDIATE iv_Dynamic_SQL USING OUT d_Return_Value;
ELSE
Raise_Application_Error(-20001,ExcMsg(-20001));
END IF;
RETURN d_Return_Value;
EXCEPTION
WHEN OTHERS THEN
v_Info :=SUBSTR('{iv_Dynamic_SQL:'||v_Dynamic_SQL||'}{iv_Init_Val:'||TO_CHAR(id_Init_Val,'YYYY-MM-DD HH24:MI:SS')||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,SQLCODE,n_Locator,LOG_TYPE_ERROR,v_Info);
END IF;
RETURN id_Init_Val;
END Dynamic_Returning;
FUNCTION Dynamic_Result(iv_Dynamic_SQL IN VARCHAR2,iv_Init_Val IN VARCHAR2) RETURN VARCHAR2
IS
v_Result VARCHAR2(1024);
v_Dynamic_SQL SQLSTR;
v_Action_Name VARCHAR2(32) :='DYNAMIC_RESULT(V,V)';
v_Info INFO;
n_Locator NUMBER :=1;
BEGIN
Appl_Info(v_Action_Name);
IF iv_Dynamic_SQL IS NOT NULL THEN
v_Dynamic_SQL :=RTRIM(iv_Dynamic_SQL,';');
EXECUTE IMMEDIATE v_Dynamic_SQL INTO v_Result;
ELSE
Raise_Application_Error(-20001,ExcMsg(-20001));
END IF;
RETURN v_Result;
EXCEPTION
WHEN OTHERS THEN
v_Info :=SUBSTR('{iv_Dynamic_SQL:'||v_Dynamic_SQL||'}{iv_Init_Val:'||iv_Init_Val||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,SQLCODE,n_Locator,LOG_TYPE_ERROR,v_Info);
END IF;
RETURN iv_Init_Val;
END Dynamic_Result;
FUNCTION Dynamic_Result(iv_Dynamic_SQL IN VARCHAR2,in_Init_Val IN NUMBER) RETURN NUMBER
IS
n_Result NUMBER;
v_Dynamic_SQL SQLSTR;
v_Action_Name VARCHAR2(32) :='DYNAMIC_RESULT(V,N)';
v_Info INFO;
n_Locator NUMBER :=1;
BEGIN
Appl_Info(v_Action_Name);
IF iv_Dynamic_SQL IS NOT NULL THEN
v_Dynamic_SQL :=RTRIM(iv_Dynamic_SQL,';');
EXECUTE IMMEDIATE iv_Dynamic_SQL INTO n_Result;
ELSE
Raise_Application_Error(-20001,ExcMsg(-20001));
END IF;
RETURN n_Result;
EXCEPTION
WHEN OTHERS THEN
v_Info :=SUBSTR('{iv_Dynamic_SQL:'||v_Dynamic_SQL||'}{iv_Init_Val:'||TO_CHAR(in_Init_Val)||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,SQLCODE,n_Locator,LOG_TYPE_ERROR,v_Info);
END IF;
RETURN in_Init_Val;
END Dynamic_Result;
FUNCTION Dynamic_Result(iv_Dynamic_SQL IN VARCHAR2,id_Init_Val IN DATE) RETURN DATE
IS
d_Result DATE;
v_Dynamic_SQL SQLSTR;
v_Action_Name VARCHAR2(32) :='DYNAMIC_RESULT(V,D)';
v_Info INFO;
n_Locator NUMBER :=1;
BEGIN
Appl_Info(v_Action_Name);
IF iv_Dynamic_SQL IS NOT NULL THEN
v_Dynamic_SQL :=RTRIM(iv_Dynamic_SQL,';');
EXECUTE IMMEDIATE iv_Dynamic_SQL INTO d_Result;
ELSE
Raise_Application_Error(-20001,ExcMsg(-20001));
END IF;
RETURN d_Result;
EXCEPTION
WHEN OTHERS THEN
v_Info :=SUBSTR('{iv_Dynamic_SQL:'||v_Dynamic_SQL||'}{iv_Init_Val:'||TO_CHAR(id_Init_Val,'YYYY-MM-DD HH24:MI:SS')||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,SQLCODE,n_Locator,LOG_TYPE_ERROR,v_Info);
END IF;
RETURN id_Init_Val;
END Dynamic_Result;
PROCEDURE Compile_Object(iv_Object_Name IN VARCHAR2)
IS
v_Compile_Str SQLSTR;
n_ExitCode NUMBER;
n_Exp_NotCertain NUMBER :=-20099;
v_Action_Name VARCHAR2(32) :='COMPILE_OBJECT(V)';
v_Info INFO;
n_Locator NUMBER :=1;
v_Object_Type VARCHAR2(18);
BEGIN
Appl_Info(v_Action_Name);
IF iv_Object_Name IS NOT NULL THEN
SELECT Object_Type INTO v_Object_Type FROM All_Objects WHERE Object_Name =iv_Object_Name AND ROWNUM=1;
n_Locator :=2;
IF v_Object_Type='PACKAGE BODY' THEN
v_Object_Type :='PACKAGE';
END IF;
v_Compile_Str :='ALTER'||BS||v_Object_Type||BS||iv_Object_Name||BS||'COMPILE';
n_Locator :=3;
Dynamic_Exec(n_ExitCode,v_Compile_Str);
n_Locator :=4;
IF n_ExitCode<0 THEN
v_Info :=SUBSTR('{iv_Object_Name:'||iv_Object_Name||'}{v_Object_Type:'||v_Object_Type||'}{v_Compile_Str:'||v_Compile_Str||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,n_ExitCode,n_Locator,LOG_TYPE_ERROR,v_Info);
END IF;
n_Locator :=5;
END IF;
ELSE
NULL;
END IF;
n_Locator :=6;
EXCEPTION
WHEN OTHERS THEN
v_Info :=SUBSTR('{iv_Object_Name:'||iv_Object_Name||'}{v_Object_Type:'||v_Object_Type||'}{v_Compile_Str:'||v_Compile_Str||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,SQLCODE,n_Locator,LOG_TYPE_ERROR,v_Info);
END IF;
END Compile_Object;
PROCEDURE Compile_Object(iv_Owner VARCHAR2,iv_Object_Name IN VARCHAR2)
IS
v_Compile_Str SQLSTR;
n_ExitCode NUMBER;
n_Exp_NotCertain NUMBER :=-20099;
v_Action_Name VARCHAR2(32) :='COMPILE_OBJECT(V,V)';
v_Info INFO;
n_Locator NUMBER :=1;
v_Object_Type VARCHAR2(18);
BEGIN
Appl_Info(v_Action_Name);
IF iv_Object_Name IS NOT NULL THEN
SELECT Object_Type INTO v_Object_Type FROM All_Objects WHERE Object_Name =iv_Object_Name AND ROWNUM=1;
n_Locator :=2;
IF v_Object_Type='PACKAGE BODY' THEN
v_Object_Type :='PACKAGE';
END IF;
v_Compile_Str :='ALTER'||BS||v_Object_Type||BS||iv_Owner||'.'||iv_Object_Name||BS||'COMPILE';
n_Locator :=3;
Dynamic_Exec(n_ExitCode,v_Compile_Str);
n_Locator :=4;
IF n_ExitCode<0 THEN
v_Info :=SUBSTR('{iv_Owner:'||iv_Owner||'}{iv_Object_Name:'||iv_Object_Name||'}{v_Object_Type:'||v_Object_Type||'}{v_Compile_Str:'||v_Compile_Str||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,n_ExitCode,n_Locator,LOG_TYPE_ERROR,v_Info);
END IF;
n_Locator :=5;
END IF;
END IF;
n_Locator :=6;
EXCEPTION
WHEN OTHERS THEN
v_Info :=SUBSTR('{iv_Object_Name:'||iv_Object_Name||'}{v_Object_Type:'||v_Object_Type||'}{v_Compile_Str:'||v_Compile_Str||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,SQLCODE,n_Locator,LOG_TYPE_ERROR,v_Info);
END IF;
END Compile_Object;
------------------------------------------------------------------------------------------------------------
/*--截断表数据函数（清除表中数据速度快）--------------------------------------------------------------------
--on_ExitCode:过程执行成功与否的标志，0-成功，<0-失败，若失败，返回oracle错误码
--iv_Table_Name:需要截断数据的表的名称
----------------------------------------------------------------------------------------------------------*/
PROCEDURE Truncate_Table(on_ExitCode OUT NUMBER,iv_Table_Name VARCHAR2)
IS
v_Trunc_SQL SQLSTR;
n_ExitCode NUMBER;
v_Action_Name VARCHAR2(32) :='TRUNCATE_TABLE(N,V)';
v_Info INFO;
n_Locator NUMBER :=1;
BEGIN
Appl_Info(v_Action_Name);
v_Trunc_SQL :='TRUNCATE TABLE'||BS||iv_Table_Name;
Dynamic_Exec(n_ExitCode,v_Trunc_SQL);
on_ExitCode :=n_ExitCode;
EXCEPTION
WHEN OTHERS THEN
v_Info :=SUBSTR('{iv_Table_Name:'||iv_Table_Name||'}{v_Trunc_SQL:'||v_Trunc_SQL||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,SQLCODE,n_Locator,LOG_TYPE_ERROR,v_Info);
END IF;
END Truncate_Table;
------------------------------------------------------------------------------------------------------------
/*--截断表数据函数的重载函数（清除表中数据速度快）----------------------------------------------------------
--on_ExitCode:过程执行成功与否的标志，0-成功，<0-失败，若失败，返回oracle错误码
--iv_Owner:需要截断数据的表所在的Schema
--iv_Table_Name:需要截断数据的表的名称
----------------------------------------------------------------------------------------------------------*/
PROCEDURE Truncate_Table(on_ExitCode OUT NUMBER,iv_Owner VARCHAR2,iv_Table_Name VARCHAR2)
IS
v_Trunc_SQL SQLSTR;
n_ExitCode NUMBER;
v_Action_Name VARCHAR2(32) :='TRUNCATE_TABLE(N,V,V)';
v_Info INFO;
n_Locator NUMBER :=1;
BEGIN
Appl_Info(v_Action_Name);
v_Trunc_SQL :='TRUNCATE TABLE'||BS||iv_Owner||'.'||iv_Table_Name;
Dynamic_Exec(n_ExitCode,v_Trunc_SQL);
on_ExitCode :=n_ExitCode;
EXCEPTION
WHEN OTHERS THEN
v_Info :=SUBSTR('{iv_Owner:'||iv_Owner||'}{iv_Table_Name:'||iv_Table_Name||'}{v_Trunc_SQL:'||v_Trunc_SQL||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,SQLCODE,n_Locator,LOG_TYPE_ERROR,v_Info);
END IF;
END Truncate_Table;
------------------------------------------------------------------------------------------------------------
/*--删除表数据函数------------------------------------------------------------------------------------------
--on_ExitCode:过程执行成功与否的标志，0-成功，<0-失败，若失败，返回oracle错误码
--iv_Table_Name:需要删除数据的表的名称
--iv_Del_Where:数据删除条件
----------------------------------------------------------------------------------------------------------*/
PROCEDURE Delete_Table(on_ExitCode OUT NUMBER,iv_Table_Name VARCHAR2,iv_Del_Where VARCHAR2)
IS
v_Del_SQL SQLSTR;
v_Del_Where SQLWHERE;
n_ExitCode NUMBER;
v_Action_Name VARCHAR2(32) :='DELETE_TABLE(N,V,V)';
v_Info INFO;
n_Locator NUMBER :=1;
BEGIN
Appl_Info(v_Action_Name);
IF iv_Table_Name IS NOT NULL THEN
v_Del_SQL :='DELETE FROM '||iv_Table_Name;
IF iv_Del_Where IS NOT NULL THEN
IF INSTR(UPPER(iv_Del_Where),'WHERE')=0 THEN
v_Del_Where :='WHERE'||BS||iv_Del_Where;
ELSE
v_Del_Where :=BS||iv_Del_Where;
END IF;
v_Del_SQL :=v_Del_SQL||v_Del_Where;
END IF;
Dynamic_Exec(n_ExitCode,v_Del_SQL);
on_ExitCode :=n_ExitCode;
END IF;
EXCEPTION
WHEN OTHERS THEN
v_Info :=SUBSTR('{iv_Table_Name:'||iv_Table_Name||'}{iv_Del_Where:'||iv_Del_Where||'}{v_Del_SQL:'||v_Del_SQL||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,SQLCODE,n_Locator,LOG_TYPE_ERROR,v_Info);
END IF;
END Delete_Table;
------------------------------------------------------------------------------------------------------------
/*--删除表数据函数的重载函数------------------------------------------------------------------------------------------
--on_ExitCode:过程执行成功与否的标志，0-成功，<0-失败，若失败，返回oracle错误码
--iv_Owner:需要删除数据的表的Schema.
--iv_Table_Name:需要删除数据的表的名称
--iv_Del_Where:数据删除条件
----------------------------------------------------------------------------------------------------------*/
PROCEDURE Delete_Table(on_ExitCode OUT NUMBER,iv_Owner VARCHAR2,iv_Table_Name VARCHAR2,iv_Del_Where VARCHAR2)
IS
v_Del_SQL SQLSTR;
v_Del_Where SQLWHERE;
n_ExitCode NUMBER;
v_Action_Name VARCHAR2(32) :='DELETE_TABLE(N,V,V)';
v_Info INFO;
n_Locator NUMBER :=1;
BEGIN
Appl_Info(v_Action_Name);
IF iv_Table_Name IS NOT NULL THEN
v_Del_SQL :='DELETE FROM'||BS||iv_Owner||'.'||iv_Table_Name;
IF iv_Del_Where IS NOT NULL THEN
IF INSTR(UPPER(iv_Del_Where),'WHERE')=0 THEN
v_Del_Where :='WHERE'||BS||iv_Del_Where;
ELSE
v_Del_Where :=BS||iv_Del_Where;
END IF;
v_Del_SQL :=v_Del_SQL||v_Del_Where;
END IF;
Dynamic_Exec(n_ExitCode,v_Del_SQL);
on_ExitCode :=n_ExitCode;
END IF;
EXCEPTION
WHEN OTHERS THEN
v_Info :=SUBSTR('{iv_Owner:'||iv_Owner||'}{iv_Table_Name:'||iv_Table_Name||'}{iv_Del_Where:'||iv_Del_Where||'}{v_Del_SQL:'||v_Del_SQL||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,SQLCODE,n_Locator,LOG_TYPE_ERROR,v_Info);
END IF;
END Delete_Table;
FUNCTION Get_PKey_Cols(iv_Owner VARCHAR2,iv_Table_Name VARCHAR2) RETURN VARCHAR2
IS
v_PKey_Cols LONGCHAR;
v_Action_Name VARCHAR2(32) :='GET_PKEY_COLS(V,V)';
v_Info INFO;
n_Locator NUMBER :=1;
BEGIN
FOR Cr IN (SELECT B.Column_Name FROM All_Constraints A,All_Cons_Columns B
WHERE A.Owner=B.Owner AND A.Constraint_Name=B.Constraint_Name
AND A.Constraint_Type='P'AND A.OWNER=UPPER(iv_Owner) AND A.Table_Name=UPPER(iv_Table_Name)) LOOP
v_PKey_Cols :=v_PKey_Cols||Cr.Column_Name||',';
END LOOP;
IF v_PKey_Cols IS NULL OR TRIM(v_PKey_Cols)='' THEN
v_Info :=SUBSTR('{iv_Owner:'||iv_Owner||'}{iv_Table_Name:'||iv_Table_Name||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,SQLCODE,n_Locator,LOG_TYPE_ERROR,v_Info);
END IF;
END IF;
RETURN RTRIM(v_PKey_Cols,',');
EXCEPTION
WHEN OTHERS THEN
v_Info :=SUBSTR('{iv_Owner:'||iv_Owner||'}{iv_Table_Name:'||iv_Table_Name||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,SQLCODE,n_Locator,LOG_TYPE_ERROR,v_Info);
END IF;
RETURN NULL;
END Get_PKey_Cols;
FUNCTION Get_All_Cols(iv_Table_Name VARCHAR2) RETURN VARCHAR2
IS
v_All_Cols LONGCHAR;
BEGIN
FOR CR1 IN (SELECT * FROM User_Tab_Columns WHERE Table_Name=UPPER(iv_Table_Name)) LOOP
v_All_Cols :=v_All_Cols||CR1.Column_Name||PUNC_COMMA;
END LOOP;
v_All_Cols :=RTRIM(v_All_Cols,PUNC_COMMA);
RETURN v_All_Cols;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END Get_All_Cols;
FUNCTION GetSQL(iv_Table_Name VARCHAR2,Flag NUMBER DEFAULT 0) RETURN VARCHAR2
IS
v_SQL SQLSTR;
v_Col_List LONGCHAR;
BEGIN
IF iv_Table_Name IS NULL THEN
RETURN NULL;
END IF;
IF Flag=0 THEN
v_Col_List :='*';
ELSE
v_Col_List :=Get_All_Cols(iv_Table_Name);
END IF;
v_SQL :='SELECT'||BS||v_Col_List||BS||'FROM'||BS||iv_Table_Name;
RETURN v_SQL;
END;
FUNCTION GetSQL(iv_Table_Name VARCHAR2,iv_Column_List VARCHAR2) RETURN VARCHAR2
IS
v_SQL SQLSTR;
v_Col_List LONGCHAR;
BEGIN
IF iv_Table_Name IS NULL THEN
RETURN NULL;
END IF;
IF iv_Column_List IS NULL THEN
v_Col_LIst :=Get_All_Cols(iv_Table_Name);
ELSE
v_Col_List :=iv_Column_List;
END IF;
v_SQL :='SELECT'||BS||v_Col_List||BS||'FROM'||BS||iv_Table_Name;
RETURN v_SQL;
END;
FUNCTION GetSQL(iv_Table_Name VARCHAR2,iv_Column_List VARCHAR2,iv_Where VARCHAR2) RETURN VARCHAR2
IS
n_HasWhere NUMBER;
BEGIN
IF iv_Where IS NOT NULL THEN
SELECT COUNT(*) INTO n_HasWhere FROM TABLE(BASE_FUNC.CHAR_TO_TABLE(iv_Where,BS)) A WHERE TRIM(UPPER(A.ITEM))='WHERE';
IF n_HasWhere>0 THEN
RETURN GetSQL(iv_Table_Name,iv_Column_List)||BS||iv_Where;
ELSE
RETURN GetSQL(iv_Table_Name,iv_Column_List)||BS||'WHERE'||BS||iv_Where;
END IF;
ELSE
RETURN GetSQL(iv_Table_Name,iv_Column_List);
END IF;
END;
FUNCTION Get_All_Cols(iv_Owner VARCHAR2,iv_Table_Name VARCHAR2) RETURN VARCHAR2
IS
v_All_Cols LONGCHAR;
BEGIN
FOR Cr IN (SELECT B.Column_Name FROM All_Tables A,All_Tab_Columns B
WHERE A.Owner=B.Owner AND A.Table_Name=B.Table_Name
AND A.OWNER=UPPER(iv_Owner) AND A.Table_Name=UPPER(iv_Table_Name)) LOOP
v_All_Cols :=v_All_Cols||Cr.Column_Name||',';
END LOOP;
RETURN RTRIM(v_All_Cols,',');
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END Get_All_Cols;
FUNCTION GetSQL2(iv_Owner VARCHAR2,iv_Table_Name VARCHAR2,Flag NUMBER DEFAULT 0) RETURN VARCHAR2
IS
v_SQL SQLSTR;
v_Col_List LONGCHAR;
BEGIN
IF iv_Table_Name IS NULL THEN
RETURN NULL;
END IF;
IF Flag=0 THEN
v_Col_List :='*';
ELSE
v_Col_List :=Get_All_Cols(iv_Owner,iv_Table_Name);
END IF;
v_SQL :='SELECT'||BS||v_Col_List||BS||'FROM'||BS||iv_Owner||'.'||iv_Table_Name;
RETURN v_SQL;
END;
FUNCTION GetSQL2(iv_Owner VARCHAR2,iv_Table_Name VARCHAR2,iv_Column_List VARCHAR2) RETURN VARCHAR2
IS
v_SQL SQLSTR;
v_Col_List LONGCHAR;
BEGIN
IF iv_Table_Name IS NULL THEN
RETURN NULL;
END IF;
IF iv_Column_List IS NULL THEN
v_Col_List :=Get_All_Cols(iv_Owner,iv_Table_Name);
ELSE
v_Col_List :=iv_Column_List;
END IF;
v_SQL :='SELECT'||BS||v_Col_List||BS||'FROM'||BS||iv_Owner||'.'||iv_Table_Name;
RETURN v_SQL;
END;
FUNCTION GetSQL2(iv_Owner VARCHAR2,iv_Table_Name VARCHAR2,iv_Column_List VARCHAR2,iv_Where VARCHAR2) RETURN VARCHAR2
IS
n_HasWhere NUMBER;
BEGIN
IF iv_Where IS NOT NULL THEN
SELECT COUNT(*) INTO n_HasWhere FROM TABLE(BASE_FUNC.CHAR_TO_TABLE(iv_Where,BS)) A WHERE TRIM(UPPER(A.ITEM))='WHERE';
IF n_HasWhere>0 THEN
RETURN GetSQL2(iv_Owner,iv_Table_Name,iv_Column_List)||BS||iv_Where;
ELSE
RETURN GetSQL2(iv_Owner,iv_Table_Name,iv_Column_List)||BS||'WHERE'||BS||iv_Where;
END IF;
ELSE
RETURN GetSQL2(iv_Owner,iv_Table_Name,iv_Column_List);
END IF;
END;
FUNCTION Get_Exception_Tab RETURN VARCHAR2
IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_Exception_Tab SYSNAME;
BEGIN
BEGIN
SELECT Table_Name INTO v_Exception_Tab FROM Exception_Tables
WHERE Status=0 AND ROWNUM=1;
UPDATE Exception_Tables SET Status=1 WHERE Table_Name=v_Exception_Tab;
COMMIT;
RETURN v_Exception_Tab;
EXCEPTION
WHEN NO_DATA_FOUND THEN
SELECT C.Table_Name INTO v_Exception_Tab FROM Exception_Tables C,(SELECT L.Object_ID,O.Object_Name FROM V$Locked_Object L,User_Objects O
WHERE L.Object_ID=O.Object_ID(+) AND Object_Type='TABLE') K WHERE C.Table_Name=K.Object_Name(+) AND K.Object_ID IS NULL AND C.Status<>EXC_LOCKING;
UPDATE Exception_Tables SET Status=1 WHERE Table_Name=v_Exception_Tab;
COMMIT;
RETURN v_Exception_Tab;
END;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END Get_Exception_Tab;
FUNCTION Get_Exception_Tab(iv_Type VARCHAR2) RETURN VARCHAR2
IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_Exception_Tab SYSNAME;
BEGIN
BEGIN
SELECT Table_Name INTO v_Exception_Tab FROM Exception_Tables
WHERE Status=0 AND ROWNUM=1 AND Type=UPPER(iv_Type);
UPDATE Exception_Tables SET Status=1 WHERE Table_Name=v_Exception_Tab AND Type=UPPER(iv_Type);
COMMIT;
RETURN v_Exception_Tab;
EXCEPTION
WHEN NO_DATA_FOUND THEN
SELECT C.Table_Name INTO v_Exception_Tab FROM Exception_Tables C,(SELECT L.Object_ID,O.Object_Name FROM V$Locked_Object L,User_Objects O
WHERE L.Object_ID=O.Object_ID(+) AND Object_Type='TABLE') K WHERE C.Table_Name=K.Object_Name(+) AND K.Object_ID IS NULL AND C.Type=iv_Type AND C.Status<>EXC_LOCKING;
UPDATE Exception_Tables SET Status=1 WHERE Table_Name=v_Exception_Tab;
COMMIT;
RETURN v_Exception_Tab;
END;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END Get_Exception_Tab;
PROCEDURE Reset_Exception_Tab
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE Exception_Tables SET Status=0;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END Reset_Exception_Tab;
PROCEDURE Reset_Exception_Tab(iv_Exeception_Table VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE Exception_Tables SET Status=0 WHERE Table_Name=UPPER(iv_Exeception_Table);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END Reset_Exception_Tab;
PROCEDURE Alter_PKey(on_ExitCode OUT NUMBER,iv_Owner VARCHAR2,iv_Table_Name VARCHAR2,iv_Status VARCHAR2)
IS
v_Alter_SQL SQLSTR;
v_PKey_Cols LONGCHAR;
v_SQL SQLSTR;
v_Action_Name VARCHAR2(32) :='Alter_PKey(N,V,V,V)';
v_Info INFO;
n_Locator NUMBER :=1;
n_ExitCode NUMBER;
v_Exc_Tab SYSNAME;
BEGIN
IF iv_Table_Name IS NOT NULL AND iv_Status IN ('ENABLE','DISABLE') THEN
BEGIN
v_Exc_Tab :=Get_Exception_Tab;
Truncate_Table(n_ExitCode,iv_Owner,v_Exc_Tab);
v_Alter_SQL :='ALTER TABLE'||BS||iv_Owner||'.'||iv_Table_Name||BS||iv_Status||BS||'PRIMARY KEY';
IF iv_Status='ENABLE' THEN
--DBMS_OUTPUT.PUT_LINE('v_Alter_SQL11'||v_Alter_SQL||' EXCEPTIONS INTO '||gv_IDB_User||'.'||'MD_Log_PK_Exceptions');
EXECUTE IMMEDIATE v_Alter_SQL||BS||'EXCEPTIONS INTO'||BS||iv_Owner||'.'||v_Exc_Tab;
ELSE
EXECUTE IMMEDIATE v_Alter_SQL;
END IF;
on_ExitCode :=0;
EXCEPTION
WHEN DUP_VAL_ON_INDEX OR Dup_PK THEN
v_PKey_Cols :=Get_Pkey_Cols(iv_Owner,iv_Table_Name);
v_SQL := 'DELETE FROM '||v_Exc_Tab||' WHERE ROW_ID IN (SELECT MAX(A.ROWID) FROM '||iv_Owner||'.'||iv_Table_Name ||
' A,'||v_Exc_Tab||' B WHERE A.ROWID=B.ROW_ID GROUP BY '||v_PKey_Cols ||')';
EXECUTE IMMEDIATE v_SQL;
v_SQL := 'DELETE FROM ' ||iv_Owner||'.'||iv_Table_Name ||
' WHERE ROWID IN (SELECT ROW_ID FROM '||v_Exc_Tab||')';
EXECUTE IMMEDIATE v_SQL;
EXECUTE IMMEDIATE v_Alter_SQL;
Truncate_Table(n_ExitCode,iv_Owner,v_Exc_Tab);
Reset_Exception_Tab(v_Exc_Tab);
v_Info :=SUBSTR('{PK Conflict}{iv_Owner:'||iv_Owner||'}{iv_Table_Name:'||iv_Table_Name||'}{iv_Status:'||iv_Status||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,SQLCODE,n_Locator,LOG_TYPE_WARNING,v_Info);
END IF;
on_ExitCode :=n_ExitCode;
END;
ELSE
Raise_Application_Error(-20002,ExcMsg(-20002));
END IF;
EXCEPTION
WHEN OTHERS THEN
Reset_Exception_Tab(v_Exc_Tab);
on_ExitCode :=SQLCODE;
v_Info :=SUBSTR('{iv_Owner:'||iv_Owner||'}{iv_Table_Name:'||iv_Table_Name||'}{iv_Status:'||iv_Status||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,SQLCODE,n_Locator,LOG_TYPE_ERROR,v_Info);
END IF;
END Alter_PKey;
PROCEDURE Alter_PKey(on_ExitCode OUT NUMBER,iv_Owner VARCHAR2,iv_Table_Name VARCHAR2,iv_Status VARCHAR2,iv_ExceptionTable VARCHAR2)
IS
v_Alter_SQL SQLSTR;
v_PKey_Cols LONGCHAR;
v_SQL SQLSTR;
v_Action_Name VARCHAR2(32) :='Alter_PKey(N,V,V,V,V)';
v_Info INFO;
n_Locator NUMBER :=1;
n_ExitCode NUMBER;
v_Exc_Tab SYSNAME;
BEGIN
IF iv_Table_Name IS NOT NULL AND iv_Status IN ('ENABLE','DISABLE') AND iv_ExceptionTable IS NOT NULL THEN
BEGIN
v_Exc_Tab :=iv_ExceptionTable;
Truncate_Table(n_ExitCode,iv_Owner,v_Exc_Tab);
v_Alter_SQL :='ALTER TABLE'||BS||iv_Owner||'.'||iv_Table_Name||BS||iv_Status||BS||'PRIMARY KEY';
IF iv_Status='ENABLE' THEN
--DBMS_OUTPUT.PUT_LINE('v_Alter_SQL11'||v_Alter_SQL||' EXCEPTIONS INTO '||gv_IDB_User||'.'||'MD_Log_PK_Exceptions');
EXECUTE IMMEDIATE v_Alter_SQL||BS||'EXCEPTIONS INTO'||BS||iv_Owner||'.'||v_Exc_Tab;
ELSE
EXECUTE IMMEDIATE v_Alter_SQL;
END IF;
on_ExitCode :=0;
EXCEPTION
WHEN DUP_VAL_ON_INDEX OR Dup_PK THEN
v_PKey_Cols :=Get_Pkey_Cols(iv_Owner,iv_Table_Name);
v_SQL := 'DELETE FROM '||v_Exc_Tab||' WHERE ROW_ID IN (SELECT MAX(A.ROWID) FROM '||iv_Owner||'.'||iv_Table_Name ||
' A,'||v_Exc_Tab||' B WHERE A.ROWID=B.ROW_ID GROUP BY '||v_PKey_Cols ||')';
EXECUTE IMMEDIATE v_SQL;
v_SQL := 'DELETE FROM ' ||iv_Owner||'.'||iv_Table_Name ||
' WHERE ROWID IN (SELECT ROW_ID FROM '||v_Exc_Tab||')';
EXECUTE IMMEDIATE v_SQL;
EXECUTE IMMEDIATE v_Alter_SQL;
Truncate_Table(n_ExitCode,iv_Owner,v_Exc_Tab);
Reset_Exception_Tab(v_Exc_Tab);
v_Info :=SUBSTR('{PK Conflict}{iv_Owner:'||iv_Owner||'}{iv_Table_Name:'||iv_Table_Name||'}{iv_Status:'||iv_Status||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,SQLCODE,n_Locator,LOG_TYPE_WARNING,v_Info);
END IF;
on_ExitCode :=n_ExitCode;
END;
ELSE
Raise_Application_Error(-20002,ExcMsg(-20002));
END IF;
EXCEPTION
WHEN OTHERS THEN
Reset_Exception_Tab(v_Exc_Tab);
on_ExitCode :=SQLCODE;
v_Info :=SUBSTR('{iv_Owner:'||iv_Owner||'}{iv_Table_Name:'||iv_Table_Name||'}{iv_Status:'||iv_Status||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,SQLCODE,n_Locator,LOG_TYPE_ERROR,v_Info);
END IF;
END Alter_PKey;
PROCEDURE Application_Info(iv_Module_Name VARCHAR2,iv_Action_Name VARCHAR2)
AS
BEGIN
Dbms_Application_Info.Set_Module(Module_Name => iv_Module_Name,Action_Name => iv_Action_Name);
END;
/*
DROP TABLE LOG;
CREATE TABLE LOG(DT DATE DEFAULT SYSDATE,USR VARCHAR2(30) DEFAULT USER,MODULE_NAME VARCHAR2(30),ACTION_NAME VARCHAR2(32),SUB_ACTION VARCHAR2(60),LOCATOR NUMBER(4,1),TYPE VARCHAR2(30),INFO VARCHAR2(1024),ERRCODE NUMBER,ERRMSG VARCHAR2(512),STACK VARCHAR2(1024));
TYPE :DEBUG,ERROR,WARNING,INFO
*/
PROCEDURE Log(iv_Module_Name VARCHAR2,iv_Action_Name VARCHAR2,iv_Sub_Action VARCHAR2,in_SQLCode NUMBER,in_Locator NUMBER,iv_Type VARCHAR2,iv_Info INFO)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
v_SQLErrM TSQLERRM;
v_LogFile SHORTCHAR;
v_LogLocation SHORTCHAR;
v_Utl_File_Dir SHORTCHAR;
n_Val INT;
v_LogLine BLONGCHAR;
v_RT_LF VARCHAR2(2);
ft_File UTL_File.File_Type;
partyp BINARY_INTEGER;
BEGIN
v_SQLErrM :=SQLERRM(in_SQLCode);
IF LogTo=LOGTO_DB THEN
INSERT INTO Log VALUES(
SYSDATE,
USER,
iv_Module_Name,
iv_Action_Name,
iv_Sub_Action,
in_Locator,
iv_Type,
iv_Info,
in_SQLCode,
v_SQLErrM,
SUBSTR(DBMS_Utility.Format_Error_Stack,1,INFO_LENGTH)
);
END IF;
IF LogTo=LOGTO_FILE THEN
IF LogFile IS NULL THEN
v_LogFile :='Log'||TO_CHAR(SYSDATE,DATE_FORMAT)||'.log';
ELSE
v_LogFile :=LogFile;
END IF;
IF LogLocation IS NULL THEN
--DBMS_Utility.Get_Parameter_Value('utl_file_dir',n_Val, v_Utl_File_Dir);
partyp := DBMS_Utility.Get_Parameter_Value('utl_file_dir',n_Val, v_Utl_File_Dir);
IF v_Utl_File_Dir IS NOT NULL THEN
v_LogLocation :=v_Utl_File_Dir;
ELSE
NULL;
END IF;
ELSE
v_LogLocation :=LogLocation;
END IF;
v_RT_LF :=RT||LF;
ft_File := UTL_FILE.FOPEN(location =>v_LogLocation,
filename => v_LogFile,
open_mode => 'a',
max_linesize => 32767);
IF LogStyle IS NULL OR LogStyle=LOG_STYLE_FORM THEN
v_LogLine := '########################################--Log Begin--########################################'||v_RT_LF||'DATE:'||TO_CHAR(SYSDATE,DATE_FORMAT)||v_RT_LF
||'USER:'||BS||USER||v_RT_LF
||'MODULE_NAME:'||BS||iv_Module_Name||v_RT_LF
||'ACTION_NAME:'||BS||iv_Action_Name||v_RT_LF
||'SUB_ACTION_NAME:'||BS||iv_Sub_Action||v_RT_LF
||'LOCATOR:'||BS||TO_CHAR(in_Locator)||v_RT_LF
||'TYPE:'||BS||iv_Type||v_RT_LF
||'SQLCODE:'||BS||TO_CHAR(in_SQLCode)||v_RT_LF
||'SQLERRM:'||BS||v_SQLErrM||v_RT_LF
||'INFO:'||BS||iv_Info||v_RT_LF
||'#########################################--Log End--#########################################'||v_RT_LF
||v_RT_LF
||v_RT_LF;
ELSIF LogStyle=LOG_STYLE_GRID THEN
--'DATE'||TB||'USER'||TB||'MODULE_NAME'||TB||'ACTION_NAME'||TB||'SUB_ACTION'||TB||'LOCATOR'||TB||'TYPE'||TB||'SQLCODE'||TB||'SQLERRM'||TB||'INFO'
v_LogLine := 'DATE:'||TO_CHAR(SYSDATE,DATE_FORMAT)||TB
||'USER:'||BS||USER||TB
||'MODULE_NAME:'||BS||iv_Module_Name||TB
||'ACTION_NAME:'||BS||iv_Action_Name||TB
||'SUB_ACTION_NAME:'||BS||iv_Sub_Action||TB
||'LOCATOR:'||BS||TO_CHAR(in_Locator)||TB
||'TYPE:'||BS||iv_Type||TB
||'SQLCODE:'||BS||TO_CHAR(in_SQLCode)||TB
||'SQLERRM:'||BS||v_SQLErrM||TB
||'INFO:'||BS||iv_Info||v_RT_LF;
END IF;
UTL_FILE.PUT_Line(ft_File,v_LogLine);
UTL_FILE.FCLOSE(ft_File);
END IF;
IF LogTo=LOGTO_SCREEN THEN
DBMS_OUTPUT.PUT_LINE('DATE:'||TO_CHAR(SYSDATE,DATE_FORMAT));
DBMS_OUTPUT.PUT_LINE('USER:'||USER);
DBMS_OUTPUT.PUT_LINE('MODULE_NAME:'||iv_Module_Name);
DBMS_OUTPUT.PUT_LINE('ACTION_NAME:'||iv_Action_Name);
DBMS_OUTPUT.PUT_LINE('SUB_ACTION_NAME:'||iv_Sub_Action);
DBMS_OUTPUT.PUT_LINE('LOCATOR:'||TO_CHAR(in_Locator));
DBMS_OUTPUT.PUT_LINE('TYPE:'||iv_Type);
DBMS_OUTPUT.PUT_LINE('SQLCODE:'||TO_CHAR(in_SQLCode));
DBMS_OUTPUT.PUT_LINE('SQLERRM:**********************************');
Screen_Output(v_SQLErrM);
DBMS_OUTPUT.PUT_LINE('INFO:*************************************');
Screen_Output(iv_Info);
END IF;
END;
PROCEDURE Set_LogFlag(b BOOLEAN)
IS
BEGIN
LogFlag :=b;
END;
PROCEDURE Set_LogTo(iv_LogTo VARCHAR2)
IS
BEGIN
LogTo :=iv_LogTo;
END;
PROCEDURE Set_LogFile(iv_LogFile VARCHAR2)
IS
BEGIN
LogFile :=iv_LogFile;
END;
PROCEDURE Set_LogLocation(iv_LogLocation VARCHAR2)
IS
BEGIN
LogLocation :=iv_LogLocation;
END;
PROCEDURE Set_LogStyle(iv_LogStyle VARCHAR2)
IS
BEGIN
LogStyle :=iv_LogStyle;
END;
PROCEDURE Screen_Output(iv_Output VARCHAR2,Fmt_Flag NUMBER DEFAULT 0)
AS
n_Len NUMBER;
n_Loops NUMBER;
v_Temp VARCHAR2(255);
v_Output VARCHAR2(8000);
n_Comma_Pos NUMBER;
n_Semicolon_Pos NUMBER;
n_FullStop_Pos NUMBER;
n_ExcalPoint_Pos NUMBER;
n_ZComma_Pos NUMBER;
n_ZSemicolon_Pos NUMBER;
n_ZFullStop_Pos NUMBER;
n_ZExcalPoint_Pos NUMBER;
n_BS_Pos NUMBER;
n_LF_Pos NUMBER;
n_Get_Pos NUMBER;
n_Id number :=0;
BEGIN
v_Output :=iv_Output;
IF Fmt_Flag=0 THEN
n_Len :=LENGTH(iv_Output);
n_Loops :=CEIL(n_Len/OUTPUT_MAX_LEN);
DBMS_OUTPUT.PUT_LINE('iv_Output:'||iv_Output);
DBMS_OUTPUT.PUT_LINE('n_Len:'||to_char(n_Len));
DBMS_OUTPUT.PUT_LINE('n_Loops:'||to_char(n_Loops));
FOR n_Loop IN 1..n_Loops LOOP
v_Temp :=SUBSTR(iv_Output,(n_Loop -1)*255+1,OUTPUT_MAX_LEN);
DBMS_OUTPUT.PUT_LINE(v_Temp);
END LOOP;
ELSIF Fmt_Flag=1 THEN
LOOP
n_id :=n_id+1;
v_Temp :=SUBSTR(v_Output,1,OUTPUT_MAX_LEN);
/*
DBMS_OUTPUT.PUT_LINE(to_char(n_id)||':--------------------');
DBMS_OUTPUT.PUT_LINE(V_TEMP);
DBMS_OUTPUT.PUT_LINE('&&&&&&&&&&&&&&&&&');
DBMS_OUTPUT.PUT_LINE(SUBSTR(V_OUTPUT,1,255));
DBMS_OUTPUT.PUT_LINE('--------------------');
*/
n_Comma_Pos :=INSTR(v_Temp,PUNC_COMMA,-1);
n_Semicolon_Pos :=INSTR(v_Temp,PUNC_SEMICOLON,-1);
n_FullStop_Pos :=INSTR(v_Temp,PUNC_FULLSTOP,-1);
n_ExcalPoint_Pos :=INSTR(v_Temp,PUNC_EXCALPOINT,-1);
n_ZComma_Pos :=INSTR(v_Temp,PUNC_ZCOMMA,-1);
n_ZSemicolon_Pos :=INSTR(v_Temp,PUNC_ZSEMICOLON,-1);
n_ZFullStop_Pos :=INSTR(v_Temp,PUNC_ZFULLSTOP,-1);
n_ZExcalPoint_Pos :=INSTR(v_Temp,PUNC_ZEXCALPOINT,-1);
SELECT GREATEST(n_Comma_Pos,n_Semicolon_Pos,n_FullStop_Pos,n_ExcalPoint_Pos,n_ZComma_Pos,n_ZSemicolon_Pos,n_ZFullStop_Pos,n_ZExcalPoint_Pos) INTO n_Get_Pos FROM DUAL;
--DBMS_OUTPUT.PUT_LINE('n_Get_Pos'||to_char(n_Get_Pos));
--DBMS_OUTPUT.PUT_LINE('v_Output Length:'||to_char(length(v_Output)));
IF n_Get_Pos<>0 THEN
v_Temp :=SUBSTR(v_Output,1,n_Get_Pos);
DBMS_OUTPUT.PUT_LINE(v_Temp);
v_Output :=SUBSTR(v_Output,n_Get_Pos+1,n_Get_Pos+OUTPUT_MAX_LEN);
ELSE
v_Temp :=SUBSTR(v_Output,1,OUTPUT_MAX_LEN);
DBMS_OUTPUT.PUT_LINE(v_Temp);
v_Output :=SUBSTR(v_Output,OUTPUT_MAX_LEN+1);
END IF;
EXIT WHEN v_Output IS NULL OR n_Get_Pos =0;
END LOOP;
ELSIF Fmt_Flag=2 THEN
LOOP
n_id :=n_id+1;
v_Temp :=SUBSTR(v_Output,1,OUTPUT_MAX_LEN);
n_BS_Pos :=INSTR(v_Temp,BS,-1);
--n_RT_Pos :=INSTR(v_Temp,RT,-1);
n_LF_Pos :=INSTR(v_Temp,LF,-1);
/*DBMS_OUTPUT.PUT_LINE(to_char(n_id)||':--------------------');
DBMS_OUTPUT.PUT_LINE(V_TEMP);
DBMS_OUTPUT.PUT_LINE('&&&&&&&&&&&&&&&&&');
DBMS_OUTPUT.PUT_LINE(SUBSTR(V_OUTPUT,1,255));
DBMS_OUTPUT.PUT_LINE('n_BS_Pos:'||TO_CHAR(n_BS_Pos)||'--n_RT_Pos:'||TO_CHAR(n_LF_Pos));
DBMS_OUTPUT.PUT_LINE('--------------------');
*/
SELECT GREATEST(n_BS_Pos,n_LF_Pos) INTO n_Get_Pos FROM DUAL;
--DBMS_OUTPUT.PUT_LINE('n_Get_Pos'||to_char(n_Get_Pos));
--DBMS_OUTPUT.PUT_LINE(to_char(n_id)||'**********************');
IF n_Get_Pos<>0 THEN
v_Temp :=SUBSTR(v_Output,1,n_Get_Pos);
DBMS_OUTPUT.PUT_LINE(v_Temp);
v_Output :=SUBSTR(v_Output,n_Get_Pos+1,n_Get_Pos+OUTPUT_MAX_LEN);
ELSE
v_Temp :=SUBSTR(v_Output,1,OUTPUT_MAX_LEN);
DBMS_OUTPUT.PUT_LINE(v_Temp);
v_Output :=SUBSTR(v_Output,OUTPUT_MAX_LEN+1);
END IF;
--DBMS_OUTPUT.PUT_LINE('**********************');
EXIT WHEN v_Output IS NULL OR n_Get_Pos =0;
END LOOP;
END IF;
END;
FUNCTION Monday_Of_Week(id_Dttm DATE,iv_ISO_Flg VARCHAR2 DEFAULT 'TRUE') RETURN DATE
IS
BEGIN
IF id_Dttm IS NOT NULL THEN
IF UPPER(iv_ISO_Flg)='FALSE' THEN
RETURN TRUNC(id_Dttm,'WW');
END IF;
RETURN TRUNC(id_Dttm,'IW');
ELSE
RETURN NULL;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END Monday_Of_Week;
FUNCTION FirstDay_Of_Month(id_Dttm DATE) RETURN DATE
IS
BEGIN
IF id_Dttm IS NOT NULL THEN
RETURN TRUNC(id_Dttm,'MM');
ELSE
RETURN NULL;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END FirstDay_Of_Month;
PROCEDURE Get_SysParam(iv_Domain VARCHAR2,iv_Name VARCHAR2,ov_Value IN OUT VARCHAR2) IS
v_Value SysParam.Value%TYPE;
v_IsDynamic CHAR(1);
v_Dynamic_SQL SQLSTR;
v_Default_Value SysParam.Default_Value%TYPE;
v_Action_Name VARCHAR2(32) :='GET_SYSPARAM(V,V,V)';
v_Info INFO;
n_Locator NUMBER :=1;
BEGIN
SELECT Default_Value,Value,IsDynamic INTO v_Default_Value,v_Value,v_IsDynamic FROM SysParam WHERE UPPER(Domain)=UPPER(iv_Domain) AND UPPER(Name)=UPPER(iv_Name) AND ROWNUM=1;
IF v_Value IS NOT NULL THEN
IF v_IsDynamic='T' THEN
v_Dynamic_SQL :='SELECT'||BS||v_Value||BS||'FROM Dual';
ov_Value :=Dynamic_Result(v_Dynamic_SQL,ov_Value);
ELSE
ov_Value :=v_Value;
END IF;
ELSE
ov_Value :=v_Default_Value;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_Info :=SUBSTR('{NO PARAMETER FOUND}{iv_Domain:'||iv_Domain||'}{iv_Name:'||iv_Name||'}{ov_Value:'||ov_Value||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,SQLCODE,n_Locator,LOG_TYPE_WARNING,v_Info);
END IF;
WHEN OTHERS THEN
v_Info :=SUBSTR('{OTHER ERRORS}{iv_Domain:'||iv_Domain||'}{iv_Name:'||iv_Name||'}{ov_Value:'||ov_Value||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,SQLCODE,n_Locator,LOG_TYPE_WARNING,v_Info);
END IF;
END Get_SysParam;
PROCEDURE Get_SysParam(iv_Domain VARCHAR2,iv_Name VARCHAR2,on_Value IN OUT NUMBER) IS
v_Value SysParam.Value%TYPE;
v_IsDynamic CHAR(1);
v_Dynamic_SQL SQLSTR;
v_Default_Value SysParam.Default_Value%TYPE;
v_Action_Name VARCHAR2(32) :='GET_SYSPARAM(V,V,N)';
v_Info INFO;
n_Locator NUMBER :=1;
BEGIN
SELECT Default_Value,Value,IsDynamic INTO v_Default_Value,v_Value,v_IsDynamic FROM SysParam WHERE UPPER(Domain)=UPPER(iv_Domain) AND UPPER(Name)=UPPER(iv_Name) AND ROWNUM=1;
IF v_Value IS NOT NULL THEN
IF v_IsDynamic='T' THEN
v_Dynamic_SQL :='SELECT'||BS||v_Value||BS||'FROM Dual';
on_Value :=Dynamic_Result(v_Dynamic_SQL,on_Value);
ELSE
on_Value :=TO_NUMBER(v_Value);
END IF;
ELSE
on_Value :=TO_NUMBER(v_Default_Value);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_Info :=SUBSTR('{NO PARAMETER FOUND}{iv_Domain:'||iv_Domain||'}{iv_Name:'||iv_Name||'}{on_Value:'||TO_CHAR(on_Value)||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,SQLCODE,n_Locator,LOG_TYPE_WARNING,v_Info);
END IF;
WHEN OTHERS THEN
v_Info :=SUBSTR('{OTHER ERRORS}{iv_Domain:'||iv_Domain||'}{iv_Name:'||iv_Name||'}{on_Value:'||TO_CHAR(on_Value)||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,SQLCODE,n_Locator,LOG_TYPE_WARNING,v_Info);
END IF;
END Get_SysParam;
PROCEDURE Get_SysParam(iv_Domain VARCHAR2,iv_Name VARCHAR2,od_Value IN OUT DATE) IS
v_Value SysParam.Value%TYPE;
v_IsDynamic CHAR(1);
v_Dynamic_SQL SQLSTR;
v_Format SysParam.Format%TYPE;
v_Default_Value SysParam.Default_Value%TYPE;
v_Action_Name VARCHAR2(32) :='GET_SYSPARAM(V,V,D)';
v_Info INFO;
n_Locator NUMBER :=1;
BEGIN
SELECT Default_Value,Value,Format,IsDynamic INTO v_Default_Value,v_Value,v_Format,v_IsDynamic FROM SysParam WHERE UPPER(Domain)=UPPER(iv_Domain) AND UPPER(Name)=UPPER(iv_Name) AND ROWNUM=1;
IF v_Value IS NOT NULL THEN
IF v_IsDynamic='T' THEN
v_Dynamic_SQL :='SELECT'||BS||v_Value||BS||'FROM Dual';
od_Value :=Dynamic_Result(v_Dynamic_SQL,od_Value);
ELSE
od_Value :=TO_DATE(od_Value,v_Format);
END IF;
ELSE
od_Value :=TO_DATE(v_Default_Value,v_Format);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_Info :=SUBSTR('{NO PARAMETER FOUND}{iv_Domain:'||iv_Domain||'}{iv_Name:'||iv_Name||'}{ov_Value:'||TO_CHAR(od_Value,'YYYY-MM-DD HH24:MI:SS')||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,SQLCODE,n_Locator,LOG_TYPE_WARNING,v_Info);
END IF;
WHEN OTHERS THEN
v_Info :=SUBSTR('{OTHER ERRORS}{iv_Domain:'||iv_Domain||'}{iv_Name:'||iv_Name||'}{od_Value:'||TO_CHAR(od_Value,'YYYY-MM-DD HH24:MI:SS')||'}',1,INFO_LENGTH);
IF LogFlag=TRUE THEN
Log(Module_Name,Current_Action,NULL,SQLCODE,n_Locator,LOG_TYPE_WARNING,v_Info);
END IF;
END Get_SysParam;
/*****************************************************************************************
--取得当前逻辑程序的版本信息--
*****************************************************************************************/
FUNCTION Get_Version RETURN VARCHAR2
IS
BEGIN
RETURN VERSION;
END Get_Version;
/*****************************************************************************************
--取得当前逻辑程序的开发者信息--
*****************************************************************************************/
FUNCTION Get_Author RETURN VARCHAR2
IS
BEGIN
RETURN AUTHOR;
END Get_Author;
/**********************************************************************
--取得当前逻辑程序的描述信息--
**********************************************************************/
FUNCTION Get_Descr RETURN VARCHAR2
IS
BEGIN
RETURN DESCR;
END Get_Descr;
PROCEDURE ExportBlob(iv_FileName VARCHAR2,iv_Directory VARCHAR2,ic_Clob CLOB)
IS
ft_File UTL_FILE.FILE_TYPE;
n_Len NUMBER;
v_Buffer VARCHAR2(32767);
i_Amount BINARY_INTEGER := 32767;
i_Pos INTEGER := 1;
BEGIN
ft_File := UTL_FILE.FOPEN(iv_Directory,iv_FileName, 'w', 32767);
n_Len :=DBMS_LOB.GetLength(ic_Clob);
LOOP
Dbms_Lob.Read (ic_Clob, i_Amount, i_Pos, v_Buffer);
dbms_output.put_line(v_buffer);
UTL_FILE.PUT(ft_File, v_Buffer);
i_Pos := i_Pos + i_Amount;
EXIT WHEN i_Pos>n_Len;
END LOOP;
UTL_FILE.FCLOSE(ft_File);
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.Is_Open(ft_File) THEN
UTL_FILE.FCLOSE(ft_File);
END IF;
END;
FUNCTION Char_To_Table(i_Char VARCHAR2,i_Split VARCHAR2) RETURN CHR2TAB_Tab_Type
AS
v_tab CHR2TAB_Tab_Type := CHR2TAB_Tab_Type();
n_Pos NUMBER;
v_Char BLONGCHAR;
v_Item VARCHAR2(120);
BEGIN
v_Char :=i_Char;
n_Pos :=INSTR(v_Char,i_Split);
WHILE n_Pos>0 LOOP
v_Item :=SUBSTR(v_Char,1,n_Pos - 1);
v_Char :=SUBSTR(v_Char,n_Pos+1);
n_Pos :=INSTR(v_Char,i_Split);
IF LENGTH(TRIM(v_Item))>0 THEN
v_tab.extend;
v_tab(v_tab.last) := CHR2TAB_Row_Type(v_Item);
END IF;
END LOOP;
IF LENGTH(TRIM(v_Char))>0 THEN
v_tab.extend;
v_tab(v_tab.last) := CHR2TAB_Row_Type(v_Char);
END IF;
RETURN v_tab;
END;
FUNCTION Table_To_Char(iv_Table_Name VARCHAR2,iv_Column_List VARCHAR2,iv_Where VARCHAR2,iv_Split VARCHAR2) RETURN VARCHAR2
AS
v_RetChars BLONGCHAR;
v_Item VARCHAR2(120);
CR1 TRefCursor;
v_SQL SQLSTR;
v_Split VARCHAR2(16);
BEGIN
IF iv_Split IS NULL THEN
v_Split :=PUNC_COMMA;
ELSE
v_Split :=iv_Split;
END IF;
v_SQL :=GetSQL(iv_Table_Name,iv_Column_List,iv_Where);
OPEN CR1 FOR v_SQL;
LOOP
FETCH CR1 INTO v_Item;
EXIT WHEN CR1%NOTFOUND;
v_RetChars :=v_RetChars||v_Item||v_Split;
END LOOP;
CLOSE CR1;
RETURN RTRIM(v_RetChars,v_Split);
END;
FUNCTION Table_To_Char(iv_Owner VARCHAR2,iv_Table_Name VARCHAR2,iv_Column_List VARCHAR2,iv_Where VARCHAR2,iv_Split VARCHAR2) RETURN VARCHAR2
AS
v_RetChars BLONGCHAR;
v_Item VARCHAR2(120);
CR1 TRefCursor;
v_SQL SQLSTR;
v_Split VARCHAR2(16);
BEGIN
IF iv_Split IS NULL THEN
v_Split :=PUNC_COMMA;
ELSE
v_Split :=iv_Split;
END IF;
v_SQL :=GetSQL2(iv_Owner,iv_Table_Name,iv_Column_List,iv_Where);
OPEN CR1 FOR v_SQL;
LOOP
FETCH CR1 INTO v_Item;
EXIT WHEN CR1%NOTFOUND;
v_RetChars :=v_RetChars||v_Item||v_Split;
END LOOP;
CLOSE CR1;
RETURN RTRIM(v_RetChars,v_Split);
END;
FUNCTION Table_To_Char(iv_SQL VARCHAR2,iv_Split VARCHAR2) RETURN VARCHAR2
AS
v_RetChars BLONGCHAR;
v_Item MLONGCHAR;
CR1 TRefCursor;
v_Split VARCHAR2(16);
BEGIN
IF iv_Split IS NULL THEN
v_Split :=PUNC_COMMA;
ELSE
v_Split :=iv_Split;
END IF;
OPEN CR1 FOR iv_SQL;
LOOP
FETCH CR1 INTO v_Item;
EXIT WHEN CR1%NOTFOUND;
v_RetChars :=v_RetChars||v_Item||v_Split;
END LOOP;
CLOSE CR1;
RETURN RTRIM(v_RetChars,v_Split);
END;
PROCEDURE Set_ToFile_Flush_Rows(R INT)
IS
BEGIN
IF R>0 THEN
ToFile_Flush_Rows :=R;
END IF;
END;
PROCEDURE Table_To_File(iv_Location VARCHAR2,iv_File_Name VARCHAR2,iv_SQL VARCHAR2,iv_Split VARCHAR2)
AS
v_RetChars BLONGCHAR;
v_Item MLONGCHAR;
CR1 TRefCursor;
v_Split VARCHAR2(16);
ft_File UTL_FILE.FILE_TYPE;
i_Loop INT;
BEGIN
ft_File := UTL_FILE.FOPEN(location =>iv_Location,
filename => iv_File_Name,
open_mode => 'w',
max_linesize => 32767);
IF iv_Split IS NULL THEN
v_Split :=RT||LF;
ELSE
v_Split :=iv_Split;
END IF;
OPEN CR1 FOR iv_SQL;
LOOP
i_Loop :=i_Loop+1;
FETCH CR1 INTO v_Item;
EXIT WHEN CR1%NOTFOUND;
UTL_FILE.PUT(ft_File,v_Item||v_Split);
IF ToFile_Flush_Rows>0 THEN
IF MOD(i_Loop,ToFile_Flush_Rows)=0 THEN
UTL_FILE.FFlush(ft_File);
END IF;
END IF;
END LOOP;
CLOSE CR1;
UTL_FILE.FCLOSE(ft_File);
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.Is_Open(ft_File) THEN
UTL_FILE.FCLOSE(ft_File);
END IF;
END;
FUNCTION Get_Char_Text(i_Char VARCHAR2,ii_ID INT,i_Split VARCHAR2) RETURN VARCHAR2
AS
n_StartPos NUMBER;
n_EndPos NUMBER;
v_Char BLONGCHAR;
v_Split VARCHAR2(16);
BEGIN
IF i_Char IS NULL THEN
RETURN NULL;
ELSE
v_Char :=i_Char;
END IF;
IF ii_ID <=0 OR ii_ID IS NULL THEN
RETURN v_Char;
END IF;
IF i_Split IS NULL THEN
v_Split :=TB;
ELSE
v_Split :=i_Split;
END IF;
IF ii_ID=1 THEN
n_StartPos :=1;
n_EndPos :=INSTR(v_Char,v_Split,1,ii_ID);
IF n_EndPos=0 THEN
n_EndPos :=LENGTH(v_Char);
ELSE
n_EndPos :=n_EndPos - 1;
END IF;
ELSE
n_StartPos :=INSTR(v_Char,v_Split,1,ii_ID - 1);
IF n_StartPos =0 THEN
RETURN NULL;
ELSE
n_StartPos :=n_StartPos +1;
END IF;
n_EndPos :=INSTR(v_Char,v_Split,1,ii_ID);
IF n_EndPos=0 THEN
n_EndPos :=LENGTH(v_Char);
ELSE
n_EndPos :=n_EndPos -1;
END IF;
END IF;
RETURN SUBSTR(v_Char,n_StartPos,n_EndPos - n_StartPos+1);
END;
FUNCTION Get_Char_Text(i_Char VARCHAR2,ii_ID INT,i_StartSplit VARCHAR2,i_EndSplit VARCHAR2) RETURN VARCHAR2
AS
n_StartPos NUMBER;
n_EndPos NUMBER;
v_Char BLONGCHAR;
v_Split VARCHAR2(16);
BEGIN
IF i_Char IS NULL THEN
RETURN NULL;
ELSE
v_Char :=i_Char;
END IF;
IF ii_ID <=0 OR ii_ID IS NULL THEN
RETURN v_Char;
END IF;
IF i_StartSplit IS NULL OR i_EndSplit IS NULL THEN
RETURN v_Char;
END IF;
n_StartPos :=INSTR(v_Char,i_StartSplit,1,ii_ID - 1);
IF n_StartPos =0 THEN
n_StartPos :=1;
ELSE
n_StartPos :=n_StartPos +1;
END IF;
n_EndPos :=INSTR(v_Char,i_EndSplit,1,ii_ID);
IF n_EndPos=0 THEN
n_EndPos :=LENGTH(v_Char);
ELSE
n_EndPos :=n_EndPos -1;
END IF;
RETURN SUBSTR(v_Char,n_StartPos,n_EndPos - n_StartPos+1);
END;
FUNCTION Get_Lines(iv_Location VARCHAR2,iv_File_Name VARCHAR2) RETURN TEXT_TAB_Type
IS
Text_Tab Text_Tab_Type := Text_Tab_Type();
ft_File UTL_FILE.File_Type;
n_Line NUMBER := 1;
v_Text VARCHAR2(8000);
v_Line1 DBLONGCHAR :=NULL;
v_Line2 DBLONGCHAR :=NULL;
BEGIN
ft_File := UTL_FILE.FOpen(iv_Location, iv_File_Name, 'r',32767);
BEGIN
LOOP
UTL_File.Get_Line(ft_File, v_Text);
v_Text :=RTRIM(v_Text,RT||LF);
IF(v_Text IS NOT NULL) THEN
Text_Tab.Extend;
v_Line1 :=SUBSTR(v_Text,1,DBCHAR_LONG);
IF(LENGTHB(v_Text)>DBCHAR_LONG) THEN
v_Line2 :=SUBSTR(v_Text,DBCHAR_LONG+1,DBCHAR_LONG);
END IF;
Text_Tab(Text_Tab.Last) := Text_Row_Type(n_Line, v_Line1,v_Line2);
n_Line := n_Line + 1;
END IF;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
IF UTL_FILE.Is_Open(ft_File) THEN
UTL_FILE.FCLOSE(ft_File);
END IF;
END;
UTL_FILE.FClose(ft_File);
RETURN Text_Tab;
END;
FUNCTION Get_Text(iv_Location VARCHAR2,iv_File_Name VARCHAR2,ii_Col_Num INT,iv_Col_Split VARCHAR2,iv_FirstRow_IsData CHAR) RETURN FromFile_Tab_Type
IS
Text_Tab FromFile_Tab_Type := FromFile_Tab_Type();
ft_File UTL_FILE.File_Type;
n_Line NUMBER := 1;
v_Text VARCHAR2(32767);
n_Col_Num NUMBER;
v_Col_Split VARCHAR2(12);
n_SPos NUMBER;
n_EPos NUMBER;
n_Loop NUMBER :=0;
v_FirstRow_IsData CHAR(1);
TYPE TextX IS VARRAY(18) OF VARCHAR2(4000);
TX TextX :=TextX();
BEGIN
ft_File := UTL_FILE.FOpen(iv_Location, iv_File_Name, 'r',32767);
IF iv_Col_Split IS NULL THEN
v_Col_Split :=TB;
ELSE
v_Col_Split :=iv_Col_Split;
END IF;
IF ii_Col_Num>18 THEN
n_Col_Num :=18;
ELSE
n_Col_Num :=ii_Col_Num;
END IF;
v_FirstRow_IsData :=iv_FirstRow_IsData;
BEGIN
FOR i IN 1..18 LOOP
TX.Extend;
TX(i) :=NULL;
END LOOP;
LOOP
n_Loop :=n_Loop+1;
IF n_Loop=1 AND UPPER(v_FirstRow_IsData)='Y' THEN
utl_file.get_line(ft_File, v_Text);
ELSE
utl_file.get_line(ft_File, v_Text);
v_Text :=RTRIM(v_Text,RT||LF);
IF(v_Text IS NOT NULL) THEN
Text_Tab.Extend;
FOR i IN 1..n_Col_Num LOOP
IF i<=n_Col_Num THEN
IF i=1 THEN
n_SPos :=1;
n_EPos :=INSTR(v_Text,v_Col_Split,1,i);
IF n_EPos=0 THEN
n_EPos :=LEAST(LENGTH(v_Text),4000);
ELSE
n_EPos :=n_EPos -1;
END IF;
ELSE
n_SPos :=INSTR(v_Text,v_Col_Split,1,i - 1);
IF n_SPos<>0 THEN
n_SPos :=n_SPos+1;
END IF;
n_EPos :=INSTR(v_Text,v_Col_Split,1,i);
IF n_EPos=0 THEN
n_EPos :=LEAST(LENGTH(v_Text),4000);
ELSE
n_EPos :=n_EPos -1;
END IF;
END IF;
DBMS_OUTPUT.PUT_LINE('n_Loop:'||to_char(n_Loop)||'---i:'||to_char(i)||'--n_SPos:'||to_char(n_SPos)||'---n_EPos:'||to_char(n_ePos));
TX(i) :=SUBSTR(v_Text,n_SPos,n_EPos - n_SPos+1);
END IF;
END LOOP;
Text_Tab(Text_Tab.Last) := FromFile_Row_Type(n_Line, TX(1),TX(2),TX(3),TX(4),TX(5),TX(6),TX(7),TX(8),TX(9),TX(10),TX(11),TX(12),TX(13),TX(14),TX(15),TX(16),TX(17),TX(18));
n_Line := n_Line + 1;
END IF;
END IF;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
UTL_FILE.fclose(ft_File);
RETURN Text_Tab;
END;
FUNCTION MD5(iv_UserName VARCHAR2,iv_Password VARCHAR2)
RETURN VARCHAR2 AS
BEGIN
RETURN DBMS_OBFUSCATION_TOOLKIT.MD5(
input_string => UPPER(iv_UserName) || '/' || iv_Password);
END;
FUNCTION MD5(iv_Password VARCHAR2)
RETURN VARCHAR2 AS
BEGIN
RETURN DBMS_OBFUSCATION_TOOLKIT.MD5(input_string => iv_Password);
END;
FUNCTION Encrypt (iv_Text IN VARCHAR2) RETURN RAW IS
v_Text VARCHAR2(32767) := iv_Text;
r_Encrypted RAW(32767);
BEGIN
padstring(v_Text);
DBMS_OBFUSCATION_TOOLKIT.desencrypt(input => UTL_RAW.cast_to_raw(v_Text),
key => Crypt_Key,
encrypted_data => r_Encrypted);
RETURN r_Encrypted;
END;
FUNCTION Decrypt (ir_Raw IN RAW) RETURN VARCHAR2 IS
r_Encrypted VARCHAR2(32767);
BEGIN
DBMS_OBFUSCATION_TOOLKIT.desdecrypt(input => ir_Raw,
key => Crypt_Key,
decrypted_data => r_Encrypted);
RETURN RTrim(UTL_RAW.cast_to_varchar2(r_Encrypted), Pad_Char);
END;
PROCEDURE PadString (iv_Text IN OUT VARCHAR2) IS
n_Units NUMBER;
BEGIN
IF LENGTH(iv_Text) MOD 8 > 0 THEN
n_Units := TRUNC(LENGTH(iv_Text)/8) + 1;
iv_Text := RPAD(iv_Text, n_Units * 8, Pad_Char);
END IF;
END;
FUNCTION BAnd(ii_Int1 INT,ii_Int2 INT) RETURN INT
IS
BEGIN
IF ii_Int1 IS NULL OR ii_Int2 IS NULL THEN
RETURN NULL;
END IF;
RETURN TO_NUMBER((UTL_RAW.Cast_To_Varchar2(UTL_RAW.Bit_And(UTL_RAW.Cast_To_Raw(ii_Int1),UTL_RAW.Cast_To_Raw(ii_Int2)))));
END;
FUNCTION BOr(ii_Int1 INT,ii_Int2 INT) RETURN INT
IS
BEGIN
IF ii_Int1 IS NULL OR ii_Int2 IS NULL THEN
RETURN NULL;
END IF;
RETURN TO_NUMBER((UTL_RAW.Cast_To_Varchar2(UTL_RAW.Bit_Or(UTL_RAW.Cast_To_Raw(ii_Int1),UTL_RAW.Cast_To_Raw(ii_Int2)))));
END;
FUNCTION BXor(ii_Int1 INT,ii_Int2 INT) RETURN INT
IS
BEGIN
IF ii_Int1 IS NULL OR ii_Int2 IS NULL THEN
RETURN NULL;
END IF;
RETURN TO_NUMBER((UTL_RAW.Cast_To_Varchar2(UTL_RAW.Bit_Xor(UTL_RAW.Cast_To_Raw(ii_Int1),UTL_RAW.Cast_To_Raw(ii_Int2)))));
END;
Procedure Exc_Init
IS
BEGIN
--EXP_TAB :=TEXP_TAB(NULL);
--EXP_TAB.EXTEND;
Exc_Tab(-20001) :='User Defined Exceptiion 1';
Exc_Tab(-20002) :='User Defined Exceptiion 2';
Exc_Tab(-20003) :='User Defined Exceptiion 3';
Exc_Tab(-20004) :='User Defined Exceptiion 4';
Exc_Tab(-20005) :='User Defined Exceptiion 5';
Exc_Tab(-20006) :='User Defined Exceptiion 6';
Exc_Tab(-20007) :='User Defined Exceptiion 7';
Exc_Tab(-20008) :='User Defined Exceptiion 8';
END Exc_Init;
FUNCTION ExcMsg(ExcCode NUMBER) RETURN VARCHAR2
IS
BEGIN
RETURN Exc_Tab(ExcCode);
EXCEPTION
WHEN OTHERS THEN
RETURN 'No Exception Message.';
END ExcMsg;
PROCEDURE Delete_Dup_Rows(iv_Table_Name VARCHAR2,iv_Unique_Fld_List VARCHAR2)
IS
v_SQL SQLSTR;
n_ExitCode NUMBER;
BEGIN
v_SQL :='DELETE FROM'||BS||iv_Table_Name||BS||'WHERE ROWID IN (SELECT ROWID FROM'||BS||iv_Table_Name
||BS||'GROUP BY'||BS||iv_Unique_Fld_List
||BS||'MINUS'
||BS||'SELECT MIN(ROWID) FROM'||BS||iv_Table_Name
||BS||'GROUP BY'||BS||iv_Unique_Fld_List||')';
DBMS_OUTPUT.PUT_LINE(v_SQL);
Dynamic_Exec(n_ExitCode,v_SQL);
END;
PROCEDURE Delete_Dup_Rows2(iv_Table_Name VARCHAR2,iv_Join_Cond VARCHAR2)
IS
v_SQL SQLSTR;
n_ExitCode NUMBER;
BEGIN
v_SQL :='DELETE FROM'||BS||iv_Table_Name||BS||'A WHERE ROWID >ANY(SELECT ROWID FROM'||BS||iv_Table_Name
||BS||'B WHERE'||BS||iv_Join_Cond||')';
DBMS_OUTPUT.PUT_LINE(v_SQL);
Dynamic_Exec(n_ExitCode,v_SQL);
END;
PROCEDURE Delete_Dup_Rows3(iv_Table_Name VARCHAR2,iv_Join_Cond VARCHAR2)
IS
v_SQL SQLSTR;
n_ExitCode NUMBER;
BEGIN
v_SQL :='DELETE FROM'||BS||iv_Table_Name||BS||'A WHERE ROWID <>(SELECT MAX(ROWID) FROM'||BS||iv_Table_Name
||BS||'B WHERE'||BS||iv_Join_Cond||')';
DBMS_OUTPUT.PUT_LINE(v_SQL);
Dynamic_Exec(n_ExitCode,v_SQL);
END;
FUNCTION RevString(x VARCHAR2) RETURN VARCHAR2
IS
c CHAR(1);
i NUMBER;
v_RevStr VARCHAR2(4000) :='';
BEGIN
FOR i IN 1..LENGTH(x) LOOP
SELECT SUBSTR(x,LENGTH(x)-I+1,1) INTO c FROM Dual;
v_RevStr:=v_RevStr||c;
END LOOP;
RETURN v_RevStr;
END;
FUNCTION RowToCol( iv_SQL In VARCHAR2,iv_Split In VARCHAR2 DEFAULT ',' ) RETURN VARCHAR2
IS
iv_Ret_Value BLONGCHAR;
iv_Col_Value DBLONGCHAR;
C_dummy TRefCursor;
L NUMBER;
BEGIN
Open C_dummy For iv_SQL;
Loop
FETCH C_dummy INTO iv_Col_Value;
EXIT WHEN C_dummy%NOTFOUND;
iv_Ret_Value := iv_Ret_Value || iv_Split || iv_Col_Value;
END LOOP;
CLOSE C_dummy;
RETURN LTRIM(iv_Ret_Value,iv_Split);
EXCEPTION
WHEN OTHERS THEN
iv_Ret_Value := SQLERRM;
IF C_dummy%ISOPEN THEN
CLOSE C_dummy;
END IF;
RETURN iv_Ret_Value;
END;
PROCEDURE Copy_Tab_Srpt_Gen(iv_LogonString VARCHAR2,
iv_Src_Owner VARCHAR2,
iv_Dest_Owner VARCHAR2,
iv_Filter VARCHAR2,
iv_How_Filter VARCHAR2,
iv_Out_Target VARCHAR2,
iv_Copy_Type VARCHAR2,
iv_Table_Suffix VARCHAR2,
iv_Location VARCHAR2,
iv_File_Name VARCHAR2)
IS
v_SQL BLONGCHAR;
v_Copy_SQL MLONGCHAR;
v_Table SYSNAME;
v_Scr_User SYSNAME;
v_Dest_User SYSNAME;
v_Copy_Type VARCHAR2(16);
CR1 TRefCursor;
BEGIN
IF iv_LogonString IS NULL THEN
RETURN;
END IF;
IF iv_Src_Owner IS NULL THEN
v_Scr_User :=UPPER(SUBSTR(iv_LogonString,1,INSTR(iv_LogonString,'/') -1));
ELSE
v_Scr_User :=UPPER(iv_Src_Owner);
END IF;
IF iv_Dest_Owner IS NOT NULL THEN
v_Dest_User :=UPPER(iv_Dest_Owner)||PUNC_FULLSTOP;
END IF;
IF iv_Copy_Type IS NULL THEN
v_Copy_Type :='CREATE';
END IF;
dbms_output.put_line('v_Dest_User:'||v_Dest_User);
IF UPPER(iv_How_Filter) ='BEFORE' THEN
v_SQL :='SELECT '||PUNC_SQUOTION||'COPY FROM '||iv_LogonString||BS||v_Copy_Type||BS||v_Dest_User||PUNC_SQUOTION||'||Table_Name||'||PUNC_SQUOTION||UPPER(iv_Table_Suffix)||' USING SELECT * FROM ''||Table_Name ||'';'' FROM All_Tables WHERE Owner='||PUNC_SQUOTION||v_Scr_User||PUNC_SQUOTION|| ' AND Table_Name LIKE '||PUNC_SQUOTION||'%'||UPPER(iv_Filter)||PUNC_SQUOTION;
ELSIF UPPER(iv_How_Filter) ='AFTER' THEN
v_SQL :='SELECT '||PUNC_SQUOTION||'COPY FROM '||iv_LogonString||BS||v_Copy_Type||BS||v_Dest_User||PUNC_SQUOTION||'||Table_Name||'||PUNC_SQUOTION||UPPER(iv_Table_Suffix)||' USING SELECT * FROM ''||Table_Name ||'';'' FROM All_Tables WHERE Owner='||PUNC_SQUOTION||v_Scr_User||PUNC_SQUOTION|| ' AND Table_Name LIKE '||PUNC_SQUOTION||UPPER(iv_Filter)||'%'||PUNC_SQUOTION;
ELSIF UPPER(iv_How_Filter) ='ALL' OR iv_How_Filter IS NULL THEN
v_SQL :='SELECT '||PUNC_SQUOTION||'COPY FROM '||iv_LogonString||BS||v_Copy_Type||BS||v_Dest_User||PUNC_SQUOTION||'||Table_Name||'||PUNC_SQUOTION||UPPER(iv_Table_Suffix)||' USING SELECT * FROM ''||Table_Name ||'';'' FROM All_Tables WHERE Owner='||PUNC_SQUOTION||v_Scr_User||PUNC_SQUOTION|| ' AND Table_Name LIKE '||PUNC_SQUOTION||'%'||UPPER(iv_Filter)||'%'||PUNC_SQUOTION;
END IF;
dbms_output.put_line('Locator02');
dbms_output.put_line('v_SQL:'||v_SQL);
IF UPPER(iv_Out_Target)='SCREEN' THEN
OPEN CR1 FOR v_SQL;
LOOP
FETCH CR1 INTO v_Copy_SQL;
IF(v_Copy_SQL IS NOT NULL) THEN
dbms_output.put_line('Locator02.5');
dbms_output.put_line('v_Copy_SQL:'||v_Copy_SQL);
BASE_FUNC.Screen_Output(v_Copy_SQL);
dbms_output.put_line('Locator03');
END IF;
EXIT WHEN CR1%NOTFOUND;
END LOOP;
CLOSE CR1;
ELSIF UPPER(iv_Out_Target)='FILE' THEN
BASE_FUNC.Table_To_File(iv_Location,iv_File_Name,v_SQL,NULL);
END IF;
END;
PROCEDURE Copy_Tab_Srpt_Gen(iv_LogonString VARCHAR2,iv_Src_Owner VARCHAR2,iv_Filter VARCHAR2)
IS
BEGIN
Copy_Tab_Srpt_Gen(iv_LogonString,iv_Src_Owner,NULL,NULL,NULL,'SCREEN',NULL,NULL,NULL,NULL);
END;
FUNCTION GetDate RETURN DATE
IS
BEGIN
RETURN Start_Date+(DBMS_Utility.Get_Time-Start_Time)/8640000;
END;
PROCEDURE Sys_Init
IS
BEGIN
SELECT SYSDATE,DBMS_Utility.Get_Time INTO Start_Date,Start_Time FROM DUAL;
ToFile_Flush_Rows :=0;
END;
FUNCTION GetLocalIP RETURN VARCHAR2
IS
BEGIN
RETURN Sys_Context('USERENV','IP_ADDRESS');
END;
FUNCTION GetHostName(IP VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN UTL_Inaddr.Get_Host_Name(IP);
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE=-29257 THEN
RETURN 'UNKOWN HOST';
ELSE
RETURN 'UNKOWN ERROR';
END IF;
END;
FUNCTION GetIPAddr(Host VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN UTL_Inaddr.Get_Host_Address(Host);
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE=-29257 THEN
RETURN 'UNKOWN HOST';
ELSE
RETURN 'UNKOWN ERROR';
END IF;
END;
PROCEDURE Send_Mail(sender IN VARCHAR2,recipient IN VARCHAR2,subject IN VARCHAR2,message IN VARCHAR2)
IS
mailhost VARCHAR2(30) := 'smtp01.us.oracle.com';
mail_conn utl_smtp.connection;
crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
mesg VARCHAR2( 1000 );
BEGIN
mail_conn := Utl_Smtp.Open_Connection(mailhost, 25);
mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
'From: <'||sender||'>' || crlf ||
'Subject: '||subject || crlf ||
'To: '||recipient || crlf ||
'' || crlf || message;
UTL_SMTP.HELO(mail_conn, mailhost);
UTL_SMTP.MAIL(mail_conn, sender);
UTL_SMTP.RCPT(mail_conn, recipient);
UTL_SMTP.DATA(mail_conn, mesg);
UTL_SMTP.QUIT(mail_conn);
END;
FUNCTION GetWordSpell(iv_Phrase VARCHAR2,
iv_Split VARCHAR2,
iv_First_Name_Split VARCHAR2,
iv_Case VARCHAR2,
iv_Order VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
IS
v_Word CHAR(2);
n_Word_Length NUMBER;
v_WordSpell VARCHAR2(8);
v_PhreseSpell VARCHAR2(30000);
v_First_Name VARCHAR2(8);
BEGIN
n_Word_Length :=LENGTH(iv_Phrase);
IF n_Word_Length=0 THEN
RETURN NULL;
END IF;
FOR ii IN 1..n_Word_Length LOOP
v_Word :=SUBSTR(iv_Phrase,ii,1);
BEGIN
SELECT Word_Spell INTO v_WordSpell FROM Base$Chinese WHERE Word=v_Word;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_WordSpell:='[*****]';
END;
IF UPPER(SUBSTR(iv_Case,1)) ='U' THEN
v_WordSpell :=UPPER(v_WordSpell);
ELSIF UPPER(SUBSTR(iv_Case,1)) ='L' THEN
v_WordSpell :=LOWER(v_WordSpell);
ELSIF UPPER(SUBSTR(iv_Case,1)) ='C' THEN
v_WordSpell :=INITCAP(v_WordSpell);
END IF;
IF ii=1 THEN
v_First_Name :=v_WordSpell;
ELSE
v_PhreseSpell :=v_PhreseSpell||v_WordSpell||iv_Split;
END IF;
END LOOP;
dbms_output.put_line('v_First_Name:'||v_First_Name||'-');
dbms_output.put_line('v_PhreseSpell:'||v_PhreseSpell||'-');
IF iv_Split IS NOT NULL THEN
v_PhreseSpell :=RTRIM(v_PhreseSpell,iv_Split);
END IF;
IF UPPER(iv_Order)='REVERSE' THEN
v_PhreseSpell :=v_PhreseSpell||iv_First_Name_Split||v_First_Name;
ELSE
v_PhreseSpell :=v_First_Name||iv_First_Name_Split||v_PhreseSpell;
END IF;
RETURN v_PhreseSpell;
END;
FUNCTION GetWordSpell(iv_Phrase VARCHAR2) RETURN VARCHAR2
IS
v_NULL VARCHAR2(30) :=NULL;
BEGIN
RETURN GetWordSpell(iv_Phrase,NULL,NULL,NULL,NULL);
END;
FUNCTION GetWordInitial(iv_Phrase VARCHAR2,iv_Split VARCHAR2,iv_First_Name_Split VARCHAR2,iv_Case VARCHAR2,iv_Order VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
IS
v_Word CHAR(2);
n_Word_Length NUMBER;
v_WordInitial VARCHAR2(8);
v_PhreseInitial VARCHAR2(30000);
v_First_Name VARCHAR2(8);
v_First_Name_Split VARCHAR2(8);
BEGIN
n_Word_Length :=LENGTH(iv_Phrase);
IF n_Word_Length=0 THEN
RETURN NULL;
END IF;
FOR ii IN 1..n_Word_Length LOOP
v_Word :=SUBSTR(iv_Phrase,ii,1);
BEGIN
SELECT Word_Initial INTO v_WordInitial FROM Base$Chinese WHERE Word=v_Word;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_WordInitial:='[*]';
END;
IF UPPER(SUBSTR(iv_Case,1)) ='U' THEN
v_WordInitial :=UPPER(v_WordInitial);
ELSIF UPPER(SUBSTR(iv_Case,1)) ='L' THEN
v_WordInitial :=LOWER(v_WordInitial);
ELSIF UPPER(SUBSTR(iv_Case,1)) ='C' THEN
v_WordInitial :=INITCAP(v_WordInitial);
END IF;
IF ii=1 THEN
v_First_Name :=v_WordInitial;
ELSE
v_PhreseInitial :=v_PhreseInitial||v_WordInitial||iv_Split;
END IF;
END LOOP;
dbms_output.put_line('v_First_Name:'||v_First_Name||'-');
dbms_output.put_line('v_PhreseInitial:'||v_PhreseInitial||'-');
IF iv_Split IS NOT NULL THEN
v_PhreseInitial :=RTRIM(v_PhreseInitial,iv_Split);
END IF;
IF iv_First_Name_Split IS NULL THEN
v_First_Name_Split :=iv_Split;
ELSE
v_First_Name_Split :=iv_First_Name_Split;
END IF;
IF UPPER(iv_Order)='REVERSE' THEN
v_PhreseInitial :=v_PhreseInitial||v_First_Name_Split||v_First_Name;
ELSE
v_PhreseInitial :=v_First_Name||v_First_Name_Split||v_PhreseInitial;
END IF;
RETURN v_PhreseInitial;
END;
FUNCTION GetWordInitial(iv_Phrase VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN GetWordInitial(iv_Phrase,NULL,NULL,NULL,NULL);
END;
FUNCTION GetWordAscii(iv_Word VARCHAR2) RETURN NUMBER
IS
n_Word_Ascii NUMBER;
BEGIN
SELECT Word_Ascii INTO n_Word_Ascii FROM Base$Chinese WHERE Word=iv_Word;
RETURN n_Word_Ascii;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
END;
FUNCTION GetWordID(iv_Word VARCHAR2) RETURN NUMBER
IS
n_Word_ID NUMBER;
BEGIN
SELECT Word_ID INTO n_Word_ID FROM Base$Chinese WHERE Word=iv_Word;
RETURN n_Word_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
END;
FUNCTION GetWord(ii_Ascii INT) RETURN CHAR
IS
c_Word CHAR(2);
BEGIN
SELECT Word INTO c_Word FROM Base$Chinese WHERE Word_Ascii=ii_Ascii;
RETURN c_Word;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN '[*]';
END;
BEGIN
Exc_Init;
Sys_Init;
END Base_Func;
/
扫一扫
384
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
