CODEWITHSUNDEEP
oracle-database
Ashok kumar
Ashok kumar

Reputation: 1621

Execute Queries in Oracle with Dynamic input in where clause

I have DEPT(DEPTNO, DNAME, LOC) table with some data.

I would like to query for all departments with search key as DNAME.

I am trying something like below, but not working. Can anyone please suggest me. I am pretty new to oracle.

dynamicDeptName varchar2(30):= 'cco';
execute immediate 'select * from dept where dname like '%' || dynamicDeptName || '%'

Upvotes: 0

Views: 83

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132750

  1. Use bind variables, it is easier as well as better (more efficient and secure)

  2. Single quotes need to be doubled-up inside a single-quoted string.

Example:

 execute immediate
   'select * from dept where dname like ''%'' || :dname || ''%'''
   into dept_rec
   using dynamicDeptName;

Or use q-quote syntax to avoid need to double-up the quotes:

execute immediate 
  q'[select * from dept where dname like '%' || :dname || '%']'
   into dept_rec
  using dynamicDeptName;

Upvotes: 2

Related Questions