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

Working with the code below how would one create a substitution variable that would allow me to
1. Pass a user entered variable with a prompt on runtime
2. Ask the Query to look for a specific group of tables say that start with 'A%'   and only pick from the long list of tables that start with 'JAS.A'  
3. Then I can enter a record to search for ' Insulation '
4. Will I have to show the whole list of 905 tables in the code so that Oracle know them or would I have to manually enter those myself showing only the tables starting with JAS.A%
5. Then of course after that run show the next variable to prompt for tables that start with 'B%' ..... then Search For Record ' Sub-Contractor' ....
6. Then next prompt "after the run of the last search".....'C%' Search Term 'Cleanup' ........so forth and so on.....

Am I thinking in the right frame of mind or way off...? The story of my life.


Select * From JAS.ACCOUNTS  ;
Select * From JAS.ADDRESSES ;
/* and 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 ;

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
/

Open in new window

Fletcher BurdineTableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?
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:
I'm not sure I'm following.  We covered basic substitution variables in a previous question:  The '&'.

I also provided you a basic function where you can pass in a string and it returns a 'table' that you can select from.

I would go with the function and just keep adding input parameters to it.
0
Mark GeerlingsDatabase AdministratorCommented:
"Substitution variables" in Oracle usually mean you are referring to a *.SQL script that will be processed by SQL*Plus, SQL Developer, TOAD or some other tool/utility that connects to Oracle databases and executes SQL code.  I don't know if that is what you are trying to describe, or if you may want to consider using or including PL\SQL, Java or some other option that may add some more flexibility.

I'm not sure what value the "EXTRACTVALUE( DBMS_XMLGEN.getxmltype(..." portion of your query adds.  It seems to me like you could remove that and simply add this line to the "where" clause after "and table_name like 'JOB%'":
and column_name = '6328'
Actually, I think you can remove the subquery, and just use a simple, single-level then with a "where" clause like that.  Or, maybe I'm missing some value that your multi-level query gives you?

Does this part of your query actually work without returning an error: "to_char(' || column_name || ')"?  I would expect Oracle to not like that very much.
0
slightwv (䄆 Netminder) Commented:
>>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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
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...?
This is a way in Crystal Reports to enter a parameter where the user picks the value to search for.
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
/

Open in new window

0
slightwv (䄆 Netminder) Commented:
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.
0
Mark GeerlingsDatabase AdministratorCommented:
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.
0
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
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%'.
0
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
My thought is if you click this Green Arrow Running the Query Buttonto 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.
I this possible to make changes in the code for future users and myself?
0
slightwv (䄆 Netminder) Commented:
Prompting isn't a universal thing mandated by connecting to an Oracle Database.

Prompting for substitution variables is a the discretion of the tool itself.  It all goes back to sqlplus and it's capabilities.

It is 100% up to the developer of the tool if they want to honor what sqlplus does.

I've posted the sqlplus docs for them but I'll post them again:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqpug/using-scripts-in-SQL-Plus.html#GUID-68AC9FF2-B92A-48D1-9699-133D47F8DDC1

For end users, you don't want to install PL/SQL Developer on everyone's machine, right?  Then you cannot rely on PL/SQL Developer prompting them.

You also probably don't want to install sqlplus on all the users machines and give them direct access to the database.  So, you will need some other tool/product to do what you want.


All that said:  If PL/SQL Developer prompts for values using '&' then something like:
select ...
from ...
where owner ='JAS' and table_name like '&TABLE_TO_FIND%'
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
Mark GeerlingsDatabase AdministratorCommented:
You have obviously found a GUI program of some kind that you are calling "PL/SQL Developer".  You may have figured out by now that most of us aren't clear which tool that is.  I've never heard of an Oracle tool with that name.  I know of "SQL Developer" which is a free GUI tool from Oracle that can submit SQL and/or PL\SQL statements to an Oracle database.  That tool can use "substitution variables" as you've described them in SQL statements (just like SQL*Plus can do).  Substitution variables are not normally used in PL\SQL procedures.  These usually accept input parameters.  

Oracle's SQL Developer tool though is primarily for programmers and DBAs.  That is not intended to be an end user tool for most users.
0
slightwv (䄆 Netminder) Commented:
>>now that most of us aren't clear which tool that is

I know what it is:  https://www.allroundautomations.com/plsqldev.html
0
Geert GOracle dbaCommented:
if you want to do all you actually want, why not program it yourself ...
personally, i find it easier to step out of sql,pl/sql and get into Delphi (or any programming environment)

you can take much more decisions based on data/structures in a programming environment than you can in a database system
allthough oracle is narrowing the gap a lot lately
0
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
Yeah sorry guys you got the Freshman Rookie here. This is PL/SQL Developer.
Tool that works really well querying the data. Nice features I am just learning. It is like anything else in learning new thing unfamiliar, a challenge!
This is really helpful from all this support.
0
Mark GeerlingsDatabase AdministratorCommented:
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.
0
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
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?
0
slightwv (䄆 Netminder) Commented:
The docs are your friend.  I provided the link above.

The '&' defines a substitution variable so &TABLE_TO_FIND is a variable.  If can easily be &purple_people_eater.

The '%' is a wildcard to mean any table that starts with the string you provided in the variable.

Again:  It is up to the tool running the SQL to decide if it will support the sqlplus substitution variables.

If you want uses to be able to run the SQL, you need to provide them a tool that executes SQL and will also honor the substitution variables.
0
Mark GeerlingsDatabase AdministratorCommented:
I expect that you should be able to provide a value like:
     A%  
or:
     B%
or
    JAS.A%
and that your PL\SQL Developer tool (that I've never seen before) will execute your query then for table names that start with the patterns you provide.

Note that since your query contains the substitution variable between single quotes, you don't need to provide single quotes when prompted.  You can just provide the value you want the query to look for.

And yes, the "%" character is the multi-character wild-card character in Oracle, so for 'A%', Oracle should find any/all table names that start with "A".

Oracle syntax allows us to also provide that wild-card character in the leading position like this: "%A%", but that has two significant differences compared to supplying that in the trailing position ('A%') only:
1. Then Oracle will find any/all tables names that contain the letter "A" anywhere in the name.
2.  The performance may be much slower, because then Oracle cannot use an "index range scan" to find the matching values efficiently.
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.

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.