Which would be the best most efficient Manual Entries, Substitution Variables or Bind Variables?

If the following code allows me to create a variable to search a range of the listed tables that start with some specific character(s) Example ITEM_
I am searching through 3 options for the best solution. Option:
1. The variable prompt '&TABLE_TO_FIND%' Is there a value like ' * ' the asterisk which would allow me to search all of the listed tables?  Like the term "All_tab_cols" when looking at a tables columns? is there an ALL_TABLES when searching with a Substitution Variable that points to the tables?
2. I could put the values in manually but it is time consuming and a constant state of back and forth of copy paste.
3. Would be to have all the tables in the Query Code (See partial portion below) and have the PL/SQL Developer Tool select only the tables I need ignoring the rest. Which I think is the Oracle Bind Variable?  I think? My problem is figuring out all the terms that make a Bind Variable up and how it will relate back to what I need. Any examples would be helpful.

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 || ') = ''Clean Up'' and rownum=1'
      ),
      '/ROWSET/ROW/X'
    ) count
  from All_tab_cols
  where owner ='JAS' and table_name like '&TABLE_TO_FIND%'
  order by table_name, column_name
)
where count=1
/

Open in new window

Fletcher BurdineTableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
>>Is there a value like ' * ' the asterisk which would allow me to search all of the listed tables?

'%' is a multiple character wild card.  It matches everything:
select table_name from all_tables where table_name like '%';
is the same as
select table_name from all_tables;

>>is there an ALL_TABLES when searching with a Substitution Variable that points to the tables?

Try it and see but it isn't what you want:
SQL> desc all_tables
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 OWNER                                     NOT NULL VARCHAR2(128)
 TABLE_NAME                                NOT NULL VARCHAR2(128)
 TABLESPACE_NAME                                    VARCHAR2(30)
... many more columns

Open in new window



>>select only the tables I need ignoring the rest

How do you know what you need?

There is also an IN list that can hold 1000 entries:

from All_tab_cols
  where owner ='JAS' and table_name in ('ITEMS','LABOR_LOGS') 

Open in new window


Just add to the list.
0
 
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
That is perfect. It is embarrassing but I know what a wildcard is in other programs and understood the way you used it earlier. I just was not confident that if using it for ALL Tables would work. Good to know as is the selection of the three separate.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.