0

Can someone help to solve this error? I couldn't write pl/sql query of this .thanks.

spool 150OMGIddl.sql

    declare c clob;
    begin
   SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name,'150OMGI') into c from dba_tables u where owner='150OMGI';
   dbms_output.put_line(c);
   dbms_output.put(';');
  end;
/
spool off

error:

ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 3

when I run SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name,'150OMGI') from dba_tables u where owner='150OMGI'; I got 26 rows.

0

The script expects just 1 row. Not the 26 you get. You must use a cursor and loop through the rows that you get:

set serveroutput on
DECLARE
  CURSOR CRS IS
    SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name,'150OMGI')
    FROM   dba_tables u
    WHERE  owner='150OMGI';
  C CLOB;
BEGIN
  OPEN CRS;
  LOOP
    FETCH CRS INTO C;
    EXIT WHEN CRS%NOTFOUND;

    dbms_output.put_line(c);
    dbms_output.put(';');
  END LOOP;
  CLOSE CRS;
END;
/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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