Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Brian.

Asked: September 24, 2001 - 8:56 am UTC

Last updated: October 05, 2020 - 12:12 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

I have been through the documentation and I am still not clear on something that is critical for me to understand before deploying into a production environment.

I have created the following function - the cursor query is just an example of creating a small resultset so I can experiment with concatenating varchar2 values and return a single clob:

create or replace function testclob return clob
as
v_clob clob;
begin
dbms_lob.createtemporary(v_clob,FALSE,DBMS_LOB.CALL);
dbms_lob.open(v_clob,dbms_lob.lob_readwrite);
for val in (select 'abc' colalias from dual union
select 'def' colalias from dual union
select 'ghi' colalias from dual)
loop
dbms_lob.writeAppend( v_clob, length(val.colalias), val.colalias );
end loop;

return v_clob;
end;

Because I am actually returning the clob, I am not executing the following within the function:

dbms_lob.close(v_clob);
dbms_lob.freetemporary(v_clob);


I test this from sql/plus as follows:

variable c clob
exec :c := testclob
print c

I get the expected result. However, when I execute

select * from v$temporary_lobs

it seems that with each call I make within the same sql/plus session, the count of nocache_lobs goes up instead of remaining at 1.

Bottom line, I have a java JDBC client that will be calling a function like the above but the session will remain open between calls. I need to make sure I handle scope correctly so that memory usage doesn't grow over time.

How do I return the clob and have it free up appropriately?

Please help me understand my options in this kind of scenario.

Thanks - Brian


and Tom said...

From the application dev guide on LOBS:

<quote>
Note: The DBMS_LOB.CREATETEMPORARY procedure takes an
optional duration parameter. In PL/SQL, this duration parameter is
used only as a hint about the duration of the LOB data. PL/SQL
calculates the duration of the LOB data internally, taking into
account your hint. You do not have to specify the duration of the
LOB data!
</quote>

In your case, it is a good thing it does this -- since you return the LOB to the client -- a scope of "CALL" is inappropriate for you. The call is the call to the function. If PLSQL did not recognize you were returning the CLOB and actually freed it as requested -- the caller would have nothing to print!


If your goal is to use one LOB per session you must either:

o free the LOB when you are done. Just as you would close a file when you are done reading it, or a cursor when you know you won't need it anymore

o use a single LOB in a package.

To use a single LOB in a package, you would code:


ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace package testclob_pkg
2 as
3
4 function testclob( p_data in varchar2 ) return clob;
5
6 end;
7 /

Package created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace package body testclob_pkg
2 as
3
4 g_clob clob;
5
6
7 function testclob( p_data in varchar2 ) return clob
8 as
9 begin
10 dbms_lob.trim( g_clob, 0 );
11 for val in (select p_data colalias from dual union all
12 select ' abc' colalias from dual union all
13 select ' def' colalias from dual union all
14 select ' ghi' colalias from dual)
15 loop
16 dbms_lob.writeAppend( g_clob, length(val.colalias), val.colalias );
17 end loop;
18
19 return g_clob;
20 end;
21
22
23 begin
24 -- elaboration code, run once when package
25 -- is first accessed in a sesion
26 dbms_lob.createtemporary(g_clob,FALSE);
27 dbms_lob.open(g_clob,dbms_lob.lob_readwrite);
28 end;
29 /

Package body created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from v$temporary_lobs;

no rows selected

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 l_clob clob;
3 begin
4 for i in 1 .. 10
5 loop
6 l_clob := testclob_pkg.testclob( 'loop ' || i || ',' );
7 dbms_output.put_line( dbms_lob.substr( l_clob, 250, 1 ) );
8 end loop;
9 end;
10 /
loop 1, abc def ghi
loop 2, abc def ghi
loop 3, abc def ghi
loop 4, abc def ghi
loop 5, abc def ghi
loop 6, abc def ghi
loop 7, abc def ghi
loop 8, abc def ghi
loop 9, abc def ghi
loop 10, abc def ghi

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from v$temporary_lobs;

SID CACHE_LOBS NOCACHE_LOBS
---------- ---------- ------------
14 0 1

(don't use sqlplus variable commands to test this particular one -- sqlplus is allocating its OWN lobs as well).


Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Excellent - clear and educational on multiple fronts

Brian White, September 24, 2001 - 10:48 am UTC

As with many questions on this forum, the answer highlights techniques that will prove generally useful and is explained in a way that provides true education. I've learned so much by reading Tom's new book in conjunction with reviewing the questions answered in this forum.

Very very Good Example!

Olga, December 16, 2003 - 7:58 am UTC

Yes, Tom is great!

Reading Tom's Books and his web-site is better than any ocp course, i bet! That's Oracle live and no pseudo science. I spent so much money for Oracle books where the pages were full of nothing (like a HTML-Reference in a JAVA-Book), now I chucked them away.

Many Thanks Tom!

CLOB

Ik, January 19, 2004 - 5:08 am UTC

Tom,

Hope you are back from your trip?

Two questions

(1) I have almost the same situation except that my function gets called within an SQL statement. I am using a CLOB datatype inorder to return a string > 4000.
I have followed your same methodology and iam opening the CLOB in a separate PL/SQL block within the package.

But, my nocache_lobs count shows 20 (which is equal to the number of rows returned by the SQL).
How do i bring the count down to 1?

(2) This question is triggered from your usage of the anonymous PL/SQL block.

I have a PL/SQL Table declared at the package level. As per design, this table builds up as the SQL progresses through the rows and should hold data for the duration of the SQL.

I have a separate procedure in the same package which i call (before executing the SQL again) inorder to flush the PL/SQL table.

For eg:-
SELECT package.function(arg1,arg2...argn) from table1..n

Before i execute the same SQL again in the same session i have to call the procedure to flush the table.

Tom, is there any way to tell whether the package is called second time from a different (or same) query in the same session. If so, i need to clear it.

Thank You









Tom Kyte
January 19, 2004 - 9:34 am UTC

1) the caller must close the lob.

2) not following you. since you cannot select from a PLSQL table type? one would need a small, yet complete, but concise example of what you mean.

Further on the same posting

Ik, January 19, 2004 - 6:37 am UTC

Tom,

Further to question (1) on CLOBs - I found that by using an anonymous PL/SQL block to call the function results in creation of only one NOCACHE_LOB. Whereas, the same query when executed either in SQL Plus or through Oracle Reports (where it is actually used), the count= number of times the function was called.

Is there an overhead this way? How do i minimize this.

Also, there is a performance difference when i have an anonymous PL/SQL block within the package to do the CREATION and OPEN once for the CLOB (your way) vs...doing it eachtime inside the function. Contrary to what i expected the second option repeatedly came out faster.

BEGIN
-- elaboration code, run once when package
-- is first accessed in a session
DBMS_LOB.CREATETEMPORARY(v_retvalue, FALSE);
DBMS_LOB.OPEN(v_retvalue,dbms_lob.lob_readwrite);

vs

v_retvalue CLOB;
BEGIN

DBMS_LOB.CREATETEMPORARY(v_retvalue, FALSE, DBMS_LOB.CALL);
DBMS_LOB.OPEN(v_retvalue,dbms_lob.lob_readwrite);
DBMS_LOB.TRIM(v_retvalue,0);

Thanks.

can I select into a clo?

RN, December 28, 2005 - 3:04 pm UTC 

Tom, 

I did lot of searches in your site, but could not get it working. I want to populate the query plan returned by function dbms_xplan.display_awr to a CLOB field in a table.
Something like this, can I SELECT INTO A CLOB directly?

  1  declare
  2  v_sql_id varchar2(13) :='0h7jc6t42jsdy';
  3  v_plan clob;
  4  begin
  5  SELECT * into v_plan FROM TABLE(dbms_xplan.display_awr(v_sql_id));
  6  insert into test1 values(v_sql_id, v_plan);
  7  commit;
  8* end;
SQL> /
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 5 

Appreciate an example. Thank you verymuch.

Tom Kyte
December 28, 2005 - 6:06 pm UTC 

well, that query returns "many rows" - each of which is a varchar2(300) 


ops$tkyte@ORA10GR2> create or replace view vw as SELECT *  FROM TABLE(dbms_xplan.display_awr(null));

View created.

ops$tkyte@ORA10GR2> desc vw;
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 PLAN_TABLE_OUTPUT                                 VARCHAR2(300)


you would have to (psuedo code here)

   insert into test1 values (v_sql_id, empty_clob() ) 
      returning plan into v_plan;
   for x in ( select * from table ( .... ) )
   loop
       dbms_lob.writeAppend( v_plan, length(x.plan_table_output), 
                             x.plan_table_output );
   end loop;

Excellent peice of info

Andrew, February 15, 2006 - 7:23 pm UTC

We came across the exact same scenario.

That is what I suspected, that you would have to free all clobs yourself, but I also like the package variable solution.


dbms_xplan.display_awr

Roland, August 16, 2007 - 7:46 pm UTC

Hi Tom,

Regarding dbms_xplan.display_awr. We have an update statement identified by the following sql_id - dr7mvk6j9jnfj and uses the following Plan Hash Value - 3167053118. The unfortunate part is that the plan in question does not reflect an accurate row count that exist in the table. Stats are gathered nightly witht the automatic job in 10g, but the plan that the sql uses still reflects the wrong row count.


select * from table(dbms_xplan.display_awr('dr7mvk6j9jnfj'))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dr7mvk6j9jnfj
--------------------
UPDATE "DEP"."APPLICATION_DATA" SET "SYS_UPDATEDATE" = SYS_EXTRACT_UTC(CURRENT_TIMESTAMP),
"SYS_SESSIONID" = 0, "SYS_STATE" = MYBITAND("SYS_STATE", 18446744073709551613) WHERE
"SYS_SESSIONID" = :V1

Plan hash value: 3167053118

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 19 (100)| | | |
| 1 | UPDATE | APPLICATION_DATA | | | | | | |
| 2 | PARTITION RANGE ALL| | 1093 | 18581 | 19 (0)| 00:00:01 | 1 | 3 |
| 3 | TABLE ACCESS FULL | APPLICATION_DATA | 1093 | 18581 | 19 (0)| 00:00:01 | 1 | 3 |
---------------------------------------------------------------------------------------------------------


An autotrace shows the cost we are getting from the explain plan.

Execution Plan
----------------------------------------------------------
Plan hash value: 3167053118

--------------------------------------------------------------------------------
-------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime | Pstart| Pstop |

--------------------------------------------------------------------------------
-------------------------

| 0 | UPDATE STATEMENT | | 2119K| 56M| 30132 (2)| 0
0:06:02 | | |

| 1 | UPDATE | APPLICATION_DATA | | | |
| | |

| 2 | PARTITION RANGE ALL| | 2119K| 56M| 30132 (2)| 0
0:06:02 | 1 | 5 |

|* 3 | TABLE ACCESS FULL | APPLICATION_DATA | 2119K| 56M| 30132 (2)| 0
0:06:02 | 1 | 5 |

--------------------------------------------------------------------------------
-------------------------

There is an index on the columns in the where clause, and the funny thing is, when running tkprof, the correct plan is used and the update runs perfectly.

TKPROF output


UPDATE "DEP"."APPLICATION_DATA"
SET "SYS_UPDATEDATE" = SYS_EXTRACT_UTC(CURRENT_TIMESTAMP),
"SYS_SESSIONID" = 0,
"SYS_STATE" = MYBITAND("SYS_STATE", 18446744073709551613)
WHERE "SYS_SESSIONID" = :V1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.07 0 3 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.07 0 3 0 0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: 26

Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE APPLICATION_DATA (cr=3 pr=0 pw=0 time=1467 us)
0 INDEX RANGE SCAN APPLICATION_DATA_IDX0 (cr=3 pr=0 pw=0 time=1461 us)(o
bject id 79758)


Is there a way to remove the above plan returned by awr? I've flushed the sga and bounced instances, but its still being used.

Thanks!

Function with clob as return

Rajasekhar, October 02, 2020 - 12:00 pm UTC

Hello Chris,

i am receiving “(HUGECLOB)” in toad, when selecting a function that returns clob data type. is it related to tool issue or i am querying a clob datatype wrongly? if yes, how to select a function that returns clob data type?

Thanks in advance.
Connor McDonald
October 05, 2020 - 12:12 am UTC

This is just the way TOAD chooses to let you know there is clob data. Double clicking on normally will then show the data itself.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here