OraFAQ Forum: Client Tools » i cudnt use bind variables in toad ..pls find me a solution for this. .

Home » Other » Client Tools » i cudnt use bind variables in toad ..pls find me a solution for this. .

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

i cudnt use bind variables in toad ..pls find me a solution for this. . [message #322764]

Mon, 26 May 2008 03:21

seyed456
Messages: 220
Registered: May 2008
Location: south india .

Senior Member

i cudnt use bind variables in toad ..pls find me a solution for this. .

the error occured was invalid sql statement...
i tried to create like
VARIABLE sender number

thanks and regards
seyed

Report message to a moderator

Re: i cudnt use bind variables in toad ..pls find me a solution for this. . [message #322772 is a reply to message #322764]

Mon, 26 May 2008 03:44

pointers
Messages: 451
Registered: May 2008

Senior Member

Hi sayed,

If i am correct, 'variable' is a sql * plus commnad and its not a plsql commnad. so you cant use it in a plsql block.
plsql uses bind variable concept automatically when you use variables.
But to use in 'execute immediate' we go for bind variables concept, as the value is passed during the run time.
try the below

create or replace procedure mine_1(a in number)
is
begin
execute immediate 'update mine_emp set sal=sal*2 where emp_id=:b' using a;
end;

Here 'b' is a bind variable and no need to define it.

Regards,
Pointers.

Report message to a moderator

Re: i cudnt use bind variables in toad ..pls find me a solution for this. . [message #322775 is a reply to message #322764]

Mon, 26 May 2008 03:45

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

"VARIABLE" is SQL*Plus command; TOAD doesn't know it.

What are you, actually, trying to do? What would you do with that variable? How / where do you plan to use it?

Report message to a moderator

Re: i cudnt use bind variables in toad ..pls find me a solution for this. . [message #322778 is a reply to message #322764]

Mon, 26 May 2008 03:54

seyed456
Messages: 220
Registered: May 2008
Location: south india .

Senior Member

thanks for your replies./ ...

CREATE OR REPLACE Procedure get_custid
(s_name IN varchar2,s_address IN varchar2,s_city IN varchar2,s_state IN varchar2,
s_phone IN varchar2,s_mobile IN number,s_email IN varchar2,s_custid OUT number,
r_name IN varchar2,r_address IN varchar2,r_city IN varchar2,r_state IN varchar2,
r_phone IN varchar2,r_mobile IN number,r_email IN varchar2,r_custid OUT number)
IS

v_s_cityid number;
v_r_cityid number;
cursor GET_S_CITYID is
select CITY_ID
from L_CITY
where CITY_NAME=s_city ;
cursor GET_R_CITYID is
select CITY_ID
from L_CITY
where CITY_NAME=r_city;
begin

open GET_S_CITYID;
fetch GET_S_CITYID into v_s_cityid;

if GET_S_CITYID%notfound then
insert into L_CITY(CITY_ID,CITY_NAME)
values (l_city_id.nextval,s_city);

end if;

insert into L_CUSTOMER( CUST_ID,CUST_NAME,ADDRESS,PHONE_NUMBER,MOBILE_NUMBER,E_MAIL,RECIEVER_SENDER,CITY_ID)
values (l_cust_id.nextval,s_name,s_address,s_phone,s_mobile,s_email,'s',v_s_cityid);

close GET_S_CITYID;

SELECT CUST_ID
into s_custid
FROM (select * from L_CUSTOMER ORDER BY CUST_ID DESC)
WHERE rownum <2;

open GET_R_CITYID;
fetch GET_R_CITYID into v_r_cityid;

if GET_R_CITYID%notfound then
insert into L_CITY(CITY_ID,CITY_NAME)
values (l_city_id.nextval,r_city);
end if;

insert into L_CUSTOMER(CUST_ID,CUST_NAME,ADDRESS,PHONE_NUMBER,MOBILE_NUMBER,E_MAIL,RECIEVER_SENDER,CITY_ID)
values (l_cust_id.nextval,r_name,r_address,r_phone,r_mobile,r_email,'r',v_r_cityid);

close GET_R_CITYID;

SELECT CUST_ID
into r_custid
FROM (select * from L_CUSTOMER ORDER BY CUST_ID DESC)
WHERE rownum <2;

commit;
end;

how can i return the value of s_custid and r_custid...
i have to get output. ..
is my procedure right ??

Report message to a moderator

Re: i cudnt use bind variables in toad ..pls find me a solution for this. . [message #322804 is a reply to message #322764]

Mon, 26 May 2008 05:12

flyboy
Messages: 1903
Registered: November 2006

Senior Member

> how can i return the value of s_custid and r_custid...
You already "return" them as OUT parameters.

> i have to get output. ..
So call the procedure in anonymous block and write the obtained values.

> is my procedure right ??
Only you know, what it shall do; but as you did not format it as suggested in OraFAQ Forum Guide, it is not very readable; so I doubt anybody will try to decipher it.

Report message to a moderator

Re: i cudnt use bind variables in toad ..pls find me a solution for this. . [message #322820 is a reply to message #322804]

Mon, 26 May 2008 05:43

seyed456
Messages: 220
Registered: May 2008
Location: south india .

Senior Member

thankls for u reply.. .

but how do i return ..can u write the anonymous block to return values for both s_custid and r_custid. .. ???

Report message to a moderator

Re: i cudnt use bind variables in toad ..pls find me a solution for this. . [message #322848 is a reply to message #322764]

Mon, 26 May 2008 07:13

flyboy
Messages: 1903
Registered: November 2006

Senior Member

> but how do i return ..
return where? the procedure already returns it, so you just specify a variable to which it shall be assigned (normal PL/SQL variable, not the bind SQL*Plus one). then, it shall be accessible in that anonymous block.

Or are you asking how to create an anonymous block and call there a procedure? Then, open PL/SQL User's Guide and Reference, found eg. online on http://tahiti.oracle.com/ and search for it.

Report message to a moderator

Previous Topic:	toad query
Next Topic:	Regional settings (windows) damage sqldeveloper

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 17:29:14 CDT 2024