Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQLPLUS - How to query long raw column?
Dwight Long kirjoitti viestissä <3603C708.E0764E21_at_cjnetworks.com>...
>I've got several tables which store data in long columns. How do I query
>and extract long values to a file?
>This doesn't work - it just displays a few lines of garbage.
>select long_result_column from the_table where long_result_id =
>x;
>
The following script helps for long columns. If your long columns include
more data than 1000 bytes then you must increase the v_text value and
add more DBMS_OUTPUT_LINEs, that all the text in the columns are
shown. Change also ALL_VIEWs into the table you want to query from
and text into the name of the long column.
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
v_text VARCHAR2(1000);
v_view VARCHAR2(100) := UPPER('&1');
BEGIN
SELECT text
INTO v_text
FROM ALL_VIEWS
WHERE VIEW_NAME = v_view;
DBMS_OUTPUT.PUT_LINE(SUBSTR(v_text,1,200)); DBMS_OUTPUT.PUT_LINE(SUBSTR(v_text,201,200)); DBMS_OUTPUT.PUT_LINE(SUBSTR(v_text,401,200)); DBMS_OUTPUT.PUT_LINE(SUBSTR(v_text,601,200)); DBMS_OUTPUT.PUT_LINE(SUBSTR(v_text,801,200));EXCEPTION