CODEWITHSUNDEEP
oracle-databaseplsql
x.509
x.509

Reputation: 2235

Need help in execute immediate update query

I have this query and it's not updating into the database. The given "where" clause is valid. When I run the query independently, it works fine but in this Procedure it's not working. There is no exception or no error. Could you guys help me in figuring out where the problem is?

EXECUTE IMMEDIATE 'UPDATE  ' || dest || ' SET COUNTRY_CODE = :v1 WHERE col_id = :v2'
          USING l_vc_CountryCode, l_vc_ColId;

if SQL%ROWCOUNT > 1 THEN
          inserts := inserts + 1;
          counter := counter + 1;
          IF counter > 500 THEN
            counter := 0;
            COMMIT;
          END IF;
        END IF;

I didn't write the commit code before. Just to clarity.

Upvotes: 3

Views: 29526

Answers (4)

tbone
tbone

Reputation: 15473

you may want to reconsider your design if your using dynamic sql to change the "dest" table in thousands of updates.

Much better to know your dest and use bind variables for the where conditions. then you can commit every x rows using mod or similar:

if (mod(v_ctr, 1000) = 0) then
  commit;
end if;

But for your example, Marcin is correct, if you are updating only 1 row at a time, then 

if SQL%ROWCOUNT > 1

will never be true;

EDIT: A simple example knowing your "dest" table:

declare

  cursor sel_cur is
  select col1, col2, from sourceTable where col3 = 'X';

  v_ctr pls_integer := 0;

begin
  for rec in sel_cur
  loop
    v_ctr := v_ctr + 1;

    -- implicit bind variables used
    update destTable
    set col1 = rec.col1,
        col2 = rec.col2
    where col3 = 'Z';

    if (mod(v_ctr, 1000) = 0) then
      commit;
    end if;

  end loop;

exception
  when others then rollback;
  raise;
end;

If using dynamic SQL, a simple example using explicit bind variables (USING clause) from Oracle docs:

CREATE OR REPLACE PROCEDURE raise_emp_salary (column_value NUMBER, 
                             emp_column VARCHAR2, amount NUMBER) IS
   v_column VARCHAR2(30);
   sql_stmt  VARCHAR2(200);
BEGIN
-- determine if a valid column name has been given as input
  SELECT COLUMN_NAME INTO v_column FROM USER_TAB_COLS 
    WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = emp_column;
  sql_stmt := 'UPDATE employees SET salary = salary + :1 WHERE ' 
               || v_column || ' = :2';
  EXECUTE IMMEDIATE sql_stmt USING amount, column_value;
  IF SQL%ROWCOUNT > 0 THEN
    DBMS_OUTPUT.PUT_LINE('Salaries have been updated for: ' || emp_column 
                        || ' = ' || column_value);
  END IF;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('Invalid Column: ' || emp_column);
END raise_emp_salary;
/

For more reading, see here.

Hope this helps, happy coding

Upvotes: 1

Marcin Wroblewski
Marcin Wroblewski

Reputation: 3571

I suppose that col_id is the primary key. So in the update statement

EXECUTE IMMEDIATE 'UPDATE  ' || dest || ' SET COUNTRY_CODE = :v1 WHERE col_id = :v2'
          USING l_vc_CountryCode, l_vc_ColId;

you are always updating at most one row and thus the condition

SQL%ROWCOUNT > 1

is never true ( 1 is not > 1 )

So if you don't have any other commit statement in your procedure, you will never commit those updates.

By the way: what is the purpose of this 

if SQL%ROWCOUNT > 1 THEN
          inserts := inserts + 1;
          counter := counter + 1;
          IF counter > 500 THEN
            counter := 0;
            COMMIT;
          END IF;
        END IF;

why don't you just commit at the end of your work?

Upvotes: 3

Gary Myers
Gary Myers

Reputation: 35401

The following code works okay (ie updates the row). I suspect your error is elsewhere.

For example, if you don't initialise COUNTER, the increment will still leave it as null and it will never commit.

Or, l_vc_ColId may be the wrong datatype and suffering from an invalid conversion.

declare
  v_emp_id number := 7839;
  v_name varchar2(4) := 'DING';
  v_tab varchar2(3) := 'EMP';
begin
  execute immediate 'update '||v_tab||
                    ' set ename = :v_name Where empno = :v_emp_id'
     using v_name, v_emp_id;
  dbms_output.put_line('C:'||sql%rowcount);
end;

Upvotes: 1

Alain Pannetier
Alain Pannetier

Reputation: 9514

Execute immediate needs explicit commit. I guess you checked that ?

Upvotes: 0

Related Questions