SQL & PL/SQL

Howdy,

Thank you in advance for any advice.

I am trying to dynamically execute a series of SQL select statements based on table names and field names from all_tab_columns. I want to count the number of null values in all fields for a specific table.

I am getting the following error message:

ORA-01422: exact fetch returns more than requested number of rows

I am sure that is it due to the following part of my code:

EXECUTE IMMEDIATE v_SQL
INTO v_result;

When I PUT_LINE the dynamically generated SQL statements without trying to load them into v_result, I receive no error. (but of course, I only get the SQL not the resultant data!)

If I limit my cursor to one row, I recieve no error message and the rultant query data is spooled out. (but of course I want to do this for all fields in the table, not just one!)

I am unsure how to resolve this. My entire code is below:





SET serveroutput ON 100000

SPOOL C:\test1.txt;

DECLARE
-- uses native dynamic SQl to reference field names from initialized cursor variables
-- declare/dimension variables

v_SQL VARCHAR2(400);
v_result VARCHAR2(600);
v_table all_tab_columns.table_name%TYPE;
v_field all_tab_columns.column_name%TYPE;
v_count NUMBER :=0;

-- cursor sql to retrieve lookup table information (used as recordset data source)

CURSOR c_1 IS
SELECT a.table_name, a.column_name
FROM all_tab_columns a
WHERE a.OWNER = 'OFZD' AND a.TABLE_NAME = 'ACCT_H'
AND a.COLUMN_NAME IN ( 'ACCT_ADDR_KEY', 'ACCT_ID')
-- AND a.COLUMN_NAME IN ( 'ACCT_ADDR_KEY')
ORDER BY a.COLUMN_NAME;


BEGIN

-- open cursor and initialize the active set

OPEN c_1;
LOOP

-- retrieve information for the first or subsequent record in the cursor/recordset
FETCH c_1 INTO v_table, v_field;

-- exit loop when there are no more rows to fetch
EXIT WHEN c_1%NOTFOUND;

v_result := '';
v_SQL := '';

-- process the fetched ROWS
v_SQL :=
'SELECT AS_OF_DATE || '', ' || v_table || ', ' || v_field || ', '' || COUNT(*)
FROM ' || v_table || '
WHERE ACCT_ADDR_KEY IS NULL AND as_of_Date = ''31-jan-2006''
GROUP BY as_of_date, ' || v_field || '';

EXECUTE IMMEDIATE v_SQL
INTO v_result;

DBMS_OUTPUT.PUT_LINE (v_result);

END LOOP;

-- free resources used by the cursor
CLOSE c_1;

END;
/