Execute Immediate Issue (merged 4) [message #445797] |
Thu, 04 March 2010 03:36 |
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 |
cookiemonster
Messages: 13961 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
|
|
|
|
|
|
|