Home » SQL & PL/SQL » SQL & PL/SQL » ref cursor
ref cursor [message #218460] Thu, 08 February 2007 05:52 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hello all,

please look after my pl/sql block.
i m getting an error, which i unable to solve.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2   type rc is ref cursor;
  3   inst_rc rc;
  4   v_emp emp%rowtype;
  5   v_dno varchar2(10);
  6   v_job varchar2(10);
  7   v_sql varchar2(2000);
  8  begin
  9   v_dno:='&deptno';
 10   v_job:='&job';
 11   dbms_output.put_line('dept no= '||v_dno||' job= '||v_job);
 12   v_sql:='select * from emp where';
 13   if
 14   v_dno is not null and
 15   v_job is not null then
 16   v_sql:=v_sql||' dept no= '||v_dno||' job no= '||''''||v_job||'''';
 17   elsif
 18   v_dno is not null then
 19   v_sql:=v_sql||' dept no= '||v_dno;
 20  elsif
 21   v_job is not null then
 22   v_sql:=v_sql||' job no= '||''''||v_job||'''';
 23   elsif
 24   v_dno is null and v_job is null then
 25   v_sql:=v_sql||'1=1';
 26   end if;
 27   dbms_output.put_line(v_sql);
 28   open inst_rc for v_sql;
 29   loop
 30   fetch inst_rc into v_emp;
 31   exit when inst_rc%notfound;
 32   dbms_output.put_line('emp no= '||v_emp.empno||' ' ||'ename= '||
 33           v_emp.ename||' job ='||v_emp.job||' dept no= '||v_emp.deptno);
 34  end loop;
 35* end;
SQL> /
Enter value for deptno: 
old   9:  v_dno:='&deptno';
new   9:  v_dno:='';
Enter value for job: 
old  10:  v_job:='&job';
new  10:  v_job:='';
dept no=  job=
select * from emp where1=1
declare
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at line 28



help me to overcome from this problem.

bye
Re: ref cursor [message #218464 is a reply to message #218460] Thu, 08 February 2007 06:01 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
select * from emp where1=1

Do you see a blank space after "where"?
v_sql:='select * from emp where ';
By
Vamsi
Re: ref cursor [message #218469 is a reply to message #218460] Thu, 08 February 2007 06:10 Go to previous messageGo to next message
ashuj20
Messages: 12
Registered: October 2005
Location: new delhi
Junior Member
Hi,


Use had not created SQL string (v_Sql)correctly. Also the column names in emp tables are ("deptno" and "job") not ("dept no" and "job no")

I had corrected it. Try now

declare
type rc is ref cursor;
inst_rc rc;
v_emp emp%rowtype;
v_dno varchar2(10);
v_job varchar2(10);
v_sql varchar2(2000);

begin
v_dno:='&deptno';
v_job:='&job';

dbms_output.put_line('deptno= '||v_dno||' job= '||v_job);

v_sql:='select * from emp where';
if v_dno is not null and v_job is not null then
v_sql:=v_sql||' deptno= '||v_dno||' and job= '||''''||v_job||'''';
elsif v_dno is not null then
v_sql:=v_sql||' dept= '||v_dno;
elsif v_job is not null then
v_sql:=v_sql||' job= '||''''||v_job||'''';
elsif v_dno is null and v_job is null then
v_sql:=v_sql||'1=1';
end if;

dbms_output.put_line(v_sql);

open inst_rc for v_sql;
loop
fetch inst_rc into v_emp;
exit when inst_rc%notfound;
dbms_output.put_line('emp no= '||v_emp.empno||' ' ||'ename= '||v_emp.ename||' job ='||v_emp.job||' dept no= '||v_emp.deptno);
end loop;

end;
/
Re: ref cursor [message #218478 is a reply to message #218469] Thu, 08 February 2007 06:24 Go to previous message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
thanx vamsi

space between where and ' works.

thanx to all

regards

ishika
Previous Topic: Error while accessing files from PLSQL
Next Topic: Query to build cursor in particular order
Goto Forum:
  


Current Time: Sat Dec 10 04:59:43 CST 2016

Total time taken to generate the page: 0.09166 seconds