Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic query using bind variables - search query
Dynamic query using bind variables - search query [message #281094] Thu, 15 November 2007 12:55 Go to next message
krishna_900
Messages: 11
Registered: June 2005
Junior Member

Dear Experts,

I have an issue and will be much appreciated if anybody can help me to resolve this.

I have a search screen which is calling an oracle procedure from application and returning a cursor to application. The procedure is parameterized. Something like follows



Current SQL.(execution method)
V_WHERECLAUSE1 :=‘SELECT ess.staffstatus as Registered
                   From es_staff ess
                   Where ese.electioneventid=:1’

IF length(TRIM(p_StaffID)) > 0 THEN
  V_WHERECLAUSE1 := V_WHERECLAUSE1 || ' AND ess.loginid 
  LIKE UPPER('''||p_StaffID||'%'') ';
END IF;

IF length(TRIM(p_Surname)) > 0 THEN
  V_WHERECLAUSE1 := V_WHERECLAUSE1 || ' AND 
 ese.extractedsurname LIKE UPPER('''||p_Surname||'%'') ';
END IF;         

IF length(TRIM(p_GivenNames)) > 0 THEN
  V_WHERECLAUSE1 := V_WHERECLAUSE1 || ' AND
 ese.extractedgivennames LIKE UPPER
 (''%'||p_GivenNames||'%'') ';
END IF;  

IF length(TRIM(p_DoB)) > 0 THEN
  V_WHERECLAUSE1 := V_WHERECLAUSE1 || ' AND ese.dateofbirth 
  = '''||p_DoB||'''';
END IF;                    

IF length(TRIM(p_Locality)) > 0 THEN
  V_WHERECLAUSE1 := V_WHERECLAUSE1 || ' AND 
 ese.localityname LIKE UPPER('''||p_Locality||'%'') ';
END IF;         

IF length(TRIM(p_Address)) > 0 THEN
  V_WHERECLAUSE1 := V_WHERECLAUSE1 || ' AND
 ese.postaladdress LIKE UPPER(''%'||p_Address||'%'') ';
END IF;    

IF length(TRIM(p_Postcode)) > 0 THEN
 V_WHERECLAUSE1 := V_WHERECLAUSE1 || ' AND ese.postcode 
 LIKE UPPER('''||p_Postcode||'%'') ';
END IF;   

OPEN prc_Applicants FOR V_WHERECLAUSE1 using p_ElectionEventID;

Programmer is constructing SQL statement and opening a ref cursor using the dynamic SQL. There are lots of problems here, precisely use of literals. We were thinking about to use bind variables (ex:- :1 electioneventid in above example) and pass through “using” clause to OPEN CURSOR. But our problem is the parameters passed will be dynamic (ex:- surname, givennames, DOB etc.) In this situation how can we make use of bind variables?.


Thanks in Advance
Krishna

[EDITED by LF: MODIFIED a typo in topic's title - was "serach". Added [code]tags]

[Updated on: Fri, 16 November 2007 04:14] by Moderator

Report message to a moderator

Re: Dynamic query using bind variables - serach query [message #281097 is a reply to message #281094] Thu, 15 November 2007 13:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: Dynamic query using bind variables - serach query [message #281107 is a reply to message #281094] Thu, 15 November 2007 14:17 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Try following (I just wrote it without actually testing):

CREATE OR REPLACE PROCEDURE ...
  parm_count BINARY_INTEGER := 0;
  TYPE parm_tab_type TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
  parm_tab parm_tab_type;
  stmt VARCHAR2(32000);
BEGIN
  stmt := 'SELECT ess.staffstatus as Registered ' ||  
          'From es_staff ess ' ||
          'Where ese.electioneventid = :electioneventid ';
  parm_count := 1;
  parm_tab(parm_count) := p_ElectionEventID;
  IF length(TRIM(p_StaffID)) > 0 THEN
     stmt := stmt || ' AND ess.loginid LIKE UPPER(:loginid) ';
     parm_count := parm_count + 1;
     parm_tab(parm_count) := p_StaffID;
  END IF;
  ...
  IF length(TRIM(p_Postcode)) > 0 THEN
     stmt := stmt || ' AND ess.loginid LIKE UPPER(:postcode) ';
     parm_count := parm_count + 1;
     parm_tab(parm_count) := p_Postcode;
  END IF;
  IF parm_count = 1 THEN
     OPEN prc_Applicants FOR stmt using parm_tab(1);
  ELSIF parm_count = 2 THEN
     OPEN prc_Applicants FOR stmt using parm_tab(1),parm_tab(2);
  ELSIF ...
  ELSIF parm_count = 7 THEN
     OPEN prc_Applicants FOR stmt
       using parm_tab(1),parm_tab(2),parm_tab(3),
             parm_tab(4),parm_tab(5),parm_tab(6), parm_tab(7);
  END IF;
END;


HTH.
Michael


Re: Dynamic query using bind variables - serach query [message #281142 is a reply to message #281107] Thu, 15 November 2007 18:29 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

why don't you passing column name as paramerer instead of defining various parameter and concatenate them with the main query's where clause?
Re: Dynamic query using bind variables - serach query [message #281248 is a reply to message #281107] Fri, 16 November 2007 04:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
A good alternative to use when you have an unknown and varying number of bindparameters is to use sys_context.
Re: Dynamic query using bind variables - search query [message #281503 is a reply to message #281094] Sat, 17 November 2007 14:32 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
Check this asktom thread.

Gints Plivna

[Updated on: Fri, 07 December 2007 13:41] by Moderator

Report message to a moderator

Re: Dynamic query using bind variables - search query [message #281706 is a reply to message #281094] Mon, 19 November 2007 01:19 Go to previous message
krishna_900
Messages: 11
Registered: June 2005
Junior Member

Excellent one Plivna.

This will solve our problem.

Any drawbacks if we use syscontext extensively on DB ?

Regards,
K
Previous Topic: Concenate a sql
Next Topic: Wierd output for even parameter, correct output for an odd one...
Goto Forum:
  


Current Time: Sun Dec 04 18:37:02 CST 2016

Total time taken to generate the page: 0.09679 seconds