How to reference a packaged procedure to a global variable
Have created a partition key inside a packaged (Package name Common) procedure using the pseudo code below. The partition key is a concatenated value of p_firstday_p_lastday.
In a different package ( say Package B) and in the initialization section, I am making a reference to the partition_key value in the package A by the code below.
This assignment fails with the error PLS:00357 Table, View or reference not allowed here. Not sure why this initialization fails. Is it because it is procedure? The intent is to initialize gv_parition_key to the partition_key value obtained from the procedure get_region_partition inside the package "Common".
PROCEDURE get_region_partition(p_tablename IN VARCHAR2,
p_firstday IN region.firstday%TYPE,
p_lastday IN region.lastday%TYPE) IS
v_partition_sql VARCHAR2(2000);
v_partition_exists NUMBER;
v_partition_key VARCHAR2(100);
BEGIN
v_partition_key := p_firstday || '_' || p_lastday;
SELECT COUNT(*)
INTO v_partition_exists
FROM user_tab_partitions
WHERE table_name = p_tablename
AND partition_name = v_partition_key;
IF v_partition_exists = 0 THEN
BEGIN
v_partition_key := v_partition_key;
EXCEPTION
-- exception raised and handled
END;
END IF;
v_partition_sql := 'alter table ' || p_tablename || ' add partition ' ||
v_partition_key || ' values (' || p_firstday || '_' || '
p_lastday ) ';In a different package ( say Package B) and in the initialization section, I am making a reference to the partition_key value in the package A by the code below.
gv_partition_key := common.get_region_partition(p_tablename => region,
p_firstday => 20170101,
p_lastday => 20170331);This assignment fails with the error PLS:00357 Table, View or reference not allowed here. Not sure why this initialization fails. Is it because it is procedure? The intent is to initialize gv_parition_key to the partition_key value obtained from the procedure get_region_partition inside the package "Common".
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, the function is in the common package. The first and last day parameters is Date.
>>The first and last day parameters is Date.
Then you need to pass in a date or change the input parameters to a number or varchar2.
or:
then you can use:
Personally, I would go with the second option since you don't need actual dates inside the function.
Then you need to pass in a date or change the input parameters to a number or varchar2.
gv_partition_key := common.get_region_partition(p_tablename => 'REGION', p_firstday => to_date('20170101','YYYYMMDD'), p_lastday => to_date('20170331','YYYYMMDD'));or:
FUNCTION get_region_partition(p_tablename IN VARCHAR2,
p_firstday IN number,
p_lastday IN number)then you can use:
gv_partition_key := common.get_region_partition(p_tablename => 'REGION', p_firstday => 20170101, p_lastday => 20170331);Personally, I would go with the second option since you don't need actual dates inside the function.
Your function would be:
FUNCTION get_region_partition(p_tablename IN VARCHAR2,
p_firstday IN number,
p_lastday IN number)
RETURN VARCHAR2 IS
BEGIN
return case when p_tablename = 'REGION' THEN to_char(p_firstday) ||'_'|| to_char(p_lastday) end;
END get_region_partition;ASKER
Thank you for the suggestions. The solutions worked
ASKER
Open in new window
However when trying to initialize a global variable gv_partition_key to this function inside another package. The call fails with wrong number of arguments (PLS-00306) in call to 'get_region_partition'.
Open in new window