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 "RNTSOFT.UPDATE_ROW", line 9
ORA-06512: at line 5
SQL>