Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\reposasst\trans

Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\reposasst\trans_plsql.sql

CREATE OR REPLACE PACKAGE WB AS

FUNCTION LOOKUP_NUM(TABLE_NAME IN VARCHAR2,
                    COLUMN_NAME IN VARCHAR2,
                    KEY_COLUMN_NAME IN VARCHAR2,
                    KEY_VALUE IN VARCHAR2) RETURN NUMBER;

FUNCTION LOOKUP_NUM(TABLE_NAME IN VARCHAR2,
                    COLUMN_NAME IN VARCHAR2,
                    KEY_COLUMN_NAME IN VARCHAR2,
                    KEY_VALUE IN NUMBER) RETURN NUMBER;

FUNCTION LOOKUP_CHAR(TABLE_NAME IN VARCHAR2,
                     COLUMN_NAME IN VARCHAR2,
                     KEY_COLUMN_NAME IN VARCHAR2,
                     KEY_VALUE IN VARCHAR2) RETURN VARCHAR2;

FUNCTION LOOKUP_CHAR(TABLE_NAME IN VARCHAR2,
                     COLUMN_NAME IN VARCHAR2,
                     KEY_COLUMN_NAME IN VARCHAR2,
                     KEY_VALUE IN NUMBER) RETURN VARCHAR2;

PROCEDURE TEST_LOOKUP (   TABLE_NAME          IN VARCHAR2,
                          NUM_COL             IN VARCHAR2,
                          VARCHAR_COL         IN VARCHAR2,
                          NUM_KEY_COL         IN VARCHAR2,
                          NUM_KEY_VAL         IN NUMBER,
                          VARCHAR_KEY_COL     IN VARCHAR2,
                          VARCHAR_KEY_VAL     IN VARCHAR2 );

END WB;

CREATE OR REPLACE PACKAGE BODY WB AS

PROCEDURE TEST_LOOKUP (   TABLE_NAME          IN VARCHAR2,
                            NUM_COL             IN VARCHAR2,
                            VARCHAR_COL         IN VARCHAR2,
                            NUM_KEY_COL         IN VARCHAR2,
                            NUM_KEY_VAL         IN NUMBER,
                            VARCHAR_KEY_COL     IN VARCHAR2,
                            VARCHAR_KEY_VAL     IN VARCHAR2 ) IS
    v_resultNum        NUMBER;
    v_resultVarChar    VARCHAR2(1000);
  BEGIN

--    DBMS_OUTPUT.PUT_LINE(' TEST '              || ' : ' ||
--                         TABLE_NAME            || ' : ' ||
--                         NUM_COL               || ' : ' ||
--                         VARCHAR_COL           || ' : ' ||
--                         NUM_KEY_COL           || ' : ' ||
--                         NUM_KEY_VAL           || ' : ' ||
--                         VARCHAR_KEY_COL       || ' : ' ||
--                         VARCHAR_KEY_VAL);

--    DBMS_OUTPUT.PUT_LINE('----');
    v_resultNum := LOOKUP_NUM(TABLE_NAME, NUM_COL, VARCHAR_KEY_COL, VARCHAR_KEY_VAL);
--    DBMS_OUTPUT.PUT_LINE(' TEST 1 (num, char): '   || v_resultNum);

--    DBMS_OUTPUT.PUT_LINE('----');
    v_resultNum := "LOOKUP_NUM"(TABLE_NAME, NUM_COL, NUM_KEY_COL, NUM_KEY_VAL);
--    DBMS_OUTPUT.PUT_LINE(' TEST 2 (num, num ): '   || v_resultNum );

--    DBMS_OUTPUT.PUT_LINE('----');
    v_resultVarChar := "LOOKUP_CHAR"(TABLE_NAME, VARCHAR_COL, VARCHAR_KEY_COL, VARCHAR_KEY_VAL);
--    DBMS_OUTPUT.PUT_LINE(' TEST 3 (varchar, varchar ): '   || v_resultVarChar );

--    DBMS_OUTPUT.PUT_LINE('----');
    v_resultVarChar := "LOOKUP_CHAR"(TABLE_NAME, VARCHAR_COL, NUM_KEY_COL, NUM_KEY_VAL);
--    DBMS_OUTPUT.PUT_LINE(' TEST 4 (varchar, num ): '   || v_resultVarChar );

END TEST_LOOKUP;

--------------------------------------------------------------------------------
--- LOOKUP_VARCHAR
--- Lookup number using a varchar key.
--------------------------------------------------------------------------------
  FUNCTION LOOKUP_NUM (TABLE_NAME         IN VARCHAR2,
                       COLUMN_NAME        IN VARCHAR2,
                       KEY_COLUMN_NAME    IN VARCHAR2,
                       KEY_VALUE          IN VARCHAR2) RETURN NUMBER IS
    v_CursorID     INTEGER;
    v_SelectStmt   VARCHAR2(1000);
    v_result       NUMBER := null;
    v_Dummy        integer;
  BEGIN
    -- DBMS_OUTPUT.PUT_LINE(' Called lookup return number, key varchar ');
    -- Use dynamic sql to lookup a number from a table.
    -- Any failure in the parse will produce an error (Exception)

-- create the sql statement.
    v_SelectStmt := 'SELECT ' || COLUMN_NAME || ' FROM ' || TABLE_NAME || ' WHERE ' || KEY_COLUMN_NAME || ' = :m1';

-- Setup Dynamic Query
    v_CursorID := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(v_CursorID, v_SelectStmt, DBMS_SQL.V7);
    DBMS_SQL.BIND_VARIABLE(v_CursorId, ':m1', KEY_VALUE);
    DBMS_SQL.DEFINE_COLUMN(v_CursorId, 1, v_result);

-- Execute the statement
    v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);

-- Fetch the result of fhe first row.
    IF DBMS_SQL.FETCH_ROWS(v_CursorID) != 0 THEN
      DBMS_SQL.COLUMN_VALUE(v_CursorID, 1, v_result);
    END IF;

DBMS_SQL.CLOSE_CURSOR(v_CursorID);
    RETURN v_result;

EXCEPTION
    WHEN OTHERS THEN
      -- Close the cursor, raise the error again
      DBMS_SQL.CLOSE_CURSOR(v_CursorID);
      RAISE;
  END LOOKUP_NUM;

--------------------------------------------------------------------------------
--- LOOKUP_VARCHAR
--- Lookup number using a number key.
--------------------------------------------------------------------------------
  -- Lookup number using a number key.
  FUNCTION LOOKUP_NUM(TABLE_NAME           IN VARCHAR2,
                      COLUMN_NAME          IN VARCHAR2,
                      KEY_COLUMN_NAME      IN VARCHAR2,
                      KEY_VALUE            IN NUMBER)
  RETURN NUMBER IS
    v_CursorID     INTEGER;
    v_SelectStmt   VARCHAR2(1000);
    v_result       NUMBER := null;
    v_Dummy        integer;
  BEGIN
    -- DBMS_OUTPUT.PUT_LINE(' Called lookup return number, key number ');
    -- Use dynamic sql to lookup a number from a table.
    -- Any failure in the parse will produce an error (Exception)

-- create the sql statement.
    v_SelectStmt := 'SELECT ' || COLUMN_NAME || ' FROM ' || TABLE_NAME || ' WHERE ' || KEY_COLUMN_NAME || ' = :m1';

-- Execute the statement
    v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);

-- Fetch the result of fhe first row.
    IF DBMS_SQL.FETCH_ROWS(v_CursorID) != 0 THEN
      DBMS_SQL.COLUMN_VALUE(v_CursorID, 1, v_result);
    END IF;

DBMS_SQL.CLOSE_CURSOR(v_CursorID);
    RETURN v_result;

EXCEPTION
    WHEN OTHERS THEN
      -- Close the cursor, raise the error again
      DBMS_SQL.CLOSE_CURSOR(v_CursorID);
      RAISE;
  END LOOKUP_NUM;

--------------------------------------------------------------------------------
  --- LOOKUP_VARCHAR
  --- Lookup varchar using a varchar key.
  --------------------------------------------------------------------------------
  FUNCTION LOOKUP_CHAR(TABLE_NAME IN VARCHAR2,
                       COLUMN_NAME IN VARCHAR2,
                       KEY_COLUMN_NAME IN VARCHAR2,
                       KEY_VALUE IN VARCHAR2) RETURN VARCHAR2 IS
    v_CursorID     INTEGER;
    v_SelectStmt   VARCHAR2(1000);
    v_result       VARCHAR2(2000);
    v_Dummy        integer;
  BEGIN

--DBMS_OUTPUT.PUT_LINE(' Called lookup return varchar2, key varchar2 ');

-- Use dynamic sql to lookup a number from a table.
    -- Any failure in the parse will produce an error (Exception)

-- create the sql statement.
    v_SelectStmt := 'SELECT ' || COLUMN_NAME || ' FROM ' || TABLE_NAME || ' WHERE ' || KEY_COLUMN_NAME || ' = :m1';

-- Execute the statement
    v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);

-- Fetch the result of fhe first row.
    IF DBMS_SQL.FETCH_ROWS(v_CursorID) != 0 THEN
      DBMS_SQL.COLUMN_VALUE(v_CursorID, 1, v_result);
    END IF;

DBMS_SQL.CLOSE_CURSOR(v_CursorID);
    RETURN v_result;

EXCEPTION
    WHEN OTHERS THEN
      -- Close the cursor, raise the error again
      DBMS_SQL.CLOSE_CURSOR(v_CursorID);
      RAISE;
  END LOOKUP_CHAR;

--------------------------------------------------------------------------------
--- LOOKUP_VARCHAR
--- Lookup varchar using a number key.
--------------------------------------------------------------------------------
  FUNCTION LOOKUP_CHAR(TABLE_NAME IN VARCHAR2,
                       COLUMN_NAME IN VARCHAR2,
                       KEY_COLUMN_NAME IN VARCHAR2,
                       KEY_VALUE IN NUMBER) RETURN VARCHAR2 IS
    v_CursorID     INTEGER;
    v_SelectStmt   VARCHAR2(1000);
    v_result       VARCHAR2(2000);
    v_Dummy        integer;
  BEGIN

-- DBMS_OUTPUT.PUT_LINE(' Called lookup return varchar2, key number ');
    -- Use dynamic sql to lookup a number from a table.
    -- Any failure in the parse will produce an error (Exception)

-- create the sql statement.
    v_SelectStmt := 'SELECT ' || COLUMN_NAME || ' FROM ' || TABLE_NAME || ' WHERE ' || KEY_COLUMN_NAME || ' = :m1';

-- Execute the statement
    v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);

-- Fetch the result of fhe first row.
    IF DBMS_SQL.FETCH_ROWS(v_CursorID) != 0 THEN
      DBMS_SQL.COLUMN_VALUE(v_CursorID, 1, v_result);
    END IF;

DBMS_SQL.CLOSE_CURSOR(v_CursorID);
    RETURN v_result;

EXCEPTION
    WHEN OTHERS THEN
      -- Close the cursor, raise the error again
      DBMS_SQL.CLOSE_CURSOR(v_CursorID);
      RAISE;
  END LOOKUP_CHAR;

END WB;

/
CREATE OR REPLACE PROCEDURE WB_Abort( 
                                  p_code              NUMBER  
                                , p_message           VARCHAR2 )
AUTHID CURRENT_USER
AS
BEGIN
  IF p_code NOT BETWEEN -20999 AND -20000 THEN
      RAISE_APPLICATION_ERROR( -20000, ' Invaild parameter for procedure Abort');
  ELSE
      RAISE_APPLICATION_ERROR( p_code, p_message );
  END IF;
END WB_Abort;

CREATE OR REPLACE PROCEDURE WB_Analyze_Schema         ( 
                                   p_method           VARCHAR2 := 'ESTIMATE'
                                 , p_estimate_rows    NUMBER   := null
                                 , p_estimate_percent NUMBER   :=  50
                                 , p_method_opt       VARCHAR2 := 'FOR TABLE' )
AUTHID CURRENT_USER
AS
    p_schema VARCHAR2(2000) := USER;
BEGIN   /* p_method               ESTIMATE, COMPUTE or DELETE 
           p_method_opt         [ FOR TABLE, 
                                [ FOR ALL [INDEXED] COLUMNS] [SIZE n]
                                [ FOR ALL INDEXES ]
        */

DBMS_UTILITY.analyze_schema( p_schema
                           , p_method
                           , p_estimate_rows
                           , p_estimate_percent
                           , p_method_opt );
END WB_Analyze_Schema;
/

CREATE OR REPLACE PROCEDURE WB_Analyze_Table          ( 
                                   p_name             VARCHAR2
                                 , p_method           VARCHAR2 := 'ESTIMATE'
                                 , p_estimate_rows    NUMBER   :=  null
                                 , p_estimate_percent NUMBER   :=  50
                                 , p_method_opt       VARCHAR2 := 'FOR TABLE'
                                 , p_type             VARCHAR2 := 'TABLE'
                                 , p_partname         VARCHAR2 :=  null )
AUTHID CURRENT_USER
AS
   p_schema VARCHAR2(2000) := USER;

BEGIN    /* p_type TABLE, CLUSTER or INDEX */ 
 
   DBMS_DDL.ANALYZE_OBJECT ( p_type
                         , p_schema
                         , p_name
                         , p_method
                         , p_estimate_rows
                         , p_estimate_percent
                         , p_method_opt
                         , p_partname );
END WB_Analyze_Table;
/

CREATE OR REPLACE FUNCTION WB_Cal_Month_Name       ( v_date IN DATE   ) RETURN VARCHAR2
  IS
    BEGIN
      RETURN ( TO_CHAR( v_date, 'fmMonth' ) );
    END WB_Cal_Month_Name;
/

CREATE OR REPLACE FUNCTION WB_Cal_Month_of_Year    ( v_date IN DATE   ) RETURN NUMBER
  IS
    BEGIN
      RETURN ( TO_NUMBER( TO_CHAR( v_date, 'MM' ) ) );
    END WB_Cal_Month_of_Year;
/

CREATE OR REPLACE FUNCTION WB_Cal_Month_Short_Name ( v_date IN DATE   ) RETURN VARCHAR2
  IS
    BEGIN
      RETURN ( TO_CHAR( v_date, 'Mon' ) );
    END WB_Cal_Month_Short_Name;
/

CREATE OR REPLACE FUNCTION WB_Cal_Qtr            ( v_date IN DATE   ) RETURN NUMBER
  IS
    BEGIN
      RETURN ( TO_NUMBER( TO_CHAR( v_date, 'Q' ) ) );
    END WB_Cal_Qtr;
/

CREATE OR REPLACE FUNCTION WB_CAL_Week_of_Year   ( v_date IN DATE   ) RETURN NUMBER
  IS
    BEGIN
      RETURN ( TO_NUMBER( TO_CHAR( v_date, 'WW' ) ) );
    END WB_Cal_Week_of_Year;
/

CREATE OR REPLACE FUNCTION WB_Cal_Year           ( v_date IN DATE   ) RETURN NUMBER
  IS
    BEGIN
      RETURN ( TO_NUMBER( TO_CHAR( v_date, 'YYYY' ) ) );
    END WB_Cal_Year;
/

CREATE OR REPLACE FUNCTION WB_Cal_Year_Name      ( v_date IN DATE   ) RETURN VARCHAR2
  IS
    BEGIN
      RETURN ( TO_CHAR( v_date, 'fmYear' ) );
    END WB_Cal_Year_Name;
/

CREATE OR REPLACE PROCEDURE WB_Compile_PLSQL         ( 
                                  p_name              VARCHAR2
                                , p_type              VARCHAR2 := 'PACKAGE' )
AUTHID CURRENT_USER
AS
   p_schema VARCHAR2(2000) := USER;
   sql_stmt VARCHAR2(2000) := NULL;

BEGIN
   IF UPPER( p_type ) NOT IN( 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TRIGGER' ) THEN
      RAISE_APPLICATION_ERROR( -20010,
                              ' Invalid parameter ' || p_type || ' for procedure WB_Compile_PLSQL' ||
                              ' Valid parameters are: PACKAGE, ' ||
                                                     'PACKAGE BODY, ' ||
                                                     'PROCEDURE, ' ||
                                                     'FUNCTION, ' ||
                                                     'TRIGGER' );
   END IF;

IF UPPER( p_type ) = 'PACKAGE' THEN
      sql_stmt := 'alter package ' || p_schema || '.' || p_name || ' compile';

ELSIF UPPER( p_type ) = 'PACKAGE BODY' THEN
      sql_stmt := 'alter package ' || p_schema || '.' || p_name || ' compile BODY';

ELSIF UPPER( p_type ) = 'PROCEDURE' THEN
      sql_stmt := 'alter procedure ' || p_schema || '.' || p_name || ' compile';

ELSIF UPPER( p_type ) = 'FUNCTION' THEN
      sql_stmt := 'alter function ' || p_schema || '.' || p_name || ' compile';

ELSIF UPPER( p_type ) = 'TRIGGER' THEN
      sql_stmt := 'alter trigger ' || p_schema || '.' || p_name || ' compile';
   ELSE
     RAISE_APPLICATION_ERROR( -20010, ' Invalid parameter for procedure Compile_Mapping' );
   END IF;

EXECUTE IMMEDIATE sql_stmt;
 
END WB_Compile_PLSQL;
/

CREATE OR REPLACE FUNCTION WB_Date_From_Julian   ( v_num  IN NUMBER ) RETURN DATE
  IS    
    BEGIN
      IF  v_num BETWEEN 1 and 5373484 THEN
         RETURN ( TO_DATE ( TRUNC( v_num ) , 'J' ) );
      ELSE 
         RAISE_APPLICATION_ERROR (-01854, 'Julian date must be between 1 and 5373484' );
      END IF; 
    END WB_Date_From_Julian;
/

CREATE OR REPLACE FUNCTION WB_Day_of_Month       ( v_date IN DATE   ) RETURN NUMBER
  IS
    BEGIN
      RETURN ( TO_NUMBER( TO_CHAR( v_date, 'DD' ) ) );
    END WB_Day_of_Month;
/

CREATE OR REPLACE FUNCTION WB_Day_of_Week        ( v_date IN DATE   ) RETURN NUMBER
  IS
    BEGIN
      RETURN ( TO_NUMBER( TO_CHAR( v_date, 'D' ) ) );
    END WB_Day_of_Week;
/
CREATE OR REPLACE FUNCTION WB_Day_of_Year        ( v_date IN DATE   ) RETURN NUMBER
  IS
    BEGIN
      RETURN ( TO_NUMBER( TO_CHAR( v_date, 'DDD' ) ) );
    END WB_Day_of_Year;
/

CREATE OR REPLACE FUNCTION WB_Day_Name           ( v_date IN DATE   ) RETURN VARCHAR2
  IS
    BEGIN
      RETURN ( TO_CHAR( v_date, 'fmDay' ) );
    END WB_Day_Name;

CREATE OR REPLACE FUNCTION WB_Day_Short_Name     ( v_date IN DATE   ) RETURN VARCHAR2
  IS
    BEGIN
      RETURN ( TO_CHAR( v_date, 'Dy' ) );
    END WB_Day_Short_Name;
/

CREATE OR REPLACE FUNCTION WB_Decade            ( v_date IN DATE   ) RETURN NUMBER
  IS
    BEGIN
      RETURN ( TO_NUMBER( TO_CHAR( v_date, 'YY' ) ) );
    END WB_Decade;

CREATE OR REPLACE PROCEDURE WB_Disable_All_Constraints  ( 
                                   p_table            VARCHAR2 )
AUTHID CURRENT_USER
AS
     p_schema VARCHAR2(2000) := USER;
     sql_stmt VARCHAR2(2000);
     v_constraintName SYS.USER_CONSTRAINTS.CONSTRAINT_NAME%TYPE;
     v_tableName SYS.USER_CONSTRAINTS.TABLE_NAME%TYPE;
     
     CURSOR c_Constraints IS
         SELECT TABLE_NAME, CONSTRAINT_NAME from SYS.USER_CONSTRAINTS
         WHERE OWNER = p_schema AND TABLE_NAME = p_table;
BEGIN
   OPEN c_Constraints;
   LOOP
       FETCH c_Constraints INTO v_tableName, v_ConstraintName;
       
       EXIT WHEN c_Constraints%NOTFOUND;
       sql_stmt := 'ALTER TABLE '
                                || p_schema
                                || '.'
                                || v_tableName
                                || ' disable constraint '
                                || v_constraintName
                                || ' cascade ';
        
       EXECUTE IMMEDIATE sql_stmt;
   END LOOP;
   CLOSE c_Constraints;
END;
/

CREATE OR REPLACE PROCEDURE WB_Disable_All_Triggers    ( 
                                   p_table            VARCHAR2)
AUTHID CURRENT_USER
AS
      p_schema VARCHAR2(2000) := USER;
      sql_stmt VARCHAR2(2000) := 'ALTER TABLE ' 
                                 || p_schema
                                 || '.'
                                 || p_table
                                 || ' DISABLE ALL TRIGGERS';
BEGIN
   EXECUTE IMMEDIATE sql_stmt;
END;
/

CREATE OR REPLACE PROCEDURE WB_Disable_Constraint  ( p_constraint_name  VARCHAR2
                                 , p_table            VARCHAR2 )
AUTHID CURRENT_USER
AS
         p_schema VARCHAR2(2000) := USER;
         sql_stmt VARCHAR2(2000) := 'ALTER TABLE ' 
                                 || p_schema
                                 || '.'
                                 || p_table
                                 || ' DISABLE CONSTRAINT '
                                 || p_constraint_name ;
BEGIN
   EXECUTE IMMEDIATE sql_stmt;
END;

CREATE OR REPLACE PROCEDURE WB_Disable_Trigger     ( 
                                p_trigger_name     VARCHAR2 )
AUTHID CURRENT_USER
AS
      p_schema VARCHAR2(2000) := USER;
      sql_stmt VARCHAR2(2000) := 'ALTER TRIGGER ' 
                                 || p_schema
                                 || '.'
                                 || p_trigger_name
                                 || ' DISABLE';

BEGIN
   EXECUTE IMMEDIATE sql_stmt;
END;

CREATE OR REPLACE PROCEDURE WB_Enable_All_Constraints  ( 
                                   p_table            VARCHAR2 )
AUTHID CURRENT_USER
AS
     p_schema VARCHAR2(2000) := USER;
     sql_stmt VARCHAR2(2000);
     v_constraintName SYS.USER_CONSTRAINTS.CONSTRAINT_NAME%TYPE;
     
     CURSOR c_Constraints IS
         SELECT CONSTRAINT_NAME from SYS.USER_CONSTRAINTS
         WHERE OWNER = p_schema AND TABLE_NAME = p_table;
BEGIN
   OPEN c_Constraints;
   LOOP
       FETCH c_Constraints INTO v_ConstraintName;
       
       EXIT WHEN c_Constraints%NOTFOUND;
       sql_stmt := 'ALTER TABLE '
                                || p_schema
                                || '.'
                                || p_table
                                || ' enable constraint '
                                || v_constraintName;

EXECUTE IMMEDIATE sql_stmt;
   END LOOP;
   CLOSE c_Constraints;
END;

CREATE OR REPLACE PROCEDURE WB_Enable_All_Triggers    ( 
                                   p_table            VARCHAR2 )
AUTHID CURRENT_USER
AS
      p_schema VARCHAR2(2000) := USER;
      sql_stmt VARCHAR2(2000) := 'ALTER TABLE ' 
                                 || p_schema
                                 || '.'
                                 || p_table
                                 || ' ENABLE ALL TRIGGERS';
BEGIN
   EXECUTE IMMEDIATE sql_stmt;
END;

CREATE OR REPLACE PROCEDURE WB_Enable_Constraint  ( 
                                   p_constraint_name  VARCHAR2
                                 , p_table            VARCHAR2 )
AUTHID CURRENT_USER
AS
         p_schema VARCHAR2(2000) := USER;
         sql_stmt VARCHAR2(2000) := 'ALTER TABLE ' 
                                 || p_schema
                                 || '.'
                                 || p_table
                                 || ' ENABLE CONSTRAINT '
                                 || p_constraint_name ;
BEGIN
   EXECUTE IMMEDIATE sql_stmt;
END;

CREATE OR REPLACE PROCEDURE WB_Enable_Trigger     ( 
                               p_trigger_name     VARCHAR2 )
AUTHID CURRENT_USER
AS
      p_schema VARCHAR2(2000) := USER;
      sql_stmt VARCHAR2(2000) := 'ALTER TRIGGER ' 
                                 || p_schema
                                 || '.'
                                 || p_trigger_name
                                 || ' ENABLE';

BEGIN
   EXECUTE IMMEDIATE sql_stmt;
END;

CREATE OR REPLACE FUNCTION WB_Hour12             ( v_date IN DATE   ) RETURN NUMBER
  IS
    BEGIN
      RETURN ( TO_NUMBER( TO_CHAR( v_date, 'HH12' ) ) );
    END WB_Hour12;
/

CREATE OR REPLACE FUNCTION  WB_Hour12MI_SS        ( v_date IN DATE   ) RETURN VARCHAR2
  IS 
    BEGIN
      RETURN ( TO_CHAR( v_date, 'HH12:MI:SS' ) );
    END WB_Hour12MI_SS;
/

CREATE OR REPLACE FUNCTION WB_Hour24             ( v_date IN DATE   ) RETURN NUMBER
  IS
    BEGIN
      RETURN ( TO_NUMBER( TO_CHAR( v_date, 'HH24' ) ) );
    END WB_Hour24;
/

CREATE OR REPLACE FUNCTION WB_Hour24MI_SS        ( v_date IN DATE   ) RETURN VARCHAR2
  IS 
    BEGIN
      RETURN ( TO_CHAR( v_date, 'HH24:MI:SS' ) );
    END WB_Hour24MI_SS;

CREATE OR REPLACE FUNCTION WB_Is_DATE ( p_var VARCHAR2, p_format VARCHAR2 := null ) 
RETURN BOOLEAN
IS
   p_date DATE;
BEGIN
   if (p_format is null) then  p_date := TO_DATE(p_var);
   else 
      p_date := TO_DATE(p_var, p_format);
   end if;

IF (p_date is null) then return false;
   else return true;
   end if;

EXCEPTION
    WHEN OTHERS THEN
          RETURN FALSE;
END;
/

CREATE OR REPLACE FUNCTION WB_Is_Number ( p_var VARCHAR2, p_format VARCHAR2 := null ) 
RETURN BOOLEAN			
IS
   p_number NUMBER;
BEGIN
   if (p_format is null) then p_number := TO_NUMBER(p_var);
   else p_number := TO_NUMBER(p_var, p_format);
   end if;

IF (p_number is null) then return false;
   else return true;
   end if;

RETURN ( p_number != NULL );
EXCEPTION
    WHEN OTHERS THEN RETURN FALSE;
END WB_Is_Number;
/

CREATE OR REPLACE FUNCTION WB_Is_Space  ( p_var VARCHAR2 ) 
RETURN BOOLEAN
IS
   p_temp NUMBER  := LENGTH( p_var );

BEGIN
   IF INSTR( p_var, ' ' )  > 0 THEN

FOR x IN 1..p_temp LOOP
         IF SUBSTR(p_var, x, 1 )<> ' ' THEN 
            RETURN ( FALSE );
         END IF;   
      END LOOP;

RETURN ( TRUE );
   ELSE
      RETURN ( FALSE );
   END IF;
EXCEPTION
   WHEN OTHERS THEN RETURN FALSE;
END WB_Is_Space;

CREATE OR REPLACE FUNCTION WB_Julian_From_Date   ( v_date IN DATE   ) RETURN NUMBER
  IS
    BEGIN
      RETURN ( TO_NUMBER( TO_CHAR( v_date, 'J' ) ) );
    END WB_Julian_From_Date;
/

CREATE OR REPLACE FUNCTION WB_MI_SS              ( v_date IN DATE   ) RETURN VARCHAR2
  IS
    BEGIN
      RETURN ( TO_CHAR( v_date, 'MI:SS' ) );
    END WB_MI_SS;
/

CREATE OR REPLACE PROCEDURE WB_Truncate_Table( p_table  VARCHAR2 )
AUTHID CURRENT_USER
AS
    p_schema VARCHAR2(2000) := USER;  -- User schema. Move this to parameter to open to other schemas.
    v_tableName VARCHAR2(2000);       -- Table to truncate. May be caps version of p_table
    v_validate VARCHAR2(20);          -- Placeholder for NOVALIDATE keyword

-- This procedurre will disable any constraints refering to this table,
-- truncate this table,
-- and enable any constraints that it disabled.

-- declare a table to hold a record of constraint info
    TYPE t_constraintDef IS RECORD (
          owner SYS.USER_CONSTRAINTS.OWNER%TYPE,
          name SYS.USER_CONSTRAINTS.CONSTRAINT_NAME%TYPE,
          tableName SYS.USER_CONSTRAINTS.TABLE_NAME%TYPE,
          validated SYS.USER_CONSTRAINTS.VALIDATED%TYPE );

TYPE t_constraints IS TABLE OF t_constraintDef
          INDEX BY BINARY_INTEGER;

v_referenceConstraints t_constraints;   -- table of enabled constraints dependent on this table.

-- cursor for all dependent constraints.
    -- bind variables.
    v_owner SYS.USER_CONSTRAINTS.OWNER%TYPE;
    v_constraintName SYS.USER_CONSTRAINTS.CONSTRAINT_NAME%TYPE;

-- cursor for all dependent constraints.
    CURSOR c_refConstraints IS
         SELECT r.OWNER, r.TABLE_NAME, r.CONSTRAINT_NAME, r.VALIDATED
         FROM SYS.ALL_CONSTRAINTS c, SYS.ALL_CONSTRAINTS r
         WHERE c.OWNER = p_schema
               AND c.TABLE_NAME = v_tableName
               AND c.CONSTRAINT_TYPE in ('P', 'U')
               AND c.CONSTRAINT_NAME = r.R_CONSTRAINT_NAME
               AND c.OWNER = r.OWNER
               AND r.CONSTRAINT_TYPE = 'R'
               AND r.STATUS = 'ENABLED';

v_j NUMBER;               -- index for enabled referant constraints.
     v_stmt VARCHAR2(2000);    -- place holder for dynamic statements

BEGIN

-- convert p_table to v_tableName. If p_table begins with ", do nothing. Else force upper.
    BEGIN
        IF (p_table = null) THEN return;
        ELSIF (SUBSTR(p_table,1,1) = '"')
           THEN v_tableName := p_table;
        ELSE v_tableName := UPPER(p_table);
        END IF;
--        DBMS_OUTPUT.PUT_LINE('Table name is ' || v_tableName);
    END;

-- loop up each of these constraints to collect referant constraints.
    BEGIN
        OPEN c_refConstraints;
        v_j := 0;
        LOOP
            FETCH c_refConstraints INTO v_referenceConstraints(v_j).owner,
                                        v_referenceConstraints(v_j).tableName,
                                        v_referenceConstraints(v_j).name,
                                        v_referenceConstraints(v_j).validated;

EXIT WHEN c_refConstraints%NOTFOUND;

--            DBMS_OUTPUT.PUT_LINE('Found constraint ' || v_referenceConstraints(v_j).name);
            v_j := v_j + 1;
        END LOOP;
        close c_refConstraints;
    END;

-- disable any dependant constraints.
    BEGIN
        FOR v_Counter in 0..v_j-1 LOOP
              v_stmt := 'ALTER TABLE '
                                   || v_referenceConstraints(v_Counter).owner
                                   || '.'
                                   || v_referenceConstraints(v_Counter).tableName
                                   || ' disable constraint '
                                   || v_referenceConstraints(v_Counter).name;
--              DBMS_OUTPUT.PUT_LINE(v_stmt);
              EXECUTE IMMEDIATE v_stmt;
        END LOOP;
    END;

-- truncate this table
    BEGIN
        v_stmt := 'TRUNCATE TABLE '
                                   || p_schema
                                   || '.'
                                   || v_tableName
                                   || ' DROP STORAGE' ;
--        DBMS_OUTPUT.PUT_LINE(v_stmt);
        EXECUTE IMMEDIATE v_stmt;
    END;

-- enable any dependant constraints.
    BEGIN
        FOR v_Counter in 0..v_j-1 LOOP
            IF (v_referenceConstraints(v_Counter).validated = 'VALIDATED')
            THEN v_validate := '';
            ELSE v_validate := 'NOVALIDATE';
            END IF;
            v_constraintName := v_referenceConstraints(v_Counter).name;

v_stmt := 'ALTER TABLE '
                                   || v_referenceConstraints(v_Counter).owner
                                   || '.'
                                   || v_referenceConstraints(v_Counter).tableName
                                   || ' enable '
                                   || v_validate
                                   || ' constraint '
                                   || v_constraintName;

--            DBMS_OUTPUT.PUT_LINE(v_stmt);
            EXECUTE IMMEDIATE v_stmt;
        END LOOP;
        EXCEPTION
          WHEN OTHERS THEN
              DBMS_OUTPUT.PUT_LINE('Warning: WB_TRUNCATE_TABLE failed to enable constraint ' || v_constraintName || ' after truncatating table ' || v_tableName);
              -- I think this is the best we can do for warning short of tapping into the runtime system.
    END;

END WB_Truncate_Table;
/
CREATE OR REPLACE FUNCTION WB_Week_of_Month      ( v_date IN DATE   ) RETURN NUMBER
  IS
    BEGIN
      RETURN ( TO_NUMBER( TO_CHAR( v_date, 'W' ) ) );
    END WB_Week_of_Month;

CREATE OR REPLACE FUNCTION WB_LOOKUP_NUM  (TABLE_NAME IN VARCHAR2,
                    COLUMN_NAME IN VARCHAR2,
                    KEY_COLUMN_NAME IN VARCHAR2,
                    KEY_VALUE IN VARCHAR2) RETURN NUMBER
  IS
    BEGIN
      RETURN WB.LOOKUP_NUM (TABLE_NAME ,
                    COLUMN_NAME ,
                    KEY_COLUMN_NAME ,
                    KEY_VALUE) ;
    END WB_LOOKUP_NUM;

CREATE OR REPLACE FUNCTION WB_LOOKUP_CHAR(TABLE_NAME IN VARCHAR2,
                     COLUMN_NAME IN VARCHAR2,
                     KEY_COLUMN_NAME IN VARCHAR2,
                     KEY_VALUE IN VARCHAR2) RETURN VARCHAR2
  IS
    BEGIN
      RETURN WB.LOOKUP_CHAR (TABLE_NAME ,
                    COLUMN_NAME ,
                    KEY_COLUMN_NAME ,
                    KEY_VALUE) ;
    END WB_LOOKUP_CHAR;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de