Skip to main content
๐Ÿ““ Tibero Online Manual Click here ๐Ÿ““

Search

Welcome to Tibero GTS!

How to Extract PSM Source

Document Type | Technical Information

Category | Administration

Applicable Product Versions | Tibero5, Tibero6, Tibero7

Document Number | TADTI152

 

Overview

This document describes a method to bypass and retrieve PSM source content when direct retrieval is not possible due to restrictions.
 
 

Method

1. Views to Retrieve PSM Source

The content of PSM source can be retrieved through the following views.
  1. dba_source
  2. user_source
  3. all_source
 
From Tibero6 version and above, the Text column in these views is VARCHAR, allowing PSM source content to be retrieved without issues. 
 
However, for Tibero5 version and below, the Text column is of Long type, and due to limitations of the Long type, applying conditions in searches on PSM source content causes a TBR-8063 error.
 
VersionTEXT Column type
Tibero6 and aboveVARCHAR
Tibero5 and belowLONG
 

2. Testing

2.1 Tibero6 and above

  • View Information
SQL> desc dba_source

COLUMN_NAME                              TYPE               CONSTRAINT
---------------------------------------- ------------------ --------------------
OWNER                                    VARCHAR(128)
NAME                                     VARCHAR(128)
TYPE                                     VARCHAR(12)
LINE                                     NUMBER
TEXT                                     VARCHAR(4000)

 

  • Querying the text column
SQL> SELECT * FROM dba_source WHERE text LIKE '%SELECT%';

OWNER      NAME       TYPE               LINE TEXT
---------- ---------- ------------ ---------- ------------------------------------------------------------
SYS        DBMS_FGA   PACKAGE              56         statement_types     IN VARCHAR2 DEFAULT 'SELECT',
SYS        HTF        PACKAGE             249   function FORMSELECTOPEN(CNAME       in varchar2,
SYS        HTF        PACKAGE             253   function FORMSELECTOPTION(CVALUE      in varchar2,
SYS        HTF        PACKAGE             254                              CSELECTED   in varchar2 default

...(omitted)...

It can be confirmed that the retrieval works normally.

 

 

2.2 Tibero5 and below

  • View Information
SQL> desc dba_source

COLUMN_NAME                              TYPE               CONSTRAINT
---------------------------------------- ------------------ --------------------
OWNER                                    VARCHAR(128)
NAME                                     VARCHAR(128)
TYPE                                     VARCHAR(12)
TEXT                                     LONG

 

  • Querying the text column
SQL> SELECT * FROM dba_source WHERE text LIKE '%SELECT%';
TBR-8063: LONG columns are not permitted.
at line 1, column 33:
SELECT * FROM dba_source WHERE text LIKE '%SELECT%'
                               ^

As shown above, applying a LIKE condition on a Long type column causes an error due to restrictions, so Long type columns cannot be used in WHERE clause conditions.

 

Note: TBR-8063

/*
* err:   -8063
* name:  ERROR_DML_LONG_NOT_PERMITTED
* desc:   LONG columns are not permitted.
* cause:  LONG type columns cannot be used.
* action: Modify the query.;
*/

 

3. Workarounds

3.1 Using the LONG_TO_CHAR Function

 

Use the LONG_TO_CHAR function to apply conditions on the TEXT column and search. 

  • Example Usage
SELECT * FROM dba_source WHERE LONG_TO_CHAR(text) LIKE '%FROM%';

Caution

However, this function reads up to 4000 bytes of LONG data and converts it to VARCHAR2 type, so it is not possible to search data beyond 4000 bytes.

 

 

3.2 Migrating TEXT Column (LONG Type) Data to CLOB Type

Create a temporary table with a CLOB type column and migrate the TEXT column (LONG type) data into this temporary table. 

Since CLOB type allows WHERE clause condition searches, apply conditions on the TEXT column (CLOB type) in the temporary table and perform searches.

  • Example Usage
#1. Create temporary table
CREATE TABLE temp_dba_source
 ( OWNER VARCHAR(128),
   NAME VARCHAR(128),
   TYPE VARCHAR(12),
   TEXT CLOB) TABLESPACE usr;
 

INSERT INTO temp_dba_source SELECT OWNER, NAME, TYPE, TO_LOB(TEXT) FROM dba_source;



#2. Query data from temporary table
SELECT TEXT FROM temp_dba_source WHERE TEXT LIKE '%MD5%';

 

Note: Restrictions on LONG Type

1) A table can only have one column of type long.

2) Cannot be used in WHERE clause conditions.

3) Cannot be used with group by, order by, connect by.

4) Cannot be used in queries with distinct, unique, intersect, minus, union.

5) Cannot be used with SQL functions such as substr.

6) Cannot be used in create table ... as select statements.

7) Cannot be used in alter table ... move tablespace statements.

8) Can store data up to 2G bytes.

9) Cannot be used as an index key.

 

 

Related to