Help for a first time package creator in PL/SQL

This is going to be a difficult question to get answered which is why for 3 days that I have worked on this package (my first package ever) I have been hesitant to ask.

Below is the layout for the spec and body of my package. Before you look at that here is what I am trying to accomplish. I AM CLOSE TO FINISHING so there is no need to fear that this question is not worth your time.

You may see a few of my personal notes to self in the code as well.

My code is incomplete and currently isn't compiling but before it ceased to compile I can tell you it did not work either. The DROP and CREATE procedures work. NO NEED TO TOUCH THOSE. My main issues are the LOG_PROC, my EXCEPTIONS, my ARCHIVE_ALL_TABLES... as far as I know

Here is what I am trying to do:

Create a package that could be used to ‘archive’ the newly created tables into archive tables in the format “TEST_TABLE_A_13AUG2012”. This package will use a view I created called VW_TEST_TABLES which has this data:

TEST_TABLE_A
TEST_TABLE_B
TEST_TABLE_C
TEST_TABLE_D

Select allOpen in new window

.

This package will need to drop all previously archived tables before it creates new ones. As such, my package will need to have both DROP_ARCHIVE_TABLES and CREATE_ARCHIVE_TABLES procedures within it. In addition to the DROP and CREATE procedures, my package has a main procedure, called ARCHIVE_ALL_TABLES. This is the procedure that would need to be called (for instance by the scheduler) and do the actual archiving. I need to incorporate proper exception handling in these procedures. (e.g. don’t care if the table does not exist when I go to drop it).

Finally, in order to properly track each archival run, I want to build a logging mechanism. To accomplish this, I built a table in my schema called TEST_PACKAGE_LOG_TBL. This table should has the following columns: ARCHIVE_DATE (DATE), TABLE_NAME (VARCHAR2(30)), STATUS_CODE(VARCHAR2(1)), COMMENTS (VARCHAR2(4000)). For each table I archive, I want to log the date, the table name, either ‘S’ for success or ‘E’ for error and, if I encounter an error in the drop or creation of the table, what the SQLERRM was should be displayed.

Finally, my ARCHIVE_ALL_TABLES procedure should check this log table when it is finishing in order to determine if any tables were not archived properly. I created a function ERRORS_FOUND (return boolean) that accepts one IN parameter (today’s date) and checks the log table for errors. If this function returns true, my ARCHIVE_ALL_TABLES procedure should account for this and ‘notify an administrator’ (For now I am leaving this untouched but eventually it will simply account for this with a comment stating that I would notify an admin and place NULL; in the if then end block.)

To summarize, my package structure must contain (at minimum) the following procedures:

      ARCHIVE_ALL_TABLES
      DROP_ARCHIVE_TABLE
      CREATE_ARCHIVE_TABLE
      ERRORS_FOUND (function)

--package specification
CREATE OR REPLACE PACKAGE PKG_TEST_TABLES IS

              -- Author  : 
              -- Created : 8/14/2012 8:40:18 AM
              -- Purpose : For storing procedures to drop, create, and archive new tables

              /* Package specification*/
              PROCEDURE ARCHIVE_ALL_TABLES;
              PROCEDURE DROP_ARCHIVE_TABLES; --2nd
              PROCEDURE CREATE_ARCHIVE_TABLES; --1st and call both from archive tables first assuming it works
              PROCEDURE LOG_PROC
              (
                            P_PROCESS_START_TIMESTAMP TIMESTAMP
                           ,P_ARCHIVE_DATE DATE
                           ,P_TABLE_NAME VARCHAR2
                           ,P_STATUS_CODE VARCHAR2
                           ,P_COMMENTS VARCHAR2
              );
              PROCEDURE W(STR VARCHAR2);

              FUNCTION ERRORS_FOUND(P_JOB_RUN_TIMESTAMP TIMESTAMP) RETURN BOOLEAN;
              
END PKG_TEST_TABLES;

Select allOpen in new window

--package body
CREATE OR REPLACE PACKAGE BODY PKG_TEST_TABLES IS
              /* Package body*/

              -------------------------------------------------------------------------------------------------------------------------------------------------------------------
              -------------------------------------------------------------------------------------------------------------------------------------------------------------------

              /* Procedure 'W' is a wrapper for DBMS output. Placed at top of package to make globally available*/
              PROCEDURE W(STR VARCHAR2) IS
                            L_STRING VARCHAR2(4000);
              BEGIN
              
                            L_STRING := STR;
                            DBMS_OUTPUT.PUT_LINE(STR);
              END;

              -------------------------------------------------------------------------------------------------------------------------------------------------------------------
              -------------------------------------------------------------------------------------------------------------------------------------------------------------------

              PROCEDURE DROP_ARCHIVE_TABLES AS
              
                            /* Purpose: For dropping previously archived tables so that new ones can be created */
              
                            L_NO_TABLES_TO_DROP EXCEPTION;
              BEGIN
                            /* Will drop previously archived tables not current ones*/
                            FOR STMT IN (SELECT 'DROP TABLE mySchema.' || TABLE_NAME AS STR
                                         FROM VW_TEST_TABLES
                                         WHERE REGEXP_LIKE(TABLE_NAME, '.+[0...9]'))
                            LOOP
                                          EXECUTE IMMEDIATE STMT.STR; --so that I don't need ';' at the end of each dynamically created SQL
                            
                            END LOOP;
              
                            W('Done'); --put the W back in here when in package scope
              
              EXCEPTION
                            WHEN L_NO_TABLES_TO_DROP THEN
                                          NULL;
                            
              END;

              -------------------------------------------------------------------------------------------------------------------------------------------------------------------
              -------------------------------------------------------------------------------------------------------------------------------------------------------------------

              PROCEDURE CREATE_ARCHIVE_TABLES AS
              /* purpose: setting variable to equal the creation of my 4 tables. Recreating the archive tables */
              
              L_NO_TABLES_TO_CREATE EXCEPTION;
              L_TABLES_NOT_SUCCESSFULLY_CREATED EXCEPTION;

BEGIN

              FOR STMT IN (SELECT 'CREATE TABLE ' || TABLE_NAME || '_' || TO_CHAR(SYSDATE, 'ddMONyyyy') || ' AS SELECT * FROM ' || TABLE_NAME AS STR
                           FROM VW_TEST_TABLES)
              --LOG_PROC( ,TO_CHAR(SYSDATE, 'ddMONyyyy')  , TABLE_NAME  ,'E' ,'TABLE ARCHIVED SUCCESSFULLY')
              
              LOOP
                            --DBMS_OUTPUT.PUT_LINE(STMT.STR); --want to do a dbms output first before using 'execute immediate'. Hit test, and run it
                            EXECUTE IMMEDIATE STMT.STR; --so that I don't need ';' at the end of each dynamically created SQL
              
              END LOOP;

--  DBMS_OUTPUT.PUT_LINE('Done'); --put the W back in here when in package scope

EXCEPTION
              WHEN L_NO_TABLES_TO_CREATE THEN
                            NULL; --logging can go here
              --can call logging procedure here for dml don't need execute immediate, just use insert into
              WHEN L_TABLES_NOT_SUCCESSFULLY_CREATED THEN
                            NULL; --W('ERROR: ' || SQLERRM);
END;

--PROCEDURE IS NOT CREATING TABLES YET

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE LOG_PROC(P_PROCESS_START_TIMESTAMP TIMESTAMP, P_ARCHIVE_DATE DATE, P_TABLE_NAME VARCHAR2, P_STATUS_CODE VARCHAR2, P_COMMENTS VARCHAR2) AS

PRAGMA AUTONOMOUS_TRANSACTION;

/* variables */

L_PROCESS_START_TIMESTAMP TIMESTAMP; L_ARCHIVE_DATE DATE; L_TABLE_NAME VARCHAR2(4000); L_STATUS_CODE VARCHAR2(1); L_COMMENTS VARCHAR2(4000);

BEGIN

L_PROCESS_START_TIMESTAMP := P_PROCESS_START_TIMESTAMP; L_ARCHIVE_DATE := P_ARCHIVE_DATE; L_TABLE_NAME := P_TABLE_NAME; L_STATUS_CODE := P_STATUS_CODE; L_COMMENTS := P_COMMENTS;

INSERT INTO TEST_PACKAGE_LOG_TBL(PROCESS_START_TIMESTAMP, ARCHIVE_DATE, TABLE_NAME, STATUS_CODE, COMMENTS) VALUES(L_PROCESS_START_TIMESTAMP, L_ARCHIVE_DATE, L_TABLE_NAME, L_STATUS_CODE, L_COMMENTS);

RETURN;
END;

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------

FUNCTION ERRORS_FOUND(P_JOB_RUN_TIMESTAMP TIMESTAMP) RETURN BOOLEAN IS
L_JOB_RUN_TIMESTAMP TIMESTAMP; ERROR_COUNT NUMBER; ERROR_BOOL BOOLEAN;
BEGIN
L_JOB_RUN_TIMESTAMP := P_JOB_RUN_TIMESTAMP;

SELECT COUNT(*) INTO ERROR_COUNT FROM TEST_PACKAGE_LOG_TBL WHERE STATUS_CODE = 'E' AND PROCESS_START_TIMESTAMP = L_JOB_RUN_TIMESTAMP; IF ERROR_COUNT > 0 THEN ERROR_BOOL := TRUE; ELSE ERROR_BOOL := FALSE;
END IF;

RETURN ERROR_BOOL;
END;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------

PROCEDURE ARCHIVE_ALL_TABLES AS

/*
                            Original Author: 
                            Created Date: 13-Aug-2012
                            Purpose: To drop all tables before recreating and archiving newly created tables
                            NOTE: in package - do not use create or replace and 'as' would be alternative to 'is'
                            */

/*variables*/
L_DROP_ARCHIVE_TABLES VARCHAR2(4000); L_SQL_CREATE_ARCHIVED_TABLES VARCHAR2(4000); L_PREVENT_SQL_INJECTION
EXCEPTION
;
--L_NOTIFY_ADMINISTRATOR VARCHAR(4000); --TO BE DONE AT A LATER TIME

BEGIN

RETURN;

EXCEPTION

WHEN L_PREVENT_SQL_INJECTION THEN NULL;

WHEN OTHERS THEN W('ERROR: ' || SQLERRM);

END;

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------

BEGIN
-- Initialization
/*archive all tables is like my 'driver' that calls drop then create while logging to the table. Pragma_auto prevents a rollback which would prevent table logging
              FIRST: This package will need to drop all previously archived tables before it creates new ones. call drop func first*/

/* calling  ARCHIVE_ALL_TABLES */
BEGIN
-- Call the function
NULL;

END;

RETURN;
END PKG_TEST_TABLES;

Select allOpen in new window