Home » SQL & PL/SQL » SQL & PL/SQL » Not All Variable Bound (merged 3)
Not All Variable Bound (merged 3) [message #403667] Mon, 18 May 2009 00:18 Go to next message
sajidrazmi
Messages: 47
Registered: August 2008
Location: oman
Member
Hi Dear All,
I am getting one error when i am going to execute this Procedure,

Declare
 Cursor C1 Is
  Select Emp_Comp_Code,Emp_Code,Emp_Name,Emp_Join_Dt
  From Asg_Sm_Emp_Key
  Where Emp_Comp_Code ='001'
  And Nvl(Emp_End_Of_service_Yn,'N') ='N'
  Order By To_Number(Emp_Code);

 Cursor C2 (Emp_Join_Dt In Date) Is
  Select Years,Trunc(Mb-Years*12)Months,Nd-Add_Months(Od,Trunc(MB))Days
	From
    (Select Od,Nd,Months_Between(Nd,Od) Mb,Trunc(Months_Between(Nd,Od)/12) Years
    (select Sysdate Nd,Sysdate-fd Od From
	(Select (Trunc(sysdate) - (:Emp_Join_Dt)) Fd From Dual)));
C2_Row C2%Rowtype;


Begin
 For I In C1 Loop
  IF C2%Isopen Then Close C2; End If;
    Open C2(I.Emp_Join_Dt);
      Loop
       Fetch C2 Into C2_Row;
	   Exit When C2%Notfound;
  	    Insert Into Temp_Test (Comp_Code,Emp_Code,Emp_Name,Dt_Date,Nu_Year,Nu_Mon
        Values (I.Emp_Comp_Code,I.Emp_Code,I.Emp_Name,Sysdate,C2_Row.Years,C2_Ro
	  End Loop;
    Close C2;
 End Loop;
End;
ORA-01008: not all variables bound
Re: Not All Variable Bound [message #403669 is a reply to message #403667] Mon, 18 May 2009 00:25 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>ORA-01008: not all variables bound
What did you do wrong?
Re: Not All Variable Bound [message #403671 is a reply to message #403667] Mon, 18 May 2009 00:29 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

so fix your error!
Re: Not All Variable Bound [message #403673 is a reply to message #403669] Mon, 18 May 2009 00:31 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
In you cursor c2 definition, this line

(Select (Trunc(sysdate) - (:Emp_Join_Dt)) Fd From Dual)));


should be written as

(Select (Trunc(sysdate) - (Emp_Join_Dt)) Fd From Dual)));

[Updated on: Mon, 18 May 2009 00:32]

Report message to a moderator

Re: Not All Variable Bound [message #403676 is a reply to message #403667] Mon, 18 May 2009 00:41 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
redundancy is the best way to teach.
Re: Not All Variable Bound [message #403677 is a reply to message #403673] Mon, 18 May 2009 00:53 Go to previous messageGo to next message
sajidrazmi
Messages: 47
Registered: August 2008
Location: oman
Member
Thanks For Reply
But if u wrote this code it give you error
Invalid Identifier;

I have Written This Because i need employee join date thats why i take it in loop
Re: Not All Variable Bound [message #403685 is a reply to message #403677] Mon, 18 May 2009 01:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why do you use all these date functions in a cursor? Simple assignment should work
Re: Not All Variable Bound [message #403686 is a reply to message #403685] Mon, 18 May 2009 01:31 Go to previous messageGo to next message
sajidrazmi
Messages: 47
Registered: August 2008
Location: oman
Member
Because I need how many days employee has worked in a company.

[Updated on: Mon, 18 May 2009 01:32]

Report message to a moderator

Re: Not All Variable Bound [message #403687 is a reply to message #403686] Mon, 18 May 2009 01:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
That is not what I asked. I asked why you chose to get them through a cursor, where you could just as well do it using simple assignment. It would make it more readable as well.

Now, about your error: You cannot refer to Od in the part where you calculate Years; the two are siblings and do not see each other
Re: Not All Variable Bound [message #403693 is a reply to message #403677] Mon, 18 May 2009 02:00 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@sajidrazmi,

sajidrazmi wrote on Mon, 18 May 2009 11:23
Thanks For Reply
But if u wrote this code it give you error
Invalid Identifier;



I didn't quiet understand why @bonker's solution didn't work for you. That sounded the possible cause.
ORA-01008 not all variables bound


Cause: A SQL statement containing substitution variables was executed without all variables bound. All substitution variables must have a substituted value before the SQL statement is executed. 

Action: In OCI, use an OBIND or OBINDN call to substitute the required values. 


If you could have read the Posting Guidelines @Swan had asked and posted a Test Case then your problem could have been much more easier to understand.

Regards,
Jo
Re: Not All Variable Bound [message #403702 is a reply to message #403687] Mon, 18 May 2009 03:03 Go to previous message
sajidrazmi
Messages: 47
Registered: August 2008
Location: oman
Member
Thanks Allot Dear,
Now Without Cursor Its Working Fine
Previous Topic: oracle_xml
Next Topic: How to make a view with rows as columns
Goto Forum:
  


Current Time: Fri Dec 09 04:23:13 CST 2016

Total time taken to generate the page: 0.08281 seconds