Update row with 'execute immediate' : Execute immediate « PL SQL Statements « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Statements
3. Execute immediate

SQL> set echo on
SQL>
SQL> create or replace
  2  function update_row( p_owner    in varchar2,
  3                       p_newDname in varchar2,
  4                       p_newLoc   in varchar2,
  5                       p_deptno   in varchar2,
  6                       p_rowid    out varchar2 )
  7  return number
  8  is
  9  begin
 10      execute immediate 'update ' || p_owner || '.dept
 11                      set dname = :bv1, loc = :bv2
 12                    where deptno = to_number(:pk)
 13                returning rowid into :out'
 14      using p_newDname, p_newLoc, p_deptno
 15      returning into p_rowid;
 16
 17      return sql%rowcount;
 18  end;
 19  /

Function created.

SQL>
SQL> set serveroutput on
SQL> declare
  2      l_rowid   varchar(50);
  3      l_rows    number;
  4  begin
  5      l_rows := update_row( 'SCOTT', 'CONSULTING', 'WASHINGTON', '10', l_rowid );
  6      dbms_output.put_line( 'Updated ' || l_rows || ' rows' );
  7      dbms_output.put_line( 'its rowid was ' || l_rowid );
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "JAVA2S.UPDATE_ROW", line 9
ORA-06512: at line 5


SQL>
SQL>