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.
Here's the code snippet of package body which has an INIT procedure and a procedure to compute
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');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;ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
if you really want to do that, you must then define and populate those variables prior to trying to compile the package