using UPDATE in dbms_sql

[sweetleaf]

Hi,

I would like to use UPDATE in dbms_sql using concantenation rather than binding.

I am unsure of the syntax.. i have no use for output variables - do i still need dbms_sql.FETCH_ROWS()????
or will the below suffice:

--SAMPLE PROC:
procedure p1 is
cursor c1 is select *
from RULE_TABLE;
begin

--OPEN RULE_TABLE:
for x in c1 loop

--UPDATE MOVEMENT_TBL where theres match in criteria:
cur_hdl := dbms_sql.open_cursor;
v_sql:='UPDATE MOVEMENT_TBL set CUSTOMER_ID=' || x.priority || 'WHERE PROCESSED = 0 AND ' || X.FROM_CRITERIA
||' AND ' || X.TO_CRITERIA;

--PARSE THE STATEMENT:
dbms_sql.parse(cur_hdl, v_sql, dbms_sql.native);

--EXECUTE THE STATEMENT:
v_rows_processed:=dbms_sql.execute(cur_hdl); --execute

All help is greatly appreciated!!

 

[sem]

I think that you shouldn't open your cursor for each statement. You don't need to fetchas in fact there's nothing to fetch from your statement. I'd also sugest you to add extra blank space before WHERE keyword.

Regards, Dima

 

[vinperla]

Steps
a.Should Be declare Cursor
b. Parse Check your sqlstatement
c. Execute
d. Close

cursor_handle := dbms_sql.open_cursor
dbms_sql.parse (cursor_handle
," delete from mytable"
,dbms_sql.v7);

results := dbms_sql.execute (cursor_handle);
dbms_sql.close_cursor;

Hope that helps