Link to home
Start Free TrialLog in
Avatar of RUA Volunteer2?
RUA Volunteer2?Flag for United States of America

asked on

Multiple errors when searching records. Blob, inconsistencies, XML and others. Stack Error:

I get the following errors a lot when searching for records in the Oracle SQL database with PL/SQL Developer.
Not sure where to even start with 5 different error codes. Does this mean 5 separate issues to correct before the code can do its job?
User generated image
I looked up blob field. It sounds like I need to address some issue before searching for a word like ' Clean Up '. I do not know if PL/SQL Developer does not like the space between the words or if the tables themselves have to have a special modification for Blob fields. I know that they are like large Comment fields with large numbers of character spaces. 500-1000+
Any thoughts on the error?
Error Stack Message : /* Source of PACKAGE BODY SYS.DBMS_XMLGEN is not available */

Select * From JAS.ITEMS ;
Select * From JAS.ITEM_CHARGES  ;
Select * From JAS.ITEM_CHARGE_SETUP ;
Select * From JAS.ITEM_COMMISSIONS  ;
Select * From JAS.ITEM_COSTS  ;
Select * From JAS.ITEM_DIMENSIONS ;
Select * From JAS.ITEM_DIMENSION_PACKAGES ;
Select * From JAS.ITEM_LABOUR_GROUPS  ;
Select * From JAS.ITEM_LAB_RATES_CODES_FORMULAS ;
Select * From JAS.ITEM_MANUFACTURER_DESCRIPTIONS  ;
Select * From JAS.ITEM_TYPES  ;
Select * From JAS.ITEM_TYPE_FORMULAS  ;
Select * From JAS.ITEM_TYPE_GROUPS  ;
Select * From JAS.ITEM_TYPE_LABOUR_PERCENT  ;
Select * From JAS.ITEM_TYPE_PROPERTIES  ;
Select * From JAS.ITEM_USAGE  ;
Select * From JAS.I_CATEGORIES  ;
Select * From JAS.I_DEPTHS  ;
Select * From JAS.I_DIMENSIONS  ;
Select * From JAS.I_GAUGES  ;
Select * From JAS.I_LENGTHS ;
Select * From JAS.I_LINKED_ITEMS  ;
Select * From JAS.I_PROPERTIES  ;
Select * From JAS.I_RATES ;
Select * From JAS.I_R_VALUES  ;
Select * From JAS.I_TYPES ;
Select * From JAS.I_TYPE_GROUPS ;
Select * From JAS.I_TYPE_PROPERTIES ;
Select * From JAS.I_WIDTHS  ;
Select * From JAS.JAVA$CLASS$MD5$TABLE  ;
Select * From JAS.JD_BUDGETS  ;
Select * From JAS.JD_DETAIL_GROUPS  ;
Select * From JAS.JD_PRODUCTION_DETAILS ;
Select * From JAS.JD_STATUS_DETAILS ;
Select * From JAS.JD_STATUS_RESOURCES ;
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 || ') = ''Cleanup'' 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
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RUA Volunteer2?

ASKER

Sounds like you and I are thinking alike. If I do not need the blob field.......chances are my info is always going to be in a smaller field type a simple string. So out with the blob and this code does it....... AND   main.DATA_TYPE IN ( 'VARCHAR2','NUMBER' ) ...?

Looks like I need to figure out how to blend that into your code and get them to work together?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

It is pretty much all in the original question you asked that started all the subsequent question and the links I first provided.

"My code" really isn't mine.  I borrowed it from another Expert in those first links and tweaked it.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This is strange. I have closed these questions more than once for several of these questions. I am confused as to why this is happening. I have awarded points and responded. Anyone know why this happens occasionally...?
I closed this last week I have been having upload problems throwing errors and this. I am reporting it as it seems to be unique. file types and sizing on upload were appropriate. Thanks again for the help.