Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ,LOAD_DATE date "MM/DD/YYYY" "to_char(trunc(schema.load_seq.get_load_date), 'mm/dd/yyyy')"
- ,LOAD_SEQ_ID decimal external "schema.load_seq.get_load_seq_id"
- CREATE OR REPLACE PACKAGE SCHEMA.LOAD_SEQ AS
- /******************************************************************************
- NAME: LOAD_SEQ
- PURPOSE: Sets unique load_date and Load_seq_id per session when
- the package is instantiated. Package functions are
- intended to be called from control files so all rows in a
- file load will have the same load_date and load_seq_id.
- When the functions are called, the package is instantiated and
- the code at the bottom is run once for the session, setting the
- load_date and load_seq_id. The functions simply return the values
- which will remain the same for that session.
- EXAMPLE: ,LOAD_SEQ_ID DECIMAL EXTERNAL "load_seq.get_load_seq_id"
- (each row then has the same load_seq_id).
- REVISIONS:
- Ver Date Author Description
- --------- ---------- --------------- ------------------------------------
- 1.0 2/20/2017 Gary_W 1. Created this package.
- ******************************************************************************/
- NEXT_LOAD_SEQ_ID NUMBER;
- NEXT_LOAD_DATE DATE;
- FUNCTION GET_LOAD_SEQ_ID RETURN NUMBER;
- FUNCTION GET_LOAD_DATE RETURN DATE;
- END LOAD_SEQ;
- /
- CREATE OR REPLACE PACKAGE BODY SCHEMA.LOAD_SEQ AS
- FUNCTION GET_LOAD_SEQ_ID RETURN NUMBER IS
- BEGIN
- RETURN LOAD_SEQ.NEXT_LOAD_SEQ_ID;
- END GET_LOAD_SEQ_ID;
- FUNCTION GET_LOAD_DATE RETURN DATE IS
- BEGIN
- RETURN LOAD_SEQ.NEXT_LOAD_DATE;
- END GET_LOAD_DATE;
- BEGIN
- -- This code is run once, when the package is first called by the session.
- -- It sets the package variables which then do not change during the life of the session.
- SELECT SYSDATE, X_LOAD_SEQ.NEXTVAL
- INTO LOAD_SEQ.NEXT_LOAD_DATE, LOAD_SEQ.NEXT_LOAD_SEQ_ID
- FROM DUAL;
- END LOAD_SEQ;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement