Home » SQL & PL/SQL » SQL & PL/SQL » Global Variable
Global Variable [message #400840] Wed, 29 April 2009 22:42 Go to next message
toshidas2000
Messages: 120
Registered: November 2005
Senior Member
Following is my package code

CREATE OR REPLACE PACKAGE test
AS


PROCEDURE test1 (p_id IN varchar2,
p_name IN varchar2
);


PROCEDURE test2 (p_id IN varchar2,
p_name IN varchar2,
p_env IN varchar2
);


END test;
/


CREATE OR REPLACE PACKAGE BODY test
AS

p_name varchar2(32);
p_new_name varchar2(32) := 'A_'||p_name;

PROCEDURE test1 (p_id IN varchar2,
p_name IN varchar2
)
AS
begin
dbms_output.put_line(' The id is '|| p_id);
dbms_output.put_line(' The setname is '|| p_name);
dbms_output.put_line(' The new setname is '|| p_new_name);

end test1;

PROCEDURE test2 (p_id IN varchar2,
p_name IN varchar2,
p_env IN varchar2
)
AS
begin
dbms_output.put_line(' The id is '|| p_id);
dbms_output.put_line(' The name is '|| p_name);
dbms_output.put_line(' The env is '|| p_env);

end test2;

end test;
/


SQL> exec test.test1('4','MAT');
The customerid is 4
The setname is MAT
The new setname is A_

PL/SQL procedure successfully completed.
Shouldn't The new setname is A_MAT
How do I fix this...??
Re: Global Variable [message #400841 is a reply to message #400840] Wed, 29 April 2009 23:01 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
CREATE OR REPLACE PACKAGE test
AS
  PROCEDURE test1(p_id  IN VARCHAR2,p_name  IN VARCHAR2);
  
  PROCEDURE test2(p_id  IN VARCHAR2,p_name  IN VARCHAR2,p_env  IN VARCHAR2);
END test;
/

CREATE OR REPLACE PACKAGE BODY test
AS
  p_name  VARCHAR2(32);
  p_new_name  VARCHAR2(32) := 'A_' ||p_name;
  
  PROCEDURE Test1
       (p_id    IN VARCHAR2,
        p_name  IN VARCHAR2)
  AS
  BEGIN
    dbms_output.Put_line(' The id is '         ||p_id);
    dbms_output.Put_line(' The setname is '    ||p_name);
    dbms_output.Put_line(' The new setname is '||p_new_name);
  END test1;
  
  PROCEDURE Test2
       (p_id    IN VARCHAR2,
        p_name  IN VARCHAR2,
        p_env   IN VARCHAR2)
  AS
  BEGIN
    dbms_output.Put_line(' The id is '   ||p_id);
    dbms_output.Put_line(' The name is ' ||p_name);
    dbms_output.Put_line(' The env is '  ||p_env);
  END test2;
END test;
/ 

You need to help us by following the Posting Guidelines as stated below.

http://www.orafaq.com/forum/t/88153/0/

Please, please, please Read & Follow Posting Guidelines above.

Go to the section labeled "Practice" & do as directed.


>Shouldn't The new setname is A_MAT
No.
>How do I fix this...??
Modify the code
Re: Global Variable [message #400842 is a reply to message #400840] Wed, 29 April 2009 23:06 Go to previous messageGo to next message
toshidas2000
Messages: 120
Registered: November 2005
Senior Member
What modification and where??
Re: Global Variable [message #400843 is a reply to message #400840] Wed, 29 April 2009 23:08 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
> p_new_name VARCHAR2(32) := 'A_' ||p_name;
At no time is "p_name" defined/exist for line above in the procedure.
Re: Global Variable [message #400844 is a reply to message #400843] Wed, 29 April 2009 23:18 Go to previous messageGo to next message
toshidas2000
Messages: 120
Registered: November 2005
Senior Member
I am calling the procedure which has p_name as MAT...
Re: Global Variable [message #400846 is a reply to message #400840] Wed, 29 April 2009 23:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not give the same name to different variables or be sure you very well know what is the scope and precedence of each one.

It is a very bad idea to name p_... a global variable, name them g_... for instance and keep p_... for procedure parameter name.

Regards
Michel
Re: Global Variable [message #400847 is a reply to message #400840] Wed, 29 April 2009 23:22 Go to previous messageGo to next message
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
Member

METHOD :1

CREATE OR REPLACE PACKAGE BODY test_112
AS

p_name varchar2(32);
S_name varchar2(32) := 'hello';
p_new_name varchar2(32) := 'A_'||'HELLO';

PROCEDURE test11_a (p_id IN varchar2, 
p_name IN varchar2
) 
AS
begin
dbms_output.put_line(' The id is '|| p_id);
dbms_output.put_line(' The setname is '|| p_name);
dbms_output.put_line(' The new setname is '|| p_new_name);
dbms_output.put_line(S_name);
end test11_a;

PROCEDURE test11_b (p_id IN varchar2, 
p_name IN varchar2, 
p_env IN varchar2
)
AS
begin
dbms_output.put_line(' The id is '|| p_id);
dbms_output.put_line(' The name is '|| p_name);
dbms_output.put_line(' The env is '|| p_env);

end test11_b;

end test_112;



METHOD :2

CREATE OR REPLACE PACKAGE BODY test_112
AS

p_name varchar2(32);
S_name varchar2(32) := 'hello';
p_new_name varchar2(32) := 'A_'||p_name;

PROCEDURE test11_a (p_id IN varchar2, 
p_name IN varchar2
) 
AS
begin
dbms_output.put_line(' The id is '|| p_id);
dbms_output.put_line(' The setname is '|| p_name);
dbms_output.put_line(' The new setname is '|| 'A_'||p_name);
dbms_output.put_line(S_name);
end test11_a;

PROCEDURE test11_b (p_id IN varchar2, 
p_name IN varchar2, 
p_env IN varchar2
)
AS
begin
dbms_output.put_line(' The id is '|| p_id);
dbms_output.put_line(' The name is '|| p_name);
dbms_output.put_line(' The env is '|| p_env);

end test11_b;

end test_112;




The p_new_name in the declartion part doesnt have the value for p_name .try the above ones
Re: Global Variable [message #400848 is a reply to message #400844] Wed, 29 April 2009 23:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
toshidas2000 wrote on Thu, 30 April 2009 06:18
I am calling the procedure which has p_name as MAT...

Both p_name are different variables not same ones.
The global one is never set. More even if you set it, p_new_name will not reflect this change as it is only set at compile (or load, don't know) time.

Regards
Michel
Re: Global Variable [message #400875 is a reply to message #400848] Thu, 30 April 2009 03:46 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The package body global would be set for your session the first time you make a call to the package - so at Load time.
Previous Topic: Passing partition name using a variable in insert statement.
Next Topic: enforcing rules
Goto Forum:
  


Current Time: Sat Dec 03 12:12:47 CST 2016

Total time taken to generate the page: 0.20905 seconds