1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55
|
ops$tkyte@ORA817.US.ORACLE.COM> create table t
2 ( x int primary key, y clob )
3 /
Table created.
ops$tkyte@ORA817.US.ORACLE.COM> insert into t values ( 1, null );
ops$tkyte@ORA817.US.ORACLE.COM>
ops$tkyte@ORA817.US.ORACLE.COM> insert into t values ( 1, null );
1 row created.
ops$tkyte@ORA817.US.ORACLE.COM>
ops$tkyte@ORA817.US.ORACLE.COM>
ops$tkyte@ORA817.US.ORACLE.COM> create or replace
2 procedure update_clob( p_tname in varchar2,
3 p_pkname in varchar2,
4 p_pkval in int,
5 p_clobName in varchar2,
6 p_clobVal in varchar2 )
7 is
8 l_clob clob;
9 begin
10 execute immediate
11 'begin
12 update ' || p_tname || '
13 set ' || p_clobName || ' = empty_clob()
14 where ' || p_pkname || ' = :x
15 returning ' || p_clobName || ' into :y;
16 end;'
17 USING IN p_pkVal, OUT l_clob;
18
19 dbms_lob.writeappend( l_clob, length(p_clobVal), p_clobVal );
20 end;
21 /
Procedure created.
ops$tkyte@ORA817.US.ORACLE.COM>
ops$tkyte@ORA817.US.ORACLE.COM> exec update_clob( 't', 'x', 1, 'y', 'Hello World' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA817.US.ORACLE.COM> select * from t;
X Y
----------
--------------------------------------------------------------------------------
1 Hello World |
Partager