Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

selection from dual is not working

684411Jul 26 2011 — edited Jul 28 2011
Hello all.

Here is my situation.

I am having a pl/sql package called TEST1 (code given bellow).

CREATE OR REPLACE PACKAGE TEST1
AS
TEST2 NUMBER := 1;

FUNCTION GET_TEST2 RETURN NUMBER ;

PROCEDURE SET_TEST2 (I_NUM IN NUMBER) ;
END;
/

CREATE OR REPLACE PACKAGE BODY TEST1
AS
FUNCTION GET_TEST2 RETURN NUMBER
IS
BEGIN
RETURN TEST2;
END GET_TEST2;

PROCEDURE SET_TEST2 (I_NUM IN NUMBER)
IS
BEGIN
TEST2 := I_NUM;
END SET_TEST2;

END TEST1;

The package is compiled with no errors.

When calling the following statement

SELECT TEST1.GET_TEST2 FROM DUAL;

I am getting the value of the package variable TEST2 (the value is '1' in my first attempt, which is the default value of the variable).

After calling the following statement

BEGIN
TEST1.SET_TEST2(5);
END;

I am expecting to get value '5' when calling again the first statement.
This does not happen. The returned value is still '1'.

On the other hand, when calling the following statement

begin
dbms_output.put_line(TEST1.TEST2);
end;

the out printed value of variable TEST2 in dbms_output is the expected one ('5').

It seems to be some kind of trouble when selecting from dual.
The database version of my environment is 11.2.0.1 .

Any ideas why is this happening?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 25 2011
Added on Jul 26 2011
8 comments
590 views