Query Parameters To Construct Where Condition

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Query Parameters To Construct Where Condition

Postby jakoboski » Sun Nov 03, 2013 6:12 pm

I have called TEST_TABLE and I use this to do Oracle text search

I could do text search query as

Code: Select all
select * from TEST_TABLE
WHERE contains (XMLFIELD,'william
INPATH (/EMPLOYEES/SUPERVISOR)') > 0


I am using the above query in a function and parameter is passed to function so that query will be dynamic to search multiple columns with boolean operators.

From my front end application I am getting parameters based on user selection.

Case 1 User selection and parameters I receive

Code: Select all
qryStr[0][Field]:DEPT_COMMENTS
qryStr[1][srchString]:cpa
qryStr[2][filter]:or
qryStr[3][Field]:PEER_COMMENTS
qryStr[4][srchString]:good


Query would be

Code: Select all
select * from TEST_TABLE
WHERE contains (XMLFIELD,'cpa
INPATH (/EMPLOYEES/DEPT_COMMENTS) or  good INPATH(/EMPLOYEES/PEER_COMMENTS)') > 0


Case 2 User selection and parameters I receive

Code: Select all
qryStr[0][Field]:HR_COMMENTS
qryStr[1][srchString]:good
qryStr[2][filter]:or
qryStr[3][srchString]:excellent  -- note here qryStr[3][srchString] is 4th parameter and in previous case 4th parameter was qryStr[3][Field]


Query would be

Code: Select all
select * from TEST_TABLE
WHERE contains (XMLFIELD,'good or excellent 
INPATH (/EMPLOYEES/HR_COMMENTS)') > 0



Case 3 User selection and parameters I receive

Code: Select all
qryStr[0][Field]:ACTION
qryStr[1][srchString]:promoted


Query would be

Code: Select all
select * from TEST_TABLE
WHERE contains (XMLFIELD,'promoted 
INPATH (/EMPLOYEES/ACTION)') > 0


Case 4 User selection and parameters I receive

Code: Select all
qryStr[0][Field]:DEPT_COMMENTS
qryStr[1][srchString]:cpa
qryStr[2][filter]:or
qryStr[3][Field]:PEER_COMMENTS
qryStr[4][srchString]:good
qryStr[5][filter]:or
qryStr[6][Field]:ADDITIONAL_COMMENT
qryStr[7][srchString]:good
qryStr[7][filter]:and
qryStr[9][Field]:STATUS
qryStr[10][srchString]:good
qryStr[11][filter]:and
qryStr[12][Field]:SUPERVISOR
qryStr[13][srchString]:william


Query would be

Code: Select all
select * from TEST_TABLE
WHERE contains (XMLFIELD,'cpa
INPATH (/EMPLOYEES/DEPT_COMMENTS) or  good INPATH(/EMPLOYEES/PEER_COMMENTS)  or good
INPATH(/EMPLOYEES/ADDITIONAL_COMMENT)  and  good INPATH(/EMPLOYEES/STATUS) and william   INPATH(/EMPLOYEES/SUPERVISOR)   ') > 0


Like the above there are many case scenarios with different parameter names and values. Parameter names and values differ based on the user selection of values in front end screen

Let me explain the problem I am facing.

How can I pass the parameters to my function so that my query will be dynamic to cater any kind of cases or scenarios?
Or are there any better way to handle this kind of complex query construction.

I hope I have explained to make you understand my problem, if not please let me know.
Appreciate your help to resolve my issue.

Table structure and sample data

Code: Select all
CREATE TABLE TEST_TABLE
(
  EMP_ID    VARCHAR2(12 ) ,
  XMLFIELD  XMLTYPE
);


Code: Select all
SET DEFINE OFF;
Insert into TEST_TABLE
   (EMP_ID, XMLFIELD)
 Values
   ('E45TY', XMLTYPE('<EMPLOYEES><EMP_ID>E45TY</EMP_ID><DEPT_COMMENTS>Finance employee with CPA grade</DEPT_COMMENTS><SUPERVISOR_COMMENTS>Above average</SUPERVISOR_COMMENTS><PEER_COMMENTS>Good</PEER_COMMENTS><QUALIFICATION>CPA</QUALIFICATION><HR_COMMENTS>Excellent</HR_COMMENTS><PACKAGE>20</PACKAGE><ACTION>to be promoted</ACTION><SUPERVISOR>ALFRED</SUPERVISOR><STATUS>Active</STATUS><ADDITIONAL_COMMENT>Excellent</ADDITIONAL_COMMENT></EMPLOYEES>'));
Insert into TEST_TABLE
   (EMP_ID, XMLFIELD)
 Values
   ('IT0087TY', XMLTYPE('<EMPLOYEES><EMP_ID>IT0087TY</EMP_ID><DEPT_COMMENTS>IT Employee with PG grade</DEPT_COMMENTS><SUPERVISOR_COMMENTS>Above average</SUPERVISOR_COMMENTS><PEER_COMMENTS>Good</PEER_COMMENTS><QUALIFICATION>Engineer</QUALIFICATION><HR_COMMENTS>Good</HR_COMMENTS><PACKAGE>22</PACKAGE><ACTION>NIL</ACTION><SUPERVISOR>WILLIAM</SUPERVISOR><STATUS>Active</STATUS><ADDITIONAL_COMMENT>Good</ADDITIONAL_COMMENT></EMPLOYEES>'));
Insert into TEST_TABLE
   (EMP_ID, XMLFIELD)
 Values
   ('E456900', XMLTYPE('<EMPLOYEES><EMP_ID>E456900</EMP_ID><DEPT_COMMENTS>IT Employee with Graduate grade</DEPT_COMMENTS><SUPERVISOR_COMMENTS> Average</SUPERVISOR_COMMENTS><PEER_COMMENTS>Good</PEER_COMMENTS><QUALIFICATION>Engineer</QUALIFICATION><HR_COMMENTS>Good</HR_COMMENTS><PACKAGE>21</PACKAGE><ACTION>already promoted</ACTION><SUPERVISOR>WILLIAM</SUPERVISOR><STATUS>Active</STATUS><ADDITIONAL_COMMENT>Good</ADDITIONAL_COMMENT></EMPLOYEES>'));
COMMIT;
jakoboski
Member
 
Posts: 8
Joined: Sun Nov 03, 2013 2:28 pm

Re: Query Parameters To Construct Where Condition

Postby Tim... » Sun Nov 03, 2013 11:26 pm

Hi.

PL/SQL functions can accept collections as parameters. Collections are just arrays. Code your function to accept a collection containing the parameter.

http://www.oracle-base.com/articles/8i/ ... ons-8i.php

You can then use dynamic SQL to build the correct statement to execute based on the parameters. You can return the result as a REF CURSOR, or wrap the lot up as a pipelined table function.

http://www.oracle-base.com/articles/mis ... rdsets.php
http://www.oracle-base.com/articles/8i/ ... rdsets.php
http://www.oracle-base.com/articles/mis ... ctions.php

I don't believe there is a really simple solution. You just have to build the query programatically.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17962
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 5 guests