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.

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 ) ';

Open in new window


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

Open in new window



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".
steve2312Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Sanjeev LabhDatabase ConsultantCommented:
Your method of accessing is wrong. In PLSQL procedure you cannot directly assign the value by the call of the procedure.

What you could have done is:

Make get_region_partition a function instead of procedure with RETURN clause as varchar2. And then at the end of the function RETURN v_partition_key. Then you will be able to assign the value as expceted. To make things clearer I have just provided the structure of your program below.

FUNCTION get_region_partition(p_tablename IN VARCHAR2,
                                                         p_firstday  IN region.firstday%TYPE,
                                                         p_lastday   IN region.lastday%TYPE) 
RETURN varchar2 IS
BEGIN
.
.
.
RETURN v_partition_key 
END get_region_partition;

Open in new window


And while calling the function you can then use:

gv_partition_key := common.get_region_partition(p_tablename => region,
                                                             p_firstday  => 20170101,
                                                             p_lastday   => 20170331);

Open in new window


Method 2:
There is another method also where you can keep v_partition_key as an OUT parameter of the procedure. So if you want to keep it as procedure only and not as a function then you can use it as an OUT parameter. And while calling the procedure just substitue the variable accordingly. E.g.

PROCEDURE get_region_partition(p_tablename IN VARCHAR2,
                                 p_firstday  IN region.firstday%TYPE,
                                 p_lastday   IN region.lastday%TYPE,
                                 p_partition_key OUT varchar2) IS
BEGIN
.
.
.
END;

Open in new window


While calling the procedure.

common.get_region_partition(p_tablename => region,
                                                             p_firstday  => 20170101,
                                                             p_lastday   => 20170331,
                                                             p_partition_key  => gv_partition_key);

Open in new window



Hope this helps.
0
steve2312Author Commented:
Thank you for the suggestion!  I went ahead with creating a function and this worked!

 FUNCTION get_region_partition(p_tablename IN VARCHAR2,
                                p_firstday  IN region.firstday%TYPE,
                                p_lastday   IN region.lastday%TYPE)
    RETURN VARCHAR2 IS
    v_partition_key VARCHAR2(100);
  
  BEGIN
    IF p_tablename = 'REGION' THEN
      EXECUTE IMMEDIATE 'SELECT to_char(p_firstday,''YYYYMMDD'')||''_''||to_char(p_lastday,''YYYYMMDD'') FROM DUAL'
        INTO v_partition_key;
    ELSE
      NULL;
    END IF;
    RETURN v_partition_key;
  END get_region_partition;

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'.

  Procedure init_vars (p_parm1 in varchar2, p_parm2 in varchar2) is

 BEGIN
  ...
  ...
  ...
    gv_partition_key := common.get_region_partition(p_tablename => 'REGION',  p_firstday  => 20170101,  p_lastday   => 20170331);
END;

Open in new window

0
slightwv (䄆 Netminder) Commented:
I'm assuming you are creating that function in the common package.

What is the data type of the firstday and lastday columns in the region table?

What you are passing into the function needs to match.

Also, you are doing a LOT of coding that you don't have to do.

Using the parameters you are passing in your function could be:
FUNCTION get_region_partition(p_tablename IN VARCHAR2,
                                p_firstday  IN region.firstday%TYPE,
                                p_lastday   IN region.lastday%TYPE)
    RETURN VARCHAR2 IS
BEGIN

	return case when p_tablename = 'REGION' THEN to_char(p_firstday) ||'_'|| to_char(p_lastday) end;
END get_region_partition;

Open in new window

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

steve2312Author Commented:
Yes, the function is in the common package.  The first and last day parameters is Date.
0
slightwv (䄆 Netminder) Commented:
>>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.

    gv_partition_key := common.get_region_partition(p_tablename => 'REGION',  p_firstday  => to_date('20170101','YYYYMMDD'),  p_lastday   => to_date('20170331','YYYYMMDD'));

Open in new window


or:

FUNCTION get_region_partition(p_tablename IN VARCHAR2,
                                p_firstday  IN number,
                                p_lastday   IN number)

Open in new window


then you can use:
    gv_partition_key := common.get_region_partition(p_tablename => 'REGION',  p_firstday  => 20170101,  p_lastday   => 20170331);

Open in new window


Personally, I would go with the second option since you don't need actual dates inside the function.
0
slightwv (䄆 Netminder) Commented:
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;

Open in new window

0
steve2312Author Commented:
Thank you for the suggestions.  The solutions worked
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
Query Syntax

From novice to tech pro — start learning today.