或-01422 ORA-06512

Question

有人能帮忙解决这个错误吗？我无法编写这个.thanks的pl/sql查询。

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 at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 3

当我运行SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name,'150OMGI') from dba_tables u where owner='150OMGI';时，我有26行。

Answer 1

脚本只需要一行。不是你得到的26块。您必须使用游标并遍历所获得的行：

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;
/