This chapter contains these topics:
The data interface for persistent LOBs includes a set of Java, PL/SQL, and OCI APIs that are extended to work with LOB datatypes. These APIs, originally designed for use with legacy datatypes such as LONG
, LONG
RAW
, and VARCHAR2
, can also be used with the corresponding LOB datatypes shown in Table 13-1 and Table 13-2. These tables show the legacy datatypes in the "bind or define type" column and the corresponding supported LOB datatype in the "LOB column type" column. You can use the data interface for LOBs to store and manipulate character data and binary data in a LOB column just as if it were stored in the corresponding legacy datatype.
Note: The data interface works for LOB columns as well as LOBs which are attributes of objects. In this chapter "LOB columns" means LOB columns and LOB attributes.You can use array bind and define interfaces to insert and select multiple rows in one round trip. |
For simplicity, this chapter focuses on character datatypes; however, the same concepts apply to the full set of character and binary datatypes listed in Table 13-1 and Table 13-2. CLOB
also means NCLOB
in these tables.
Table 13-1 Corresponding LONG and LOB Datatypes in SQL and PL/SQL
Bind or Define Type | LOB Column Type | Used For Storing |
---|---|---|
CHAR |
CLOB |
Character data |
LONG |
CLOB |
Character data |
VARCHAR2 |
CLOB |
Character data |
LONG RAW |
BLOB |
Binary data |
RAW |
BLOB |
Binary data |
Table 13-2 Corresponding LONG and LOB Datatypes in OCI
Bind or Define Type | LOB Column Type | Used For Storing |
---|---|---|
SQLT_AFC(n) |
CLOB |
Character data |
SQLT_CHR |
CLOB |
Character data |
SQLT_LNG |
CLOB |
Character data |
SQLT_VCS |
CLOB |
Character data |
SQLT_BIN |
BLOB |
Binary data |
SQLT_LBI |
BLOB |
Binary data |
SQLT_LVB |
BLOB |
Binary data |
Using the data interface for persistent LOBs has the following benefits:
If your application already uses LONG
datatypes, then you can use the same application with LOB datatypes with little or no modification of your existing application required. To do so, just convert LONG
datatype columns in your tables to LOB datatype columns as discussed in Chapter 11, "Migrating Columns from LONGs to LOBs".
Performance is better for OCI applications that use sequential access techniques. A piecewise INSERT
or fetch using the data interface has comparable performance to using OCI functions like OCILobRead2()
and OCILobWrite2()
. Because the data interface allows more than 4K bytes of data to be inserted into a LOB in a single OCI call, a round-trip to the server is saved.
You can read LOB data in one OCIStmtFetch()
call, instead of fetching the LOB locator first and then calling OCILobRead2()
. This improves performance when you want to read LOB data starting at the beginning.
You can use array bind and define interfaces to insert and select multiple rows with LOBs in one round trip.
The data interface enables you to use LONG
and LOB datatypes listed in Table 13-1 to perform the following operations in PL/SQL:
INSERT
or UPDATE
character data stored in datatypes such as VARCHAR2
, CHAR
, or LONG
into a CLOB
column.
INSERT
or UPDATE
binary data stored in datatypes such as RAW
or LONG
RAW
into a BLOB
column.
Use the SELECT
statement on CLOB
columns to select data into a character buffer variable such as CHAR
, LONG
, or VARCHAR2
.
Use the SELECT
statement on BLOB
columns to select data into a binary buffer variable such as RAW
and LONG
RAW
.
Make cross-type assignments (implicit type conversions) between CLOB
and VARCHAR2
, CHAR
, or LONG
variables.
Make cross-type assignments (implicit type conversions) between BLOB
and RAW
or LONG
RAW
variables.
Pass LOB datatypes to functions defined to accept LONG
datatypes or pass LONG
datatypes to functions defined to accept LOB datatypes. For example, you can pass a CLOB
instance to a function defined to accept another character type, such as VARCHAR2
, CHAR
, or LONG
.
Use CLOB
s with other PL/SQL functions and operators that accept VARCHAR2
arguments such as INSTR
and SUBSTR
. See "Passing CLOBs to SQL and PL/SQL Built-In Functions" for a complete list.
Note: When using the data interface for LOBs with theSELECT statement in PL/SQL, you cannot specify the amount you want to read. You can only specify the buffer length of your buffer. If your buffer length is smaller than the LOB data length, then the database throws an exception. |
See Also:
|
This section describes techniques you use to access LOB columns or attributes using the data interface for persistent LOBs.
Data from CLOB
and BLOB
columns or attributes can be referenced by regular SQL statements, such as INSERT
, UPDATE
, and SELECT
.
There is no piecewise INSERT
, UPDATE
, or fetch routine in PL/SQL. Therefore, the amount of data that can be accessed from a LOB column or attribute is limited by the maximum character buffer size. PL/SQL supports character buffer sizes up to 32KB - 1 (32767 bytes). For this reason, LOB only less than 32K bytes in size can be accessed by PL/SQL applications using the data interface for persistent LOBs.
If you need to access more than 32KB -1 using the data interface, then you must make OCI calls from the PL/SQL code to use the APIs for piecewise insert and fetch.
Use he following are guidelines for using the data interface to access LOB columns or attributes:
INSERT operations
You can INSERT
into tables containing LOB columns or attributes using regular INSERT
statements in the VALUES
clause. The field of the LOB column can be a literal, a character datatype, a binary datatype, or a LOB locator.
UPDATE
operations
LOB columns or attributes can be updated as a whole by UPDATE
... SET
statements. In the SET
clause, the new value can be a literal, a character datatype, a binary datatype, or a LOB locator.
4000 byte limit on hexadecimal to raw and raw to hexadecimal conversions
The database does not do implicit hexadecimal to RAW
or RAW
to hexadecimal conversions on data that is more than 4000 bytes in size. You cannot bind a buffer of character data to a binary datatype column, and you cannot bind a buffer of binary data to a character datatype column if the buffer is over 4000 bytes in size. Attempting to do so will result in your column data being truncated at 4000 bytes.
For example, you cannot bind a VARCHAR2
buffer to a LONG
RAW
or a BLOB
column if the buffer is more than 4000 bytes in size. Similarly, you cannot bind a RAW
buffer to a LONG
or a CLOB
column if the buffer is more than 4000 bytes in size.
SELECT
operations
LOB columns or attributes can be selected into character or binary buffers in PL/SQL. If the LOB column or attribute is longer than the buffer size, then an exception is raised without filling the buffer with any data. LOB columns or attributes can also be selected into LOB locators.
Implicit assignment and parameter passing are supported for LOB columns. For the datatypes listed in Table 13-1 and Table 13-2, you can pass or assign: any character type to any other character type, or any binary type to any other binary type using the data interface for persistent LOBs.
Implicit assignment works for variables declared explicitly and for variables declared by referencing an existing column type using the %TYPE
attribute as show in the following example. This example assumes that column long_col
in table t
has been migrated from a LONG
to a CLOB
column.
CREATE TABLE t (long_col LONG); -- Alter this table to change LONG column to LOB DECLARE a VARCHAR2(100); b t.long_col%type; -- This variable changes from LONG to CLOB BEGIN SELECT * INTO b FROM t; a := b; -- This changes from "VARCHAR2 := LONG to VARCHAR2 := CLOB b := a; -- This changes from "LONG := VARCHAR2 to CLOB := VARCHAR2 END;
Implicit parameter passing is allowed between functions and procedures. For example, you can pass a CLOB
to a function or procedure where the formal parameter is defined as a VARCHAR2
.
Note: The assigning aVARCHAR2 buffer to a LOB variable is somewhat less efficient than assigning a VARCHAR2 to a LONG variable because the former involves creating a temporary LOB. Therefore, PL/SQL users will see a slight deterioration in the performance of their applications. |
Implicit parameter passing is also supported for built-in PL/SQL functions that accept character data. For example, INSTR
can accept a CLOB
as well as other character data.
Any SQL or PL/SQL built-in function that accepts a VARCHAR2
can accept a CLOB
as an argument. Similarly, a VARCHAR2
variable can be passed to any DBMS_LOB
API for any parameter that takes a LOB locator.
In PL/SQL, the following explicit conversion functions convert other data types to CLOB
and BLOB
datatypes as follows:
TO_CLOB()
converts LONG
, VARCHAR2
, and CHAR
to CLOB
TO_BLOB()
converts LONG RAW
and RAW
to BLOB
Also note that the conversion function TO_CHAR()
can convert a CLOB
to a CHAR
type.
When a PL/SQL or C procedure is called from SQL, buffers with more than 4000 bytes of data are not allowed.
You can call a PL/SQL or C procedure from PL/SQL. You can pass a CLOB
as an actual parameter where CHR
is the formal parameter, or vice versa. The same holds for BLOB
s and RAW
s.
One example of when these cases can arise is when either the formal or the actual parameter is an anchored type, that is, the variable is declared using the table_name.column_name
%type
syntax.
PL/SQL procedures or functions can accept a CLOB
or a VARCHAR2
as a formal parameter. For example the PL/SQL procedure could be one of the following:
When the formal parameter is a CLOB:
CREATE OR REPLACE PROCEDURE get_lob(table_name IN VARCHAR2, lob INOUT CLOB) AS ... BEGIN ... END; /
When the formal parameter is a VARCHAR2:
CREATE OR REPLACE PROCEDURE get_lob(table_name IN VARCHAR2, lob INOUT VARCHAR2) AS ... BEGIN ... END; /
The calling function could be of any of the following types:
When the actual parameter is a CHR:
create procedure ... declare c VARCHAR2[200]; BEGIN get_lob('table_name', c); END;
When the actual parameter is a CLOB:
create procedure ... declare c CLOB; BEGIN get_lob('table_name', c); END;
Binds of all sizes are supported for INSERT
and UPDATE
operations on LOB columns. Multiple binds of any size are allowed in a single INSERT
or UPDATE
statement.
Note: When you create a table, the length of the default value you specify for any LOB column is restricted to 4000 bytes. |
If you bind more than 4000 bytes of data to a BLOB
or a CLOB
, and the data consists of a SQL operator, then Oracle Database limits the size of the result to at most 4000 bytes.
The following statement inserts only 4000 bytes because the result of LPAD
is limited to 4000 bytes:
INSERT INTO print_media (ad_sourcetext) VALUES (lpad('a', 5000, 'a'));
The following statement inserts only 2000 bytes because the result of LPAD
is limited to 4000 bytes, and the implicit hexadecimal to raw conversion converts it to 2000 bytes of RAW
data:
INSERT INTO print_media (ad_photo) VALUES (lpad('a', 5000, 'a'));
The following example illustrates how the result for SQL operators is limited to 4000 bytes.
/* The following command inserts only 4000 bytes because the result of * LPAD is limited to 4000 bytes */ INSERT INTO print_media(product_id, ad_id, ad_sourcetext) VALUES (2004, 5, lpad('a', 5000, 'a')); SELECT LENGTH(ad_sourcetext) FROM print_media WHERE product_id=2004 AND ad_id=5; ROLLBACK; /* The following command inserts only 2000 bytes because the result of * LPAD is limited to 4000 bytes, and the implicit hex to raw conversion * converts it to 2000 bytes of RAW data. */ INSERT INTO print_media(product_id, ad_id, ad_composite) VALUES (2004, 5, lpad('a', 5000, 'a')); SELECT LENGTH(ad_composite) from print_media WHERE product_id=2004 AND ad_id=5; ROLLBAACK;
The following lists the restrictions for binds of more than 4000 bytes:
If a table has both LONG
and LOB columns, then you can bind more than 4000 bytes of data to either the LONG
or LOB columns, but not both in the same statement.
In an INSERT
AS
SELECT
operation, binding of any length data to LOB columns is not allowed.
Parallel execution of the following DML operations on tables with LOB columns is supported. These operations run in parallel execution mode only when performed on a partitioned table. DML statements on non-partitioned tables with LOB columns continue to execute in serial execution mode.
INSERT AS SELECT
CREATE TABLE AS SELECT
DELETE
UPDATE
MERGE
(conditional UPDATE
and INSERT
)
Multitable INSERT
See Also: Oracle Database Administrator's Guide section "Managing Processes for Parallel SQL Execution" |
DECLARE bigtext VARCHAR2(32767); smalltext VARCHAR2(2000); bigraw RAW (32767); BEGIN bigtext := LPAD('a', 32767, 'a'); smalltext := LPAD('a', 2000, 'a'); bigraw := utl_raw.cast_to_raw (bigtext); /* Multiple long binds for LOB columns are allowed for INSERT: */ INSERT INTO print_media(product_id, ad_id, ad_sourcetext, ad_composite) VALUES (2004, 1, bigtext, bigraw); /* Single long bind for LOB columns is allowed for INSERT: */ INSERT INTO print_media (product_id, ad_id, ad_sourcetext) VALUES (2005, 2, smalltext); bigtext := LPAD('b', 32767, 'b'); smalltext := LPAD('b', 20, 'a'); bigraw := utl_raw.cast_to_raw (bigtext); /* Multiple long binds for LOB columns are allowed for UPDATE: */ UPDATE print_media SET ad_sourcetext = bigtext, ad_composite = bigraw, ad_finaltext = smalltext; /* Single long bind for LOB columns is allowed for UPDATE: */ UPDATE print_media SET ad_sourcetext = smalltext, ad_finaltext = bigtext; /* The following is NOT allowed because we are trying to insert more than 4000 bytes of data in a LONG and a LOB column: */ INSERT INTO print_media(product_id, ad_id, ad_sourcetext, press_release) VALUES (2030, 3, bigtext, bigtext); /* Insert of data into LOB attribute is allowed */ INSERT INTO print_media(product_id, ad_id, ad_header) VALUES (2049, 4, adheader_typ(null, null, null, bigraw)); /* The following is not allowed because we try to perform INSERT AS SELECT data INTO LOB */ INSERT INTO print_media(product_id, ad_id, ad_sourcetext) SELECT 2056, 5, bigtext FROM dual; END; /
INSERT
and UPDATE
statements on LOBs are used in the same way as on LONG
s. For example:
DECLARE ad_buffer VARCHAR2(100); BEGIN INSERT INTO print_media(product_id, ad_id, ad_sourcetext) VALUES(2004, 5, 'Source for advertisement 1'); UPDATE print_media SET ad_sourcetext= 'Source for advertisement 2' WHERE product_id=2004 AND ad_id=5; /* This will get the LOB column if it is up to 100 bytes, otherwise it will * raise an exception */ SELECT ad_sourcetext INTO ad_buffer FROM print_media WHERE product_id=2004 AND ad_id=5; END; /
The data interface for LOBs enables implicit assignment and parameter passing as shown in the following example:
CREATE TABLE t (clob_col CLOB, blob_col BLOB); INSERT INTO t VALUES('abcdefg', 'aaaaaa'); DECLARE var_buf VARCHAR2(100); clob_buf CLOB; raw_buf RAW(100); blob_buf BLOB; BEGIN SELECT * INTO clob_buf, blob_buf FROM t; var_buf := clob_buf; clob_buf:= var_buf; raw_buf := blob_buf; blob_buf := raw_buf; END; / CREATE OR REPLACE PROCEDURE FOO ( a IN OUT CLOB) IS BEGIN -- Any procedure body a := 'abc'; END; / CREATE OR REPLACE PROCEDURE BAR (b IN OUT VARCHAR2) IS BEGIN -- Any procedure body b := 'xyz'; END; / DECLARE a VARCHAR2(100) := '1234567'; b CLOB; BEGIN FOO(a); SELECT clob_col INTO b FROM t; BAR(b); END; /
This example illustrates the use of CLOB
s in PL/SQL built-in functions, using the data interface for LOBs:
DECLARE my_ad CLOB; revised_ad CLOB; myGist VARCHAR2(100):= 'This is my gist.'; revisedGist VARCHAR2(100); BEGIN INSERT INTO print_media (product_id, ad_id, ad_sourcetext) VALUES (2004, 5, 'Source for advertisement 1'); -- select a CLOB column into a CLOB variable SELECT ad_sourcetext INTO my_ad FROM print_media WHERE product_id=2004 AND ad_id=5; -- perform VARCHAR2 operations on a CLOB variable revised_ad := UPPER(SUBSTR(my_ad, 1, 20)); -- revised_ad is a temporary LOB -- Concat a VARCHAR2 at the end of a CLOB revised_ad := revised_ad || myGist; -- The following statement will raise an error if my_ad is -- longer than 100 bytes myGist := my_ad; END; /
This section discusses OCI functions included in the data interface for persistent LOBs. These OCI functions work for LOB datatypes exactly the same way as they do for LONG
datatypes. Using these functions, you can perform INSERT
, UPDATE
, fetch, bind, and define operations in OCI on LOBs using the same techniques you would use on other datatypes that store character or binary data.
Note: You can use array bind and define interfaces to insert and select multiple rows with LOBs in one round trip. |
See Also: Oracle Call Interface Programmer's Guide, chapter 5, section "Runtime Data Allocation and Piecewise Operations in OCI" |
You can bind LOB datatypes in the following operations:
Regular, piecewise, and callback binds for INSERT
and UPDATE
operations
Array binds for INSERT
and UPDATE
operations
Parameter passing across PL/SQL and OCI boundaries
Piecewise operations can be performed by polling or by providing a callback. To support these operations, the following OCI functions accept the LONG
and LOB datatypes listed in Table 13-2.
OCIBindByName()
and OCIBindByPos()
These functions create an association between a program variable and a placeholder in the SQL statement or a PL/SQL block for INSERT
and UPDATE
operations.
OCIBindDynamic()
You use this call to register callbacks for dynamic data allocation for INSERT
and UPDATE
operations
OCIStmtGetPieceInfo()
and OCIStmtSetPieceInfo()
These calls are used to get or set piece information for piecewise operations.
The data interface for persistent LOBs allows the following OCI functions to accept the LONG and LOB datatypes listed in Table 13-2.
OCIDefineByPos()
This call associates an item in a SELECT
list with the type and output data buffer.
OCIDefineDynamic()
This call registers user callbacks for SELECT
operations if the OCI_DYNAMIC_FETCH
mode was selected in OCIDefineByPos()
function call.
When you use these functions with LOB types, the LOB data, and not the locator, is selected into your buffer. Note that in OCI, you cannot specify the amount you want to read using the data interface for LOBs. You can only specify the buffer length of your buffer. The database only reads whatever amount fits into your buffer and the data is truncated.
When the client character set is in a multibyte format, functions included in the data interface operate the same way with LOB datatypes as they do for LONG datatypes as follows:
For a piecewise fetch in a multibyte character set, a multibyte character could be cut in the middle, with some bytes at the end of one buffer and remaining bytes in the next buffer.
For a regular fetch, if the buffer cannot hold all bytes of the last character, then Oracle returns as many bytes as fit into the buffer, hence returning partial characters.
This section discusses the various techniques you can use to perform INSERT
or UPDATE
operations on LOB columns or attributes using the data interface. The operations described in this section assume that you have initialized the OCI environment and allocated all necessary handles.
To perform simple INSERT
or UPDATE
operations in one piece using the data interface for persistent LOBs, perform the following steps:
Call OCIStmtPrepare()
to prepare the statement in OCI_DEFAULT
mode.
Call OCIBindByName()
or OCIBindbyPos()
in OCI_DEFAULT
mode to bind a placeholder for LOB as character data or binary data.
Call OCIStmtExecute()
to do the actual INSERT
or UPDATE
operation.
To perform piecewise INSERT
or UPDATE
operations with polling using the data interface for persistent LOBs, do the following steps:
Call OCIStmtPrepare()
to prepare the statement in OCI_DEFAULT
mode.
Call OCIBindByName()
or OCIBindbyPos()
in OCI_DATA_AT_EXEC
mode to bind a LOB as character data or binary data.
Call OCIStmtExecute()
in default mode. Do each of the following in a loop while the value returned from OCIStmtExecute()
is OCI_NEED_DATA
. Terminate your loop when the value returned from OCIStmtExecute()
is OCI_SUCCESS
.
Call OCIStmtGetPieceInfo()
to retrieve information about the piece to be inserted.
Call OCIStmtSetPieceInfo()
to set information about piece to be inserted.
To perform piecewise INSERT
or UPDATE
operations with callback using the data interface for persistent LOBs, do the following steps:
Call OCIStmtPrepare()
to prepare the statement in OCI_DEFAULT
mode.
Call OCIBindByName()
or OCIBindbyPos()
in OCI_DATA_AT_EXEC
mode to bind a placeholder for the LOB column as character data or binary data.
Call OCIBindDynamic()
to specify the callback.
Call OCIStmtExecute()
in default mode.
To perform array INSERT
or UPDATE
operations using the data interface for persistent LOBs, use any of the techniques discussed in this section in conjunction with OCIBindArrayOfStruct()
, or by specifying the number of iterations (iter
), with iter
value greater than 1, in the OCIStmtExecute()
call.
This section discusses techniques you can use to fetch data from LOB columns or attributes in OCI using the data interface for persistent LOBs.
To perform a simple fetch operation on LOBs in one piece using the data interface for persistent LOBs, do the following:
Call OCIStmtPrepare()
to prepare the SELECT
statement in OCI_DEFAULT
mode.
Call OCIDefineByPos()
to define a select list position in OCI_DEFAULT
mode to define a LOB as character data or binary data.
Call OCIStmtExecute()
to run the SELECT
statement.
Call OCIStmtFetch()
to do the actual fetch.
To perform a piecewise fetch operation on a LOB column with polling using the data interface for LOBs, do the following steps:
Call OCIStmtPrepare()
to prepare the SELECT
statement in OCI_DEFAULT
mode.
Call OCIDefinebyPos()
to define a select list position in OCI_DYNAMIC_FETCH
mode to define the LOB column as character data or binary data.
Call OCIStmtExecute()
to run the SELECT
statement.
Call OCIStmtFetch()
in default mode. Do each of the following in a loop while the value returned from OCIStmtFetch()
is OCI_NEED_DATA.
Terminate your loop when the value returned from OCIStmtFetch()
is OCI_SUCCESS
.
Call OCIStmtGetPieceInfo()
to retrieve information about the piece to be fetched.
Call OCIStmtSetPieceInfo()
to set information about piece to be fetched.
To perform a piecewise fetch operation on a LOB column with callback using the data interface for persistent LOBs, do the following:
Call OCIStmtPrepare()
to prepare the statement in OCI_DEFAULT
mode.
Call OCIDefinebyPos()
to define a select list position in OCI_DYNAMIC_FETCH
mode to define the LOB column as character data or binary data.
Call OCIStmtExecute()
to run the SELECT
statement.
Call OCIDefineDynamic()
to specify the callback.
Call OCIStmtFetch()
in default mode.
To perform an array fetch in OCI using the data interface for persistent LOBs, use any of the techniques discussed in this section in conjunction with OCIDefineArrayOfStruct()
, or by specifying the number of iterations (iter
), with the value of iter
greater than 1, in the OCIStmtExecute()
call.
When you call a PL/SQL procedure from OCI, and have an in or out or in/out bind, you should be able to:
Bind a variable as SQLT_CHR
or SQLT_LNG
where the formal parameter of the PL/SQL procedure is SQLT_CLOB
, or
Bind a variable as SQLT_BIN
or SQLT_LBI
where the formal parameter is SQLT_BLOB
The following two cases work:
Here is an example of calling PL/SQL out-binds in the "begin foo(:1); end;" Manner:
text *sqlstmt = (text *)"BEGIN get_lob(:c); END; " ;
Here is an example of calling PL/SQL out-binds in the "call foo(:1);" manner:
text *sqlstmt = (text *)"CALL get_lob( :c );" ;
In both these cases, the rest of the program has these statements:
OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); curlen = 0; OCIBindByName(stmthp, &bndhp[3], errhp, (text *) ":c", (sb4) strlen((char *) ":c"), (dvoid *) buf5, (sb4) LONGLEN, SQLT_CHR, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 1, (ub4 *) &curlen, (ub4) OCI_DATA_AT_EXEC);
The PL/SQL procedure, get_lob()
, is as follows:
procedure get_lob(c INOUT CLOB) is -- This might have been column%type BEGIN ... /* The procedure body could be in PL/SQL or C*/ END;
void insert3() { /* Insert of data into LOB attributes is allowed. */ ub1 buffer[8000]; text *insert_sql = (text *)"INSERT INTO Print_media (ad_header) \ VALUES (adheader_typ(NULL, NULL, NULL,:1))"; OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 2000, SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); OCIStmtExecute(svchp, stmthp, errhp, 1, 0, (const OCISnapshot*) 0, (OCISnapshot*)0, OCI_DEFAULT); }
The data interface for LOBs allows LOB PL/SQL binds from OCI to work as follows. When you call a PL/SQL procedure from OCI, and have an IN
or OUT
or IN OUT
bind, you should be able to bind a variable as SQLT_CHR
, where the formal parameter of the PL/SQL procedure is SQLT_CLOB
.
Note: C procedures are wrapped inside a PL/SQL stub, so the OCI application always calls the PL/SQL stub. |
For the OCI calling program, the following are likely cases:
For example:
text *sqlstmt = (text *)"BEGIN PKG1.P5 (:c); END; " ;
For example:
text *sqlstmt = (text *)"CALL PKG1.P5( :c );" ;
In both these cases, the rest of the program is as follows:
OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); curlen = 0; OCIBindByName(stmthp, &bndhp[3], errhp, (text *) ":c4", (sb4) strlen((char *) ":c"), (dvoid *) buf5, (sb4) LONGLEN, SQLT_CHR, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 1, (ub4 *) &curlen, (ub4) OCI_DATA_AT_EXEC); OCIStmtExecute(svchp, stmthp, errhp,(ub4) 0,(ub4) 0, (const OCISnapshot*) 0, (OCISnapshot*) 0,(ub4) OCI_DEFAULT);
The PL/SQL procedure PKG1.P5
is as follows:
CREATE OR REPLACE PACKAGE BODY pkg1 AS ... procedure p5 (c OUT CLOB) is -- This might have been table%rowtype (so it is CLOB now) BEGIN ... END p5; END pkg1;
The following example illustrates binding character data for a LOB column:
void simple_insert() { word buflen; text buf[5000]; text *insstmt = (text *) "INSERT INTO Print_media(Product_id, Ad_id,\ Ad_sourcetext) VALUES (2004, 1, :SRCTXT)"; OCIStmtPrepare(stmthp, errhp, insstmt, (ub4)strlen((char *)insstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); OCIBindByName(stmthp, &bndhp[0], errhp, (text *) ":SRCTXT", (sb4) strlen((char *) ":SRCTXT"), (dvoid *) buf, (sb4) sizeof(buf), SQLT_CHR, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT); memset((void *)buf, (int)'A', (size_t)5000); OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (const OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT); }
The following example illustrates using piecewise INSERT
with polling using the data interface for LOBs.
void piecewise_insert() { text *sqlstmt = (text *)"INSERT INTO Print_media(Product_id, Ad_id,\ Ad_sourcetext) VALUES (:1, :2, :3)"; ub2 rcode; ub1 piece, i; word product_id = 2004; word ad_id = 2; ub4 buflen; char buf[5000]; OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); OCIBindByPos(stmthp, &bndhp[0], errhp, (ub4) 1, (dvoid *) &product_id, (sb4) sizeof(product_id), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT); OCIBindByPos(stmthp, &bndhp[1], errhp, (ub4) 2, (dvoid *) &ad_id, (sb4) sizeof(ad_id), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT); OCIBindByPos(stmthp, &bndhp[2], errhp, (ub4) 3, (dvoid *) 0, (sb4) 15000, SQLT_LNG, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC); i = 0; while (1) { i++; retval = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT); switch(retval) { case OCI_NEED_DATA: memset((void *)buf, (int)'A'+i, (size_t)5000); buflen = 5000; if (i == 1) piece = OCI_FIRST_PIECE; else if (i == 3) piece = OCI_LAST_PIECE; else piece = OCI_NEXT_PIECE; if (OCIStmtSetPieceInfo((dvoid *)bndhp[2], (ub4)OCI_HTYPE_BIND, errhp, (dvoid *)buf, &buflen, piece, (dvoid *) 0, &rcode)) { DISCARD printf("ERROR: OCIStmtSetPieceInfo: %d \n", retval); break; } break; case OCI_SUCCESS: break; default: printf( "oci exec returned %d \n", retval); report_error(errhp); retval = OCI_SUCCESS; } /* end switch */ if (retval == OCI_SUCCESS) break; } /* end while(1) */ }
The following example illustrates binding LONG
data to LOB columns using a piecewise INSERT
with callback:
void callback_insert() { word buflen = 15000; word product_id = 2004; word ad_id = 3; text *sqlstmt = (text *) "INSERT INTO Print_media(Product_id, Ad_id,\ Ad_sourcetext) VALUES (:1, :2, :3)"; word pos = 3; OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT) OCIBindByPos(stmthp, &bndhp[0], errhp, (ub4) 1, (dvoid *) &product_id, (sb4) sizeof(product_id), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT); OCIBindByPos(stmthp, &bndhp[1], errhp, (ub4) 2, (dvoid *) &ad_id, (sb4) sizeof(ad_id), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT); OCIBindByPos(stmthp, &bndhp[2], errhp, (ub4) 3, (dvoid *) 0, (sb4) buflen, SQLT_CHR, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC); OCIBindDynamic(bndhp[2], errhp, (dvoid *) (dvoid *) &pos, insert_cbk, (dvoid *) 0, (OCICallbackOutBind) 0); OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (const OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT); } /* end insert_data() */ /* Inbind callback to specify input data. */ static sb4 insert_cbk(dvoid *ctxp, OCIBind *bindp, ub4 iter, ub4 index, dvoid **bufpp, ub4 *alenpp, ub1 *piecep, dvoid **indpp) { static int a = 0; word j; ub4 inpos = *((ub4 *)ctxp); char buf[5000]; switch(inpos) { case 3: memset((void *)buf, (int) 'A'+a, (size_t) 5000); *bufpp = (dvoid *) buf; *alenpp = 5000 ; a++; break; default: printf("ERROR: invalid position number: %d\n", inpos); } *indpp = (dvoid *) 0; *piecep = OCI_ONE_PIECE; if (inpos == 3) { if (a<=1) { *piecep = OCI_FIRST_PIECE; printf("Insert callback: 1st piece\n"); } else if (a<3) { *piecep = OCI_NEXT_PIECE; printf("Insert callback: %d'th piece\n", a); } else { *piecep = OCI_LAST_PIECE; printf("Insert callback: %d'th piece\n", a); a = 0; } } return OCI_CONTINUE; }
The following example illustrates binding character data for LOB columns using an array INSERT
operation:
void array_insert() { ub4 i; word buflen; word arrbuf1[5]; word arrbuf2[5]; text arrbuf3[5][5000]; text *insstmt = (text *)"INSERT INTO Print_media(Product_id, Ad_id,\ Ad_sourcetext) VALUES (:PID, :AID, :SRCTXT)"; OCIStmtPrepare(stmthp, errhp, insstmt, (ub4)strlen((char *)insstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); OCIBindByName(stmthp, &bndhp[0], errhp, (text *) ":PID", (sb4) strlen((char *) ":PID"), (dvoid *) &arrbuf1[0], (sb4) sizeof(arrbuf1[0]), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT); OCIBindByName(stmthp, &bndhp[1], errhp, (text *) ":AID", (sb4) strlen((char *) ":AID"), (dvoid *) &arrbuf2[0], (sb4) sizeof(arrbuf2[0]), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT); OCIBindByName(stmthp, &bndhp[2], errhp, (text *) ":SRCTXT", (sb4) strlen((char *) ":SRCTXT"), (dvoid *) arrbuf3[0], (sb4) sizeof(arrbuf3[0]), SQLT_CHR, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT); OCIBindArrayOfStruct(bndhp[0], errhp sizeof(arrbuf1[0]), indsk, rlsk, rcsk); OCIBindArrayOfStruct(bndhp[1], errhp, sizeof(arrbuf2[0]), indsk, rlsk, rcsk); OCIBindArrayOfStruct(bndhp[2], errhp, sizeof(arrbuf3[0]), indsk, rlsk, rcsk); for (i=0; i<5; i++) { arrbuf1[i] = 2004; arrbuf2[i] = i+4; memset((void *)arrbuf3[i], (int)'A'+i, (size_t)5000); } OCIStmtExecute(svchp, stmthp, errhp, (ub4) 5, (ub4) 0, (const OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT); }
The following example illustrates selecting a LOB column using a simple fetch:
void simple_fetch() { word retval; text buf[15000]; text *selstmt = (text *) "SELECT Ad_sourcetext FROM Print_media WHERE\ Product_id = 2004"; OCIStmtPrepare(stmthp, errhp, selstmt, (ub4)strlen((char *)selstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); retval = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 0, (ub4) 0, (const OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT); while (retval == OCI_SUCCESS || retval == OCI_SUCCESS_WITH_INFO) { OCIDefineByPos(stmthp, &defhp, errhp, (ub4) 1, (dvoid *) buf, (sb4) sizeof(buf), (ub2) SQLT_CHR, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT); retval = OCIStmtFetch(stmthp, errhp, (ub4) 1, (ub4) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT); if (retval == OCI_SUCCESS || retval == OCI_SUCCESS_WITH_INFO) DISCARD printf("buf = %.*s\n", 15000, buf); } }
The following example illustrates selecting a LOB column into a LONG
buffer using a piecewise fetch with polling:
void piecewise_fetch() { text buf[15000]; ub4 buflen=5000; word retval; text *selstmt = (text *) "SELECT Ad_sourcetext FROM Print_media\ WHERE Product_id = 2004 AND Ad_id = 2"; OCIStmtPrepare(stmthp, errhp, selstmt, (ub4) strlen((char *)selstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); OCIDefineByPos(stmthp, &dfnhp, errhp, (ub4) 1, (dvoid *) NULL, (sb4) 100000, SQLT_LNG, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DYNAMIC_FETCH); retval = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 0, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT); retval = OCIStmtFetch(stmthp, errhp, (ub4) 1 , (ub2) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT); while (retval != OCI_NO_DATA && retval != OCI_SUCCESS) { ub1 piece; ub4 iter; ub4 idx; genclr((void *)buf, 5000); switch(retval) { case OCI_NEED_DATA: OCIStmtGetPieceInfo(stmthp, errhp, &hdlptr, &hdltype, &in_out, &iter, &idx, &piece); buflen = 5000; OCIStmtSetPieceInfo(hdlptr, hdltype, errhp, (dvoid *) buf, &buflen, piece, (CONST dvoid *) &indp1, (ub2 *) 0); retval = OCI_NEED_DATA; break; default: printf("ERROR: piece-wise fetching, %d\n", retval); return; } /* end switch */ retval = OCIStmtFetch(stmthp, errhp, (ub4) 1 , (ub2) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT); printf("Data : %.5000s\n", buf); } /* end while */ }
The following example illustrates selecting a LONG
column into a LOB buffer when using a piecewise fetch with callback:
char buf[5000]; void callback_fetch() { word outpos = 1; text *sqlstmt = (text *) "SELECT Ad_sourcetext FROM Print_media WHERE\ Product_id = 2004 AND Ad_id = 3"; OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); OCIDefineByPos(stmthp, &dfnhp[0], errhp, (ub4) 1, (dvoid *) 0, (sb4)3 * sizeof(buf), SQLT_CHR, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) OCI_DYNAMIC_FETCH); OCIDefineDynamic(dfnhp[0], errhp, (dvoid *) &outpos, (OCICallbackDefine) fetch_cbk); OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (const OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT); buf[ 4999 ] = '\0'; printf("Select callback: Last piece: %s\n", buf); } /* -------------------------------------------------------------- */ /* Fetch callback to specify buffers. */ /* -------------------------------------------------------------- */ static sb4 fetch_cbk(dvoid *ctxp, OCIDefine *dfnhp, ub4 iter, dvoid **bufpp, ub4 **alenpp, ub1 *piecep, dvoid **indpp, ub2 **rcpp) { static int a = 0; ub4 outpos = *((ub4 *)ctxp); ub4 len = 5000; switch(outpos) { case 1: a ++; *bufpp = (dvoid *) buf; *alenpp = &len; break; default: *bufpp = (dvoid *) 0; *alenpp = (ub4 *) 0; printf("ERROR: invalid position number: %d\n", outpos); } *indpp = (dvoid *) 0; *rcpp = (ub2 *) 0; buf[len] = '\0'; if (a<=1) { *piecep = OCI_FIRST_PIECE; printf("Select callback: 0th piece\n"); } else if (a<3) { *piecep = OCI_NEXT_PIECE; printf("Select callback: %d'th piece: %s\n", a-1, buf); } else { *piecep = OCI_LAST_PIECE; printf("Select callback: %d'th piece: %s\n", a-1, buf); a = 0; } return OCI_CONTINUE; }
The following example illustrates selecting a LOB column into a LONG
buffer using an array fetch:
void array_fetch() { word i; text arrbuf[5][5000]; text *selstmt = (text *) "SELECT Ad_sourcetext FROM Print_media WHERE\ Product_id = 2004 AND Ad_id >=4"; OCIStmtPrepare(stmthp, errhp, selstmt, (ub4)strlen((char *)selstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); OCIStmtExecute(svchp, stmthp, errhp, (ub4) 0, (ub4) 0, (const OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT); OCIDefineByPos(stmthp, &defhp1, errhp, (ub4) 1, (dvoid *) arrbuf[0], (sb4) sizeof(arrbuf[0]), (ub2) SQLT_CHR, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT); OCIDefineArrayOfStruct(dfnhp1, errhp, sizeof(arrbuf[0]), indsk, rlsk, rcsk); retval = OCIStmtFetch(stmthp, errhp, (ub4) 5, (ub4) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT); if (retval == OCI_SUCCESS || retval == OCI_SUCCESS_WITH_INFO) { printf("%.5000s\n", arrbuf[0]); printf("%.5000s\n", arrbuf[1]); printf("%.5000s\n", arrbuf[2]); printf("%.5000s\n", arrbuf[3]); printf("%.5000s\n", arrbuf[4]); } }
You can also read and write CLOB
and BLOB
data using the same streaming mechanism as for LONG
and LONG
RAW
data. To read, use defineColumnType(nn, Types.LONGVARCHAR)
or defineColumnType(nn, Types.LONGVARBINARY)
on the column. This produces a direct stream on the data as if it is a LONG
or LONG
RAW
column. For input in a PreparedStatement
, you may use setBinaryStream()
, setCharacterStream()
, or setAsciiStream()
for a parameter which is a BLOB
or CLOB
. These methods use the stream interface to create a LOB in the database from the data in the stream. Both of these techniques reduce database round trips and may result in improved performance in some cases. See the Javadoc on stream data for the significant restrictions which apply, at http://www.oracle.com/technology/index.html
.
Refer to the following in the JDBC Developer's Guide and Reference:
See Also:
|
The data interface for insert, update, and select of remote LOBs (access over a dblink) is supported since Oracle Database 10g Release 2. The examples in the following sections are for the print_media
table created in two schemas: dbs1
and dbs2
. The CLOB
column of that table used in the examples shown is ad_finaltext
. The examples to be given for PL/SQL, OCI, and Java use binds and defines for this one column, but multiple columns can also be accessed. Here is the functionality supported and its limitations:
You can define a CLOB
as CHAR
or NCHAR
and an NCLOB
as CHAR or NCHAR. CLOB
and NCLOB
can be defined as a LONG
. A BLOB
can be defined as a RAW
or a LONG
RAW
.
Queries involving more than one database are not supported:
SELECT t1.lobcol, a2.lobcol FROM t1, t2.lobcol@dbs2 a2 WHERE LENGTH(t1.lobcol) = LENGTH(a2.lobcol);
Neither is this query:
SELECT t1.lobcol FROM t1@dbs1 UNION ALL SELECT t2.lobcol FROM t2@dbs2;
DDL commands are not supported, so the following returns an error:
CREATE VIEW v AS SELECT lob_col FROM tab@dbs;
Only binds and defines for data going into remote persistent LOBs are supported, so that parameter passing in PL/SQL where CHAR
data is bound or defined for remote LOBs is not allowed. These statements all produce errors:
SELECT foo() FROM table1@dbs2; SELECT foo()@dbs INTO char_val FROM DUAL; SELECT XMLType().getclobval FROM table1@dbs2;
If the remote object is a view such as
CREATE VIEW v AS SELECT foo() FROM ... /* The local database then tries to get the CLOB data and has an error */ SELECT * FROM v@dbs2;
RETURNING
INTO
does not support implicit conversions between CHAR
and CLOB
.
PL/SQL parameter passing is not allowed where the actual argument is a LOB type and the remote argument is a VARCHAR2
, NVARCHAR2
, CHAR
, NCHAR
, or RAW
.
Array binds and defines are supported.
The data interface only supports data of size less than 32KB in PL/SQL. The following snippet shows a PL/SQL example:
CONNECT pm/pm declare my_ad varchar(6000) := lpad('b', 6000, 'b'); BEGIN INSERT INTO print_media@dbs2(product_id, ad_id, ad_finaltext) VALUES (10000, 10, my_ad); -- Reset the buffer value my_ad := 'a'; SELECT ad_finaltext INTO my_ad FROM print_media@dbs2 WHERE product_id = 10000; END; /
If ad_finaltext
were a BLOB
column instead of a CLOB
, my_ad
has to be of type RAW
. If the LOB is greater than 32KB - 1 in size, then PL/SQL will raise a truncation error and the contents of the buffer are undefined.
The data interface only supports data of size less than 2 GB (the maximum value possible of a variable declared as sb4
) for OCI. The following pseudocode can be enhanced to be a part of an OCI program:
... text *sql = (text *)"insert into print_media@dbs2 (product_id, ad_id, ad_finaltext) values (:1, :2, :3)"; OCIStmtPrepare(...); OCIBindByPos(...); /* Bind data for positions 1 and 2 * which are independent of LOB */ OCIBindByPos(stmthp, &bndhp[2], errhp, (ub4) 3, (dvoid *) charbuf1, (sb4) len_charbuf1, SQLT_CHR, (dvoid *) 0, (ub2 *)0, (ub2 *)0, 0, 0, OCI_DEFAULT); OCIStmtExecute(...); ... text *sql = (text *)"select ad_finaltext from print_media@dbs2 where product_id = 10000"; OCIStmtPrepare(...); OCIDefineByPos(stmthp, &dfnhp[2], errhp, (ub4) 1, (dvoid *) charbuf2, (sb4) len_charbuf2, SQLT_CHR, (dvoid *) 0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT); OCIStmtExecute(...); ...
If ad_finaltext
were a BLOB
instead of a CLOB
, then you bind and define using type SQLT_BIN
. If the LOB is greater than 2GB - 1 in size, then OCI will raise a truncation error and the contents of the buffer are undefined.
The following code snippets works with all three JDBC drivers (OCI, Thin, and kprb
in the database):
Bind:
This is for the non-streaming mode:
... String sql = "insert into print_media@dbs2 (product_id, ad_id, ad_final_text)" + " values (:1, :2, :3)"; PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setInt( 1, 2 ); pstmt.setInt( 2, 20); pstmt.setString( 3, "Java string" ); int rows = pstmt.executeUpdate(); ...
For the streaming mode, the same code as the preceding works, except that the setString()
statement is replaced by one of the following:
pstmt.setCharacterStream( 3, new LabeledReader(), 1000000 ); pstmt.setAsciiStream( 3, new LabeledAsciiInputStream(), 1000000 );
Here, LabeledReader()
and LabeledAsciiInputStream()
produce character and ASCII streams respectively. If ad_finaltext
were a BLOB
column instead of a CLOB
, then the preceding example works if the bind is of type RAW
:
pstmt.setBytes( 3, <some byte[] array> ); pstmt.setBinaryStream( 3, new LabeledInputStream(), 1000000 );
Here, LabeledInputStream()
produces a binary stream.
Define:
For non-streaming mode:
OracleStatement stmt = (OracleStatement)(conn.createStatement());
stmt.defineColumnType( 1, Types.VARCHAR );
ResultSet rst = stmt.executeQuery("select ad_finaltext from print_media@dbs2" );
while( rst.next() )
{
String s = rst.getString( 1 );
System.out.println( s );
}
For streaming mode:
OracleStatement stmt = (OracleStatement)(conn.createStatement());
stmt.defineColumnType( 1, Types.LONGVARCHAR );
ResultSet rst = stmt.executeQuery("select ad_finaltext from print_media@dbs2" );
while( rst.next() )
{
Reader reader = rst.getCharacterStream( 1 );
while( reader.ready() )
{
System.out.print( (char)(reader.next()) );
}
System.out.println();
}
If ad_finaltext
were a BLOB
column instead of a CLOB
, then the preceding examples work if the define is of type RAW
:
stmt.defineColumnType( 1, Types.RAW ) // or LONGRAW ... String s = rst.getBytes( 1 ); Reader reader = rst.getBinaryStream( 1 );
See Also: Oracle Database JDBC Developer's Guide and Reference, chapter 14, "Working with LOBs and BFILEs", section "Shortcuts for Inserting and Retrieving CLOB Data" |