Home » SQL & PL/SQL » SQL & PL/SQL » One-time-only procedure doubt (Oracle 9i)
One-time-only procedure doubt [message #327283] Sun, 15 June 2008 23:25 Go to next message
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 Go to previous messageGo to next message
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 #327300 is a reply to message #327288] Mon, 16 June 2008 00:12 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
I guess, I have not used as you said.....
please explain me the answer....

Regards,
ps.
Re: One-time-only procedure doubt [message #327304 is a reply to message #327283] Mon, 16 June 2008 00:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
How does
>DBMS_OUTPUT.put_line ('u r in the only block execution');
produce
>you are in the only block execution


In the USN it classified as "gun decking" your position as in flasifying.
You can not shot the sun from the gun deck.
Re: One-time-only procedure doubt [message #327310 is a reply to message #327304] Mon, 16 June 2008 00:39 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Viewing formatted data from a table
Next Topic: update column and text search (merged)
Goto Forum:
  


Current Time: Fri Apr 26 05:18:03 CDT 2024