Global Variables

[getjbb]

I have the following package and I not clear on how to use the parameters coming in from one procedure (Create_Auto_Data) to flow to another procedure (Get_Trans_Info) and function(Get_Max_RowNum) in the package.

The params are date fields. Where the date is hardcoded as TO_DATE('06-22-2009', 'mm-dd-yyyy')the variable a_begin is needed.

The code is:

CREATE OR REPLACE PACKAGE BODY UNRESLVD AS

PROCEDURE Create_Auto_Data ( a_begin IN DATE ) IS

CURSOR after_dups_upd_data_cur is
SELECT *
FROM unres_dtl
ORDER BY row_id;

TYPE after_dups_upd_data IS
TABLE OF after_dups_upd_data_cur%ROWTYPE
INDEX BY PLS_INTEGER;

l_after_dups_upd_data after_dups_upd_data;


BEGIN


OPEN after_dups_upd_data_cur;
LOOP
FETCH after_dups_upd_data_cur BULK COLLECT INTO l_after_dups_upd_data LIMIT 200;
FOR indx2 IN 1..l_after_dups_upd_data.COUNT
LOOP
Get_Trans_Info(l_after_dups_upd_data(indx2).ROW_ID, l_after_dups_upd_data(indx2).ACCOUNT);
END LOOP;
EXIT WHEN l_after_dups_upd_data.COUNT = 0;
END LOOP;
CLOSE after_dups_upd_data_cur;


COMMIT;


END Create_Auto_Data;




FUNCTION Get_Max_RowNum(a_account IN vw_trans.ACCOUNT%TYPE) RETURN NUMBER IS

v_max NUMBER := 0;

BEGIN

SELECT MAX(rownum) INTO v_max FROM (SELECT *
FROM vw_trans
WHERE to_date(last_write_date) <= TO_DATE('06-22-2009', 'mm-dd-yyyy'));
RETURN v_max;

END Get_Max_Rownum;



PROCEDURE Get_Trans_Info (a_rowid IN NUMBER,
a_account IN vw_trans.ACCOUNT%TYPE) IS

v_from_getmax NUMBER := 0;
v_trans_num vw_trans.TRANS_NUM%TYPE;
v_account vw_trans.ACCOUNT%TYPE;
v_amount vw_trans.AMOUNT%TYPE;
v_pays_bal vw_trans.PAYS_BAL%TYPE;
v_type vw_trans.TYPE%TYPE;
v_code vw_trans.CODE%TYPE;

BEGIN

v_from_getmax := Get_Max_RowNum(a_account);

if v_from_getmax > 0 then

SELECT trans_num, account, amount, pays_bal, type, code
INTO v_trans_num, v_account, v_amount, v_pays_bal, v_type, v_code
FROM (
SELECT a.*, rownum rnum
FROM (SELECT trans_num, account, amount, pays_bal, type, code
FROM vw_trans
WHERE TO_DATE(last_write_date) <= TO_DATE('06-22-2009', 'mm-dd-yyyy')
AND account = a_account
ORDER BY last_write_date) a
WHERE rownum < (SELECT MAX(rownum)+1 FROM (SELECT trans_num, account, amount, pays_bal, type, code
FROM vw_trans
WHERE TO_DATE(last_write_date) <= TO_DATE('06-22-2009', 'mm-dd-yyyy')
AND account = a_account)
WHERE rnum >= v_from_getmax;


END Get_Trans_Info;

END UNRESLVD;

Thanks.

getjbb

 

[SantaMufasa]

GetJBB,

Please forgive my density, but what is it you would like us to respond to? Do you want to know how to create a global variable that is persistent throughout your session? (To do that, you define the variable in the package (header, not the package body)).

Let us know what you want us to do specifically.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[getjbb]

SantaMufasa,

You are not dense at all. I guess I did not phrase my question correcty.

I wanted to know how to create a global variable that is persistent throughout a session. I have seen some examples of the variables being put just below the create statement within the package body. I tried it, but it did not work.

Thanks.

getjbb

 

[SantaMufasa]

Ah, I understand now.

I have seen some examples of the variables being put just below the create statement within the package body. I tried it, but it did not work.

Whoever created the "examples" apparently did not understand the creation of global, persistent variables in PL/SQL. (Perhaps they did not test their code illustrating the persistence.) In any case, the examples are wrong.


As I mentioned in my earlier response, global (persistent) PL/SQL variables reside in the package header, not the package body.

Here is an extremely simplified example of how to create and access a global variable:

SQL> l
  1  create or replace package getjbb is
  2      my_number number;
  3* end;
SQL> /

Package created.

SQL> exec getjbb.my_number := 42;

PL/SQL procedure successfully completed.

SQL> set serveroutput on format wrap

SQL> exec dbms_output.put_line('My number = '||to_char(getjbb.my_number+7)||'.')
My number = 49.

PL/SQL procedure successfully completed.

Notice in my example, above, that I don't even need to define a package body to create, and benefit from, global variables.

Let us know if this resolves your question.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[getjbb]

SantaMufasa,

Thanks, I put the global variables in the spec and was able to access the data put into the variables.

getjbb

 

[Beilstwh]

Thank Him with a star, he helped you out.

Bill
Lead Application Developer
New York State, USA