COLUMN_VALUE_LONG v17

Suggest edits

The COLUMN_VALUE_LONG procedure gets part of a value of a LONG column to be returned and retrieved in a cursor. 

COLUMN_VALUE_LONG(<c> IN NUMBER, <position> IN NUMBER, <length> IN NUMBER, 
   <offset> IN NUMBER, <value> OUT VARCHAR2, <value_length> OUT NUMBER);

Parameters

c

Cursor ID of the cursor returning data.

position

Position of the returned data in the cursor. The first value in the cursor is position 1.

length

Number of bytes of the long value to fetch.

offset

Offset into the long field at which point the fetch begins.

value

Value of the column as a VARCHAR2.

value_length

Number of bytes returned.

Examples

-- Creating the required object
CREATE TABLE rm44142_dtypes (
   col_long             LONG,
   col_int  INTEGER);

-- Insert data into table
INSERT INTO rm44142_dtypes VALUES ('TestingForDefineColumnValueLong', 1);

-- Use zero offset
DECLARE
   cur_id               INTEGER;
   v_long       VARCHAR(20);
   sql_stmt     VARCHAR2(50) := 'SELECT col_long ' || ' FROM rm44142_dtypes';
   status       INTEGER;
   length       INTEGER;

BEGIN
   cur_id := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(cur_id, sql_stmt, DBMS_SQL.native);
   DBMS_SQL.define_column_long(cur_id, 1);
   status := DBMS_SQL.EXECUTE(cur_id);
   status := DBMS_SQL.FETCH_ROWS(cur_id);
   DBMS_SQL.COLUMN_VALUE_LONG(cur_id, 1, 7, 0, v_long, length);
   DBMS_OUTPUT.PUT_LINE('col_long: ' || v_long);
   DBMS_OUTPUT.PUT_LINE('value_length: ' || length);
   DBMS_SQL.CLOSE_CURSOR(cur_id);
END;

Output:
col_long: Testing
value_length: 7

EDB-SPL Procedure successfully completed


-- Use positive offset
DECLARE
   cur_id               INTEGER;
   v_long       VARCHAR2(20);
   sql_stmt     VARCHAR2(50) := 'SELECT col_int, col_long ' || ' FROM rm44142_dtypes';
   status       INTEGER;
   length       INTEGER;

BEGIN
   cur_id := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(cur_id, sql_stmt, DBMS_SQL.native);
   DBMS_SQL.define_column_long(cur_id, 2);
   status := DBMS_SQL.EXECUTE(cur_id);
   status := DBMS_SQL.FETCH_ROWS(cur_id);
   DBMS_SQL.COLUMN_VALUE_LONG(cur_id, 2, 12, 10, v_long, length);
   DBMS_OUTPUT.PUT_LINE('col_long: ' || v_long);
   DBMS_OUTPUT.PUT_LINE('value_length: ' || length);
   DBMS_SQL.CLOSE_CURSOR(cur_id);
END;

Output:
col_long: DefineColumn
value_length: 12

EDB-SPL Procedure successfully completed
← Prev

COLUMN_VALUE_RAW

↑ Up

DBMS_SQL

Next →

COLUMN_VALUE_ROWID