Tools for Oracle database - Any idea about Dynamic bind arguments

Author

Any idea about Dynamic bind arguments

raghugunanv@gmail.com

2005-10-28, 7:23 am

I need a help for the below situation.

For example : I have a table called QUERIES with two COLUMNS viz.
QUERY and VALUE.

CREATE TABLE QUERIES (
QUERY VARCHAR2(2000),
VALUE VARCHAR2(500));

In this table each QUERY Column will have query with bind variable and
VALUE column will have variable names LIKE:

ROW1:
QUERY COLUMN: Select count(1) from Emp where dept_no = :1
VALUE COLUMN: deptno (runtime it will have the specific values).

ROW2:

QUERY COLUMN: Select sum(Salary) from Sal_table
where dept_no = :1 and sal_date = :2
VALUE COLUMN: deptno and saldate.

I have to fetch each column and process one by one :
LIKE
1) Execute Immediate 'Select count(1) from Emp where dept_no = :1'
using deptno;
2) Execute Immediate 'Select sum(Salary) from Sal_table where dept_no =
:1 and sal_date = :2' using deptno, saldate

and so on ... these queries will increase in future.

So i want to write dynamic code to this like
select query, value into qry, bind_val from queries

and then
EXECUTE IMMEDIATE qry USING bind_val

..... but this doesn't work ... can anyone help how write a code for
this without much difficult.

regards, ghu

Michel Cadot

2005-10-28, 11:23 am

<raghugunanv@gmail.com> a écrit dans le message de news: 1130496806.669749.56740@g44g2000cwa.googlegroups.com...
|I need a help for the below situation.
|
| For example : I have a table called QUERIES with two COLUMNS viz.
| QUERY and VALUE.
|
|
| CREATE TABLE QUERIES (
| QUERY VARCHAR2(2000),
| VALUE VARCHAR2(500));
|
| In this table each QUERY Column will have query with bind variable and
| VALUE column will have variable names LIKE:
|
| ROW1:
| QUERY COLUMN: Select count(1) from Emp where dept_no = :1
| VALUE COLUMN: deptno (runtime it will have the specific values).
|
| ROW2:
|
| QUERY COLUMN: Select sum(Salary) from Sal_table
| where dept_no = :1 and sal_date = :2
| VALUE COLUMN: deptno and saldate.
|
| I have to fetch each column and process one by one :
| LIKE
| 1) Execute Immediate 'Select count(1) from Emp where dept_no = :1'
| using deptno;
| 2) Execute Immediate 'Select sum(Salary) from Sal_table where dept_no =
| :1 and sal_date = :2' using deptno, saldate
|
| and so on ... these queries will increase in future.
|
| So i want to write dynamic code to this like
| select query, value into qry, bind_val from queries
|
| and then
| EXECUTE IMMEDIATE qry USING bind_val
|
| .... but this doesn't work ... can anyone help how write a code for
| this without much difficult.
|
| regards, ghu
|

I think you should have a look at dbms_sql package:
http://download-west.oracle.com/doc...sql.htm#1006190

Regards
Michel Cadot

Sponsored Links