Home » SQL & PL/SQL » SQL & PL/SQL » Execute Immediate Issue (merged 4) (Oracle 10g R2)
Execute Immediate Issue (merged 4) [message #445797] Thu, 04 March 2010 03:36 Go to next message
antenc
Messages: 13
Registered: September 2009
Location: UK
Junior Member
set serveroutput on

DECLARE
    CURSOR fail_tables_cur IS
        SELECT table_name FROM user_tables WHERE table_name like '%_FAIL';
    vSQL    varchar2(200) := null;
BEGIN
    FOR rec IN fail_tables_cur
    LOOP
        vSQL := 'SELECT count(*) FROM ' || rec.table_name;
        dbms_output.put_line(vSQL);
         EXECUTE IMMEDIATE vSQL;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('Error: ' || sqlerrm);
END;

Hi,

I am trying to find the number of records in each of my fail tables, but at the time of executing the SQL query I don't know the name of the table. So I have tried to build up a SQL string and EXECUTE IMMEDIATE on that SQL query. However, it doesn't appear to be executing at all. I am printing out the SQL string and the output is as follows:
SELECT count(*) FROM MATITEMBADGES_FAIL
SELECT count(*) FROM RELPERSONFORENAMES_FAIL
SELECT count(*) FROM RELPERSONLOCATION_FAIL
SELECT count(*) FROM PERSONWARNINGSIGS_FAIL
SELECT count(*) FROM PERSONWARNSIGS_FAIL
SELECT count(*) FROM PERSON_FAIL
SELECT count(*) FROM PRECOURTEVENT_FAIL
SELECT count(*) FROM VHINTEL_FAIL
SELECT count(*) FROM XML_CUST_NOMINAL_FAIL
SELECT count(*) FROM RELPERSON_FAIL
SELECT count(*) FROM SEARCHPERSONINTIMATE_FAIL
SELECT count(*) FROM SEARCHPERSONPROP_FAIL
SELECT count(*) FROM VHTEXT_FAIL
SELECT count(*) FROM MATITEMCUSTODYSAMPLE_FAIL
SELECT count(*) FROM MATITEMCYCLES_FAIL
SELECT count(*) FROM MATITEMDRUGS_FAIL
SELECT count(*) FROM MATITEMMARKS_FAIL
SELECT count(*) FROM MATITEMPERSONPROP_FAIL
SELECT count(*) FROM MATITEMPROPERTY_FAIL
SELECT count(*) FROM MATITEMSAMPLEINTIMATE_FAIL
SELECT count(*) FROM MATITEMSOCO_FAIL
SELECT count(*) FROM CRIMEMATITEM_FAIL
SELECT count(*) FROM CRIMEPERSON_FAIL
SELECT count(*) FROM CRIME_FAIL
SELECT count(*) FROM CUSTODYCHARGES_FAIL
SELECT count(*) FROM CUSTODYPOLICEWORKER_FAIL
SELECT count(*) FROM CUSTODYRECREVIEW_FAIL
SELECT count(*) FROM CUSTODYREC_FAIL
SELECT count(*) FROM DETENTION_FAIL
SELECT count(*) FROM DISPOSAL_FAIL
SELECT count(*) FROM LIADDRESS_FAIL
SELECT count(*) FROM LIINTEL_FAIL
SELECT count(*) FROM LITEXT_FAIL
SELECT count(*) FROM MATITEMVEHICLE_FAIL
SELECT count(*) FROM NIADDRESS_FAIL
SELECT count(*) FROM NIBAIL_FAIL
SELECT count(*) FROM NICONVICTIONDETAILS_FAIL
SELECT count(*) FROM NICONVICTIONS_FAIL
SELECT count(*) FROM NIINTEL_FAIL
SELECT count(*) FROM NITEXT_FAIL
SELECT count(*) FROM NIWARRANT_FAIL
SELECT count(*) FROM OFFENCEDISPOSAL_FAIL
SELECT count(*) FROM OFFENCE_FAIL
SELECT count(*) FROM BAILCONDITIONS_FAIL
SELECT count(*) FROM BIADDRESS_FAIL
SELECT count(*) FROM BIINTEL_FAIL
SELECT count(*) FROM BITEXT_FAIL
SELECT count(*) FROM CASEPERSON_FAIL
SELECT count(*) FROM CASEPOLICEWORKER_FAIL
SELECT count(*) FROM PROCESS_FAIL
SELECT count(*) FROM OLD_PERSON_FAIL
SELECT count(*) FROM PERSONLOCATION_FAIL
PL/SQL procedure successfully completed.

Can anyone help? As I thought it would give me the count of each of these tables one by one?

Thanks in advance,

Carl



CM: Added code tags, please do so yourself next time - see the orafaq forum guide if you're not sure how.

[Updated on: Thu, 04 March 2010 03:57] by Moderator

Report message to a moderator

Re: Execute Immediate Issue (merged 4) [message #445804 is a reply to message #445797] Thu, 04 March 2010 03:56 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
When using execute immediate for select statements you need to tell oracle to put the result somewhere, as you can see in the documentation. It doesn't send it to screen.

So:
EXECUTE IMMEDIATE vSQL INTO l_output_variable;


However for your requirement I prefer the xml approach:
select table_name,
       to_number(
         extractvalue(
           dbms_xmlgen.getxmltype('select count(*) counter from ' ||
                                  u.table_name),
                      '/ROWSET/ROW/COUNTER')) counter
from user_tables
Re: Execute Immediate Issue (merged 4) [message #445814 is a reply to message #445797] Thu, 04 March 2010 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select table_name,
       to_number(extractvalue(
                   dbms_xmlgen.getXMLtype ('select count(*) cnt from '||table_name),
                   '/ROWSET/ROW/CNT')) rows_in_table
from user_tables
where ( tablespace_name is not null or partitioned='YES' )
  and table_name like '%_FAIL'
order by 1
/

Regards
Michel
Re: Execute Immediate Issue (merged 4) [message #445816 is a reply to message #445814] Thu, 04 March 2010 04:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Call me picky, but I'd actually prefer an application design where I knew at compile time what tables I had to play with.
Re: Execute Immediate Issue (merged 4) [message #445821 is a reply to message #445797] Thu, 04 March 2010 04:33 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
He could just be using it for testing.
That's what I use that query for - checking some of my archiving routines. It's nowhere in the actual application code.
Re: Execute Immediate Issue (merged 4) [message #445823 is a reply to message #445804] Thu, 04 March 2010 04:39 Go to previous message
antenc
Messages: 13
Registered: September 2009
Location: UK
Junior Member
Aha,

That's great! Thanks alot. I have taken your advice, and used the other way. Works a lot better. Cheers,

Carl
Previous Topic: timestamp = sysdate-1 (merged 2)
Next Topic: Need help in function
Goto Forum:
  


Current Time: Thu Sep 29 05:35:55 CDT 2016

Total time taken to generate the page: 0.11078 seconds