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

RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RUA Volunteer2?Tableau 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.