How to search LONG column in Oracle using SQL only

I as pretty much everyone started with Googling to figure out the answer.
Sorry to sound negative, absolutely not the point here ... most of the answer are to avoid using LONG as data type in your table structure of most of the obvious reasons, stated by the technical ingenuous'. (NOTE: Honestly, I do not understand all the technicalities, but I did understand it is quite to consume it SQL operations.)

Coming back to, why I needed it or most of us requesting for the answer - (two fold, is my opinion)

1. When the data structure is designed by (gently put- else dictated) and is built-into package you are dealing with, ideally then you left with no choice of changing the DS, but to come to need of quering it.
2. (Plus) a bonus 'need' typically by the technicians cut their hands-off of privilege of writing or running PLSQL blocks. Also, let me confess, you might still not be so sure to get access to run this (as this would mean needing to use an 11g built-in function), given some of insecurities of clientele / the systems you are working with.

Now, I know there could be advocates suggesting, to approach people front on accessing privileges of accommodating the task, the proper way. Pardon my excusing of the path, only to alternatively identify a solution to help.

So, with no further ado

Here it is -

• Now point one premium piece of the puzzle, and I admit this happen to be fairly quickly.

SELECT
    DBMS_XMLGEN.getxml (
        'SELECT t.' || :LONG_COLUMN || ' LONG_COL
         FROM ' || :TABLE_NAME || ' t
         WHERE ' || :SEARCH_COLUMN || ' = 
        ' || :SEARCH_CRITERIA
 )
    xml_text
FROM DUAL

• Covering second base, I had tough luck for a long time, specially when the data contains some of the special characters of XML - like & (ampersand), > (greater than), < (less than) and ' (apostrophe or single quote)
• So, if you know, or running this temporarily you may avoid the next crude way of dealing with this, that is, the way to work with XML special characters.

Now, with 'xml' text, if the objective is to put it to use for comparison directly under SQL in where clause, you got all you need.

Else, if you need to extract the full text output (clean), or compare the content to something within DB, you might need the next section to accomplish that -

Remove the wrapped XML tags to get the clean content. (as far as I am concerned, this was the solution, complete for my end of the problem)

SELECT 
    REPLACE (
        REPLACE (
            xml_text,
            SUBSTR (xml_text,1,
                INSTR (xml_text, '<LONG_COL>') + LENGTH ('<LONG_COL>') - 1),
            ''),
        SUBSTR (xml_text, INSTR (xml_text, '</LONG_COL>'),
            LENGTH (xml_text) ),
        '')
  FROM (SELECT DBMS_XMLGEN.getxml (
      'SELECT t.' || :LONG_COLUMN || ' LONG_COL
         FROM ' || :TABLE_NAME || ' t
         WHERE ' || :SEARCH_COLUMN || ' = '
      || :SEARCH_CRITERIA)
      xml_text
    FROM DUAL)

Receivables - Payment Terms

Oracle Release

12.1.3

 

Setup : Transactions : Payment Terms

 

SELECT RTT.NAME "Name",

       RTT.DESCRIPTION "Description",

       RT.PARTIAL_DISCOUNT_FLAG "Allow Discounts on Part. Pay.",

       RT.PREPAYMENT_FLAG "Prepayment",

       RT.CREDIT_CHECK_FLAG "Credit Check",

       ACBCT.CYCLE_NAME "Billing Cycle",

       RT.BASE_AMOUNT "Base Amount",

       DECODE (RT.CALC_DISCOUNT_ON_LINES_FLAG, 'N', 'Invoice Amount', FLV_DISC.MEANING) "Discount Basis",

       TO_CHAR(RT.START_DATE_ACTIVE, 'DD-MON-RRRR') || ' - ' ||

       TO_CHAR(RT.END_DATE_ACTIVE, 'DD-MON-RRRR') "Effective Dates",

       RT.PRINTING_LEAD_DAYS "Print Lead Days",

       FLV_INST.MEANING "Installment Options",

       RTL.SEQUENCE_NUM "Seq",

       RTL.RELATIVE_AMOUNT "Relative Amount",

       RTL.DUE_DAYS "Days",

       RTL.DUE_DATE "Date",

       RTL.DUE_DAY_OF_MONTH "Day of Month",

       RTL.DUE_MONTHS_FORWARD "Months Ahead"

  FROM APPS.RA_TERMS_TL            RTT,

       APPS.RA_TERMS_B             RT,

       APPS.AR_CONS_BILL_CYCLES_TL ACBCT,

       APPS.RA_TERMS_LINES         RTL,

       APPS.FND_LOOKUP_VALUES      FLV_DISC,

       APPS.FND_LOOKUP_VALUES      FLV_INST

 WHERE 1 = 1

   AND RT.TERM_ID = RTT.TERM_ID(+)

   AND RTT.LANGUAGE = RTT.SOURCE_LANG

   AND RT.BILLING_CYCLE_ID = ACBCT.BILLING_CYCLE_ID(+)

   AND ACBCT.LANGUAGE(+) = 'US'

   AND RT.TERM_ID = RTL.TERM_ID

   AND RT.CALC_DISCOUNT_ON_LINES_FLAG = FLV_DISC.LOOKUP_CODE(+)

   AND FLV_DISC.LOOKUP_TYPE(+) = 'DISCOUNT_BASIS'

   AND FLV_DISC.LANGUAGE(+) = 'US'

   AND RT.FIRST_INSTALLMENT_CODE = FLV_INST.LOOKUP_CODE(+)

   AND FLV_INST.LOOKUP_TYPE(+) = 'INSTALLMENT_OPTION'

   AND FLV_INST.LANGUAGE(+) = 'US'

   AND RT.LAST_UPDATED_BY NOT IN DECODE(NVL(&EXCLUD_SEEDED, 'N'), 'Y', 1, -999)

 ORDER BY RTT.NAME, RTL.SEQUENCE_NUM

 

Filters

NAME	POSSIBLE VALUE	FOR ALL VALUES
&EXCLUD_SEEDED	Non-seeded values only (i.e. ‘Y’ to exclude)	NULL

 

DB Access

OWNER	OBJECT NAME	OBJECT TYPE	PRIVILEGE
AR	RA_TERMS_TL	TABLE	SELECT
AR	RA_TERMS_B	TABLE	SELECT
AR	AR_CONS_BILL_CYCLES_TL	TABLE	SELECT
AR	RA_TERMS_LINES	TABLE	SELECT
APPLSYS	FND_LOOKUP_VALUES	TABLE	SELECT

 

Piyush Ohri

 

“He who is too busy doing good finds no time to be good.” – Rabindranath Tagore

Order Management - Payment Types

Oracle Release

12.1.3

 

Setup : Orders : Payment Types

 

SELECT HAOU.NAME "Operating Unit",

       OPTT.NAME "Payment Type Name",

       OPTT.DESCRIPTION "Description",

       FLV.MEANING "Payment Type Code",

       ARM.NAME "Receipt Method",

       TO_DATE(OPTA.START_DATE_ACTIVE, 'DD-MON-RRRR') "Start Date",

       TO_DATE(OPTA.END_DATE_ACTIVE, 'DD-MON-RRRR') "End Date",

       OPTA.ENABLED_FLAG "Enabled",

       OPTA.DEFER_PAYMENT_PROCESSING_FLAG "Defer",

       OPTA.CREDIT_CHECK_FLAG "Credit Check"

  FROM APPS.OE_PAYMENT_TYPES_ALL      OPTA,

       APPS.OE_PAYMENT_TYPES_TL       OPTT,

       APPS.FND_LOOKUP_VALUES         FLV,

       APPS.AR_RECEIPT_METHODS        ARM,

       APPS.HR_ALL_ORGANIZATION_UNITS HAOU

 WHERE 1 = 1

   AND OPTA.PAYMENT_TYPE_CODE = OPTT.PAYMENT_TYPE_CODE

   AND OPTA.ORG_ID = OPTT.ORG_ID

   AND OPTT.LANGUAGE = OPTT.SOURCE_LANG

   AND OPTA.PAYMENT_TYPE_CODE = FLV.LOOKUP_CODE

   AND FLV.LOOKUP_TYPE = 'OE_PAYMENT_TYPE'

   AND FLV.LANGUAGE = FLV.SOURCE_LANG

   AND OPTA.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID(+)

   AND OPTA.ORG_ID = HAOU.ORGANIZATION_ID

   AND OPTA.LAST_UPDATED_BY NOT IN DECODE(NVL(&EXCLUD_SEEDED, 'N'), 'Y', 1, -999)

   AND HAOU.NAME IN

       (SELECT DISTINCT NVL(PARAM_LIST, NAME)

          FROM (SELECT TRIM(REGEXP_SUBSTR(PARAMETER, '[^,]+', 1, LEVEL)) PARAM_LIST, NULL

                  FROM (SELECT TRIM(DECODE(&OPERATING_UNITS, 'ALL', NULL, &OPERATING_UNITS)) PARAMETER

                          FROM DUAL) T

                CONNECT BY REGEXP_SUBSTR(PARAMETER, '[^,]+', 1, LEVEL) IS NOT NULL) PARAM_TBL,

               (SELECT NULL, NAME FROM APPS.HR_ALL_ORGANIZATION_UNITS) BASE_TBL)

 ORDER BY HAOU.NAME, OPTT.NAME

 

Filters

NAME	POSSIBLE VALUE	FOR ALL VALUES
&EXCLUD_SEEDED	Non-seeded values only (i.e. ‘Y’ to exclude)	NULL
& OPERATING_UNITS	Comma separated list, enclosed in single quotes for e.g. ‘OU1, OU2’	NULL

 

DB Access

OWNER	OBJECT NAME	OBJECT TYPE	PRIVILEGE
ONT	OE_PAYMENT_TYPES_ALL	TABLE	SELECT
ONT	OE_PAYMENT_TYPES_TL	TABLE	SELECT
APPLSYS	FND_LOOKUP_VALUES	TABLE	SELECT
AR	AR_RECEIPT_METHODS	TABLE	SELECT
HR	HR_ALL_ORGANIZATION_UNITS	TABLE	SELECT

 

Piyush Ohri

 

“You can’t get much done in life if you only work on the days when you feel good.” – Jerry West

Order Management - System Parameters

Oracle Release

12.1.3

 

Setup : System Parameters

 

SELECT HAOU.NAME "Operating Unit"

       ,OSPDT.NAME "Parameter"

       ,OSPA.PARAMETER_VALUE "Value"

FROM   APPS.OE_SYS_PARAMETERS_ALL   OSPA

      ,APPS.OE_SYS_PARAMETER_DEF_TL OSPDT

      ,APPS.HR_ALL_ORGANIZATION_UNITS HAOU

WHERE  1 = 1

AND    OSPA.PARAMETER_CODE = OSPDT.PARAMETER_CODE

AND    OSPDT.LANGUAGE = 'US'

AND    HAOU.ORGANIZATION_ID (+) = OSPA.ORG_ID

AND    HAOU.NAME IN (

SELECT DISTINCT NVL(PARAM_LIST, NAME)

  FROM (SELECT TRIM(REGEXP_SUBSTR(PARAMETER, '[^,]+', 1, LEVEL)) PARAM_LIST, NULL

          FROM (SELECT TRIM(DECODE(:OPERATING_UNITS, 'ALL', NULL,:OPERATING_UNITS) )PARAMETER FROM DUAL) T

        CONNECT BY REGEXP_SUBSTR(PARAMETER, '[^,]+', 1, LEVEL) IS NOT NULL) PARAM_TBL,

       (SELECT NULL, NAME FROM APPS.HR_ALL_ORGANIZATION_UNITS) BASE_TBL)

ORDER  BY HAOU.NAME

         ,OSPDT.NAME

 

Filters

NAME	POSSIBLE VALUE	FOR ALL VALUES
: OPERATING_UNITS	Comma separated list, enclosed in single quotes for e.g. ‘OU1, OU2’	NULL

 

DB Access

OWNER	OBJECT NAME	OBJECT TYPE	PRIVILEGE
ONT	OE_SYS_PARAMETERS_ALL	TABLE	SELECT
ONT	OE_SYS_PARAMETER_DEF_TL	TABLE	SELECT
HR	HR_ALL_ORGANIZATION_UNITS	TABLE	SELECT

 

Enhancement

Please help answer the two questions below –

·         Did you use this SQL in anyone of the listed EBS releases, if not mention the release you tried it on?

·         Was any information missing or incorrect from that available on front-end of your release? Please specify release # and missing information details.

 

Piyush Ohri

 

“Clouds come floating into my life, no longer to carry rain or usher storm, but to add color to my sunset sky.” - Rabindranath Tagore