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






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>








22.16.Execute immediate
22.16.1.Simple EXECUTE IMMEDIATE
22.16.2.Call EXECUTE IMMEDIATE in Pl/SQL block
22.16.3.Execute sql statement in a procedure
22.16.4.'execute immediate in' action
22.16.5.Define a procedure to drop a database object
22.16.6.Create a function to count table row
22.16.7.Use procedure to create an index dynamically
22.16.8.Call function and get result by using 'EXECUTE IMMEDIATE'
22.16.9.EXECUTE IMMEDIATE dynamic sql to alter session
22.16.10.Use 'execute immediate' to run a insert statement
22.16.11.select into rowtype then use it in 'execute immediate'
22.16.12.Quotation string
22.16.13.Use 'EXECUTE IMMEDIATE' to execute an update statement
22.16.14.Wrap 'EXECUTE IMMEDIATE' for current user
22.16.15.Wrap statement with 'BEGIN...END'
22.16.16.Catch exception from 'EXECUTE IMMEDIATE'
22.16.17.EXECUTE IMMEDIATE USING IN
22.16.18.Drop user, create user and grant permission with PL/SQL code
22.16.19.execute immediate into
22.16.20.Update row with 'execute immediate'