Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56

    Unanswered: How to access user-defined constant in View?

    I need to access a user-defined constant, which locates inside a package, within a view. Is it possible to do that?

    <<Package definition>>
    create or replace package PK_TEST is
    begin
    ...
    c_emp_type CONSTANT NUMBER := 123;
    ...
    end;

    <<View definition>>
    create or replace view V_TEST as
    select name from emp where emp_type = PK_TEST.c_emp_type;


    I receive the following error when trying to execute the view:
    "ORA-06553: PLS-221: 'c_emp_type' is not a procedure or is undefined"

    Please offer help. Thank you.
    Last edited by shev; 10-23-03 at 04:04.
    Cheers,
    Shev

  2. #2
    Join Date
    Oct 2003
    Posts
    22

    Re: How to access user-defined constant in View?

    Dont use 'BEGIN' in your package specification.
    I ur "declaring" a procedure in the specification, BEGIN will indicate the start of that particular procedure's code.
    The rest is OK.
    Last edited by stephen_pe; 10-24-03 at 10:21.

  3. #3
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56
    Even if I take out "begin", it still returns error.
    Cheers,
    Shev

  4. #4
    Join Date
    Oct 2003
    Location
    Germany - Stuttgart
    Posts
    14
    You should create a table wich contains all constants of your programms (how about the name alpas = all parameters)...The package can read the value when needed and the constant is easy to modify if neccessary

  5. #5
    Join Date
    Oct 2003
    Posts
    22

    Re: How to access user-defined constant in View?

    try coppying the package constant into a variable in the view.

    select package.constant
    into variable
    from dual;

    I'll try to find if the previous where expression is ok or not.
    Last edited by stephen_pe; 10-26-03 at 15:38.

  6. #6
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    IIRC there can be issues over reading package variable and constants directly from a package, use wrapper function as follows...

    PHP Code:
    CREATE OR REPLACE PACKAGE PCK_REPORTING IS
       ReportOrganisation A_ORGS_NAMES
    .CODE%TYPE DEFAULT NULL;
       FUNCTION 
    GetOrganisation  RETURN A_ORGS_NAMES.CODE%TYPE;
       
    PROCEDURE SetOrganisation AOrg IN A_ORGS_NAMES.CODE%TYPE                );
       
    PRAGMA RESTRICT_REFERENCES GetOrganisation WNDSRNDSWNPS );
    END PCK_REPORTING;
    /
    CREATE OR REPLACE PACKAGE BODY PCK_REPORTING AS

       FUNCTION 
    GetOrganisation  RETURN A_ORGS_NAMES.CODE%TYPE IS 
          BEGIN 
             
    RETURN ReportOrganisation
          
    END;
          
       
    PROCEDURE SetOrganisation AOrg IN A_ORGS_NAMES.CODE%TYPE IS 
          BEGIN ReportOrganisation 
    := AOrg;
       
    END;
    END PCK_REPORTING;

    then, in your view use the function rather the constant directly. I know the example above isn't a constant, it's a variable, but the principle is the same.

    PHP Code:
    create or replace view fred is 
       select 

       
    from table
       where something 
    pck_reporting.GetOrganisation
    Hope that helps,
    Bill

  7. #7
    Join Date
    Oct 2003
    Posts
    22

    Re: How to access user-defined constant in View?

    I have tried it my self and got the same problem.

    could not find a solution.

    found an alterative.

    -----------------------
    <package specification>

    create or replace package package_name is
    function function_name return data_type;
    end

    <package body>

    create or replace package body package_name is
    function function_name return data_type
    return(value)
    end;
    end;

    <view>

    create or replace view view_name as
    select.........
    from.......
    where coulmn_name = package_name.function_name;

    ---------------------

    you are simply defing a function that returns the constant, and calling it as if it were a constant.

    sorry for the wrong solution ive provided previously.

  8. #8
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56
    Thanks all for the input. The function method works!

    I wonder if the later Oracle release can access the package constant directly without the need of wrapper function.
    Cheers,
    Shev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •