0

I have a table "active_t" in a schema called "test", having the following structure:

      instance      active
          IN          1
          SI          0

The ask is to loop through this table and for each active schema(i.e., having active=1), the code should query another table "company_t" having the following structure:

 code    deldate
 1234     "some date"
 9876     null
 7321     null
 9097     "some date"

The code needs to retrieve those company codes from company_t which have deldate as null. Once picked up, the code should further run an insert statement and insert some data in the another table "rec_t" in the test schema.

The basic code that I tried was this:

SET SERVEROUTPUT ON;
declare
v_sql varchar2(200);
v_cntry active_t.instancename%type;
cursor c1 is
select instancename, active from active_t
where active = '1';
begin
for rec in c1
loop
execute IMMEDIATE 'select code from '||rec.instancename||'.company_t where deldate is null' into v_sql;
dbms_output.put_line(v_sql);
end loop;
end;
/ 

But I get an error as below:

ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 10
ORA-06512: at line 10
01422. 00000 -  "exact fetch returns more than requested number of rows"
*Cause:    The number specified in exact fetch is less than the rows returned.
*Action:   Rewrite the query or change number of rows requested

Please help what needs to be fixed. If this simple code works then I will try to implement the insert statement. Thanks in advance.

2
  • Any suggestions please ?
    – Yuvraj
    Commented Sep 20, 2022 at 3:45
  • the error is in line 10. Line 1 of the pl/sql block is "declare", line 10 is the "execute IMMEDIATE" statement.
    – miracle173
    Commented Oct 11, 2022 at 7:38

1 Answer 1

0

It looks like you want to see: select code from IN.company_t where deldate is null as the output from your code block? In which case the execute immediate is your problem as it is trying to run the query which is returning 2 rows to an expected single-row result.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.