Oracle PLSQL无效游标错误我不明白

只是为了缩小范围 simple test case ,我想这就是您看到的错误:

declare
    my_cursor sys_refcursor;
begin
    open my_cursor for 'select ''Hello, world'' as message from dual';
    dbms_sql.return_result(my_cursor);
    close my_cursor;  -- << Remove this line
end;
/

ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at line 6

这是因为您试图在已将光标传递给时关闭光标 dbms_sql 用于处理。使用移除线路 close my_cursor .

declare
    my_cursor sys_refcursor;
begin
    open my_cursor for 'select ''Hello, world'' as message from dual';
    dbms_sql.return_result(my_cursor);
end;
/

PL/SQL procedure successfully completed.

ResultSet #1

MESSAGE
------------
Hello, world

1 row selected.

I had same kind of issue when i tried to print Ref_cursor directly. Then i created a Record type variable and then fetched field values in that variable and then i used DBMS_OUTPUT for that record type variable.
Please see if below code and scenario can help you-

set serveroutput on;
declare
v_sql  varchar2(1000);  
v_cursor sys_refcursor;
type myrec is record(col1 varchar2(100),col2 varchar2(1000));
rec myrec;
begin
  v_sql:='select name,status from t_employee where user_id in (''C001117'',''C001122'')'; 

    open v_cursor for v_sql;

  loop
    fetch v_cursor
     into rec;
    exit when v_cursor%notfound;
    dbms_output.put_line( rec.col1||':status  '||rec.col2 );
  end loop;


end;
/

以下是我的半完整脚本。给定一个表列表,它将尝试激活RI约束,如果它们失败,它将打印出子表中阻止应用它的FK数据记录。

该项目最困难的部分是FKs可以是任意数量的列和任何类型,因此在这种情况下打印select的结果非常棘手(IMO)。

感谢人们提供的帮助。

cd $OGGHOME/scripts
. ./functions.sh

$ORACLE_HOME/bin/sqlplus ${ORACLE_USERID}/${ORACLE_PASSWORD}@${ORACLE_SID} << EOF 
set echo on
set feedback on
set heading off
set serveroutput on size unlimit

DECLARE 
  finalsql varchar2(2048);
  part1sql varchar2(1024) ;
  part2sql varchar2(1024) := ' ';
  cownername varchar2(1024);
  ctablename varchar2(1024);
  pownername varchar2(1024);
  ptablename  varchar2(1024);
  cnt number := 0;

  desc_tab dbms_sql.desc_tab;
  col_count INTEGER;

  cursor_name INTEGER;

  -- Weak cursor defs
  my_cursor sys_refcursor;
  col1 varchar2(50);
  d     number;
  j     number;
  lineout varchar2(2048);
  plineout varchar2(2048);
  rows number;
  eCount number := 0;


BEGIN
    FOR i in (
                select owner, table_name, constraint_name
                from dba_constraints
                where constraint_type = 'R'
                and status = 'DISABLED'
                and owner = '$DBSCHEMA'
                and TABLE_NAME in (
'RRNG_MTC_STN_CPLY',
'NRNG_MTC_VST_MTRL_USG',
'NRNG_MTC_VST',
'MTC_TSK_HRHY'))
    LOOP
        BEGIN
                dbms_output.put_line ('.');
                dbms_output.put_line ('=====================================');
                dbms_output.put('alter table '||i.owner|| '.' || i.table_name || ' enable constraint '||i.constraint_name);
                execute immediate 'alter table '||i.owner|| '.' || i.table_name || ' enable constraint '||i.constraint_name;
                dbms_output.put_line (' ... SUCCESS');

            EXCEPTION -- exception handling - dump offending data
            WHEN OTHERS THEN
            eCount := eCount + 1;
            dbms_output.put_line (' ... FAILED.  Constraint Name:  ' || i.constraint_name);

            finalsql := 'SELECT DISTINCT ';
            part1sql := '';
            part2sql := ' ';
            cnt := 0;


            for constraint in (
            SELECT      ucc1.OWNER as childowner,
                        ucc1.TABLE_NAME as childtable,
                        ucc1.column_name as childcolumn,
                        ucc2.OWNER as parentowner,
                        ucc2.TABLE_NAME as parenttable,
                        ucc2.column_name as parentcolumn,
                        utc1.data_type as childdatatype,
                        utc1.data_length as childdatalen
            FROM        all_constraints uc ,
                        all_cons_columns ucc1 ,
                        all_cons_columns ucc2,
                        all_tab_columns utc1
            WHERE       
                uc.constraint_name = ucc1.constraint_name
                AND uc.r_constraint_name = ucc2.constraint_name
                AND ucc1.POSITION        = ucc2.POSITION
                AND ucc1.table_name      = utc1.table_name
                AND ucc1.column_name     = utc1.column_name 
                AND uc.constraint_type   = 'R'
                AND uc.constraint_name   = i.constraint_name
            ORDER BY ucc1.TABLE_NAME ,
                     uc.constraint_name)
            loop
                cownername := constraint.childowner;
                ctablename := constraint.childtable;
                pownername := constraint.parentowner;
                ptablename := constraint.parenttable;
                if cnt > 0 then
                    part1sql := part1sql || ' , ';
                    part2sql := part2sql || ' AND ';
                end if;
                part1sql := part1sql || constraint.childtable || '.' || constraint.childcolumn || ' ';
                part2sql := part2sql || constraint.childtable || '.' || constraint.childcolumn || ' = ' 
                            || constraint.parenttable || '.' || constraint.parentcolumn;
                cnt := cnt + 1;
            end loop;

            finalsql := finalsql || part1sql || ' FROM ' ||  ' ' || cownername || '.' || ctablename ||  ' WHERE NOT EXISTS (SELECT 1 FROM ' || 
            pownername || '.' || ptablename || ' WHERE ' || part2sql || ') FETCH FIRST 100 rows only';


            dbms_output.put_line ('Final SQL = (' || finalsql || ')');          
--          dbms_output.put_line ('---xxx End SQL');

            lineout := 'Child Table:  ' || ctablename || '('; 
            plineout := 'Parent Table:  ' || ptablename;

            cursor_name := dbms_sql.open_cursor;
            dbms_sql.PARSE (cursor_name, finalsql, DBMS_SQL.NATIVE);

            d := dbms_sql.execute (cursor_name);


            dbms_sql.describe_columns (cursor_name, col_count, desc_tab);
            for j in 1..col_count
            LOOP
                DBMS_SQL.DEFINE_COLUMN (cursor_name, j, col1, 30);

                lineout := lineout || desc_tab(j).col_name || ' , ';
--              plineout := plineout || constraint.parentcolumn || ' ';
--              dbms_output.put_line ('Column 1: ' || j || ' is ' || desc_tab(j).col_name || ' type ' 
--                      || desc_tab(j).col_type);
            END LOOP j;
        lineout := lineout || ')';
--      plineout := plineout || ')';

        dbms_output.put_line (lineout);
        dbms_output.put_line (plineout);

        lineout := NULL;
        for j in 1..col_count
        LOOP
            if j > 1 then
                lineout := lineout || '        ';
            end if;
            lineout := lineout || desc_tab(j).col_name;
        END LOOP;

        dbms_output.put_line (lineout);

        dbms_output.put_line ('----------------------------------------');

        LOOP
            rows := dbms_sql.fetch_rows (cursor_name);
            EXIT WHEN rows = 0;
            lineout := NULL;

            for j in 1..col_count
            LOOP
                dbms_sql.column_value (cursor_name, j, col1);
                if j > 1 then
                    lineout := ltrim(lineout || '        ' || col1);
                else
                    lineout := col1;
                END IF;
            END LOOP;
            dbms_output.put_line (lineout);
        END LOOP;

        dbms_sql.close_cursor (cursor_name);
        END;
    end loop;
end;
/
EOF

你的 FETCH FIRST 100 rows only 似乎不合适。 这是 BULK COLLECT a中的条款 SELECT PL/SQL语句;据我所知,它不是SQL语句的一部分,您不能像这样传递到游标中 这将导致游标语句无效