Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Using CLOB
A copy of this was sent to klone5399_at_my-deja.com
(if that email address didn't require changing)
On Mon, 15 Nov 1999 00:15:56 GMT, you wrote:
>I created the following package to access a CLOB column
>
>create or replace package test_pack as
>function test_func return varchar2;
>pragma restrict_references(test_func, WNDS);
>end;
>
>create or replace package body test_pack as
>function test_func return varchar2 is
>get_clob CLOB;
>buffer varchar2(20);
>amt integer:=10;
>pos integer:=1;
>begin
>select test_clob into get_clob from test_table where test_key = 1;
>dbms_lob.read(get_clob, amt, pos, buffer);
>return buffer;
>end test_func;
>end test_pack;
>
>I got the compile time error
>
> PLS-00452: Subprogram 'TEST_FUNC' violates its associated pragma
>
because the function dbms_lob.read does not promise to WNDS -- in order to do what you are trying to do, you can use the pure function dbms_lob.substr (in fact, you need not write a package yourself, you can
select dbms_lob.substr( lob_column, for_bytes, from_offset ) from T;
directly)
See the spec of dbms_lob (in $ORACLE_HOME/rdbms/admin/dbmslob.sql) to see which functions are pure enough to be called from plsql called from sql.
>Since I am not writing to the database, should I get this error?
>
>Thanks
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Nov 14 1999 - 20:08:11 CST