Home » SQL & PL/SQL » SQL & PL/SQL » FIND table FROM all_tab_cols WHERE column value = something
FIND table FROM all_tab_cols WHERE column value = something [message #295561] Tue, 22 January 2008 14:38 Go to next message
manningda
Messages: 31
Registered: January 2008
Member
All:

Wanting to identify all tables where a particular column equals or is similar to a particular value.

Think Im getting close, but frankly my coding skills are you?? ng and Im missing a syntax issue I dont understand.

Did make an effort to search for existing posts that may have dealt with this, but did not see anything directly relevant.

I will gladly follow any direction to existing posts if necessary...

Current Effort:
DECLARE
  answer varchar2(200);
  
BEGIN
	FOR REC IN
		(SELECT owner, table_name, column_name
		FROM all_tab_cols
		WHERE owner = '<schema name>'
		AND column_name = '<column name>')
LOOP
	EXECUTE IMMEDIATE
--		'SELECT COUNT (rowid)FROM BARCODE_FIELD_STAGING_AREA'
      'SELECT COUNT (rowid) FROM ' || rec.table_name ||
     INTO answer;
	DBMS_OUTPUT.PUT_LINE(rec.table_name||'-'||answer);
END LOOP;
END;

...the commented line indicates a table name where I was certain the column_name we are looking for was included and wanted to confirm that DBMS_OUTPUT would actually produce a result.

When I comment out the implicit table name and try to reference

|| rec.table_name ||


I get the following error:

ORA-06550: line 13, column 6
PLS-00103: Encountered the symbol "INTO" when expecting one of the following:

(-+ case mod new null <an identifier>
<a double-quoted delimted-identifier> <a bind variable> 
avg
count current max min prior sql stddev sum variance 
execute forall merge time timestamp interval date 
<a string literal with charecter set specification> 
<a number> <a single-quoted SQL string> pipe
The symbol "null" was substituted for "INTO" to continue.


...I think I am supposed to understand from this list a "coice" to apply to repair the EXECUTE IMMEDIATE clause, but frankly my self-education is lacking to understand which choice to make and why.

As I said, I am glad to follow direction to other threads, posts that may apply...

Thanks in advance for all input!!!

[Updated on: Tue, 22 January 2008 14:58] by Moderator

Report message to a moderator

Re: FIND table FROM all_tab_cols WHERE column value = something [message #295563 is a reply to message #295561] Tue, 22 January 2008 15:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Wanting to identify all tables where a particular column equals or is similar to a particular value.

Why not just a select from all_tab_columns?

Regards
Michel
Re: FIND table FROM all_tab_cols WHERE column value = something [message #295568 is a reply to message #295561] Tue, 22 January 2008 15:15 Go to previous messageGo to next message
manningda
Messages: 31
Registered: January 2008
Member
Well...

ALL_TAB_COLS certainly give me every TABLE where there is a COLUMN_NAME = <value>

However...

...in this case that list is over 100 tables long; and ultimately Im only interrested in those TABLES where the actual "FIELD_VALUE" = <value>...

Trying to guard a little customer data here, so Im sorry for being coy with table names, column names, etc.

Here's the actual code with actual names...

DECLARE
  answer varchar2(200);
  
BEGIN
	FOR REC IN
		(SELECT owner, table_name, column_name
		FROM all_tab_cols
		WHERE owner = 'PEMS'
		AND column_name = 'ML_ID')
LOOP
	EXECUTE IMMEDIATE
--		'SELECT COUNT (rowid)FROM BARCODE_FIELD_STAGING_AREA'
      'SELECT COUNT (rowid) FROM ' || rec.table_name ||
     INTO answer;
	DBMS_OUTPUT.PUT_LINE(rec.table_name||'-'||answer);
END LOOP;
END;



Ultimately, Im only interrested in those tables where ML_ID = <value>.

I can do...

SELECT ml_id
FROM [TABLE]
WHERE ml_id = <value>


But Id have to do that 100+ times to isolate the records I want to update.

Instead Id like to narrow down those tables where that upadate is necessary based on the particular value of ML_ID.


Is that a better question??
Confused
CONTINUED THANKS!
Re: FIND table FROM all_tab_cols WHERE column value = something [message #295571 is a reply to message #295568] Tue, 22 January 2008 15:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If I understand, you don't know where you put your data but it is in a column in some tables.
I don't know who made this model but he is dump.

Just concatenate your condition in the execute immediate, where is the problem?

Regards
Michel
Re: FIND table FROM all_tab_cols WHERE column value = something [message #295573 is a reply to message #295568] Tue, 22 January 2008 15:29 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Are you looking for something like this? I'm posting a script I used (so you'll have to modify it in order to suit your needs), but - it works:
REM Search for all tables where column 'pogon' equals '123456'
DECLARE
   str     VARCHAR2 (1000);
   l_cnt   NUMBER;
BEGIN
   FOR cur_t IN (SELECT table_name
                   FROM user_tables)
   LOOP
      BEGIN
         str :=
               'SELECT COUNT(*) FROM '
            || cur_t.table_name
            || ' WHERE pogon = ''123456''';
			
         EXECUTE IMMEDIATE (str)
                      INTO l_cnt;

         IF l_cnt > 0
         THEN
            DBMS_OUTPUT.put_line (cur_t.table_name);
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;
   END LOOP;
END;
/
Re: FIND table FROM all_tab_cols WHERE column value = something [message #295575 is a reply to message #295561] Tue, 22 January 2008 15:37 Go to previous messageGo to next message
manningda
Messages: 31
Registered: January 2008
Member
Sorry, must be doing a bad job of asking this question, but you've basically got the idea Michel.

To try and clarify...

1. Not my data. Inherited DB from another development group entirely. Delivered with little documentation (poorly written at that...). --- so commonly we see a user request a data update that does not necessarily apply globally to all tables where the field occurs.

2. Concantanate Condition... I think Im tring to do exactly that with...

|| rec.table_name ||


...but when I do that I get the error I mentioned before...

ORA-06550: line 13, column 6
PLS-00103: Encountered the symbol "INTO" when expecting one of the following:

(-+ case mod new null <an identifier>
<a double-quoted delimted-identifier> <a bind variable> 
avg
count current max min prior sql stddev sum variance 
execute forall merge time timestamp interval date 
<a string literal with charecter set specification> 
<a number> <a single-quoted SQL string> pipe
The symbol "null" was substituted for "INTO" to continue.


...my problem is (as if I had only one Razz )...

I don't understand why the EXECUTE IMMEDIATE clause will work with a table_name, but not with a reference to the rec.table_name...

Im thinking there is a syntax where I can loop through the all_tab_col results from the BEGIN clause and apply each table name to a condition where the column name = a particluar value.

...yes??? Embarassed



CONTINUED THANKS!!!
Re: FIND table FROM all_tab_cols WHERE column value = something [message #295577 is a reply to message #295575] Tue, 22 January 2008 15:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Concantanate Condition... I think Im tring to do exactly that with... || rec.table_name ||

Yes, then where is the condition, the "WHERE ml_id = <value>"? It should be after ||.

Regards
Michel

[Updated on: Tue, 22 January 2008 15:41]

Report message to a moderator

Re: FIND table FROM all_tab_cols WHERE column value = something [message #295584 is a reply to message #295577] Tue, 22 January 2008 16:01 Go to previous messageGo to next message
manningda
Messages: 31
Registered: January 2008
Member
Well, the first thing I was trying to do was just make certain I could write the EXECUTE IMMEDIATE correctly to get the full list of tables and a record count where COLUMN_NAME = ml_id.

With that much working I think I should be able to add there where condition I need.
Re: FIND table FROM all_tab_cols WHERE column value = something [message #295590 is a reply to message #295561] Tue, 22 January 2008 17:53 Go to previous messageGo to next message
manningda
Messages: 31
Registered: January 2008
Member
Ok ---got it!!! --THANKS LittleFoot

Here's what works!!

DECLARE
	LocsFnd     VARCHAR2 (1000);
	rec_cnt	    NUMBER;

BEGIN
	FOR rec IN
		(SELECT owner, table_name, column_name
		FROM all_tab_cols
		WHERE owner = 'PEMS'
		AND column_name = 'ML_ID')
	
	LOOP
		BEGIN
			LocsFnd :=
			'SELECT COUNT(*) FROM '
			||rec.table_name||
			' WHERE ml_id = ''0622C''';
		
		EXECUTE IMMEDIATE (LocsFnd)
		INTO rec_cnt;
		
		IF rec_cnt > 0
		THEN
		DBMS_OUTPUT.put_line (rec.table_name || '-' || rec_cnt);
		END IF;
	
		EXCEPTION
		
		WHEN OTHERS
		THEN 
			NULL;
		
		END;
	END LOOP;
END;



So I think what was wrong originally was the way I was trying to present the dynamic SQL after the EXECUTE IMMEDIATE statement.

LittleFoot's method of loading those results to the [LocsFnd] variable coorected the original PL/SQL error reported.

I do however have a new question...

On line 17 of the code above...

To try and understand this syntax more clearly I basically took LittleFoot's (LF) suggestion completely apart and then reconstructed it so that I could see how each piece fit against the other.

(...yea Im one of those types...)

When I started to test it, I got repeated failures on line 17; but my code and LF's seemed identical.

Then I noticed the space after the first tic.

LF:
' WHERE ml_id = ''0622C''';

MINE:
'WHERE ml_id = ''0622C''';

Both would execute, but mine would not produce any results; simply a blank output.

Can someone explain to me why the space after the tic is so critical. I think that syntax of the dynamic SQL with the tic's and pipes is still a little cloudy for me. Seems there are some critical spacing issues here, but I just don't understand why.

CONTINUED THANKS TO ALL FOR THIER INPUT!!!

VERY VALUABLE LESSONS IN THE END!!!
Re: FIND table FROM all_tab_cols WHERE column value = something [message #295766 is a reply to message #295590] Wed, 23 January 2008 06:25 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You got no output because dynamic SELECT statement wasn't correctly written; the procedure hit an exception and exception handler (WHEN OTHERS ... I know, not the best solution, but it was just fine for me in this case) hid the reason.

Without an extra space, your statement looked like this:
SELECT COUNT(*) FROM some_tableWHERE ml_id = '0622C';
In a real case, it would produce
SQL> SELECT COUNT(*) FROM deptWHERE deptno = 10;
SELECT COUNT(*) FROM deptWHERE deptno = 10
                                      *
ERROR at line 1:
ORA-00933: SQL command not properly ended
because "deptWHERE" is simply illegal.

With an extra space, statement looks like
SELECT COUNT(*) FROM some_table WHERE ml_id = '0622C';
and everything is fine.
Re: FIND table FROM all_tab_cols WHERE column value = something [message #295801 is a reply to message #295561] Wed, 23 January 2008 08:50 Go to previous messageGo to next message
manningda
Messages: 31
Registered: January 2008
Member
Shocked Laughing Cool

...NOW i get it... Thanks again LittleFoot!

When I initially started studying the dynamic code blocks, I thought I realized that the tic's indicated query "phrases" that were being re-assembled the way you showed in your last example.

But I think I was looking for the data solution so hard that I was not paying close enough attention to the syntax.

Embarassed

one more THANKS!!!

Hope this post is helpful to others in the future!!!
Re: FIND table FROM all_tab_cols WHERE column value = something [message #295825 is a reply to message #295766] Wed, 23 January 2008 11:50 Go to previous messageGo to next message
manningda
Messages: 31
Registered: January 2008
Member
...well, Im nearly there...

LittleFoot, any chance you're still listening??? Embarassed


So, I can use your code to query one WHERE condition, but Im missing how to implement multiple conditions.


...this fails...
' WHERE ml_id IN '('WP-01','WP-02','WP-03','WP-04')'';



...and this fails...
' WHERE ml_id = ''WP-01'''
' OR ml_id = ''WP-02''';

...etc...


...I guess I need to take back what I thought I understood Embarassed

Im still missing something about how to write the dynamic sql...

Any Ideas... Confused
Re: FIND table FROM all_tab_cols WHERE column value = something [message #295828 is a reply to message #295825] Wed, 23 January 2008 12:20 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
The best way to try and debug dynamic sql is to dmbs_output your sql string, and manually run it to see what errors you are receiving.

It looks like your parenthesis for the in condition need to be inside the quotes. But that was just a quick look.

Re: FIND table FROM all_tab_cols WHERE column value = something [message #295832 is a reply to message #295561] Wed, 23 January 2008 12:47 Go to previous messageGo to next message
manningda
Messages: 31
Registered: January 2008
Member
Thanks Marc --- that sounds like a very good idea.

Now we've discovered a new lesson
DBMS_OUTPUT

Ive tinkered with several different version of

DBMS_OUTPUT.put_line (LocsFnd) 


and am getting a DONE mesasge from PL/SQL Developer, but no records or text in output.



full code...
DECLARE
	LocsFnd     VARCHAR2 (1000);
	rec_cnt	    NUMBER;

BEGIN
	FOR rec IN
		(SELECT owner, table_name, column_name
		FROM all_tab_cols
		WHERE owner = 'PEMS'
		AND column_name = 'ML_ID')
	
	LOOP
		BEGIN
			LocsFnd :=
			'SELECT COUNT(*) FROM '
			||rec.table_name||
	   ' WHERE ml_id IN ''(''WP-01'',''WP-02'',''WP-03'',''WP-04'')''';

			
		
		EXECUTE IMMEDIATE (LocsFnd)
		INTO rec_cnt;
		
		IF rec_cnt = 0
		THEN
		DBMS_OUTPUT.put_line (LocsFnd);
		END IF;
	
		EXCEPTION
		
		WHEN OTHERS
		THEN 
			NULL;
		
		END;
	END LOOP;
END;



...can you see what's missing?
Re: FIND table FROM all_tab_cols WHERE column value = something [message #295834 is a reply to message #295832] Wed, 23 January 2008 12:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you put '' before and after parenthesis in IN?
Do you have such ' in static query?

Regards
Michel

[Updated on: Wed, 23 January 2008 12:53]

Report message to a moderator

Re: FIND table FROM all_tab_cols WHERE column value = something [message #295837 is a reply to message #295561] Wed, 23 January 2008 13:13 Go to previous messageGo to next message
manningda
Messages: 31
Registered: January 2008
Member
GOT IT!! Laughing Cool Shocked Surprised

...heres the line written correctly...

' WHERE ml_id IN (''WP-01'',''WP-02'',''WP-03'',''WP-04'')';



I promise I really was paying attention when LittleFoot was explaining the tic and space "relationship", but...

Instead of seeing the obvious test Marc and Michael were trying to suggest, I had stuck myself in this ridiculous "loop" of adding and subtracting tics to try and find the correct combination.

Finally just wrote the correct query for a result from a single table taken from the [ALL_TAB_COLS] result using the necessary list.

When that succeeded, I enclosed that line in tics, and then enclosed each value from my IN list in thier tics and VOILA!!
Laughing


EXCELLEN FORUM!
EXCELLENT ADVICE!
EXCELLENT PATIENCE FOR THOSE OF US STILL BUILDING OUR EXPEREINCE!

Much Thanks All!!
Re: FIND table FROM all_tab_cols WHERE column value = something [message #295853 is a reply to message #295837] Wed, 23 January 2008 14:10 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh, I wish there were more thrilled users here, just like you! Many of them come and go, happy with answers or not, but such an enthusiasm is rarely seen.

I'm glad you found the solution and learnt something new. Really nice!
Re: FIND table FROM all_tab_cols WHERE column value = something [message #295854 is a reply to message #295853] Wed, 23 January 2008 14:14 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
And as far as dbms_output goes, be sure you

set serveroutput on


Re: FIND table FROM all_tab_cols WHERE column value = something [message #296202 is a reply to message #295837] Fri, 25 January 2008 01:45 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Now that you've got it, try not to use it for anything else. Although this dynamically creating sql-statements looks promising, it is something that you should try to avoid as much as possible.
There are some cases in which it is useful (you just found one), but in general you should first try to do it with static sql.
Re: FIND table FROM all_tab_cols WHERE column value = something [message #296274 is a reply to message #296202] Fri, 25 January 2008 08:39 Go to previous messageGo to next message
manningda
Messages: 31
Registered: January 2008
Member
Shocked

...ahhh the voice of "reaason" shows it face... Smile


I must confess I saw this advice repeatedly while I was trying to research my answers above, and do certainly see the benefit of stadard SQL "nesting" for complex queries.

But...

Im not entirely certain I understand why dynamic sql is "bad" per-se...

Is it due to processing concerns or other reasons.

Id love to hear some opinions about why dynamic sql is discouraged by so many...

Smile
Re: FIND table FROM all_tab_cols WHERE column value = something [message #296275 is a reply to message #295561] Fri, 25 January 2008 08:43 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
>Id love to hear some opinions about why dynamic sql is discouraged by so many...
It does not scale.
It forces HARD parse for every execution & does not use bind variables.
Forces context switch between PL/SQL & SQL engines.
Re: FIND table FROM all_tab_cols WHERE column value = something [message #296276 is a reply to message #296275] Fri, 25 January 2008 08:45 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
plus:
it is not checked at compile-time but at runtime
it does not automatically handle (or shows) dependencies
in general it is harder to read/maintain (especially when using lots of quotes and concatenations)


[Edit: although it CAN use bind variables..]

[Updated on: Fri, 25 January 2008 08:46]

Report message to a moderator

Previous Topic: multiple table trigger or constraint
Next Topic: Handling different datatypes
Goto Forum:
  


Current Time: Mon Dec 05 23:47:17 CST 2016

Total time taken to generate the page: 0.05517 seconds