Home » SQL & PL/SQL » SQL & PL/SQL » Inserting dynamic values into a dynamic query.
Inserting dynamic values into a dynamic query. [message #196499] Thu, 05 October 2006 11:32 Go to next message
sathyaSrinivasan
Messages: 2
Registered: October 2006
Junior Member
Hi, I have a situation where i want to insert a value by getting it dynamically.

Here's a snippet.

procedure xyz is

Open C1 For Select a,b from tableJ;
...
...
sqlXabc(); -- get the string from abc function.. for eg.

execute immediate sqlX;

end;


function abc return varchar2 is
another query getting some values
loop

within a loop or something
strSql := insert into tableX values(C1.'||A || ' c1.'||b); ...

end loop;
return strSql
...
...

when running this query, obviously, the execute immediate sees C1.A which is in the string query as a simple string but doesnt see it as a placeholder to replace it with the value of the cursor within which it's running.

let me know if any1 can help.

cheers

Sath
Re: Inserting dynamic values into a dynamic query. [message #196522 is a reply to message #196499] Thu, 05 October 2006 14:33 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
See if these links help:
http://asktom.oracle.com/pls/ask/f?p=4950:61:::::P61_ID:6711304251199
http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/x/Table_Functions_Cursor_Expressions/start.htm
Re: Inserting dynamic values into a dynamic query. [message #196704 is a reply to message #196522] Fri, 06 October 2006 10:51 Go to previous messageGo to next message
sathyaSrinivasan
Messages: 2
Registered: October 2006
Junior Member
The topics you mentioned did not solve my problem.

I have attempted to explain the problem once again below. What i need is a way to inject the cursor value into the string dynamically as it's not possible for me to know the cursor value or the no of columns before hand because both are generated dynamically.


eg.,

procedure ProcessNETADS as
...

Select * from subs where typeid='ABC' into eRow;

--loop for all columns in the table using user_tab_columsn table values

longSql:= 'Update tableX set ';
for (all columns in the table except the p.k in cursor CX1)
loop
longSQL := longSql || CX1 || ' = ' || 'eRow.'||colName ...
--as you see, the values will keep on adding up here
end loop;

-- finally
longSQL := longSQL || ' Where pkCol ='|| pkValue;

--now longSQL cotains something like this (as a string)
/*
Update tableX set
jan82 = erow.jan82, feb82 = erow.feb82, ... ... ;
*/

now the million$ Q is, how do i execute this query in such a way that the application parses this string as sql by adding the corresponding values in (i.e. erow.jan82 and so on)

at the moment, the application is not injecting the value, but assuming erow.feb82 is some object column or something and throws an exception.

hope i've made this clear. if requied, i can attached the procedure in.

cheers,
Sath






Re: Inserting dynamic values into a dynamic query. [message #196708 is a reply to message #196704] Fri, 06 October 2006 12:38 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Maybe this will give you some ideas... If your eRow is fixed definition then it's easy, else you need to do it the hard way (
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1035431863958)
user_constraints, user_cons_columns will give you composite PK columns if you don't have a rule of thumb for your table structure...

SQL> create table x_tst (col1 number, col2 date, col3 varchar2(10));

Table created.

SQL> insert into x_tst values (1, null, null);

1 row created.

SQL> 
SQL> create table x_vals (col1 number, col2 date, col3 varchar2(10));

Table created.

SQL> insert into x_vals values (1, to_date('01-01-1980', 'dd-mm-yyyy'), 'New String');

1 row created.

SQL> 
SQL> declare
  2    l_rec x_tst%rowtype;
  3    v_sql long;
  4  begin
  5    select * into l_rec from x_vals where rownum = 1;
  6    -- build v_sql up looping through user_tab_columns or whatever
  7    -- ...
  8    -- final statement:
  9    v_sql := 'update x_tst set col2 = :v1, col3 = :v2 where col1 = :v3';
 10    execute immediate v_sql using l_rec.col2, l_rec.col3, l_rec.col1;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> select * from x_tst;

      COL1 COL2      COL3
---------- --------- ----------
         1 01-JAN-80 New String
Previous Topic: How to wait for a condition and timeout?
Next Topic: How to track DDL Changes and Sourcecode Changes ?
Goto Forum:
  


Current Time: Sun Dec 04 04:29:21 CST 2016

Total time taken to generate the page: 0.13653 seconds