Oracle PLSQL invalid cursor error I don't understand

Question

I'm still a relatively newbe when it comes to PL/SQL.

Using Oracle 12c on Linux RHEL 6.8, the following shell script will attempt to activate all RI constraints in a collection of tables, and if they fail with parent key failures, it will dump the first 100 rows (or less) of the offending data. Or at least that is the goal. Since the script deals mostly with system tables on 12c (with only a small user table list that is unique to my installation), I'm including the whole thing exactly from my environment.

The main work occurs in the exception handling where the system tables are queried for the constraint, and user queries are formed from those data.

As a extra goal, the output is rather messy and I want to clean it up, but first it has to work :)

The output / error I get for my tables is the following:

ERROR Handling here for table NRNG_MTC_VST Constraint Name: SYS_C0011790 Final SQL = SELECT DISTINCT NRNG_MTC_VST.LOG_CRT_DT , NRNG_MTC_VST.NRRNG_MTC_LG_ID FROM ODB_PRIMARY.NRNG_MTC_VST WHERE NOT EXISTS (SELECT 1 FROM ODB_PRIMARY.NRNG_MTC_LOG WHERE NRNG_MTC_VST.LOG_CRT_DT = NRNG_MTC_LOG.LOG_CRT_DT AND NRNG_MTC_VST.NRRNG_MTC_LG_ID = NRNG_MTC_LOG.NRRNG_MTC_LG_ID) FETCH FIRST 100 rows only ---xxx End SQL DECLARE * ERROR at line 1: ORA-01001: invalid cursor ORA-06512: at line 111 ORA-02298: cannot validate (ODB_PRIMARY.SYS_C0011790) - parent keys not found

The output SQL from my print_line is correct, and would work if pasted directly into a SQLDeveloper session. There is just something silly about how the cursor is defined I don't understand.

The full text of the script. BYW, if you see other bonehead changes that should be made unrelated to the error, please suggest them as well.

cd $OGGHOME/scripts
export ORACLE_SID=odbod07 $ORACLE_HOME/bin/sqlplus <<-EOF / as sysdba
alter session set container=p01_odbod07; 
set echo on set feedback on
set heading off 
set serveroutput on size 10000

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;
-- Weak cursor defs   
my_cursor sys_refcursor;

BEGIN   FOR i in (
                select owner, table_name, constraint_name
                from dba_constraints
                where constraint_type = 'R'
                and status = 'DISABLED'
                and owner = 'ODB_PRIMARY'
                and TABLE_NAME in 
-- enter user tables with RI constraints here
('RRNG_MTC_STN_CPLY',   
'NRNG_MTC_VST_MTRL_USG',    
'NRNG_MTC_VST',     
'CAR_CORE',
'NRNG_MTC_LOG')) 
-- end user table definitions, rest of code should rely only on system tables   
LOOP BEGIN
   dbms_output.put_line('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;
EXCEPTION
-- exception handling - dump offending data 
WHEN OTHERS THEN      -- take all exceptions for now        
  dbms_output.put_line ('ERROR Handling here for table ' ||
      i.table_name || ' 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');            
    open my_cursor for finalsql;            
    dbms_sql.return_result(my_cursor);              
    close my_cursor;
--  EXECUTE IMMEDIATE finalsql;         
END;    
end loop; end; 
/ 
EOF

Many thanks for any help provided. Brian

score 1 · Accepted Answer · answered Jan 21 '18 at 14:32

Just to narrow this down to a simple test case, I think this is the error you are seeing:

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

This is because you attempted to close the cursor when you have already passed it to dbms_sql for processing. Remove the line with 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.

You've technically solved the direct problem I encountered. What I discovered however was this really didn't solve my business need. And I had to go further into dbms_sql land. See my completed script below. — bfoddy, Jan 23 '18 at 21:38

score 0 · Answer 2 · answered Jan 21 '18 at 07:53

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

I think you're on the right track, but missing a critical element. Until runtime I don't know how many columns to define a record for. I think I need to close this question and simplify it to this issue. — bfoddy, Jan 21 '18 at 09:39

score 0 · Answer 3 · answered Jan 23 '18 at 21:45

The following is my semi-complete script. Given a table list, it will attempt to activate the RI Constraints, and if they fail it will print out the FK data records in the child table that prevent it from being applied.

The hardest part of this project was the fact that the FKs can be any number of columns and of any type, so the print the results of the select in this case was very tricky (IMO).

Thanks for the help people provided.

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

score -1 · Answer 4 · answered Jan 21 '18 at 02:42

-1

your FETCH FIRST 100 rows only would seem to be out of place. This is part of the BULK COLLECT clause in a SELECT statement in PL/SQL; as far as I know, it is not part of a SQL statement you can pass into a cursor like this This is resulting in the cursor statement being invalid

answered Jan 21 '18 at 02:42

ishando

306
3
7

When I remove the FETCH FIRST clause, it still errors. As I started hunting down the error more, I determined the CLOSE statement was actually where the error was. By removing the close call, it actually works. But the result is all the diagnostic information is printed above the results output, making it difficult to correlate the table with the data rows the query is running. Why the close call is invalid? That's another mystery. – bfoddy Jan 21 '18 at 03:01
Odd, when I was testing a version of this, it was erroring on the open cursor clause.Possibly the return_result is closing the cursor, so with the explicit close the cursor is no longer valid. I don't have v12 handy to check this. – ishando Jan 21 '18 at 03:43
I think I'm improperly mixing PLSQL cursors and DBMS cursors by using the line dbms_sql.return_result. When I remove that line, then the close line works. So I need to figure out how to print the results of the cursor for dynamic sql back to the screen. – bfoddy Jan 21 '18 at 04:22
`fetch first 100 rows only` is perfectly valid SQL, although it would make more sense accompanied by an `order by`. – William Robertson Jan 21 '18 at 14:21
Also, if there was a syntax error in the dynamic SQL then the error message would reflect that, not 'invalid cursor'. – William Robertson Jan 21 '18 at 14:45

Oracle PLSQL invalid cursor error I don't understand

4 Answers4

Linked