One-time-only procedure doubt [message #327283] |
Sun, 15 June 2008 23:25 |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
Hi,
I have a doubt. Please clarify it.
How does one-time-only procedure works.I read, one-time-only procedure executedonly once, when the package is invoked.
In the following code, how does the one-time-only procedure gets invoked when the procedure (pk_demo.p_proc_1) is invoked.
The output of one-time-only procedure is printed only for the first time for a session why not every time.
Please explain me the functionality and usage of one-time-only procedure elaborately.
CREATE OR REPLACE PACKAGE pk_demo
AS
PROCEDURE p_proc_1;
FUNCTION f_func_1
RETURN NUMBER;
END pk_demo;
/
CREATE OR REPLACE PACKAGE BODY pk_demo
IS
a NUMBER;
FUNCTION f_func_1
RETURN NUMBER
AS
BEGIN
RETURN 10;
END f_func_1;
PROCEDURE p_proc_1
IS
li_num NUMBER;
BEGIN
SELECT f_func_1
INTO li_num
FROM DUAL;
DBMS_OUTPUT.put_line (li_num);
END p_proc_1;
//one-time-only procedure defination
BEGIN
DBMS_OUTPUT.put_line ('u r in the only block execution');
END pk_demo;
/
SQL> execute pk_demo.p_proc_1;
you are in the only block execution
10
PL/SQL procedure successfully completed.
//here in the following the output is not same as earlier
SQL> execute pk_demo.p_proc_1;
10
PL/SQL procedure successfully completed.
Please clarify it.
Regards,
Ps.
|
|
|
Re: One-time-only procedure doubt [message #327288 is a reply to message #327283] |
Sun, 15 June 2008 23:36 |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
It is used for initialisation of package variables etc. Set everything ready for usage of the package.
Please don't use things like "u" or "r", as you can see, our forum software translates it to "you" and "are" in some cases, so it looks as though you mocked up the outcome of your code.
|
|
|
|
|
Re: One-time-only procedure doubt [message #327310 is a reply to message #327304] |
Mon, 16 June 2008 00:39 |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
anacedent wrote on Mon, 16 June 2008 07:18 | How does
>DBMS_OUTPUT.put_line ('u r in the only block execution');
produce
>you are in the only block execution
|
This is what I meant. The forum software rewrote the outcome. The quote in the code, prior to the "u" prevented that one from being translated.
|
|
|
Re: One-time-only procedure doubt [message #327311 is a reply to message #327300] |
Mon, 16 June 2008 00:40 |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
pointers wrote on Mon, 16 June 2008 07:12 | I guess, I have not used as you said.....
please explain me the answer....
Regards,
ps.
|
What is your question? In your code, you can see exactly the behavior you describe.
|
|
|
Re: One-time-only procedure doubt [message #327321 is a reply to message #327311] |
Mon, 16 June 2008 01:08 |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
I am sorry for that, I have just copied the code from my database. I have checked the writing before submitting but dint check inside the code.
DBMS_OUTPUT.put_line ('u r in the only block execution'); is inside the code.
my question is--
1. How does the one-time-only procedure got invoked, if it is just because the procedure gets invoked for the first time when the package call is made, then why not the other(function or procedure).
|
|
|
Re: One-time-only procedure doubt [message #327322 is a reply to message #327321] |
Mon, 16 June 2008 01:20 |
|
Michel Cadot
Messages: 68645 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
PL/SQL User's Guide and Reference
Chapter 9 Using PL/SQL Packages
Section Understanding The Package Body
Quote: | Following the declarative part of a package body is the optional initialization part, which typically holds statements that initialize some of the variables previously declared in the package.
The initialization part of a package plays a minor role because, unlike subprograms, a package cannot be called or passed parameters. As a result, the initialization part of a package is run only once, the first time you reference the package.
|
Regards
Michel
|
|
|