Re: Displaying LONG column via DBMS

From: Rauno Seppanen <rauno.seppanen_at_icon.fi>
Date: Tue, 10 Aug 1999 06:56:56 GMT
Message-ID: <Y2Qr3.32$st5.195@read2.inet.fi>

Robert C. Rogers, Jr. kirjoitti viestissä <37AF9BE3.1C9CC988_at_mindspring.com>...
>
>I am trying to create a PL/SQL script that will, in part, generate a
>VIEW creation script for a specified schema. As such, I am attempting
>to create a cursor that cycles thru ALL_VIEWS and prints the TEXT field
>to he screen. DBMS_OUTPUT.PUT() expects a VARCHAR2 argument...the TEXT
>column is a LONG datatype. Is there a way to display such a column via
>DBMS_OUTPUT (or any other way) in a PL/SQL block?
>
>Thanks in advance.
>Robert.

Hi
There at least two solutions for your problem. 1: in SQLPLUS you can do
set long 2000
set heading off
select text from all_views where view_name = 'YOUR_VIEW_NAME';

and by changing it into dynamic sql you can do it from pl/sql block.

2: in pl/sql you can also do something like following (this isn't dynamic sql)

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;
/

Hope this helps you
Rauno Received on Tue Aug 10 1999 - 01:56:56 CDT

Re: Displaying LONG column via DBMS_OUTPUT