Home » SQL & PL/SQL » SQL & PL/SQL » Parameterized One-Time-Only procedure....
Parameterized One-Time-Only procedure.... [message #645789] Tue, 15 December 2015 03:13 Go to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member

Hi

Can we build parameterized 'One Time Only' procedure?

Regards
Re: Parameterized One-Time-Only procedure.... [message #645791 is a reply to message #645789] Tue, 15 December 2015 03:31 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I've no idea what you mean by one time only procedure.
Procedures aren't one time only by definition.
Re: Parameterized One-Time-Only procedure.... [message #645796 is a reply to message #645791] Tue, 15 December 2015 03:39 Go to previous messageGo to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member

I assume that all experts know that in oracle package we can build 'one time only' procedure which executes once in a user session if the package is called/executed.
Re: Parameterized One-Time-Only procedure.... [message #645797 is a reply to message #645796] Tue, 15 December 2015 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You mean a procedure that is declared in the initialization section?

Re: Parameterized One-Time-Only procedure.... [message #645799 is a reply to message #645797] Tue, 15 December 2015 04:14 Go to previous messageGo to next message
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 #645800 is a reply to message #645799] Tue, 15 December 2015 04:17 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Dear,

Please follow comments given by Cookiemonster and read below mentioned document for getting details:

https://books.google.co.in/books?id=GTcLAAAAQBAJ&pg=PA266&lpg=PA266&dq=one+time+procedure+in+oracle11g&source=bl& ots=-0ZmAwJOTK&sig=lM_mFUSlQX4kGAuzvaAlkpeMrfc&hl=en&sa=X&ved=0ahUKEwjR-uW8yd3JAhWBzRoKHRw2DgoQ6AEIJDAA#v=onepage& ;q=one%20time%20procedure%20in%20oracle11g&f=false
Re: Parameterized One-Time-Only procedure.... [message #645801 is a reply to message #645799] Tue, 15 December 2015 04:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can "parameterize" it using global variables like CLIENT_INFO session parameter or context variable for instance.

Re: Parameterized One-Time-Only procedure.... [message #645802 is a reply to message #645800] Tue, 15 December 2015 04:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

@msol,

Your link returns me
"Restricted Page
You have reached your viewing limit for this book (why?)."

Re: Parameterized One-Time-Only procedure.... [message #645804 is a reply to message #645802] Tue, 15 December 2015 05:06 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
I can view the link without any restriction. It is a book by Joan Casteel on Oracle 11g PL/SQL Programming.

Although I never searched it before, so certainly I can't hit the viewing limit.

By the way, it talks about global variable as the solution which you suggested.

[Updated on: Tue, 15 December 2015 05:09]

Report message to a moderator

Re: Parameterized One-Time-Only procedure.... [message #645806 is a reply to message #645804] Tue, 15 December 2015 05:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, maybe I read it some time ago and have a bl... cookie somewhere. Smile

Re: Parameterized One-Time-Only procedure.... [message #645807 is a reply to message #645806] Tue, 15 December 2015 05:27 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Yes Michel Sir...

It's True.Really You are Genius Smile
Re: Parameterized One-Time-Only procedure.... [message #646019 is a reply to message #645801] Fri, 18 December 2015 00:09 Go to previous messageGo to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member

Thanks Michel for this nice suggestion.

Regards
Re: Parameterized One-Time-Only procedure.... [message #646059 is a reply to message #645789] Fri, 18 December 2015 07:00 Go to previous messageGo to next message
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 Go to previous messageGo to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member

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 #646171 is a reply to message #646165] Mon, 21 December 2015 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I can parameter this using packaged global variable from another package:


As I said, "global variables", any kind, I just gave 2 I didn't list all of them.
Now the type of global variables is best depends on how you will come to initialize your package and what you need as parameters.

Re: Parameterized One-Time-Only procedure.... [message #646184 is a reply to message #646165] Mon, 21 December 2015 08:21 Go to previous messageGo to next message
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 Go to previous messageGo to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member

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 #646207 is a reply to message #646206] Tue, 22 December 2015 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In this case you should use a context or session variable, for instance in a logon trigger, and not a package variable.

Re: Parameterized One-Time-Only procedure.... [message #646236 is a reply to message #646206] Tue, 22 December 2015 17:27 Go to previous message
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.
Previous Topic: Help with oracle query to manipulate dates
Next Topic: TO_NUMBER Doesn't Support These Elements: RN, rn, TM, and V ?
Goto Forum:
  


Current Time: Fri Mar 29 01:29:01 CDT 2024