Oracle/plsql

Advertisement


Question
Hello , i have written a plsql block that uses reference cursors.I am getting this error that says
"
Error report -
ORA-00933: SQL command not properly ended
ORA-06512: at line 11
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:
"


the code is attached below:

DECLARE
 TYPE emp_refcursor IS REF CURSOR;
 emp_cv emp_refcursor;
 emprec employees%ROWTYPE;
 v_deptno number(2) := 90;
 stmt varchar2(200) := 'select * from employees';
BEGIN
 if v_deptno is null then open emp_cv for stmt;
   else
     stmt := stmt || 'where DEPARTMENT_ID = :v_deptno';
     OPEN emp_cv FOR stmt USING v_deptno; /--error points to this line --/
   end if;  
   loop
     fetch emp_cv into emprec;
     exit when emp_cv%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE(emprec.DEPARTMENT_ID || '---->>' || emprec.FIRST_NAME);
   end loop;
   CLOSE emp_cv;
end;
/

hope i was clear, waiting for your response.

Answer
Hello Prakash Kumar,

Try one of the following -

stmt varchar2(200) := 'select * from employees ';  ---- space after table name

OR

stmt := stmt || ' where DEPARTMENT_ID = :v_deptno'; ---- space before where condition

If it still does not work, just display the value of stmt using dbms_output just before the open statement and send me so that I can research further.

Hope this helps.

Regards

Suchitra

Oracle

All Answers


Answers by Expert:


Ask Experts

Volunteer


Suchitra Joshi

Expertise

I can answer questions regarding SQL, PL/SQL, Procedures, Functions, Triggers, SQL Loader, Oracle Forms, Oracle Reports, and some basic dba and performance tuning activities.

Experience

15+ years of Oracle PL/SQL Development

Education/Credentials
B.Sc (Electronics), Diploma in Computer Applications (DCA)

Awards and Honors
Oracle PL/SQL Developer Certified Associate (OCA)
Oracle Database: SQL Certified Expert
Brainbench certifications in Oracle Administration, PL/SQL, Developer 2000

©2016 About.com. All rights reserved.