Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: SQLPLUS - How to query long raw column?

Re: SQLPLUS - How to query long raw column?

From: Rauno Seppanen <rauno.seppanen_at_icon.fi>
Date: Sun, 20 Sep 1998 23:59:07 +0300
Message-ID: <6u3li4$p7u$1@hiisi.inet.fi>

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
  WHEN NO_DATA_FOUND THEN
   DBMS_OUTPUT.PUT_LINE('View not found!'); END;
/
>sqlplus help on 'long' and 'raw' doesn't give a good example of how to
>use them in a query.
>
>
>
>
Received on Sun Sep 20 1998 - 15:59:07 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US