• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

getting this error PLS-00103: please help

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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>
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Rajendra Gautam
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
reply
    Bookmark Topic Watch Topic
  • New Topic