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