/ * - Create Directory, use create user a4 Identified by A4; Create Directory MyDir on 'E: / Oracle'; Grant Read, Write on Directory MyDir to A4; Conn A4 / A4 @ orCL - Create a log table DROP Table log; crete 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)); - establishing EXCEPTION_TABLES table 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_EXCEPTION', '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_EXCEPTIO NS2 ',' F ', NULL, NULL, 0); - Establish character transformation of basic type 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; /
- Establish system parameter table Drop Table System; 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)); - if necessary conn sys / bing @ orcl as sydbagrant select on v_ $ locked_object To System; - Creating Chinese Character 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 $ Chinese is 'Chinese character pinyin (foundation, not included in the system!)' /
Comment On Column Base $ CHINESE.WORD_ID IS 'Serial Number' /
Comment On Column Base $ CHINESE.WORD IS 'Chinese characters' /
Comment On Column Base $ Chinese.word_spell is 'Pinyin' /
Comment On Column Base $ Chinese.Word_initial IS 'First Letter' /
- Establish a fundamental type DROP TYPE TEXT_TAB_TYPE; DROP TYPE TEXT_ROW_TYPE; CREATE 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 ", 'd.txt');
- Reads return a table establishing the type DROP TYPE FromFile_Tab_Type base from a text file; 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), 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; /
* / Crete 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 varcha2 (32): = 'form'; log_style_grid constant varcha2 (32): = 'grid'; BS constant varcha2 (1): = '; rt varcha2 (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 varcha2 (12): = 'db'; logto_screen constant varcha2 (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_stab texc_tab; start_date date; start_time number; tofile_flush_rows number; - ************************************************* *********************************************************** *********** --2.Declare procedure or function ************************************************* ************************************************; / ********* *********************************************************** ************************************* Function Description: Call Dynamic SQL statements include (DML and DDL statements ), But limiting only a row of statements. ON_EXITCODE returns the success of the execution or not. 0- Successful return,> 0- Return operation of the statement returns the number of lines of operation, <0- Failed error code IV_Dynamic_SQL: Pass the statement to be dynamically executed, but must be a statement, and without " ;"of. Otherwise an error. Example: "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);
/ ************************************************** ********************************************* Function Description: Call Dynamic SQL statements include (DML, and DDL statements), but limits only a row of statements. ON_EXITCODE returns the success of the execution or not. 0- Successful return,> 0- Rowless statement returns the number of lines of operation, <0- Failed error code IV_DYNAMIC_SQL: Pass the statement to be dynamically executed, the specific format is determined according to IV_DM_TYPE. IV_DM_TYPE determines the format of the dynamic statement, there are two parameters "r" and "x". If the parameter is "R", it is indicated that the normalized dynamic statement is executed, beware that the "begin" and "end" symbols are not, and the user will use a semicolon between the different statements as long as the statement to be executed. Example: "Truncate Table MD_INV_STYLE; TRUNCATE TABLE MD_CAL; INSERT INTO MD_CAL VALUES (100, 'CAL_NAME', 6)" If the parameter is "x" or for other values, it indicates that the user-defined dynamic statement execution is completely Control structure and syntax, the system does not do any changes directly, and different statements should be separated by semicolons. Note For DDL operations (such as "Truncate Table", use dynamic processing. Example: "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; - ***** *********************************************************** ************************************************
/ ************************************************** ******************************************** Function Description: Execution Dynamic statement, and return to the OUT parameter (only one parameter, if multiple processes, multiple processes can be called, multiple OUT return parameters during one process, can not use this function) The return value of the call, this function includes three The overload function call dynamic SQL statement includes (DML statement) returns the result, but does not limit the number of rows of the statement (executing multi-line dynamic SQL statements using this function). IV_Dynamic_sql: Pass the statement to be dynamically executed, the format is correct, pay this statement according to the standard specification, pay attention to "begin" and "end" symbols, users only need to write the statement to be executed, the different statements should be separated by semicolons . IV_INIT_VAL: Used to indicate the initialization value of the type and parameter of the return parameter, for example: the return value can only be one, and the form of the bonding variable has a colon: ", such as:" Begin delete from student_table; Mypackage1.mainProc2 (: x); end; "After calling, the function return value returns the process call return value" 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; - ******************************************** *********************************************************** ************
/ ************************************************** ******************************************** Function Description: Execution Dynamic statement, and return the statement of the result set (only one row of data, only one column, is a result) the return value of the call, this function includes three overload function call dynamic SQL statements including (DML and DDL statements), Only one dynamic SQL statement can be performed. IV_DYNAMIC_SQL: Pass the statement to be dynamically executed, the format is correct, passed this statement according to the standard specification, pay attention to "Begin" and "END" symbols, and cannot bring ";".
IV_INIT_VAL: The initialization value used to indicate the type and parameter of the return parameter, for example: "Select sysdate from dual" or "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; - ***************************************************** *********************************************************** ********* / *************************************************** *********************************************************** ****** Function Description: Compile IV_OBJECT_NAME for objects invalidated) state: Pass object name IV_OWNER: Owner Usage: Begin Base_f UNC.Compile_Object ('myprocedure1'); end; --------------------------------------- ---- Begin Base_Func.Compile_Object ('Myowner', 'MyProcedure1'); END; ***************************************** *********************************************************** **************** / Procedure Compile_Object (iv_object_name in varcha2; procedure compile_object (iv_owner varcha2, iv_Object_name in varchar2); - ************ *********************************************************** *********************************************************** *********************************************************** ************************************ Function Description: Trip Table ON_EXITCODE: Return Value, 0- Success, <
0- error code iv_Table_Name: to be truncated to show iv_Owner: Owner Usage object: 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); / *********************************** *********************************************************** ******************** Function Description: Delete some records in_Exitcode: Return Value,> = 0 - Remove the number of rows, <0- error code iv_Table_Name: the name of the table to be deleted iv_Owner: Owner iv_Del_Where object: deleting condition Usage: DECLARE X NUMBER; BEGIN Base_Func.Delete_Table (X, 'MYTABLE1', 'ID> 1'); Base_Func.Delete_Table (X, 'MYOWNER ',' Mytable1 '); END; *************************************************** *********************************************************** ***** / procedure delete_tab le (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); / ******************* *********************************************************** ************************** Function Description: Get an Exception Table Name Usage: Base_Func.get_Exception_Tab; ********** *********************************************************** ********************************************** / FUNCTION GET_EXCEPTION_TAB RETURN VARCHAR2; / ******* *********************************************************** ******************************************* Function Description:
Get an Exception Table Name IV_TYPE: Pass the Exception Table Type Usage: Base_Func.get_Exception_Tab ('P'); Base_Func.get_Exception_Tab ('f'); ******************* *********************************************************** ************************** / FUNCTION GET_EXCEPTION_TAB (IV_TYPE VARCHAR2) RETURN VARCHAR2; / ************* *********************************************************** ********************************* Function Description: Reset the status of Exception Table USAGE: base_func.reset_exception_tab; ** *********************************************************** **************************************************************************** / Procedure Reset_exception_tab; / * *********************************************************** ***************************************************** Function Description: Reset Exception Table Status IV_EXECEPTION_TABLE: Pass Exception Table Name Base_func.reset_exception_tab ('Exceptions'); ***************************************** *********************************************************** *************** / Procedure RESET_EXCEPTION_TAB (IV_EXECEPTION_TABLE VA RCHAR2);
/ ************************************************** ********************************************* Function Description: Change the primary key to Enabled or Disabled ON_EXITCODE: Return the execution result, 0- correct, <0 error code IV_WNER: Owner IV_TABLE_NAME: Table Name IV_STATUS: Status IV_EXCEPTIONTABLE: Repeat Record Table Usage: Declare X Number; begin base_func.alter_pkey (x, 'myowner'); 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_EXI) TCODE OUT NUMBER, IV_TABLE_NAME VARCHAR2, IV_STATUS VARCHAR2, IV_STATUS VARCHAR2, IV_EXCEPTIONTABLE VARCHAR2; / ********************************************* *********************************************************** ************* Function Description: Set the system session execution status IV_MODULE_NAME: Module Name IV_AMTION_NAME: Behavior Name Usage: Begin Base_func.Application_info ('Base_Func', 'ALTER_PKEY'); END; * *********************************************************** ********************************************************************* / Procedure Application_info (iv_module_name VARCHAR2, IV_ACITION_NAME VARCHAR2);
/ ************************************************** ********************************************* Function Description: Setting system session client system name IV_Module_name: Module Name Usage: Begin Base_func.client_info ('MySystemClient'); END; ************************ *********************************************************** ******************** / Procedure Client_info (iv_module_name varcha2); / ******************** *********************************************************** ************************** Set system log processing logo B: Processing flag true- Write log, false - Do not write log usage: begin base_func .S; ******************************************************************************************************************************** *********************************************************** **** / procedure set_logflag (b boolean); / ********************************************************** *********************************************************** ******** Log record main function IV_MODULE_NAME: Module Name IV_Action_Name: Action Name IV_SUB_ACTION: Sub Action Name In_Sqlcode: IN_LOCATOR: Location IV_TYPE: Type, Debug - Debug, Error- Warning - Warning, Info - Notification IV_IN FO: Error Details 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 varcha2, IV_SUB_AME VARCHAR2, IV_SUB_AMTION VARCHAR2, IV_SQLCODE NUMBER, IN_LOCATOR NUMBER, IV_TYPE VARCHAR2, IV_INFO INFO); / ************************************************* *********************************************************** **************** Sets the target IV_LOGTO: Output target parameters, file-output to file, DB-output to the database, Screen-output to the screen USAGE: Begin Base_Func. Set_logto ('screen ");
*********************************************************** ******************************************************** / Procedure set_logto ( Iv_logto varchar2); / ********************************************************************* *********************************************************** * Set the system log file name IV_logfile: Output file name parameter usage: begin set_logfile ('mylog20050322.txt'); end; *********************** *********************************************************** ******************** / Procedure set_logfile (iv_logfile varcha2); / ******************* *********************************************************** ************************** Set system log file location IV_LogFile: Output file location parameter usage: begin base_func.set_loglocation ('E: / Oracle '); End; --------------------------------------- Begin set_loglocation (' TextOutput '); END; ************************************************************* *********************************************************** * / Procedure set_loglocation (iv_loglocation varchar2); / ******************** *********************************************************** ************************** Setting the system log screen output Style IV_LogStyle: Output File Style Parameters, Grid-Table Style, Form-Format USAGE: Begin Base_func.set_logstyle ('grid'); End; ------------------------------------ --- Begin Base_func.set_logstyle ('form'); END; ****************************************** *********************************************************** *********** / Procedure set_logstyle (iv_logstyle varchar2); / **************************************** *********************************************************** **************** Set the style IV_logstyle, output file style parameters, Grid-Table style, Form-form: begin base_func.set_logstyle ('grid) );
End; --------------------------------------- Begin Base_Func.SET_LOGSTYLE ('Form' ); END; ************************************************************* *********************************************************** / Procedure screen_output (iv_output varcha2, fmt_flag number default 0); / ****************************************************** *********************************************************** ********** Function Description: Take a certain time on Monday ID_DTTM: Enter the date IV_ISO_FLG: Output Sign, the default is true, True- Returns by ISO mode, FALSE is returned according to normal mode. Usage: = base_func.monday_of_week (sysdate, true); end; ******************************* *********************************************************** ***************** / FUNCTION MONDAY_OF_WEEK (ID_DTTTM DATE, IV_ISO_FLG VARCHAR2 Default 'Return Date; / ************** *********************************************************** ******************************** Function Description: Take a certain date ID_DTTM: Enter the date USAGE: DECLARE X date; Begin x: = BAS E_MONTH (SYSDATE, TRUE); END; *************************************************** *********************************************************** ******* / FUNCTION FIRSTDAY_OF_MONTH (ID_DTTTM Date) Return Date; / ***************************************** *********************************************************** ************* Function Description: Get System Configuration Parameters IV_DOMAIN: Parameter belonging IV_NAME: Parameter Name OV_Value: Default value of parameters 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); / ******* *********************************************************** ********************************************** Function Description: Get system basic information, including the version, Sample *********************************************************** ********************** / FUNCTION GET_VERSION RETURN VARCHAR2; FUNCTION GET_AUTHOR RETURN VARCHAR2; FUNCTION GET_DESCR RETURN VARCHAR2; - *********** *********************************************************** *********************************************************** ************ *********************************************************** ******************* Function Description: Output CLOB and BLOB Data to File IV_FileName: File Name IV_Clob: Clob Database Usage: Begin Base_Func. Exportblog ('myclob1.txt', 'mydirectory1', [clob type]); end; **************************************** *********************************************************** **************** / Procedure Exportblob (IV_FileName Varchar2, IV_Directory Varchar2, IC_Clob Clob); / ******************** *********************************************************** *********************************** Function Description: Returns all field constructs string list IV_TABLE_NAME: Table Name Usage: SELECT BASE_FUNC.GET_ALL_COLS 'MyTable1') from dual;
*********************************************************** ********************************************** / FUNCTION GET_ALL_COLS IV_TABLE_NAME VARCHAR2) RETURN VARCHAR2; / **************************************************** *********************************************************** *** Function Description: Return to the SQL Statement IV_TABLE_NAME: Table Name Flag: Logo Labeling Default 0,0- Use "*", <> 0- Use all field string list IV_COLUMN_LIST: String field list IV_where: limitation factor ************************************************ *********************************************************************************** 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;
/ ************************************************** ********************************************* Function Description: Return all comma-constructed comma-separated strings list IV_OWNER: owner IV_TABLE_NAME: Table Name Usage: SELECT BASE_FUNC.GET_ALL_COLS ('Myowner', 'MyTable1') from dual; ************ *********************************************************** *************************************************************************************************************************** *********************************************************** ***************************************************** Function Description: Return to a SQL statement IV_TABLE_NAME: Table Name Flag: Logo Default is 0,0- Use "*", <> 0- using all field strings list IV_COLUMN_LIST: String field list IV_where: Restriction condition usage: select base_func.getsql2 ( 'MyOwner', 'MyTable1', 0) from Dual; Select Base_Func.getsql2 ('Myowner', 'MyTable1', 0, 'ID, Name', 'Id> 8') from Dual; ******* *********************************************************** *********************************************************************** ********************************************************* Number Default 0) Return Var CHAR2; 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;
/ ************************************************** ********************************************* Function Description: Convert the string of the separator to be converted into a table to return i_CHAR: String i_Split: Separator useage: Select * from table (base_func.char_to_table ('jiangbing, zhanglirong, chenyanxia,', ')); ****** *********************************************************** ************************************************************ / FUNCTION CHAR_TO_TABLE (i_char varcha2, i_split varcha2) RETURN chr2tab_tab_type; / ******************************************************************* *********************************************************** Function Description: Transform the value of a field in the table Returns IV_OWNER: Owner IV_TABLE_NAME: Table IV_COLUMN_LIST: Field list, regardless of the returns of several fields, and formatted Example: To_char (id) || ',' || r r (name) IV_where: Restriction Condition I_SPLIT: Sneeak 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_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; / **** *********************************************************** ************************************************** Function Description: Return the table Result set (character format) Output to file IV_LOCATION: Output Position IV_FILE_NAME: Output file name IV_SQL: Sproduct IV_SPLIT: Delivery 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; / ******************************************************* *********************************************************** ********** Function Description: String I_CHAR: String II_ID: String II_ID: Location I_Split: Snex ('JIANGBING; CHENYANXIA) from the string of the specified separator ZHANGLIRONG; LIWEN; ', 2,'; ') from Dual; **************************************************** *********************************************************** *********** / FUNCTION GET_CHAR_TEXT (i_char varcha2, II_id int, i_split varcha2) Return varchar2; / ********************** *********************************************************** ************************* Function Description: Get a string I_CHAR: String II_ID from the specified separator string: String II_ID: Location I_SPLIT: Separator 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; / ********************************* *********************************************************** ************************ Function Description: A string IV_LOCATION: String IV_FILE_NAME: Location II_COL_NUM from the string of the specified separator : Separator IV_COL_SPLIT IV_FIRSTROW_ISDATA Useage: Select * from table (get_text ('mydir', 'd.txt', 2, chr (9), 'n'); ************ *********************************************************** ***************************************************************************************** CHAR) RETURN FROMFILE_TAB_TYPE;
/ ************************************************** ********************************************* Function Description: MD5 encryption IV_USERNAME: Login Name IV_Password: Login Password Useage: SET ServerOutput On Size 100000 Declare X VARCHAR2 (60); begin x: = base_func.md5 ('jiangbing', '123456'); dBMS_output.put_Line (x); ------------------------------------------ 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; / **** *********************************************************** *************************************************** Function Description: Encryption IV_Text: To encrypt 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; / *********************** *********************************************************** ************************* Function Description: Bit Operation II_INT1: Bit Calculation Numerical 1 II_INT2: Bit Calculation Numerical 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 II_INT2 II_INT2 INT) Return Int; / ********************************************* *********************************************************** ****************** Function Description: Depending on the custom message code, returning a custom exception message content EXCCCODE: Custom Message Useage: Declare X varcha2 (1024); Begin x: = base_func.excmsg (-20001); END; ************************************************** *********************************************************** ********** / FUNCTION EXCMSG (Excode Number) Return VARCHAR2; / ***************************************** ** *********************************************************** ************** Function Description: Delete the repeated line IV_TABLE_NAME under specific conditions in the table: Table Name IV_UNIQUE_FLD_LIST: Unique Conditions List IV_JOIN_COND: Joint Condition, Example: a.Table_ID = B.TABLE_ID or 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 varcha2, iv_unique_fld_list varcha2); Procedure DELETE_DUP_ROWS2 (IV_TABLE_NAME VARCHAR2, IV_JOIN_COND VARCHAR2); Procedure delete_dup_rows3 (iv_table_name varcha2, iv_join_cond varcha2);
/ ************************************************** ********************************************* Function Description: Row the string of strings x: String Useage: select base_func.revstring ('jiangbing') from dual; *********************************** *********************************************************** ************************************************************************************************************************ *********************************************************** ************************* Function Description: Output IV_SQL in a certain format string in a certain format: Output SQL statement IV_SPLIT: Delivery, By default, comma users: select base_func.rowtocol ('select name from tx', ',') from dual; ********************************** *********************************************************** ****************************************************************************************************************************************** 10; / *** *********************************************************** ******************************************** Function Description: Generate Table Copy Script IV_LOGONSTRING: Script of the Output Use the login string, mode is: user / password @ orcl iv_src_owner: Source User IV_DEST_OWNER: Target User I v_filter: Filter condition IV_HOW_FILTER: How to filter, "%" adds to IV_FILTER which section, in front? Behind? Still before and after. Before, add to the front, after- add IV_OUT_TARGET: Output Target, Screen-Output to the screen, file-output to file IV_copy_type: Copy type, append- increase, create- creation , INSERT- insert, REPLACE- Alternatively iv_Table_Suffix: a duplicate generating suffix iv_File_Name table: Useage output file name: BEGIN Base_Func.Copy_Tab_Srpt_Gen ( 'system / bing @ orcl', 'A4', '%'); Base_Func.Copy_Tab_Srpt_Gen ( 'System / Bing @ Orcl', 'A4', 'Myowner', 'Exception', '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); / ***** *********************************************************** ************************************************** Function Description: Get server system time availableage: Select base_func..getdate from dual; *************************************************** *********************************************************** **** / function; / ********************************************************************************************************************************************************************** *********************************************************** ******* Function Description: Get the server local IP address useage: select base_func..getlocalip from dual; ********************************** ****************** *********************************************************** *** / function getLocalip return varchar2; / *************************************************** *********************************************************** ****** Function Description: Depending on the IP address, the server host name IP: IP address useage: select base_func.gethostname ('192.168.100.2') from dual; ************** *********************************************************** ************************************************************************************************************************ *********************************************************** ************************************** Function Description: Get the IP address host based on the server host name : Server host name Useage: select base_func.gethostname ('192.168.100.2') from dual;
*********************************************************** *************************************************************** / FUNCTION GetIPAddr ( Host varchar2) return varcha2; / ************************************************************* *********************************************************** *** Function Description: Send Mail Sender: Send Man Recipient: Receiver Subject: Theme Message: 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 varchail2, recipient in varcha2, subject in varchar2, message in varchar2); / ************* *********************************************************** ************************************** Function Description: Acaming Chinese characters Strings The first letter combination IV_phrase: Chinese character string IV_SPLIT : Split IV_FIRST_NAME_SPLIT: IV_CASE: Size: U-uppercase, L-lowercase, C- Clord, L-lowercase, C-first letters IV_Order: Sequential REVERSE - Reverse Useage: SELECT BASE_FUNC.GETWORDInitial ('Pie') from Dual; SELECT BASE_FUNC.GETWORDINITIAL ('Pan History', ',', '.', '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 varcha2; / **************************************************** *********************************************************** **** Function Description: A letter combination IV_PHRASE: Chinese character string Useage: select base_func.getWordspell ('Pie') from Dual;
*********************************************************** ****************************************************** / Function GetWordspell IV_phrase varchar2) return varcha2; / ************************************************************************ *********************************************************** *** Function Description: Take the Pinyin combination of Chinese characters Strings IV_PHRASE: Chinese character string IV_SPLIT: Split IV_FIRST_NAME_SPLIT: IV_CASE: Size Sign, U-uppercase, L-lowercase, c- 首 大 大 __order: Sequential REVERSE - Reverse Useage: Select Base_Func.getWordspell ('Pie History', ',', '.', '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; / ********************************************************** ******************* ********************************* Function Description: Get Chinese characters in the system ID iv_word: Chinese criterion Useage: SELECT SELECTIDID ('Will') from dual; ***************************************************** *********************************************************** **** / function getWordId (iv_word varcha2) Return Number; / ************************************************************* *********************************************************** *********** Function Description: Get the ASCII code IV_Word of Chinese characters: Chinese characters Useage: SELECT BASE_FUNC.GETWORDASCII ('will') from Dual;
*********************************************************** ***************************************************************** / Function GetWordascii ( Iv_word varchar2) Return Number; / *********************************************************************** *********************************************************** *** Function Description: According to the Chinese characters, the ASCII code, the Chinese character IV_Word: ASCII code 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 autonommitflag 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 varcha2 (120): = 'Oracle Database Development Base Library'; 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; - ******************************* *********************************************************** *********************************************************** **** - Internal function, external unacceptable. --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_ACITION VARCHAR2 (6 0), 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) ; ***************************************************** *********************************************************** ************* - Internal function,
Externally cannot be accessed. --End. - *************************************************** *********************************************************** ******************************** --------------- -------------------------------------------------- ------------------- / * - Dynamic SQL call overload function --on_exitcode: return value, provide to the calling process to determine whether the call is successful, successfully returns 0, Otherwise returning to zero error code - IV_Dynamic_sql: Provides dynamically executed statements, can only make SQL statements, such as [INSERT INTO MyTable Values (...), and cannot be a PL / SQL statement.
* / 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); Endiff; 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 ife_dynamic_sql is no T 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 Ten 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 varcha2 (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) Endness; 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); endiff; return ID_INIT_VAL; End Dynamic_Returning; Function Dyna mic_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 ({I_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 ); Endiff; 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 ('{ibject_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); endiff; 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 ||'. '|| iv_object_name || @|' 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_nam e: '|| 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); endiff; end compile_object; -------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ---------------------------------- / * - Tripate table data function (clear data speed in the table fast)------------------------------------------------ -------------------- --on_Exitcode: The process performs success or not, 0- Success, <0- Failure, if it fails, return Oracle error code - IV_TABLE_NAME: The name of the table that needs to be truncated data --------------------------------------- -------------------------------------------------- --------------- * / 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; endiff; end truncate_table; ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ------------------------- / * - Remove the overload function of the table data function (clear data speed in the table) ----- -------------------------------------------------- --- --on_exitcode: The process performs success or not, 0- success, <0- Failure, if it fails,
Return Oracle error code - IV_OWNER: Schema - IV_TABLE_NAME you need to cut off data: The name of the table that needs to be truncated data --------------------- -------------------------------------------------- --------------------------------- * / 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; ----- -------------------------------------------------- -------------------------------------------------- --- / * - Delete Table Data Functions -------------------------- -------------------------------------------------- - --on_exitcode: The process performs success or not, 0- Success, <0- Failure, if it fails, return Oracle error code - IV_TABLE_NAME: Name of the table that needs to delete data - IV_Del_where: Data Delete Conditions --- -------------------------------------------------- -------------------------------------------------- --- * / 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_ACITION, NULL, SQLCODE, N_LOCATOR, LOG_TYPE_ERROR, V_INFO); endiff; end dele TE_TABLE; ------------------------------------------------ -------------------------------------------------- --------- / * - Remove the overload function of the table data function ----------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ------------ --on_exitcode: The process performs success or not, 0- success, <0- Failure,
Return to Oracle Errors - IV_WV_OWNER: Schema needs to delete the data of the table. --IV_TABLE_NAME: Remove the name of the table in the data - IV_Del_where: Data Delete Condition ---------------- -------------------------------------------------- ---------------------------------------- * / 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' || '.' || 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; en D 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) Endness; end delete_table; function get_pkey_cols (iv_owner varcha2, iv_table_name varchar2) Return varcha2 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: '||}', 1, Info_Length); i F 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; 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 The 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; END IF; V_SQL: = 'SELECT' || BS || V_COL_LIST || BS || 'from' || '.' || 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 || B S || '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_WNER, 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; returnv_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 the 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 '; 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} {{{iNER ||'} {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_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 ROWID IN (SELECT MAX (A.ROWID) from' || IV_WNER || '.' || 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 varcha2 (2); ft_file utl_file.file_type; Partyp Bi NARY_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 ));
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: = = r || = utl_file.fopen (location => v_loglocation, filename => v_logfile, open_mode => 'a', max_linesize => 32767); if logStyle Is Null or logStyle = log_style_form kiln v_logline: = '############################################################################################################################################################################################################################################################################### # - log beg - ############################# v_RT_LF || 'Date:' || to_CHAR (Sysdate, Date_Format) || V_RT_LF || 'User:' || BS || User || V_RT_LF || '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 the -' 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_AC Tion_Name: '|| BS || IV_SUB_ACTION || TB ||' Locator: '|| BS || TO_CHAR (In_Locator) || TB ||' Type: '|| BS || IV_TYPE || TB ||' Sqlcode: '|| BS || || 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); endiff;
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); endiff;
End; procedure set_logflag (b boolean) is beginningflag: = b; end; procedure set_logto (iv_logto varchar2) IS begin logto: = IV_LOGTO;
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 varcha2 (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_poutput)); 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; --dbm S_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 kil ('{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) ; = Substr ('{{{{er er} {iv_domain:' || {iv_name: '|| {}_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); endiff; end_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 life v_info: = SUBSTR ('{parameter found} {iv_dom AIN: '|| 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 (iv_name) and rownum = 1; if v_value is not null dam = f = f _ _n = 'T' TENV_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 the v_info: = SUBSTR ('{no parameter found} {iv_domain:' || {iv_name: '|| {ov_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 ('{{{{:' || {iv_domain || '} {iv_name ||'} {od_value: '|| to_char (od_value,' YYYY-MM-DD HH24: MI: SS ') ||'} ', 1, info_length); if Logflag =
True the log (module_name, current_action, null, sqlcode, n_locator, log_type_warning, v_info); endiff; end_sysparam;
/ ************************************************** ******************** - Take the version information of the current logic program - - ********************************************************** ********************************************************************** / FUNCTION GET_VERSION RETURN VARCHAR2 IS Begin Return Version; End get_version; / ********************************************************** ********************** - Get the current logic program Developer information - **************************************************** ************************************************************************** / FUNCTION GET_AUTHOR RETURN VARCHAR2 IS BEGIN RETURN Author; End get_author; / ************************************************************** ****************** - Detailed description of the current logic program - *********** *********************************************************** ********* / 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 the if utl_file.is_open (ft_file) THL_FILE.FCLOSE (ft_file); endiff;
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: = I_SPLIT; if longth (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 VARC HAR2 (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 info v_item; exit when cr1% notfound; v_retchars: = v_retchars || v_item || v_split; end loop; close cr1; return RTRIM (v_retchars, v_split);
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);
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; utsl_file.fclose (ft_file);
EXCEPTION WHEN OTHERS THEN IF UTL_FILE.IS_Open (ft_file) THL_FILE.FCLOSE (ft_file); endiff;
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; 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 i F; 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);
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) 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) th EN text_tab.extend; for i in 1..n_col_num loop if i <= n_col_num dam = 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 Endness; End loop; Exception When no_data_found the 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 (ip_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_TA B.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);
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 the 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 =' || 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 || '%' || 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_WNER, 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 varcha2 (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 Bas E $ 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' Ten v_wordspell: = LOWER (v_wordspell); Elsif Upper (Substr (IV_CASE, 1)) = 'c' TEN V_WORDSPELL: = INTCAP (v_wordspell); end if; if; 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' Ten 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); endiff;
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 TEN RETURN '[*]'; END; begin EXC_INIT; SYS_INIT; END BASE_FUNC; /
