CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
JavaQuest
JavaQuest

Reputation: 713

How to access a variable in stored procedure into a sql query

I want to access a variable that is present in a stored procedure

e.g.

Create or Replace package body ABC
as 

    Procedure XYZ 
    as
    var Number;
    var := 2+3

    END XYZ

END ABC

Now I want to access this variable var in a select statement. Calling a the body.procedure.variable name work? e.g. 

select something as new_variable from table_name where ABC.XYZ.var > 10;

Upvotes: 0

Views: 1550

Answers (2)

mahi_0707
mahi_0707

Reputation: 1062

You can add the below function in your package and call it using select from dual:

    create or replace function CHECK_FUNC
    return number
    AS
    var number;
    BEGIN
    var := 2+3;
    return var;
    END CHECK_FUNC;
    /
Function created.     

SQL> select 10 from dual where 10 > (select check_func from dual);
  -- 10 > (2+3) condition satisfied. Hence '10' is returned.                                                                                                

        10                                                                                                                  
----------                                                                                                                  
        10                                                                                                                  

SQL> select 10 from dual where 10 < (select check_func from dual);                                                          
        --10 < (2+3) condition NOT satisfied. Hence no rows returned.                                                                                                                        
no rows selected

Few more examples: 

 select 'SOMETHING' from dual where 10 > (select check_func from dual); 

    'SOMETHIN                                                                                                                   
    ---------                                                                                                                   
    SOMETHING     


select 'SOMETHING' from dual where 10 < (select check_func from dual); 

no rows selected

Upvotes: 0

kevinskio
kevinskio

Reputation: 4551

You can only access package variables from an external query if they are declared in the package header.

CREATE OR REPLACE PACKAGE TEST is

var  VARCHAR2(10);
 /*or use the function*/
FUNCTION get_var RETURN VARCHAR2;
END TEST;

You can access the yourSchema.test.var in PL/SQL 

DECLARE
my_var yourSchema.test.var%TYPE;
BEGIN
my_var := yourSchema.test.var;
END;

or get the value of var in SQL by using the function;

select yourSchema.test.get_var from dual;

In your package body you can initialize the value of var at load time or use the function to return the value.

Upvotes: 2

Related Questions