wood burning stoves 2.0*
The moose likes Oracle/OAS and the fly likes getting this error  PLS-00103: please help Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "getting this error  PLS-00103: please help" Watch "getting this error  PLS-00103: please help" New topic
Author

getting this error PLS-00103: please help

Rajendra Gautam
Greenhorn

Joined: Oct 22, 2005
Posts: 3
hello friends
i m trying to execute following code in "sqlplus" to make a function.

create or replace function GETADVANCESEARCHRESULT
(
keyword in varchar, pkgtype in varchar, catname in varchar, datefrom in date, todate in date
)
return packagesinfo.packagechildinfo_refcur
as
advancesearchresult packagesinfo.packagechildinfo_refcur;
basequery varchar(100);
condition1 varchar(100);
condition2 varchar(100);
condition3 varchar(100);
condition4 varchar(100);
condition5 varchar(100);
condition6 varchar(100);
begin
basequery := 'select
distinct(tblpackage.packageid), tblpackage.packagename
from
tblpackage, tblpackageattributes,
tblpackagetype, tblcategory, tblpackagecategory
where ';
condition1 := '( tblpackage.packagename like keyword or
(
tblpackageattributes.attributename in
('+ 'Description'+','+'System Requirements'+') and
tblpackageattributes.attributevalue like keyword
)
and
tblpackage.packageid = tblpackageattributes.packageid ';
condition2 := '( tblcategory.categoryname like catname or
tblcategory.categorydescription like catname ) and
tblpackagecategory.categoryid = tblcategory.categoryid and
tblpackagecategory.packageid = tblpackage.packageid ';
condition3 := '( tblpackagetype.packagetype like pkgtype or
tblpackagetype.packagetypedesc like pkgtype) and
tblpackagetype.packagetypeid = tblpackage.packageid ';
condition4 := ' tblpackageattributes.createdon
date between datefrom to todate ';
condition5 := ' tblpackageattributes.createdon >= datefrom ';
condition6 := ' tblpackageattributes.createdon <= todate ';
open advancesearchresult for
( if( pkgtype != null ) then
basequery := basequery + '(' + condition3 + ')';
end if;
if( catname != null) then
if( pkgtype != null ) then
basequery := basequery + 'or' + '(' + condition2 + ')';
else
basequery := basequery + '(' + condition2 + ')';
end if;
end if;
if ( datefrom != null ) then
if ( todate != null ) then
if ( pkgtype != null or catname != null ) then
basequery := basequery + 'and' + '(' + condition4 + ')';
end if;
else
basequery := basequery + 'and' + '(' + condition6 + ')';
end if;
else
basequery := basequery + 'and' + '(' + condition5 + ')';
end if;
end if;
if ( keyword != null ) then
if( datefrom != null) then
if( todate != null ) then
basequery := basequery + '(' + condition1 + ')' +
'or' + '(' + condition2 + ')' + 'or'
+ '(' + condition3 + ')' + 'and' + condition4 + ')';
else
basequery := basequery + '(' + condition1 + ')'
+ 'or' + '(' + condition2 + ')' + 'or'
+ '(' + condition3 + ')' + 'and' + condition5 + ')';
end if;
elseif (datefrom = null and todate != null )
basequery := basequery + '(' + condition1 + ')'
+ 'or' + '(' + condition2 + ')' + 'or'
+ '(' + condition3 + ')' + 'and' + condition6 + ')';
end if;
end if;
end if;

but this is giving following error. i know there is some syntax error but i m unable to understand the error please someone help me. its urgent... thanks in advance

Errors for FUNCTION GETADVANCESEARCHRESULT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
42/5 PLS-00103: Encountered the symbol "IF" when expecting one of the
following:
( - + case mod new not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe

63/5 PLS-00103: Encountered the symbol "IF" when expecting one of the
following:

LINE/COL ERROR
-------- -----------------------------------------------------------------
; <an identifier> <a double-quoted delimited-identifier>
delete exists prior <a single-quoted SQL string>


Rajendra
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30130
    
150

Rajendra,
Below is the beginning of your code with just the if statements. The first two sections are fine. The third has one more end if than if.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Rajendra Gautam
Greenhorn

Joined: Oct 22, 2005
Posts: 3
Thank You Jeanne

I have got the solution.
i have created the function but not getting proper results.
it is now returning null pointer exception( i am developing the search functionality using JSP)
Thank Again.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30130
    
150

Rajendra,
This sounds like a new question.

Please post the JSP throwing the null pointer in the JSP forum. If you can figure out what line is causing the null pointer, post that too.

Thanks,
Jeanne
Forum Bartender
 
Don't get me started about those stupid light bulbs.
 
subject: getting this error PLS-00103: please help
 
Similar Threads
Nested if()
Execute Multiple SQL Queries in PL/SQL Procedure based on parameter
mapping.findForward doubt
How to get all request stored in databse between two date
writing If blocks