Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00103 error while running package
PLS-00103 error while running package [message #297571] Fri, 01 February 2008 07:05 Go to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
my table and data

create table emp(eno number,ename varchar2(10),dno number,sal number(9,2));

insert into emp values(1,'a',10,500);
/
insert into emp values(2,'b',20,700);
/
insert into emp values(3,'c',20,800);
/
insert into emp values(4,'d',20,900);
/

commit;



i have 2 packages


create or replace package type_def is
TYPE t_RefCurTyp IS REF CURSOR RETURN Emp%rowtype;
end type_def;
/




create or replace procedure 

Get_UserList( 

n_eno in emp.eno%type,
n_ename in emp.ename%type,
n_sal in emp.sal%type,
n_dno in emp.deptno%type,
o_RowCount OUT NUMBER,
o_RefCurUserList 
OUT Type_Def.t_RefCurTyp ) 

is

l_Prg_Name VARCHAR2(30) := 'Get_UserList'; 


l_SqlStmt  VARCHAR2(32767); 
l_Whr_Clause VARCHAR2(1000); 


BEGIN
 


-- Make sure essential parameters are passed 

IF n_eno IS NULL OR n_ename IS NULL THEN 

RAISE_APPLICATION_ERROR(-20001, ' n_eno or n_ename is NULL in ' ||l_Prg_Name); 

END IF; 

-- 
l_Whr_Clause := ' where eno ='||n_eno||' and ename='||n_ename||' ; 



IF n_sal IS NOT NULL THEN 
l_Whr_Clause := l_Whr_Clause||' AND SAL =n_sal; 
END IF; 


IF n_dno IS NOT NULL THEN 
l_Whr_Clause := l_Whr_Clause||' AND DNO = n_dno; 
END IF; 


l_SqlStmt :='SELECT count(1) FROM Emp '; 


l_SqlStmt :=l_SqlStmt||l_Whr_Clause; 



EXECUTE IMMEDIATE l_SqlStmt INTO o_RowCount ; 


l_SqlStmt := 'SELECT *  FROM Emp' ; 
l_SqlStmt :=l_SqlStmt||l_Whr_Clause; 



open o_RefCurUserList for l_SqlStmt; 


EXCEPTION


WHEN OTHERS THEN 

RAISE_APPLICATION_ERROR(-20000, 'DB Error: ' ||SQLERRM||' IN '||l_Prg_Name); 


END;

/




when i run this package, i get the error


LINE/COL ERROR
-------- -----------------------------------------------------------------
41/5     PLS-00103: Encountered the symbol "IF" when expecting one of the
         following:
         ; <an identifier> <a double-quoted delimited-identifier>
         delete exists prior <a single-quoted SQL string>




also, is my refcursor definition the correct one? i think i
declared it correctly !

but what if tomorrow, i want to write a package, where in i want
to declare a generalized refcursor for all my tables?

how to declare the refcursor in the package then?

Re: PLS-00103 error while running package [message #297573 is a reply to message #297571] Fri, 01 February 2008 07:10 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
l_Whr_Clause := ' where eno ='||n_eno||' and ename='||n_ename||' ; 
...
l_Whr_Clause := l_Whr_Clause||' AND SAL =n_sal;
...
l_Whr_Clause := l_Whr_Clause||' AND DNO = n_dno;

These are the problematic statements.
Quote:
but what if tomorrow, i want to write a package, where in i want
to declare a generalized refcursor for all my tables?


To be honest don't try to develop something generic. Also declaring a variable in package is not a good practice. I could be wrong but that's the way I was taught. It is something like declaring a global variable. But if you need to what you want is a weak type ref cursor which is sys_refcursor. Read the Pl/Sql Reference manual for more details.

Regards

Raj

[Updated on: Fri, 01 February 2008 07:13]

Report message to a moderator

Previous Topic: View Error
Next Topic: first five rows and last five rows
Goto Forum:
  


Current Time: Thu Dec 08 10:40:53 CST 2016

Total time taken to generate the page: 0.23205 seconds