Home » SQL & PL/SQL » Client Tools » i cudnt use bind variables in toad ..pls find me a solution for this. .
i cudnt use bind variables in toad ..pls find me a solution for this. . [message #322764] Mon, 26 May 2008 03:21 Go to next message
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
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 Go to previous messageGo to next message
pointers
Messages: 410
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.
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 Go to previous messageGo to next message
Littlefoot
Messages: 20900
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?
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 Go to previous messageGo to next message
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 ??
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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.
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 Go to previous messageGo to next message
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. .. ???
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 Go to previous message
flyboy
Messages: 1832
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.
Previous Topic: toad query
Next Topic: Regional settings (windows) damage sqldeveloper
Goto Forum:
  


Current Time: Wed Dec 07 16:42:06 CST 2016

Total time taken to generate the page: 0.12634 seconds