How to pass the Substitution variables where to nest them and is my thought process on par or way off?

>>I'm not sure what value the "EXTRACTVALUE( DBMS_XMLGEN.getxmltype(..." portion of your query adds.

This is a series of questions that at the beginning is:  Search all tables and columns for a value when you don't know what table or column it exists in.

I thought substitution variables were a way to prompt the user with a simple  input window. Let me explain what I thought substitution variables were based on all I read in the PL/SQL Help documentation. I did not get that the '&' was a substitution variable.....I thought that is where you would put one or rather name one. I thought at run time it would actually prompt the user. The way i relate to it is in Crystal Reports. The first report development tool I ever learned. I thought this was more like a substitution variable. A way for a user to pick a value without actually coming in contact with the actual code...?

Since the code you helped design needs a value to search for and I need to have the tables I am searching for in there when it is running my question is :
Can I just post all the tables ....all 905 of them..... in the code and leave them or I get a weird error when I do. Not sure if it is related to the code or an internal security issue. Researching that and having to wait on administrators to get back to me.
Do I have to put the only the tables I am searching on in the code. In the instance of searching through the JOB tables seen below

Select * From JAS.ACCOUNTS  ;
Select * From JAS.ADDRESSES ;
/* and all 901 other tables in between the line above and line below*/
Select * From JAS.WIP_EXPORT_DETAILS  ;
Select * From JAS.WORK_ORDER_LABOUR_CODES ;
Select * From JAS.XLSX_TEXT ;

OR OR OR OR does the code only allow "the tables" I am searching for in the code. Like one search group at a time? That was why I asked above could I search on all tables that start with 'A&', then 'B&' then 'CA&' then 'D&' then 'E&' ...etc

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  ;

THEN THE REST OF THE CODE CAN RUN....? I ask

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

Select allOpen in new window

It is a substation variable but only for tools that recognize sqlplus instructions.

They still have to have some program to run the code since SQL is scripted/interpreted.

If you want compiled code, then use the function in the other question but then you don't have substitution variables since that is a tool thing.

Please confirm that when you say PLSQL you mean the "PLSQL Developer" GUI tool.

Yes, substitution variables [in Oracle SQL queries] are a way to prompt the user to provide a value to the query to search for, in an interface (like SQL*Plus, SQL Developer or TOAD) that processes Oracle SQL statements.  But these are not PL\SQL.  SQL and PL\SQL are not synonyms.

So what I think you are saying is I cannot prompt the user with an input window as shown above to search through this database at least with the PL/SQL Developer with a Substitution Variable. I personally do not mind putting in the value but if it is possible to put a simple window to insert a value that would make future searches easier. I would welcome the knowledge on how to do that if it is actually possible in PL/SQL DEV?

Also did not hear back on whether I have to put the specific tables only in or can I put all 905 tables in whether the code below is searching on specifically named tables.....like 'JOB%'.

My thought is if you click this Green Arrow to run the code that PL/SQL Developer will give me a prompting window to enter the value I would like to modify as the substitution variable? So instead of  ......table_name like 'JOB&' I could change with a Prompt Window that generates for at each running of the code....the Green Arrow...it to table_name like 'A&', 'B&', 'C&' etc. Maybe I do not even need the Ampersand just the letter.
What do I put in the PL/SQL to make that happen.

So I would look something like this every time I click the Green Arrow Button.

Yeah sorry guys you got the Freshman Rookie here. This is PL/SQL Developer.

This is really helpful from all this support.

That may be a good tool for Oracle developers and DBAs but that doesn't look like a good tool to plan to deploy to most users.

it is for whoever remotes into the server I am working on. Not for a group. I am setting this up for ease of use. I am now trying to determine if the code :
 If PL/SQL Developer prompts for values using '&' then something like:
select ...
from ...
where owner ='JAS' and table_name like '&TABLE_TO_FIND%'

Is the term inside of  '& .....%'  '&TABLE_TO_FIND%' an actual function
or is TABLE_TO_FIND a variable I will have to declare
or is it simply the text I have to enter myself that is simply the name of the Table I want to Find...?

Hopefully the statements I make are the correct form and syntax needed for clarity?