asked on

Rules for embedding code in SQL in just the right place. PL/SQL Developer for Oracle.

Based on the question and response in the LINK below. I had an Expert be so kind as to help me with what I need. So I have been working the last 2 hours on how to embed this inside of the existing code in order to get the desired result seen here.
Blob fields and how to manage them when they throw an error.
I need to embed the code that I think identifies the type of data (DATA_TYPE) to search for. My concern is where do I embed it? IF there are rules somewhere or "best practices" on the when and where rules on how to embed code so that it runs in the right order without throwing error messages. If anyone knows I am all ears...?

[b][i]
/*IF THIS IS THE CORRECT CONTENT I NEED TO INSERT .... NOT 100% SURE?*/
CASE 
WHEN main.DATA_TYPE = 'VARCHAR2' 
	AND val.IsValueNumeric = 0 
		THEN main.COLUMN_NAME || ' = ''' || val.SearchValue || ''''
               
WHEN main.DATA_TYPE = 'NUMBER' 
	AND val.IsValueNumeric = 1 
		THEN main.COLUMN_NAME || ' = ' || val.SearchValue  
        ELSE ''
END

/* I think this statement has to go in the WHERE clause in the Second Section.
AND   main.DATA_TYPE IN ( 'VARCHAR2','NUMBER' )[/i][/b]

*/ Information above needs to go into this Second Section of code below*/

Select * From JAS.JOBS  ;
Select * From JAS.JOB_BLOCKS  ;
Select * From JAS.JOB_CONTACTS  ;
Select * From JAS.JOB_CONTACT_DIVISIONS ;
Select * From JAS.JOB_CPR_CLASSES ;
Select * From JAS.JOB_DETAILS ;
Select * From JAS.JOB_DETAIL_REC_TYPES  ;
Select * From JAS.JOB_EXPENSES  ;
Select * From JAS.JOB_GROUPS  ;
Select * From JAS.JOB_ITEM_PRICING  ;
Select * From JAS.JOB_LABOUR  ;
Select * From JAS.JOB_OVR_PROFILES  ;
Select * From JAS.JOB_REPORTING_CLASSES ;
Select * From JAS.JOB_REP_CLASSES ;
Select * From JAS.JOB_REQUESTS  ;
Select * From JAS.JOB_REQUEST_DETAILS ;
Select * From JAS.JOB_REQUEST_DOCUMENTS ;
Select * From JAS.JOB_REQUEST_REC_TYPES ;
Select * From JAS.JOB_REQUEST_SETUP ;
Select * From JAS.JOB_SECTIONS  ;
Select * From JAS.JOB_STATUSES  ;
Select * From JAS.JOB_SUBCONTRACTS  ;
Select * From JAS.JOB_SUBCON_DETAILS  ;
Select * From JAS.JOB_SUPPLIERS ;
Select * From JAS.J_ITEMS ;
Select * From JAS.J_ITEM_GROUPS ;
Select * From JAS.J_ITEM_LABOUR ;
Select * From JAS.J_MAT_EPA ;
Select * From JAS.J_MAT_EPA_LOGS  ;
Select * From JAS.J_MAT_LISTS ;
Select * From JAS.J_MAT_LIST_DETAILS  ;
Select * From JAS.J_OVERHEAD_CHART  ;
Select * From JAS.LABOR_LOGS  ;
Select * From JAS.LABOR_LOG_ERRORS  ;
Select * From JAS.LABOR_LOG_FILE_STYLES ;
Select * From JAS.LABOR_LOG_MAPPINGS  ;
Select * From JAS.LABOR_TIME_LOGS ;
Select * From JAS.LABOR_TIME_LOG_MOBILE_HISTORY ;
Select * From JAS.LABOR_TIME_LOG_SETUP  ;
Select * From JAS.LABOR_TIME_LOG_STAGE_DATA ;
Select * From JAS.LABOUR_ADDITIONAL_CODES ;
Select * From JAS.LABOUR_ANALYSIS ;
Select * From JAS.LABOUR_CODES  ;
Select * From JAS.LABOUR_CODES_PRV_SETUP  ;
Select * From JAS.LABOUR_COMPARISONS  ;
Select * From JAS.LABOUR_RATES  ;
Select * From JAS.LAB_GROUPS  ;


select table_name,column_name from (
  select table_name,
    column_name,
    EXTRACTVALUE(
      DBMS_XMLGEN.getxmltype(
        'select count(*) X from ' || table_name || ' where to_char(' || column_name || ') = ''Insulation'' and rownum=1'
      ),
      '/ROWSET/ROW/X'
    ) count
  from All_tab_cols
  where owner ='JAS' and table_name like '%' 
  order by table_name, column_name
)
where count=1
/

Open in new window

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.