tYcP4DDU

1--Insert VIEW to table, optional truncate,
2--May not work if VIEW and TABLE are not in same sch
3create or replace PROCEDURE UPDATE_TMP_TABLE
4(viewName IN VARCHAR2, tempTableName IN VARCHAR2, truncateTable IN VARCHAR2)
5AUTHID CURRENT_USER AS --Need this to create table in dynamic SQL
6--Declarations
7v_flag VARCHAR2(1):='N';
8rowCount number:=0;
9ex EXCEPTION;
10BEGIN
11--Check if view exists
12SELECT count(*)
13INTO rowCount
14FROM user_views
15WHERE view_name = viewName;
16IF rowCount = 0 THEN
17    RAISE ex;
18END IF;
19rowCount := 0; --Reset row count
20--Check if entry exists
21SELECT count(*)
22INTO rowCount
23FROM UPDATE_FLAG_DAILY
24WHERE TABLE_NAME = tempTableName;
25IF rowCount > 0 THEN
26    --Set flag to N
27    UPDATE SGVFDB05.UPDATE_FLAG_DAILY
28    SET update_flag='N'
29    WHERE TABLE_NAME = tempTableName;
30ELSE 
31    --Insert new table name entry with flag N
32    INSERT INTO UPDATE_FLAG_DAILY(TABLE_NAME, UPDATE_FLAG)
33    VALUES(tempTableName, 'N');
34END IF;
35rowCount := 0;
36--Check repdb refresh completed
37LOOP
38    SELECT REPLICATION_FLAG INTO v_flag
39    FROM SGVFRT.SGVRT_MST_SUBSIDIARY;
40    EXIT WHEN v_flag='Y';
41    dbms_lock.sleep(180);
42END LOOP;
43--Check if table exists
44SELECT count(*)
45INTO rowCount
46FROM USER_TABLES
47WHERE TABLE_NAME = tempTableName;
48IF rowCount = 0 THEN
49    BEGIN
50    --Create table
51    EXECUTE IMMEDIATE 'CREATE TABLE SGVFDB05.' || tempTableName || ' AS SELECT * FROM SGVFDB05.' || viewName;
52    COMMIT;
53    END;
54ELSE
55    --Truncate data table prior to insert
56    IF truncateTable = 'Y' THEN
57        EXECUTE IMMEDIATE 'TRUNCATE TABLE SGVFDB05.' || tempTableName;
58    END IF;
59    --Insert
60    BEGIN
61    EXECUTE IMMEDIATE 'INSERT INTO SGVFDB05.' || tempTableName || ' SELECT * FROM SGVFDB05.' || viewName;
62    END;
63END IF;
64--Update flag, refresh date to Y
65UPDATE sgvfdb05.update_flag_daily
66SET update_flag='Y', update_date=sysdate
67WHERE TABLE_NAME = tempTableName;
68EXCEPTION
69    WHEN ex THEN
70        RAISE_APPLICATION_ERROR(-20001,'View does not exist.');
71END UPDATE_TMP_TABLE;