CODEWITHSUNDEEP
databaseoracle-databasetoad
k21Anaiyalate
k21Anaiyalate

Reputation: 11

reading widememo valus in oracle toad

I have the following query

SELECT A.COLUMN_NAME AS COLUMN_NAME_1_ON_2,
       UC.CONSTRAINT_NAME AS NAME_1_CONSTRAINTS_2_,
       UC.SEARCH_CONDITION AS CONDITION
  --,UC2.TABLE_NAME AS REFERENCE_TABLE
  FROM USER_CONSTRAINTS UC
       JOIN ALL_CONS_COLUMNS A ON A.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
       LEFT JOIN USER_CONSTRAINTS UC2
          ON UC.R_CONSTRAINT_NAME = UC2.CONSTRAINT_NAME

This query SEARCH_CONDITION condition of the constraint but it is coming as wide memo and not being being read by my code. Earlier In toad 11.5 I used the SEARCH_CONDITION_VC which gave the proper result, but now when I run this column it gives the following error

[Error] Execution (2: 2): ORA-00904: "UC2"."SEARCH_CONDITION_VC": invalid identifier

How do I get use the result of the search condition in oracle toad 13.4?

Upvotes: 1

Views: 1004

Answers (1)

Littlefoot
Littlefoot

Reputation: 142958

It isn't about TOAD version, but database version.

I presume you're connected to a database whose version is lower than 12c because - in those versions, there's no SEARCH_CONDITION_VC column.

See the difference:

SQL> select banner from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> desc user_constraints;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 OWNER                                                          VARCHAR2(128)
 CONSTRAINT_NAME                                       NOT NULL VARCHAR2(128)
 CONSTRAINT_TYPE                                                VARCHAR2(1)
 TABLE_NAME                                            NOT NULL VARCHAR2(128)
 SEARCH_CONDITION                                               LONG
 SEARCH_CONDITION_VC                                            VARCHAR2(4000)    --> here it is
 R_OWNER                                                        VARCHAR2(128)
 R_CONSTRAINT_NAME                                              VARCHAR2(128)
 DELETE_RULE                                                    VARCHAR2(9)
 STATUS                                                         VARCHAR2(8)
 DEFERRABLE                                                     VARCHAR2(14)
 DEFERRED                                                       VARCHAR2(9)
 VALIDATED                                                      VARCHAR2(13)
 GENERATED                                                      VARCHAR2(14)
 BAD                                                            VARCHAR2(3)
 RELY                                                           VARCHAR2(4)
 LAST_CHANGE                                                    DATE
 INDEX_OWNER                                                    VARCHAR2(128)
 INDEX_NAME                                                     VARCHAR2(128)
 INVALID                                                        VARCHAR2(7)
 VIEW_RELATED                                                   VARCHAR2(14)
 ORIGIN_CON_ID                                                  NUMBER

SQL>

SQL> select banner from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SQL> desc user_constraints;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ----------------------------------
 OWNER                                                          VARCHAR2(30)
 CONSTRAINT_NAME                                       NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE                                                VARCHAR2(1)
 TABLE_NAME                                            NOT NULL VARCHAR2(30)
 SEARCH_CONDITION                                               LONG            --> no SEARCH_CONDITION_VC column
 R_OWNER                                                        VARCHAR2(30)
 R_CONSTRAINT_NAME                                              VARCHAR2(30)
 DELETE_RULE                                                    VARCHAR2(9)
 STATUS                                                         VARCHAR2(8)
 DEFERRABLE                                                     VARCHAR2(14)
 DEFERRED                                                       VARCHAR2(9)
 VALIDATED                                                      VARCHAR2(13)
 GENERATED                                                      VARCHAR2(14)
 BAD                                                            VARCHAR2(3)
 RELY                                                           VARCHAR2(4)
 LAST_CHANGE                                                    DATE
 INDEX_OWNER                                                    VARCHAR2(30)
 INDEX_NAME                                                     VARCHAR2(30)
 INVALID                                                        VARCHAR2(7)
 VIEW_RELATED                                                   VARCHAR2(14)

SQL>

What to do, then? Create a function which will return a string out of the "original" search_condition. Something like this:

SQL> CREATE OR REPLACE FUNCTION f_long (par_con_name IN VARCHAR2)
  2     RETURN VARCHAR2
  3  IS
  4     l_col  LONG;
  5  BEGIN
  6     SELECT search_condition
  7       INTO l_col
  8       FROM user_constraints
  9      WHERE constraint_name = par_con_name;
 10
 11     RETURN SUBSTR (l_col, 1, 4000);
 12  END;
 13  /

Function created.

SQL> SELECT f_long ('SYS_C0085257') FROM DUAL;

F_LONG('SYS_C0085257')
--------------------------------------------------------------------------
"OIB" IS NOT NULL

SQL>

Upvotes: 1

Related Questions