Referencing substitution variable inside a packaged procedure

The package perforns a calculation for p0wer consumption.  The below package body has 2 procedures, one to initialize the variables (init_system_vars) and other performing a calculation (calc_pwrchannelcut_data) by referencing 2 substitution variables, FirstDay and LastDay.

I get an error PLS00103 (encountered symbol = when expecting one of the following, a constant exception, a double quoted delimited identifier...) on  the gv_firstdaystarttime initialization statement.  I am using the global variables (gv_firstdaystarttime and gv_lastdaystarttime) in the procedure performing the calculation.  The way I am referencing the variables inside the procedure might be incorrect, but can't seem to figure out the reason.

  -- Operating FirstDay Start Time
  gv_firstdaystarttime := to_date(&FirstDay, 'MM/DD/YYYY');

Open in new window


Here's the code snippet of package body which has an INIT procedure and a procedure to compute

CREATE OR REPLACE PACKAGE BODY RP_PWR_CALC_CMPT AS

  C_VERSION CONSTANT VARCHAR2(20) := '$Revision: 1.0 $';
  -- Context/Engine Variables
  --
  -- Internally generated execution engine process identifer
  gv_execution_id RP_REC_GEN_EXTRACT.EXECUTION_ID%TYPE;
  --
  -- Truncated start date for the Market operating day to which all calculation will apply
  gv_operating_day statementschedule.operatingdate%TYPE;
  --
  -- Key used for accessing partitioned data for work/stage tables. Required for all DML
  gv_partition_key   RP_CALC_PWR_CSMPT.PARTITION_KEY%TYPE;
  --
  -- Operating FirstDay Start Time
  gv_firstdaystarttime := to_date(&FirstDay, 'MM/DD/YYYY');

  --
  -- Operating FirstDay Stop Time
  gv_firstdaystoptime := to_date(&FirstDay, 'MM/DD/YYYY') + 1 - (1 / 86400);

  --Operating LastDayStart Time
  gv_lastdaystarttime := to_date(&LastDay, 'MM/DD/YYYY');
  --
  --Operating LastDayStop Time
  gv_lastdaystoptime := to_date(&LastDay, 'MM/DD/YYYY') + 1 - (1 / 86400);
  --                           
  -- Start date for the Market operating day to which all calculation will apply
  p_opday statementschedule.operatingdate%TYPE;

  PROCEDURE init_system_vars IS
    C_SUB CONSTANT VARCHAR2(128) := 'init_vars';
  
  BEGIN
  
    rp_log.info(p_message => ('Entering.. ' || C_SUB), p_sub => C_SUB);
  
    gv_execution_id := rp_context.get_execution_id();
  
    rp_log.info(p_message => ('EXECUTION_ID: ' || gv_execution_id),
                p_sub     => C_SUB);
  
    rp_log.info(p_message => ('Exiting.. ' || C_SUB), p_sub => C_SUB);
  
  END init_system_vars;

  -- Procedure to calculate PWR_CHANNELCUT data 

  PROCEDURE calc_pwrchannelcut_data(p_execution_id  IN NUMBER,
                                   p_partition_key IN rp_calc_pwr_csmpt.partition_key%TYPE,
                                   p_opday         IN statementschedule.operatingdate%TYPE,
                                   p_backout       IN VARCHAR2 := NULL,
                                   p_input_start   IN DATE := NULL,
                                   p_input_stop    IN DATE := NULL) AS
  
    C_SUB         CONSTANT VARCHAR2(30) := 'calc_pwrchannelcut_data';
    
  
  BEGIN
  
    INSERT INTO RP_CALC_PWR_CSMPT
      (Execution_ID,
       TRADEDATE,
       STARTTIME,
       STOPTIME,
       DUNSNUMBER,
       INTERVALDATASOURCE,
       GENSITENAME,
       GENSITECODE,
       UIDRESOURCE,
       PGCCODE,
       PGCNAME,
       RENEWOFFSET,
       PARTITION_KEY,
       TOTALS)
      WITH opdays AS
       (select opday as opdaystart,
               (opday + 1) - (1 / 86400) as opdaystop,
               gv_firstDaystarttime as FirstDayStarttime,
               gv_firstDaystoptime as FirstDayStoptime,
               gv_lastdaystarttime as LastDayStartime,
               gv_lastdaystoptime as Lastdaystoptime
          from (select (trunc(x.day_in_yr, 'Q') + Y.dateoffset) as opday,
                       Y.firstdaystarttime,
                       Y.lastdaystarttime
                  from (
                        --
                        select gv_firstdaystarttime as day_in_yr -- This date is the driving date 
                          from dual -- Change this date if you want to run for a different Period
                        --
                        ) X
                 cross join (
                            --
                            select (level - 1) as dateoffset, -- get dateoffset ranges
                                    gv_FirstDayStarttime, -- get firstdatestarttime input by user global variable
                                    gv_LastDayStarttime -- get lastdaystarttime input by the user
                              from dual
                            CONNECT BY LEVEL <= 1000 -- Get 1000 dateoffset in case period is large
                            --
                            ) Y
                -- 
                ) Z
         where Z.opday <= Z.LastdayStoptime
        --
        )
      
      select p_execution_id AS Execution_id,
             V.opdaystart AS TRADE_DATE,
             V.opdaystart AS STARTTIME,
             V.opdaystop AS STOPTTIME,
             V.dunsnumber AS DUNSNUMBER,
             'LSCHANNEL' AS INTERVALDATASOURCE,
             V.gensitecode AS GENSITENAME,
             V.gencode AS GENCODE,
             V.uidresource AS UIDRESOURCE,
             V.pgccode AS PGCCODE,
             V.pgcname AS PGCNAME,
             V.renewoffset AS RENEWOFFSET,
             gv_partition_key AS PARTITION_KEY,
             --to_char(to_date(V.opdaystop), 'YYYY') || '0' ||
             --to_char(to_date(V.opdaystop), 'Q') AS PARTITION_KEY,
             (SUM(V.interval_value) / 1000) as TOTALS -- Daily Sum by gensitecode, gencode, pgccode, dunsnumber
        from (select d.uidchannelcut,
                     h.recorder,
                     r.gensitecode,
                     rn.uidresource,
                     gh.gencode,
                     p.pgccode,
                     p.pgcname,
                     p.dunsnumber,
                     decode(gh.renewoffset,
                            '(RN) Renewable',
                            'RN',
                            '(OS) Renewable Offset',
                            'OS',
                            '(RRP) Renewable Re-Powered',
                            'RRP',
                            '(RCF) Renewable Co-Fired',
                            'RCF') as renewoffset,
                     h.starttime as blob_starttime,
                     h.stoptime as blob_stoptime,
                     gv_FirstDayStarttime,
                     gv_LastDayStoptime,
                     O.opdaystart,
                     O.opdaystop,
                     T.interval_number,
                     T.interval_value
                from resourceid r
                JOIN billdeterminant b
                  on (r.uidbilldeterminant = b.uidbilldeterminant and
                     b.billdetermcode = 'GENMET')
                JOIN channel c
                  ON (r.uidchannel = c.uidchannel AND c.channelnum = 1) -- 1 is Gen, 4 is Load
                JOIN lschannelcutheader h
                  ON (r.resourceid = h.recorder and
                     h.uidchannel = c.uidchannel)
                JOIN lschannelcutdata d
                  ON (d.uidchannelcut = h.uidchannelcut)
                JOIN generatorhist gh
                  on (r.gensitecode = gh.gensitecode) -- to filter by renewoffset
                JOIN resourcenodal rn
                  on (gh.gencode = rn.resourcecode) -- to get uidresource
                JOIN generatorsitehist ghs
                  on (r.gensitecode = ghs.gensitecode) -- to get gensitecode
                JOIN pgcownership po
                  on (gh.gencode = po.gencode) -- to get PgcOwnership active for the OpDay
                JOIN pgc p
                  on (po.pgccode = p.pgccode) -- to get Pgc active for the OpDay
                JOIN opdays O
                  on (1 = 1) -- Join for all operating days in the quarter
                JOIN table(unpack_blob_subset(d.valuecodes, h.starttime, O.opdaystart)) T
                  ON (1 = 1) -- Extract All Intervals for a Single OpDay from a Multi-Day Interval Blob
               where r.mrecode <> '65' -- exclude Non-Ercot-Read
                 and h.spi = 900 -- unpack_blob_subset function works only for 15-minute data i.e. spi 900
                 and (h.starttime <= gv_lastdayStoptime and
                     h.stoptime > gv_FirstDayStarttime) -- Interval Blob contains at least 1 Opday of the Quarter
                 and (r.starttime <= h.starttime and
                     nvl(r.stoptime, h.stoptime) >= h.stoptime) -- Interval Blob falls within (start, stop) for Recorder
                 and (o.opdaystart >= h.starttime and
                     O.opdaystop <= nvl(h.stoptime, O.opdaystop)) -- Interval Blob contains Opday being considered
                 and (r.starttime <= O.opdaystart and
                     nvl(r.stoptime, O.opdaystop) >= O.opdaystop) -- ResourceID is active for the OpDay
                 and (gh.starttime <= O.opdaystart and
                     nvl(gh.stoptime, O.opdaystop) >= O.opdaystop) -- Generator is active for the OpDay
                 and (ghs.starttime <= O.opdaystart and
                     nvl(ghs.stoptime, O.opdaystop) >= O.opdaystop) -- GeneratorSite is active for the OpDay
                 and (po.starttime <= O.opdaystart and
                     nvl(po.stoptime, O.opdaystop) >= O.opdaystop) -- PgcOwnership is active for the OpDay
                 and (p.starttime <= O.opdaystart and
                     nvl(p.stoptime, O.opdaystop) >= O.opdaystop) -- PGC record is active for the OpDay
              ) V
       group by V.gensitecode,
                V.uidresource,
                V.gencode,
                V.pgccode,
                V.pgcname,
                V.dunsnumber,
                V.renewoffset,
                V.opdaystart,
                V.opdaystop
       order by V.gensitecode,
                V.gencode,
                V.dunsnumber,
                V.opdaystart,
                V.opdaystop;
  
  END calc_pwrchannelcut_data;

BEGIN
-- call to init_vars
-- call to procedure...

END package_body;

Open in new window

steve2312Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
why are you using substitution variables in your package body?

if you really want to do that, you must then define and populate those variables prior to trying to compile the package
0
sdstuberCommented:
if you want to initialize your variables, then provide parameters to your intialization procedure and intialize them there.

something like this...

    PROCEDURE init_system_vars(p_firstday IN VARCHAR2, p_lastday IN VARCHAR2)
    IS
        c_sub   CONSTANT VARCHAR2(128) := 'init_vars';
    BEGIN
        rp_log.info(p_message => ('Entering.. ' || c_sub), p_sub => c_sub);

        -- Operating FirstDay Start Time
        gv_firstdaystarttime := TO_DATE(p_firstday, 'MM/DD/YYYY');

        --
        -- Operating FirstDay Stop Time
        gv_firstdaystoptime := TO_DATE(p_firstday, 'MM/DD/YYYY') + 1 - (1 / 86400);

        --Operating LastDayStart Time
        gv_lastdaystarttime := TO_DATE(p_lastday, 'MM/DD/YYYY');
        --
        --Operating LastDayStop Time
        gv_lastdaystoptime := TO_DATE(p_lastday, 'MM/DD/YYYY') + 1 - (1 / 86400);
        --

        gv_execution_id := rp_context.get_execution_id();

        rp_log.info(p_message => ('EXECUTION_ID: ' || gv_execution_id), p_sub => c_sub);

        rp_log.info(p_message => ('Exiting.. ' || c_sub), p_sub => c_sub);
    END init_system_vars;


and then remove lines 15-27 in your package body
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert GOracle dbaCommented:
i'd create date variables
at least that way the procedure would always run correct, and not be error prone to the date format in the varchar
0
steve2312Author Commented:
Thank you sdstuber and Greet Gruwez for the solutions.   The p_firstday and p_last day declarations would mean these would need to be the procedure declarations as well, correct?   I have the p_input_start and p_input_stop  as DATE parameters and these seem incorrect now.

PROCEDURE calc_pwrchannelcut_data(p_execution_id  IN NUMBER,
                                   p_partition_key IN rp_calc_pwr_csmpt.partition_key%TYPE,
                                   p_opday         IN statementschedule.operatingdate%TYPE,
                                   p_backout       IN VARCHAR2 := NULL,
                                   p_input_start   IN DATE := NULL,
                                   p_input_stop    IN DATE := NULL) AS

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PL/SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.