DECLARE
cursor1INTEGER; -- declare a cursor
ignore INTEGER; -- value is meaningless for a SELECT statement
statement VARCHAR2(100); -- holds the SQL statement to be executed
out_val VARCHAR2(100); -- value of the portion of the column
returned
out_length INTEGER; -- length of the portion of the column returned
num_bytes INTEGER := 10; -- length in bytes of the segment of the
column
- value to be selected
offset INTEGER; -- the byte position in the LONG column at which
- the SELECT is to start
num_fetches INTEGER; -- the number of segments returned
row_count INTEGER := 0; -- the number of rows selected
BEGIN
statement := 'SELECT col2 FROM long_table WHERE col1 = 1';
cursor1 := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor1, statement, DBMS_SQL.NATIVE);
/* Define the LONG column. */
DBMS_SQL.DEFINE_COLUMN_LONG(cursor1, 1);
/* Execute the query. */
ignore := DBMS_SQL.EXECUTE(cursor1);
/* Fetch the rows in a loop. Exit when there are no more rows. */
LOOP
IF DBMS_SQL.FETCH_ROWS(cursor1) > 0 THEN
row_count := row_count + 1;
offset := 0;
num_fetches := 1;
DBMS_OUTPUT.PUT_LINE('Row ' || row_count || ' fetched.');
/* Get the value of the LONG column piece by piece. Here a loop
is used to get the entire column. The loop exits when there
is no more data. */
LOOP
/* Get the value of a portion of the LONG column. */
DBMS_SQL.COLUMN_VALUE_LONG(cursor1, 1, num_bytes, offset,
out_val, out_length);
IF out_length != 0 THEN
DBMS_OUTPUT.PUT_LINE('Fetch ' || num_fetches || ': ');
DBMS_OUTPUT.PUT_LINE('Value Fetched = ' || out_val);
DBMS_OUTPUT.PUT_LINE('Length of fetched segment= ' ||
out_length);
offset := offset + num_bytes;
num_fetches := num_fetches + 1;
ELSE EXIT;
END IF;
IF out_length < num_bytes THEN EXIT;
END IF;
END LOOP;
ELSE EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE('--------------------------------');
END LOOP;
DBMS_SQL.CLOSE_CURSOR(cursor1);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm);
IF dbms_sql.is_open(cursor1) THEN
DBMS_SQL.CLOSE_CURSOR(cursor1);
END IF;
END;
/
"The Views expressed here are my own and not necessarily those of Oracle
Corporation"
Venkatesh Kumar <vkumar_at_fundu.com> wrote in message
news:379A951D.ABDA4C11_at_fundu.com...
> Hi guys,
> We are trying to figure out a way to populate the Long columns in a
table
> using PL/Sql. We have a Web application with a Text area field. The data
in the
> Text area field is to be stored in a long column. However PL/Sl puts limit
on
> the size of a variable ( I think 2000K). I would like to know if there any
other
> solutions to this issue or if any any one has tried other methods using
Pl/Sql.
>
> Thanks
> Venkatesh
>
Received on Sun Jul 25 1999 - 13:05:48 CDT
