Re: return clob to java program

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Sun, 15 Feb 2004 09:58:48 -0800
Message-ID: <1076867884.900181@yasure>

Ted McCabe wrote:

> Have you looked in ORACLE_HOME\rdbms\demo\lobs\java\*.  I don't use it but I
> know it's there
> 
> HTH
> tED
> "Vijays" <vbaskar_at_indiatimes.com> wrote in message
> news:65a7c6c4.0402132342.7ceba782_at_posting.google.com...
> 

>>Hi All,
>>
>>The below is the version of oracle we are using
>>
>>Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
>>PL/SQL Release 9.2.0.1.0 - Production
>>CORE 9.2.0.1.0 Production
>>TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
>>NLSRTL Version 9.2.0.1.0 - Production
>>
>>We have an SP that return the CLOB database to the calling program.
>>The calling program is an JSP and we use oracle thin driver. My size
>>of CLOB is around 32KB and sometimes little more than 32KB. If we send
>>below 30kb it is working, but if it is more than 30 KB it is not
>>working. Any solution ?
>>
>>Rgds
>>
>>Vjys

The solution, if I understand the OP's problem, can be solved by using the DBMS_SQL package's ability to execute a PL/SQL table.

The following stored procedure will execute any PL/SQL anonymous block thrown at it no matter the size up to 4GB.

CREATE OR REPLACE PROCEDURE execute_plsql_block( plsql_code_block CLOB) IS

ds_cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR; sql_table DBMS_SQL.VARCHAR2S;

c_buf_len CONSTANT BINARY_INTEGER := 256;
v_accum INTEGER := 0;
v_beg INTEGER := 1;
v_end INTEGER := 256;
v_loblen PLS_INTEGER;
v_RetVal PLS_INTEGER;

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

-- local function to the execute_plsql_block procedure FUNCTION next_row (
clob_in IN CLOB,
len_in IN INTEGER,
off_in IN INTEGER) RETURN VARCHAR2 IS

BEGIN
    RETURN DBMS_LOB.SUBSTR(clob_in, len_in, off_in); END next_row;

---

BEGIN
    v_loblen := DBMS_LOB.GETLENGTH(plsql_code_block);

    LOOP

• Set the length to the remaining size
• if there are < c_buf_len characters remaining. IF v_accum + c_buf_len > v_loblen THEN v_end := v_loblen - v_accum; END IF;

       sql_table(NVL(sql_table.LAST, 0) + 1) :=
       next_row(plsql_code_block, v_end, v_beg);

       v_beg := v_beg + c_BUF_LEN;
       v_accum := v_accum + v_end;

       IF v_accum >= v_loblen THEN
          EXIT;
       END IF;

    END LOOP;

• Parse the pl/sql and execute it DBMS_SQL.PARSE(ds_cur, sql_table, sql_table.FIRST, sql_table.LAST, FALSE, DBMS_SQL.NATIVE); v_RetVal := DBMS_SQL.EXECUTE(ds_cur); DBMS_SQL.CLOSE_CURSOR(ds_cur);

END execute_plsql_block;
/

It is a demo from the University of Washington Advanced Oracle class and if incorporated into a production environment could use a bit of clean up and some error handling.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Sun Feb 15 2004 - 11:58:48 CST