Click here to Skip to main content
13,090,299 members (89,988 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


2 bookmarked
Posted 5 Feb 2011

Dynamic Where Clause in Oracle 11g stored procedures

, 6 Feb 2011
Rate this:
Please Sign up or sign in to vote.
we are going to see how we can make a nice code to make search inside an oracle spc
It's been a long time since I'm using stored procedures in both Oracle and SQL server to do my programmings and this is what all other programmers are doing too. But when we move everything in spc, then sometimes we need to do search inside our spc too and return the result. So I was asking my own how I can generate an spc in Oracle that recieves parameters from caller and does a dynamic search based on what is passed to.
Certainly, I need to have a flexible dynamic WHERE at the end of my spc that has to be generated based on input parameters.
The following example shows how easily we are able to do this.

Please pay attention:
● Oracle is not case sensitive and this type of coding is just for reading it better.
● In this example, we have two parameters, a numeric and a string. If Numeric parameter be >0 then it will be used inside the where clause and also if the string one has a length greater than zero then it will be used inside where clause too
● Result will be returned as sys_refcursor

Using the code
This is very simple code and I think it is not needed to be described.

create or replace PROCEDURE   SP_MyProc
( Para_01_IN IN  NUMBER,     Para_02_IN In Nvarchar2 ,RESULT_OUT OUT sys_refcursor  )
 SelectClause       clob; --this will save the dynamic sql statement of search
  whereClause       clob;
    SelectClause:=' select * from MyTable';
    whereClause := ' where {FixedConditionsHere} '
--Generating dynamic conditions
  if Para_01_IN != 0 then
          whereClause:=whereClause || ' and Para_01=   '|| to_char(Para_01_IN);
      end if;
      if LENGTH(Para_02 ) > 0  then
          whereClause:=whereClause||' and Para_02  ='||Para_02_IN ;
      end if;
-- This will open the cursor dynamically based on generated SQL commands 
       open RESULT_OUT for   SelectClause||whereClause ;--using whereClause;
 RAISE_APPLICATION_ERROR (-20001,'Error code ' || SQLCODE || ': ' || SUBSTR(SQLERRM, 1 , 200));

Give me your ideas about this topic here or in my website:


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Kaveh Yazdi Nezhad
Software Developer
Canada Canada
I'm a computer programmer Have worked within the information technology companies for over 10 years.

Object oriented programming, N-Tire architecture, SQL and Oracle stored procedures, HR, Accounting, Banking, Smart card programming, optimizing SQL queries and database structure and also training end-users are some of my experiences .

You may also be interested in...

Comments and Discussions

QuestionSQL injection Pin
Member 812894826-Apr-17 6:53
memberMember 812894826-Apr-17 6:53 
QuestionHelpful Solution Pin
Joe Gakenheimer30-May-14 1:29
memberJoe Gakenheimer30-May-14 1:29 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170813.1 | Last Updated 7 Feb 2011
Article Copyright 2011 by Kaveh Yazdi Nezhad
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid