We help IT Professionals succeed at work.
Private
Research Question

Search for a value in a Oracle schema

155 Views
Last Modified: 2020-10-10
Hi,
Can you please provide a query/procedure that will display the table_name, column_name for a particular value.
Meaning , i need to find out , for example , which tables and column is the value 'GCU'  stored, in Oracle schema.

regards
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Welcome to the site!

It will be incredibly slow but see the solution in this previous question:
https://www.experts-exchange.com/questions/28069987/identifying-columns-in-all-the-tables-with-a-specific-value.html#a39001854

I referenced that post in this other question where I also added a complete test case with results:
https://www.experts-exchange.com/questions/29129481/Oracle-query-to-search-all-non-system-tables-for-a-given-value.html#a42756512
Farhad AhmedIt Student

Author

Commented:
i tried the following , which i found online, but it is throwing an error-
DECLARE
  match_count integer;
  v_search_string varchar2(4000) := 'GCU';
BEGIN  
  FOR t IN (SELECT owner, table_name, column_name FROM all_tab_columns) LOOP   
    EXECUTE IMMEDIATE    
      'SELECT COUNT(*) FROM '||t.owner || '.' || t.table_name||
      ' WHERE '||t.column_name||' = :1'   
       INTO match_count  
      USING v_search_string; 
    IF match_count > 0 THEN 
      dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );
    END IF; 
  END LOOP;
END;
/
---
Error report-
ORA-01722: invalid number
ORA-06512: at line 6
ORA-06512: at line 6
01722.  00000  -  "invalid number"
*Cause:    The specified number was invalid.
*Action:     Specify a valid number.


Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
You only want to check character type columns.

Change line 5 to:

FOR t IN (SELECT owner, table_name, column_name FROM all_tab_columns WHERE data_type = 'VARCHAR2') LOOP
There are other data types that you might want to include, but VARCHAR2 is the most common.
Farhad AhmedIt Student

Author

Commented:
Slightwv, Thank you for the response.
However i couldn't understand the information posted from that link you shared.
Can someone please post a query/procedure that will work, instead of sharing links. Appreciate it.

regards
Farhad AhmedIt Student

Author

Commented:
Kent Olsen, Thank you for the update.
I made the change suggested and now i am getting the following error. I guess i need ask for certain privileges to run this query? any idea what specific privilege i need to request? i am sure they will not give DBA or any privileges like that.

ORS-01031: insufficient privileges
ORA-06512 at line 6
ORA-06512 at line 6
01031. 00000 - "insufficient privileges'

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I provided a complete working example in the second link that searches for 'Hello'.

I don't have the time right now to set up a complete test case based on your exact requirements.

For example:  all_tab_columns is ALL the tables your user has access to.  The question is for a specific schema.  If you only want the schema you are logged in as, remove the owner column and use 'user_tab_columns'.

Kent gave you what you need to fix the code you found.  You were pulling in ALL data types like dates, timestamps and blobs which caused the error.

The insufficient privs is likely from the ALL_TAB_COLS I mentioned above.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
try

DECLARE
  match_count       INTEGER;
  v_search_string   VARCHAR2 (4000) := 'GCU';
BEGIN
  FOR t IN (SELECT owner, table_name, column_name
              FROM all_tab_columns
             --WHERE table_name LIKE 'X%'
)
  LOOP
    EXECUTE IMMEDIATE
      'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name || ' WHERE to_char(' || t.column_name || ') = :srch'
      INTO match_count
      USING v_search_string;

    IF match_count > 0
    THEN
      DBMS_OUTPUT.put_line (t.owner || '.' || t.table_name || ' ' || t.column_name || ' ' || match_count);
    END IF;
  END LOOP;
END;
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
also put a filter to the column / table list, dont search number columns
if you use thıs query, you dont need "to_char(...)" in my previous solution

SELECT owner, table_name, column_name
  FROM all_tab_columns
WHERE table_name LIKE 'ANYTHING%' -- are you sure all tables?
  and data_type in ('VARCHAR2','CHAR','VARCHAR')
Farhad AhmedIt Student

Author

Commented:
I tried both the above mentioned query's by Mr.Kent and Mr.Hain, with suggestion from SlightWV to change to remove "owner" and change to user_tab_columns. The query appears to be successfully completed without error . A Message PL/SQL procedure successfully completed is displayed. What now? What am i missing, to see the results?
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
you have to check output window for result...
what tool are you using?

you should see something like

MY_SCHEMA.TABLE1 FIRST_NAME 8
MY_SCHEMA.TABLE1 LAST_NAME 2
MY_SCHEMA.TABLE2 PRD_NAME 3
MY_SCHEMA.TABLE3 EVAL_CODE 1
Farhad AhmedIt Student

Author

Commented:
i am using Oracle SQL Developer. The output window is where the "PL/SQL procedure successfully completed" message was displayed.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
  1. Open Oracle Developer.
  2. Click "View" and then click "Dbms Output."
  3. Click the green "+" sign in the window that opens and select the database connection from which you want output. Output will now appear for that database in a new tab.

https://smallbusiness.chron.com/turn-output-sql-developer-51732.html
Farhad AhmedIt Student

Author

Commented:
tried that , i see the DBMS Output tab , but nothing is displayed in it.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
old values are lost probably...

turn it on, and run again...

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Then it might not have found anything?

Between the BEGIN and FOR, output a message to confirm it is there:

...the rest of the code
BEGIN
DBMS_OUTPUT.put_line('Hello World!');
FOR t IN (SELECT owner, table_name, column_name
...the rest of the code

If you see 'Hello World!' and nothing else, then nothing was found.  If you don't see 'Hello World!', then there are other issues.
Farhad AhmedIt Student

Author

Commented:
Thanks! I see "Hello World" and nothing else. I have tried other value besides 'GCU' and i know those values are present in the database. I can see the value GCU when i directly select the table. Wondering what am i missing?

Just to clarify , the value i am looking for goes here , correct? --> v_search_string   VARCHAR2 (4000) := 'GCU';
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
then use this, comment out condition
will you see all 0's?

try "gcu" or "Gcu", oracle is case sensitive...

    --IF match_count > 0
    --THEN
      DBMS_OUTPUT.put_line (t.owner || '.' || t.table_name || ' ' || t.column_name || ' ' || match_count);
    --END IF;
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
run the query to select table/column seperately...
do you see your table and column here?

what is your query that returns result?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
The code uses an '=' so the column value MUST match exactly.

It sounds like you want to search for GCU anywhere in the column and more than likely, in any case.

So if table bob has a column value:  'xxxGcUzzz', you want it returned?
Farhad AhmedIt Student

Author

Commented:
Select store_type  from  corporate_branch where store_id= 'GCU';

Result--
STORE_TYPE
--------------------
SM
BG
null



HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
but S is not C

"GSU" is not equal to "GCU"
Farhad AhmedIt Student

Author

Commented:
i corrected it , same result. i am testing it for several other values , the result is the same.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
what is your script now, copy paste please

did you comment out "if ... then ... end if" and see all tables/columns with 0 value?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I have a feeling this is a class assignment.

Based on my example in the second link I provided, here is how I would solve this problem.  It uses pure SQL with not PL/SQL logic and context switches.  It also only looks in the current user's schema not in ALL_.

Not sure if GCU or GSU is correct so I used GCU.
SELECT
        table_name,
        column_name,
        TO_NUMBER(
           EXTRACTVALUE(
               DBMS_XMLGEN.getxmltype(
                   'select count(*) X from ' || table_name || ' where ' || column_name || ' = ''GCU'' '
               ),
               '/ROWSET/ROW/X'
           )
       )
           COUNT
FROM user_tab_cols
WHERE data_type in ('CHAR','VARCHAR2','VARCHAR','CLOB')
ORDER BY table_name, column_name; 

Open in new window



My test case setup:
drop table my_corporate_branch purge;
drop table my_tab1 purge;
drop table my_tab2 purge;
drop table my_tab3 purge;
drop table my_tab4 purge;

create table my_corporate_branch(store_type varchar2(5), store_id varchar2(5));
create table my_tab1(col1 varchar2(50));
create table my_tab2(col1 number);
create table my_tab3(col1 date);
create table my_tab4(col1 BLOB);

insert into my_corporate_branch values('SM','GCU');
insert into my_tab1 values('GCU');
insert into my_tab1 values('GcU');
insert into my_tab2 values(12345);
insert into my_tab3 values(sysdate);
insert into my_tab4 values(utl_raw.cast_to_raw('Hello'));
commit;

Open in new window


You can run the complete example here:
    https://livesql.oracle.com/apex/livesql/s/koqv1fvyhge1p2yl4vkf0uruk 

Registration is free.
Farhad AhmedIt Student

Author

Commented:
apologies for the delayed response and appreciate your updates.
No Sir this is not a class assignment. I am afraid to reveal the actual table, columns name, data(values) in light of confidentiality. I tried the latest query uploaded by SlightWV and the result is the same , no records returned. No errors.

There must be something i am missing. Just so you know , below is what i am doing and looking for
1.Logged into the schema using Oracle SQL Developer
2. run a simple select like, "Select store_type  from  corporate_branch where store_id= 'GCU'; ", which returned the values for store_type. Ex: SM, BG, null
3.I want to find out what are all the tables, columns in the schema that have the value "BG" or "GCU" , or any value for that matter is stored. "GCU" is also a value in the table.
4. If i run another simple select , say , "Select store_id  from  corporate_branch;" , i see several records returned with GCU.
5. Running the query's provided by you and am not getting any results back. :-(

so far you guys helped me resolving two errors from my original query/procedure, i.e. change the value to VARCHAR2 and removing OWNER from the query to avoid "insufficient privileges" issue. Is there anything else you need to know that i am not providing? scratching my head here .....
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
The only way I can think of is whoever set up the username you are logging as isn't the table owner.  They might have created synonyms for you.

I get insufficient privs errors as a normal user when going against all_tab_columns because some of those are owned by Oracle created users.  Instead of just removing SYS and others that you probably don't care about, better to just look for objects owned by users that you do.

See what this returns:
select table_owner, count(*) from user_synonyms group by table_owner;

I created a BOB user and ran my test case setup.  I then tweaked the SQL to look for tables owned by BOB and got results.

If the synonym query above returns owners that you aren't logged in as, add them in the where clause of this (replace BOB of course):
SELECT
        owner,
		table_name,
        column_name,
        TO_NUMBER(
           EXTRACTVALUE(
               DBMS_XMLGEN.getxmltype(
                   'select count(*) X from ' || owner || '.' || table_name || ' where ' || column_name || ' = ''GCU'' '
               ),
               '/ROWSET/ROW/X'
           )
       )
           COUNT
FROM all_tab_cols
WHERE data_type in ('CHAR','VARCHAR2','VARCHAR')
	and owner in ('BOB')
ORDER BY table_name, column_name; 

Open in new window

Farhad AhmedIt Student

Author

Commented:
Yes, that is exactly how it was setup, a user with synonyms. The user name is 'DEV10SU', which i use to login via SQL Developer.

I ran the query above by changing 'BOB' to actual schema owner name 'DEV10SO' and now getting
ORA-00904: "OWNER":invalid identifier , Error at Line: 16 Column 5

Also, SQL Developer doesn't seem to like using "owner" in this query as it was highlighted in blue , at all three places where "owner" was used in this query.

besides that, didn't we remover OWNER from the previous query to fix the "insufficient privileges" issue? 
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Did you also change to all_tab_columns instead of user_tab_columns?  the owner column doesn't exist in the USER level views.
Farhad AhmedIt Student

Author

Commented:
all_tab_columns, did the trick, however the query has been running since 30 mins and not sure if it is really doing anything. Looks like its hung? 
Farhad AhmedIt Student

Author

Commented:
yes, it is hung, i had to force quit SQL Developer. :-(
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
put a filter to test

...
FROM all_tab_cols
WHERE data_type in ('CHAR','VARCHAR2','VARCHAR') AND table_name='a_table_to_test'
Farhad AhmedIt Student

Author

Commented:
i let the query run this time, i was away for about 40 mins and when i got back below are the errors displayed.

ORA-19202: Error occurred in XML processing
ORA-00920: Invalid relational operator
ORA-06512: at "SYS.DBMS_XMLGEN", line 288
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
did you run the query for just one table, which you can run and get the result?

like

...
FROM all_tab_cols
WHERE data_type in ('CHAR','VARCHAR2','VARCHAR') AND table_name='CORPORATE_BRANCH'
Farhad AhmedIt Student

Author

Commented:
I just did and zero records returned. Tried it for different values and diff table names as well, no luck.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
If it is a large database it will take quite a while.

How large is the database you are using?  How many tables and column?
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
if the tables are large and no index exist on searched columns, then whatever you do, it will take huge amounts of time...
Farhad AhmedIt Student

Author

Commented:
i am not quite sure, but i know it is huge. But why the above error?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>i am not quite sure, but i know it is huge.

Then the query may never return or take days/weeks to run.

>>But why the above error?

You might have tables/column with special characters or spaces in the names that cause the selecet to not be 'valid'.

Change the select line to be:
                   'select count(*) X from ' || owner || '."' || table_name || '" where "' || column_name || '" = ''GCU'' '

Open in new window

Farhad AhmedIt Student

Author

Commented:
changed it , still no data. Just the column headers. :-(
no break thru in this case?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Verify the synonym names with table names.  Using double quotes forces case sensitivity in the object names which is VERY BAD in Oracle and can causes issues like what you might be running into.

I would suggest you work directly with the DBA and/or developers.  We've given you code that works in every test case we can make up.

Unless we know more about your system and how things are set up, I'm out of ideas.  This would probably require getting access which is impossible.

I'll keep trying but this will probably take a while to go through.

What does this return:
select * from user_synonyms where synonym_name='CORPORATE_BRANCH';
Farhad AhmedIt Student

Author

Commented:
fair enough. I will contact the DBA's and see if i can get the privileges on the schema.

SYNONYM_NAME            TABLE_OWNER.   TABLE_NAME                    DB_LINK.    ORIGIN_CON_ID
CORPORATE_BRANCH    QA10SU.               CORPORATE_BRANCH.   (null)             0
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Not sure you need schema access.  You just need help figuring out why the SQL we have provided doesn't return rows.

The "."'s in that output seem suspect.

What does this return:
select owner, table_name, column_name from all_tab_columns where table_name='CORPORATE_BRANCH' and owner='QA10SU';
Farhad AhmedIt Student

Author

Commented:
That "." is being added by EE window as i type the result. i tried to delete it but it puts it back.

as far as the query, SQL Developer is not liking "owner", it is giving me an option to select "owner_name" and also when i type "all_tab_columns" , it gives me two options , PUBLIC.all_tab_columns and SYS.all_tab_columns.

however i typed-in owner and ran the query for owner= 'QA10SU', and zero records were returned, as expected, since QA01SU is not the owner of any tables.
I changed the owner='QA10SO' and got 60 records, as expected.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I cannot help with the prompt for owner_name.  That isn't one of the columns in all_tab_columns or all_tab_cols.

public or sys should be the same.  So shouldn't matter.  Not sure what the public one is.  I don't have that in my 19c database.  Hopefully just another synonym.

I know SQL Developer has auto complete and I assume that is what you are referring to.  You don't have to take what it suggests.  Should be able to copy/paste/run.

Just try pasting and running:
select owner, table_name, column_name from all_tab_columns where table_name='CORPORATE_BRANCH' and owner='QA10SU';
Farhad AhmedIt Student

Author

Commented:
yes, i did (paste and run), hence the comment....
""however i typed-in owner and ran the query for owner= 'QA10SU', and zero records were returned, as expected, since QA01SU is not the owner of any tables.
I changed the owner='QA10SO' and got 60 records, as expected."
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I missed the 60 rows from the previous comment.  Wasn't concerned with the number of rows.  I was looking for the actual results to help diagnose the SQL in #a43161850

If adding the double quotes caused nothing to be returned, I was thinking you may have special characters or spaces in the column names.  If everything is upper case and no spaces then I cannot explain why adding quotes caused no data to be returned.

If you see "QA10SO" but the query you posted in  #a43162086 shows "QA10SU." or as you mention, EE is messing with the actual values, were you typing it or pasting it?  If pasting, I suspect a special character of some type is messing with the EE editor.
Farhad AhmedIt Student

Author

Commented:
typing.

Now that we have the output from "select owner, table_name, column_name from all_tab_columns where table_name='CORPORATE_BRANCH' and owner='QA10SU';"

which is ZERO , but QA10SO is returning values.
what next?

we have already established that QA10SU is a user with synonyms to the schema QA10SO.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I have to apologize.  I am so confused about what it doing what.

We have provided SQL that should return counts based on values.  It has been tested in test cases here and posted as a solution in other questions on this site.  I know the code works.

Why it isn't working for you, I have no idea.  I don't know your database and how the DBAs set everything up for you.


The last think we were working on is why adding double quotes to the DBMS_XMLGEN query back in #a43161850

You said it ran but didn't return any rows.

The only thing I could think of is the possibility that your table might have column names that aren't "standard".

I have asked for the actual output from the select not the number of rows returned from the select.  Without actually being able to see the actual output, I'm not sure how to help you diagnose what might be causing the query to not return data.

I also asked for you to check with your DBA about why the provided code isn't working.  They have direct access to the system and can probably tell you why it isn't working in a couple of minutes.
Farhad AhmedIt Student

Author

Commented:
I ran the following and am getting the error show below, what am i missing?
There is a suggestion by SQL developer when i hover the mouse over the "column_name" , stating that "INTO clause is expected in this SELECT statement".

DECLARE
   match_count integer;  
   v_search_string varchar2(4000) := 'GSU';
BEGIN    
   FOR t IN (SELECT owner, table_name, column_name FROM all_tab_columns and owner ='QA10SU') LOOP      
   EXECUTE IMMEDIATE          
         'SELECT COUNT(*) FROM '||t.owner || '.' || t.table_name||      
         ' WHERE '||t.column_name||' = :1'          
      INTO match_count        
         USING v_search_string;    
      IF match_count > 0 THEN      
         dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );    
      END IF;  
   END LOOP;
END;
/

---
Error report-
ORA-00936: missing expression
ORA-06512: at line 6
ORA-06512: at line 6
00936.  00000  -  "missing expression"
*Cause:    
*Action:    

-----


CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
A couple of problems.  You don't have WHERE in the FOR loop it is AND only.

You also don't check only character data types.  Same issue above using my sql.  It will probably error on the first date, timestamp, non-character column it finds.

You will also probably have the same issue with mine on what I think is bad naming which is why I added the double quotes to my sql.

But since you want to try it, this worked for me:
DECLARE
   match_count integer;  
   v_search_string varchar2(4000) := 'GSU';
BEGIN    
   FOR t IN (SELECT owner, table_name, column_name FROM all_tab_columns where owner ='QA10SU' and data_type in ('VARCHAR2','CHAR')) LOOP      
   EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||t.owner || '.' || t.table_name||      
         ' WHERE '||t.column_name||' = :1'          
      INTO match_count        
         USING v_search_string;    
      IF match_count > 0 THEN      
         dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );    
      END IF;  
   END LOOP;
END;
/

Open in new window


I expect it to fail with a similar error as my last SQL.  If it does, can you please post the information I asked for?
Farhad AhmedIt Student

Author

Commented:
Error report-
ORA-00936: missing expression
ORA-06512: at line 8
ORA-06512: at line 8
00936.  00000  -  "missing expression"
*Cause:    
*Action:    

-----
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
It runs for me.

Here is my complete test:
SQL> create user QA10SU quota unlimited on users;

User created.

SQL> create table QA10SU.tab1(col1 varchar2(50));

Table created.

SQL> insert into QA10SU.tab1 values('GSU');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
  2     match_count integer;
  3     v_search_string varchar2(4000) := 'GSU';
  4  BEGIN
  5     FOR t IN (
  6             SELECT owner, table_name, column_name
  7             FROM all_tab_columns
  8             WHERE owner ='QA10SU' and data_type in ('VARCHAR2','CHAR')
  9     ) LOOP
 10     EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||t.owner || '.' || t.table_name||
 11           ' WHERE '||t.column_name||' = :1'
 12        INTO match_count
 13           USING v_search_string;
 14        IF match_count > 0 THEN
 15           dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );

 16        END IF;
 17     END LOOP;
 18  END;
 19  /
QA10SU.TAB1 COL1 1

PL/SQL procedure successfully completed.

SQL>

Open in new window

Farhad AhmedIt Student

Author

Commented:
i don't know what to say. It is showing be the same suggestion at "cloumn_name".
In my case the user i am logged-in as is not the owner of the table, i guess that does not matter.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Turn on line numbers in the worksheet:
https://www.oreilly.com/library/view/oracle-sql-developer/9781785281273/ch06s12.html

What version of SQL Developer are you using?
What version or Ofacle are you connecting to?

In 19.2, SQL DEveloper the code still works for me:
Untitled.jpg
Have you contacted the DBA yet?
Farhad AhmedIt Student

Author

Commented:
Enabled the line #'s.
SQL Developer version -- 18.4  
Oracle Database Version 19.0

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Sorry, I missed it without the line numbers.  I made another small change to your original code I forgot about.

PL/SQL can be picky on syntax from time to time.

If you notice, I started my SQL string on the same line as the EXECUTE IMMEDIATE.

You either need to do that or add a line continuation character after the EXECUTE IMMEDIATE.
Farhad AhmedIt Student

Author

Commented:
ok, i moved the SQL string next to EXECUTE IMMEDIATE and re-ran it, below is the result. Looks like something from the data it was retrieving?

Error report-
ORA-00904: "CURRENCY":  invalid identifier
ORA-06512: at line 8
ORA-06512: at line 8
00904.  00000  -  "%s: invalid identifier"
*Cause:    
*Action:    

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I already mentioned that I didn't expect it to run.  Whatever was causing the issue I tried to address back up in #a43161850 was likely to cause this method to fail.

You will have to debug this on your own.  The message is pretty clean:  It seems to be finding a CURRENCY column that cannot be selected.  No, I cannot guess what would be causing that one.

You will have to find the tables with that column and figure out what is different about it.
Farhad AhmedIt Student

Author

Commented:
Can the procedure be modified to check for all types of data , instead of just the character type columns?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Maybe you should focus on getting character data types actually working first?

But, sure.  You just need to have functions to convert all the possible data types you want to look for as strings.

For example:
numbers (can probably be implicitly converted but get used to explicit data type conversions):  to_char(number_column)
dates: to_char(date_column,'MM/DD/YYYY HH24:MI:SS');

LONG and LONG RAW probably cannot be done easily.

BLOBs are doable but it won't be fun.

XMLTYPE is pretty much a CLOB so doable.


Since this type of query takes forever to run, I suppose I should ask what your actual reason for doing this is?
Farhad AhmedIt Student

Author

Commented:
for data validation purposes.
Farhad AhmedIt Student

Author

Commented:
This is what the DBA suggested, "can you add dbms_output statement before EXECUTE IMMEDIATE , print table name and column name. This should point to where Error is"

it didn't make sense to me, does it to you?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Yes.

It will output the SQL being executed then try to execute it.  When it dies, you will have the statement it died on.  Then you can run that SQL all by itself and see if it errors.

I didn't test this but it gives you the idea:
DECLARE
    match_count integer;
    v_search_string varchar2(4000) := 'GSU';
BEGIN
    FOR t IN (
            SELECT owner, table_name, column_name
            FROM all_tab_columns
            WHERE owner ='QA10SU' and data_type in ('VARCHAR2','CHAR')
    ) LOOP

   dbms_output.put_line('SELECT COUNT(*) FROM '||t.owner || '.' || t.table_name||
         ' WHERE '||t.column_name||' = :1');

   EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||t.owner || '.' || t.table_name||
         ' WHERE '||t.column_name||' = :1'
      INTO match_count
         USING v_search_string;
      IF match_count > 0 THEN
         dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );

      END IF;
   END LOOP;
END;
/

Open in new window

Farhad AhmedIt Student

Author

Commented:
Got a bunch of rows returned, for different tables and columns , where all of them showing the same value, :1
Below are some of them

select count(*) from QA10SU.BKLOG_MGR where PAL_NAME= :1
select count(*) from QA10SU.BKLOG_MGR where DEL_NAME= :1
select count(*) from QA10SU.JOB_EXEC where STATUS= :1
select count(*) from QA10SU.JOB_AUD where EXC_ID= :1
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Yes, you will get output for every column it runs.  The LAST ONE will be the one that causes the error.

>>where all of them showing the same value, :1

Yes, that is what it was told to output......  Did you expect something different?
Farhad AhmedIt Student

Author

Commented:
ah ok, i was thinking the Output will be , just the Table Name, Column Name and the count.
Didn't think that the output will be in a "Select" statement format. I guess it is displaying the select statements since the query did not complete all the way?

"The LAST ONE will be the one that causes the error." Does this mean , i have 99.99% of the results and just missing one record, that is the last one? IF that is the case i can work with it.

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>> I guess it is displaying the select statements since the query did not complete all the way?

Do you try to understand the code before you run it?

If is outputting exactly what you told it to:
 dbms_output.put_line('SELECT COUNT(*) FROM '||t.owner || '.' || t.table_name||
         ' WHERE '||t.column_name||' = :1');

It is the exact select statement being executed in the 'execute immediate'.

It's your code, you can make it output anything you want.

>>i have 99.99% of the results and just missing one record, that is the last one?

This goes back to understanding the code.

You have a loop.
When an error occurs, the loop throws an error and exits.
The error occurs on the execute immediate statement.
You issue an output statement just before the execute immediate.

So, the last line in the output is probably the one that caused the error.

It might be the first table and column, it might be the last, it might be somewhere in the middle.  You have no way of knowing.  It errors when it errors.

To save a LOT of output, you might just trap the exception and display the error causing data there.

Like this:
DECLARE
   match_count integer;  
   v_search_string varchar2(4000) := 'GSU';
BEGIN    
   FOR t IN (
		SELECT owner, table_name, column_name
		FROM all_tab_columns
		WHERE owner ='QA10SU' and data_type in ('VARCHAR2','CHAR')
	order by table_name
	) LOOP      

	begin
   		EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||t.owner || '.' || t.table_name||      
         ' WHERE '|| t.column_name ||' = :1'          
      INTO match_count        
         USING v_search_string;    
      IF match_count > 0 THEN      
         dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );    
      END IF;  

	exception when others then
 		dbms_output.put_line('SELECT COUNT(*) FROM '||t.owner || '.' || t.table_name||
         ' WHERE '||t.column_name||' = :1');
		raise;
	end;

   END LOOP;

END;
/

Open in new window

Farhad AhmedIt Student

Author

Commented:
Below is what it returned:

Error report-
ORA-00936: missing expression
ORA-06512: at line 24
ORA-06512: at line 23
ORA-06512: at line 13
00936.  00000  -  "missing expression"
*Cause:    
*Action:
--    
DBMS OUTPUT

select count(*) from QA10SU.ADJ_MGR where NOTES= :1

----




Farhad AhmedIt Student

Author

Commented:
i tried to check the table/column value from the sql generated above, as
select * from QA10SU.ADJ_MGR where NOTES= 'GSU';  and got the missing expression error.

Then i did a , select * from QA10SU.ADJ_MGR; , only one record was in the table and column NOTES , did not have the value GSU. It has a value as, 'Market', stored in it.

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>i tried to check the table/column value from the sql generated above, and got the missing expression error.

That's what you wanted.  NOW you have a place to start troubleshooting.  What is "unique" about ADJ_MGR?


My guess is that it is a view.  Off the top of my head, I cannot think of how someone could mess up a view that would cause an ORA-936 when querying it...  but IF it is a view, dig into what is causing it...


If you ONLY want to look at tables and NOT views, just remove views from the Pl/SQL you are running.

This is untested so might have a minor syntax issue or two.
DECLARE
   match_count integer;  
   v_search_string varchar2(4000) := 'GSU';
BEGIN    
   FOR t IN (
		SELECT owner, table_name, column_name
		FROM all_tab_columns
		WHERE owner ='QA10SU' and data_type in ('VARCHAR2','CHAR')
		and table_name not in (select view_name from all_views where owner ='QA10SU')
	order by table_name
	) LOOP      

	begin
   		EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||t.owner || '.' || t.table_name||      
         ' WHERE '|| t.column_name ||' = :1'          
      INTO match_count        
         USING v_search_string;    
      IF match_count > 0 THEN      
         dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );    
      END IF;  

	exception when others then
 		dbms_output.put_line('SELECT COUNT(*) FROM '||t.owner || '.' || t.table_name||
         ' WHERE '||t.column_name||' = :1');
		raise;
	end;

   END LOOP;

END;
/

Open in new window

Farhad AhmedIt Student

Author

Commented:
I just verified and it is a table. Strange that only "Select * from " is working on that table and anything with a where clause on the NOTES column is causing the above mentioned error. 
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
What is the table definition?

Just for grins try:
select * from QA10SU.ADJ_MGR where "NOTES"= 'GSU';
Farhad AhmedIt Student

Author

Commented:
Yes Sir! "NOTES" worked! Why is that and how to incorporate this into the main query?

i just generated the SQL on that table using SQL Developer and there are quotes for that column NOTES, as in -- "Notes" varchar2 (300 char)
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I cannot find NOTES in the reserved words so no idea why.

>>Why is that and how to incorporate this into the main query?

Remember way back up in this question, I added double quotes to my XMLGEN query because of an error?  #a43161850  Well, this was probably the cause of that error.

You'll need to do that to your current code.  BUT, since that didn't work in the XMLGEN query and you received a different error, it probably won't work in the PL/SQL method...

The XMLGEN query and your PL/SQL block are doing the exact same things.  The only difference is one uses straight SQL and the other uses PL/SQL.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
The only 19c I have access to right now is livesql:  https://livesql.oracle.com/

I cannot reproduce the issue:
drop table tab1 purge;
create table tab1(notes varchar2(4000));
insert into tab1 values('GSU');
commit;

select * from tab1 where NOTES='GSU';

Open in new window


I would check with your DBA for what might be different on your database.
Farhad AhmedIt Student

Author

Commented:
i ran it on a diff schema and similar issue , with a columns name "COMMENT". I searched and COMMENT is a reserved word. How to overcome this?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>How to overcome this?

Use double quotes.
Farhad AhmedIt Student

Author

Commented:
double quotes, in the above procedure? How? Can you please update.

regards
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Really?  Same way I added them back up in a43161850 12 days ago.

It is literally exactly the same............  Did you try it?  If you wanted to wrap the table and column names in double quotes, where would you need to put them in the SQL string?

Remember:  This too will likely fail for the same reasons the XML one did.........  You really need to work with your DBA.  This is maybe a one hour thing for them to troubleshoot and likely leaves 30 minutes for lunch.

Untested
DECLARE
   match_count integer;  
   v_search_string varchar2(4000) := 'GSU';
BEGIN    
   FOR t IN (
		SELECT owner, table_name, column_name
		FROM all_tab_columns
		WHERE owner ='QA10SU' and data_type in ('VARCHAR2','CHAR')
	order by table_name
	) LOOP      

	begin
   		EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM "'||t.owner || '"."' || t.table_name||      
         '" WHERE '|| t.column_name ||' = :1'          
      INTO match_count        
         USING v_search_string;    
      IF match_count > 0 THEN      
         dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );    
      END IF;  

	exception when others then
 		dbms_output.put_line('SELECT COUNT(*) FROM "'||t.owner || '"."' || t.table_name||
         '" WHERE '||t.column_name||' = :1');
		raise;
	end;

   END LOOP;

END;
/

Open in new window

Farhad AhmedIt Student

Author

Commented:
Error report-
ORA-00936: missing expression
ORA-06512: at line 25
ORA-06512: at line 25
ORA-06512: at line 14
00936.  00000  -  "missing expression"
*Cause:    
*Action:
--    
DBMS OUTPUT

select count(*) from QA10SU.ADJ_MGR where COMMENT= :1

----
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I don't see the double quotes in the dbms_output.  Make sure you are using the code with them in it.
Farhad AhmedIt Student

Author

Commented:
that is the result from running the query https://www.experts-exchange.com/questions/29195143/Search-for-a-value-in-a-Oracle-schema.html#a43169972
which i belive is the latest you provided.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Farhad AhmedIt Student

Author

Commented:
Looks there is an issue with the syntax on the below shown portion of the query, I added ");" after the =:1',
but still throwing an error "encountered the symbol 'RAISE' when expecting one of the following"
 := . ( % ;

exception when others then       dbms_output.put_line('SELECT COUNT(*) FROM "'||t.owner || '"."' || t.table_name||         '" WHERE "'|| t.column_name ||'" = :1'                raise;   end;
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Are you not learning the syntax yet?  It was a copy/paste error.  Take the SQL that sort of worked in #a43169972 and add the double quotes to the column name in both places.


I tested this one for you.
DECLARE
   match_count integer;  
   v_search_string varchar2(4000) := 'GSU';
BEGIN    
   FOR t IN (
		SELECT owner, table_name, column_name
		FROM all_tab_columns
		WHERE owner ='QA10SU' and data_type in ('VARCHAR2','CHAR')
	order by table_name
	) LOOP      

	begin
   		EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM "'||t.owner || '"."' || t.table_name||      
         '" WHERE "'|| t.column_name ||'" = :1'          
      INTO match_count        
         USING v_search_string;    
      IF match_count > 0 THEN      
         dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );    
      END IF;  

	exception when others then
 		dbms_output.put_line('SELECT COUNT(*) FROM "'||t.owner || '"."' || t.table_name||
         '" WHERE "'||t.column_name||'" = :1');
		raise;
	end;

   END LOOP;

END;
/

Open in new window



I should also point out that whomever created your database did bad things...  They DID use double quotes around object names.  That will cause you NOTHING but problems.

COMMENT is a reserved word so it should NEVER be a table/column/object name.

Trying to create a table the proper way errors:
create table tab1(comment char(1))
                  *
ERROR at line 1:
ORA-00904: : invalid identifier

Open in new window


To create it, I had to add the double quotes:
SQL> create table tab1("comment" char(1));

Table created.

Open in new window


The problem you have is the comment table is ALWAYS in the EXACT case you created it with and has to ALWAYS be double quoted in the EXACT case it was created:
SQL> select "COMMENT" from tab1;
select "COMMENT" from tab1
       *
ERROR at line 1:
ORA-00904: "COMMENT": invalid identifier


SQL> select "comment" from tab1;

no rows selected

Open in new window


To drive the point home:
SQL> create table tab1("CoMmEnT" char(1));

Table created.

SQL> select "comment" from tab1;
select "comment" from tab1
       *
ERROR at line 1:
ORA-00904: "comment": invalid identifier


SQL> select "COMMENT" from tab1;
select "COMMENT" from tab1
       *
ERROR at line 1:
ORA-00904: "COMMENT": invalid identifier

Open in new window

Farhad AhmedIt Student

Author

Commented:
The solution i selected works. The only down side is it takes good 4+ hours to fetch the results, which is way better than what we started with. -regards

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.