how to store information in variables accessible to procedures in the packages??

5 pts.
Tags:
Oracle 10g
how do i store information in variables which are accesible by more than one procedures in the same package?? (i.e. i want to take input information using a procedure and i want to call other procedures internally which will use these input information to give results...) CAN ANYONE HELPME ON THIS????
ASKED: February 5, 2008  10:01 AM
UPDATED: February 5, 2008  4:00 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,

Use package variables. If the variable is declared in the package head, it is said to be “public” and it can be referenced in any PL/SQL, but if it is declared in the package body, it is called “private” and can only be referenced from the package itself. In the example below, bar is a public variable and foo is private. If you want to initialize variables, you can do it either when you declare the variable (like foo, below) or in the BEGIN section of the package body (like bar).

Try running this code:

<pre>
CREATE OR REPLACE PACKAGE pk_foo
AS
bar VARCHAR2 (20);

FUNCTION get_foo
RETURN NUMBER;

PROCEDURE set_foo
( in_val IN NUMBER
);

END pk_foo;
/
SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY pk_foo
AS
foo NUMBER := 0;

FUNCTION get_foo
RETURN NUMBER
IS
BEGIN
RETURN foo;
END get_foo;

PROCEDURE set_foo
( in_val IN NUMBER
)
IS
BEGIN
foo := in_val;
END set_foo;

BEGIN
bar := ‘Hello!’;
END pk_foo;
/
SHOW ERRORS

SELECT pk_foo.get_foo
FROM dual;

SET SERVEROUTPUT ON SIZE 10000

BEGIN
dbms_output.put_line (pk_foo.bar || ‘ = bar before change’);
pk_foo.bar := ‘Hi!’;
dbms_output.put_line (pk_foo.bar || ‘ = bar after change’);
pk_foo.set_foo (1);
END;
/

SELECT pk_foo.get_foo
FROM dual;
</pre>

The first time you run it, you’ll get output like this:

<pre>
Package created.

No errors.

Package body created.

No errors.

GET_FOO
———-
0

Hello! = bar before change
Hi! = bar after change

PL/SQL procedure successfully completed.

GET_FOO
———-
1
</pre>

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following