|
|
|
|
Re: Parameterized One-Time-Only procedure.... [message #645799 is a reply to message #645797] |
Tue, 15 December 2015 04:14 |
cookiemonster
Messages: 13917 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
He means the bit you can put at the end of the package that is automatically run the first time a package is executed in a given session.
No it can't be parameterized. It's not named and there is no way of calling it directly.
|
|
|
|
|
|
|
|
|
|
Re: Parameterized One-Time-Only procedure.... [message #646059 is a reply to message #645789] |
Fri, 18 December 2015 07:00 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
mokarem wrote on Tue, 15 December 2015 04:13
Can we build parameterized 'One Time Only' procedure?
Sure we can:
SQL> set serveroutput on
SQL> declare
2 procedure one_time_proc(
3 n number
4 )
5 is
6 begin
7 for i in 1..n loop
8 dbms_output.put_line(lpad('*',i,'*'));
9 end loop;
10 end;
11 begin
12 one_time_proc(10);
13 end;
14 /
*
**
***
****
*****
******
*******
********
*********
**********
PL/SQL procedure successfully completed.
SQL>
SY.
|
|
|
Re: Parameterized One-Time-Only procedure.... [message #646165 is a reply to message #646059] |
Mon, 21 December 2015 00:48 |
|
Thanks Solomon.
My query was to find out is there any way to add parameters in One-Time-Only procedure of Package.
I believe below syntax shows/clarify the One-Time-Only procedure:
CREATE OR REPLACE PACKAGE BODY <package_name>
IS
PROCEDURE prc1
IS
BEGIN
<....>
END prc1;
BEGIN
<executable section of One-Time-Only procedure>
END <package_name>;
I think Michel's suggestion is acceptable to parameterize the One-Time-Only procedure using CONTEXT variable.
However there is some workaround as below:
I can parameter this using packaged global variable from another package:
Below is code:
CREATE OR REPLACE PACKAGE test_pkg
IS
g_deptno NUMBER;
g_cnt_emp NUMBER;
PROCEDURE prc;
END test_pkg;
/
CREATE OR REPLACE PACKAGE BODY test_pkg
IS
PROCEDURE prc
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Department ID provided in test_pkg.prc'||g_deptno);
END prc;
BEGIN
DBMS_OUTPUT.PUT_LINE('Department ID provided test_pkg.prc from One-Time-Only '||test_pkg.g_deptno);
SELECT count(employee_id)
INTO g_cnt_emp
FROM employees
WHERE department_id=test_pkg.g_deptno;
DBMS_OUTPUT.PUT_LINE('Total no of emp. from One-Time-Only of test_pkg '||g_cnt_emp);
END test_pkg;
CREATE OR REPLACE PACKAGE test_pkg1
IS
g_deptno NUMBER;
g_cnt_emp NUMBER;
PROCEDURE prc1;
END test_pkg1;
/
CREATE OR REPLACE PACKAGE BODY test_pkg1
IS
PROCEDURE prc1
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Department ID provided in test_pkg1.prc1'||g_deptno);
END prc1;
BEGIN
DBMS_OUTPUT.PUT_LINE('Department ID provided in test_pkg from test_pkg1::'||test_pkg.g_deptno);
SELECT count(employee_id)
INTO g_cnt_emp
FROM employees
WHERE department_id=test_pkg.g_deptno;
DBMS_OUTPUT.PUT_LINE('Total no of emp from test_pkg1::'||g_cnt_emp);
END test_pkg1;
BEGIN
test_pkg.prc;
test_pkg.g_deptno := 90;
test_pkg1.g_deptno := 90;
END;
Output is as below:
Department ID provided test_pkg.prc from One-Time-Only
Total no of emp. from One-Time-Only of test_pkg 0
Department ID provided in test_pkg.prc
Department ID provided in test_pkg from test_pkg1::90
Total no of emp from test_pkg1::3
|
|
|
|
Re: Parameterized One-Time-Only procedure.... [message #646184 is a reply to message #646165] |
Mon, 21 December 2015 08:21 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
There is no such thing as "One-Time-Only procedure of Package". I think you are referring to package initialization section. If so, then you should keep in mind - package initialization section is executed once per session (unless package is created as serially reusable). I suggest you explain what exactly you are trying to achieve.
SY.
[Updated on: Mon, 21 December 2015 08:22] Report message to a moderator
|
|
|
Re: Parameterized One-Time-Only procedure.... [message #646206 is a reply to message #646184] |
Tue, 22 December 2015 02:03 |
|
Thanks SY.
My query was, can we parameterize this 'Initialization Section' of Package?
In a Radiology Information System, based on a connected Radiologist(user id) in the system, different categories of orders' would be populated on his/her worklist. Radiologists(Users) and Exams(orders) categories are mapped in a DB table.
Regards
|
|
|
|
Re: Parameterized One-Time-Only procedure.... [message #646236 is a reply to message #646206] |
Tue, 22 December 2015 17:27 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
So what's the issue? Your design should have something like:
1. User table
2. Order Category table
3. User Order Categorie table
Then all you need is
select oc.name
bulk collect
into v_order_category_collection
from order_category oc,
users u
where oc.user_id = u.user_id
and u.username = 'Radiologist Name'
SY.
|
|
|