CODEWITHSUNDEEP
oracle-databaseplsqlview
Yaga
Yaga

Reputation: 125

Converting LONG column to VARCHAR2 into a view

I'm trying to convert a table with a LONG column into a VARCHAR2 column and build a view off that in Oracle 11g. All the other examples I've been reading have shown how to do it in a SELECT query, but never in a CREATE OR REPLACE FORCE VIEW statement.

I can create views and objects under my user profile, and have successfully run a PL/SQL script that transformed the LONG field into a VARCHAR with the following script:

DECLARE
  LONGCOMMENTS LONG;
  VAR2 VARCHAR2(4000);
BEGIN
  SELECT PGX.COMMENTS INTO LONGCOMMENTS FROM USER.PGX 
  WHERE ROWNUM < 100;

  VAR2 := SUBSTR(LONGCOMMENTS, 1, 4000);
  PGX.COMMENTS:=DBMS_OUTPUT.PUT_LINE(VAR2);
END;

However, I can't find a way to store the output into a view. I tried wrapping the above script into a CREATE VIEW but Oracle can't handle it.

Any pointers on how to achieve this?

Upvotes: 1

Views: 4387

Answers (1)

Gary Myers
Gary Myers

Reputation: 35401

It can be interesting to see how Oracle themselves have addressed the problem, as they use LONGs heavily in the data dictionary.

DBA_VIEWS, amongst others, has the columns TEXT (as LONG) and TEXT_VC as VARCHAR2. That is based of an internal view INT$DBA_VIEWS, where the TEXT_VC is derived from a function getlong(1, v.rowid). DBA_ARGUMENTS shows the parameters for that function are an OPCODE and a ROWID.

A trace (or inspired poking around of v$sql) shows that corresponds with the execution of "select text from view$ where rowid=:1" .

So basically they have a a PL/SQL function that selects the column into a varchar2 variable and returns that.

If you don't want to create an independent function, the latest versions of Oracle can include that as part of the view:

create or replace view my_views as
WITH
 FUNCTION view_text(i_name VARCHAR2) RETURN VARCHAR2 IS
   v_ret varchar2(32000);
 BEGIN
   execute immediate 'select text from user_views where view_name = :name' into v_ret using i_name ;
   RETURN substr(v_ret,1,4000);
 END;
select view_name, view_text(view_name) view_text
from user_views
/

Upvotes: 4

Related Questions