Home » SQL & PL/SQL » SQL & PL/SQL » A cursor problem
A cursor problem [message #328934] Mon, 23 June 2008 08:07 Go to next message
krithi86
Messages: 19
Registered: June 2008
Location: India
Junior Member
my employee table is described as follows

EMPNO NUMBER(4,0)
ENAME VARCHAR2(10 BYTE)
JOB VARCHAR2(9 BYTE)
MGR NUMBER(4,0)
HIREDATE DATE
SAL NUMBER(7,2)
DEPTNO NUMBER(2,0)

i wrote a cursor as follows, which is showing error


declare
cursor cur1(job_in varchar2(9))
is
select empno,ename,job from employee
where job=upper(job_in);
cur_rec cur1%rowtype;
begin
open cur1(:employee.job_in)
fetch cur1 into cur_rec;
dbms_output.put_line(cur_rec);
end;


pl tel what is wrong in tis anonymous block??

[RENAMED by LF: the original title was "pbm wit cursor". krithi86, please, use English language when communicating on the OraFAQ Forum]

[Updated on: Mon, 23 June 2008 14:56] by Moderator

Report message to a moderator

Re: pbm wit cursor [message #328935 is a reply to message #328934] Mon, 23 June 2008 08:08 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above
Re: pbm wit cursor [message #328936 is a reply to message #328934] Mon, 23 June 2008 08:10 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
krithi86 wrote on Mon, 23 June 2008 09:07

pl tel what is wrong in tis anonymous block??


I do not understand this language? What is it, baby talk?
Re: pbm wit cursor [message #328937 is a reply to message #328934] Mon, 23 June 2008 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Quote:
pl tel what is wrong in tis anonymous block??

pl -> please
tel -> tell
tis -> this
?? -> ?
If you don't make the effort to correctly write english, how could you expect to correctly write PL/SQL?

Regards
Michel

[Updated on: Mon, 23 June 2008 08:11]

Report message to a moderator

Re: pbm wit cursor [message #328942 is a reply to message #328934] Mon, 23 June 2008 08:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quite a bit.

Nice of you to tell us the error that you're getting.

On visual inspection I@ve spotted an extraneous "(9)" in the cursor definition, a missing ";" on the OPEN statement, and a very suspect looking bind variable construction, also in the OPEN line
Re: pbm wit cursor [message #328945 is a reply to message #328942] Mon, 23 June 2008 08:23 Go to previous messageGo to next message
krithi86
Messages: 19
Registered: June 2008
Location: India
Junior Member
JRowBottom

thanks a lot,

am getting the following error

ORA-01008: not all variables bound

please help me to rectify it.
Re: pbm wit cursor [message #328947 is a reply to message #328934] Mon, 23 June 2008 08:27 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
We don't know how you are invoking it.
Re: pbm wit cursor [message #328948 is a reply to message #328945] Mon, 23 June 2008 08:35 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That error is almost certainly raised by your attempt to use a bind variable called ":employee.job_in"

Unless the client software that you're calling this from supports bind records, and substitutes the values into the SQL before passing it to the server, then this construct is unsupported.

Try using
open cur1(:job_in);


Previous Topic: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Next Topic: Executing procedure from SQL server via linked server
Goto Forum:
  


Current Time: Mon Dec 05 11:17:33 CST 2016

Total time taken to generate the page: 0.09773 seconds