?
Solved

Help for a first time package creator in PL/SQL

Posted on 2012-08-15
3
Medium Priority
?
509 Views
Last Modified: 2012-08-16
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

Open 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;

Open 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;

Open in new window

0
Comment
Question by:Mark_Co
3 Comments
 
LVL 20

Accepted Solution

by:
gatorvip earned 1500 total points
ID: 38300195
PROCEDURE LOG_PROC(P_PROCESS_START_TIMESTAMP TIMESTAMP,
      P_ARCHIVE_DATE DATE,
      P_TABLE_NAME VARCHAR2,
      P_STATUS_CODE VARCHAR2, P_COMMENTS VARCHAR2) AS

You forgot to include the "in", here as well as in other definitions.

For example
PROCEDURE LOG_PROC(P_PROCESS_START_TIMESTAMP in TIMESTAMP, 
	P_ARCHIVE_DATE in DATE, 
	P_TABLE_NAME in VARCHAR2, 
	P_STATUS_CODE in VARCHAR2, 
	P_COMMENTS in VARCHAR2) AS

Open in new window


If you're still getting compilation errors, make sure you post them.
0
 

Author Comment

by:Mark_Co
ID: 38300207
I will. Thank you!
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 500 total points
ID: 38301686
A couple of observations. First if parameters are not specifically defined as in, out, or in out, they automatically default to in so I don't think that's an issue. Secondly, I have found it much easier to create packages a piece at a time, starting with any piece that other pieces may rely upon. Just write the code for the package with one procedure (or function), then compile it using a show errors statement afterward, making corrections until no errors occur, then add another procedure to your package and repeat the process until all procedures and/or functions are included and have no errors. What errors are you getting now when trying to compile your package?
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses
Course of the Month17 days, 4 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question