PLSQL- SV01

The error occurs because you can't directly use a PL/SQL collection type (t_pk_tab) with EXECUTE IMMEDIATE's USING OUT clause - it expects SQL types, not PL/SQL types. To fix this, we need to either: Use a SQL nested table type instead of a PL/SQL collection, or Use an intermediate SQL query with a cursor Here's the corrected version using a SQL nested table type approach: CREATE OR REPLACE PROCEDURE delete_records_parallel_with_comp_pk (p_table_name IN VARCHAR2, p_pk_columns IN VARCHAR2, p_where_clause IN VARCHAR2 DEFAULT '1=1', p_chunk_size IN NUMBER DEFAULT 10000) IS -- Define SQL types at schema level (create these once outside the procedure) /* CREATE TYPE t_pk_rec AS OBJECT ( pk_col1 NUMBER, pk_col2 VARCHAR2(100) ); CREATE TYPE t_pk_tab AS TABLE OF t_pk_rec; */ l_sql_stmt VARCHAR2(4000); l_task_name VARCHAR2(100) := 'DELETE_TASK_' || TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS'); l_status NUMBER; l_deleted_pks t_pk_tab; -- Using SQL type now l_temp_table_name VARCHAR2(30) := 'TEMP_DEL_PKS_' || TO_CHAR(SYSDATE, 'HH24MISS'); l_pk_col1 VARCHAR2(30); l_pk_col2 VARCHAR2(30); BEGIN -- Parse composite PK columns l_pk_col1 := TRIM(REGEXP_SUBSTR(p_pk_columns, '[^,]+', 1, 1)); l_pk_col2 := TRIM(REGEXP_SUBSTR(p_pk_columns, '[^,]+', 1, 2)); -- Create temporary table EXECUTE IMMEDIATE ' CREATE GLOBAL TEMPORARY TABLE ' || l_temp_table_name || ' ( pk_col1 NUMBER, pk_col2 VARCHAR2(100) ) ON COMMIT PRESERVE ROWS'; DBMS_PARALLEL_EXECUTE.create_task(task_name => l_task_name); DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid( task_name => l_task_name, table_owner => USER, table_name => p_table_name, by_row => TRUE, chunk_size => p_chunk_size ); l_sql_stmt := ' DECLARE TYPE t_pk_rec IS RECORD ( pk_col1 NUMBER, pk_col2 VARCHAR2(100) ); TYPE t_pk_tab IS TABLE OF t_pk_rec INDEX BY PLS_INTEGER; l_pks t_pk_tab; BEGIN FORALL i IN ( SELECT ' || l_pk_col1 || ', ' || l_pk_col2 || ' FROM ' || p_table_name || ' WHERE ' || p_where_clause || ' AND ROWID BETWEEN :start_id AND :end_id ) DELETE FROM ' || p_table_name || ' WHERE (' || l_pk_col1 || ', ' || l_pk_col2 || ') IN ( SELECT ' || l_pk_col1 || ', ' || l_pk_col2 || ' FROM ' || p_table_name || ' WHERE ' || p_where_clause || ' AND ROWID BETWEEN :start_id AND :end_id ) RETURNING ' || l_pk_col1 || ', ' || l_pk_col2 || ' BULK COLLECT INTO l_pks; FORALL i IN 1..l_pks.COUNT INSERT INTO ' || l_temp_table_name || ' (pk_col1, pk_col2) VALUES (l_pks(i).pk_col1, l_pks(i).pk_col2); END;'; DBMS_PARALLEL_EXECUTE.run_task( task_name => l_task_name, sql_stmt => l_sql_stmt, language_flag => DBMS_SQL.NATIVE, parallel_level => 4 ); l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task_name); IF l_status = DBMS_PARALLEL_EXECUTE.FINISHED THEN -- Use explicit SELECT with BULK COLLECT SELECT t_pk_rec(pk_col1, pk_col2) BULK COLLECT INTO l_deleted_pks FROM (SELECT pk_col1, pk_col2 FROM temp_del_pks_123456); -- Replace with actual temp table name DBMS_PARALLEL_EXECUTE.drop_task(l_task_name); EXECUTE IMMEDIATE 'DROP TABLE ' || l_temp_table_name; DBMS_OUTPUT.put_line('Deleted ' || l_deleted_pks.COUNT || ' records'); ELSE EXECUTE IMMEDIATE 'DROP TABLE ' || l_temp_table_name; DBMS_PARALLEL_EXECUTE.drop_task(l_task_name); RAISE_APPLICATION_ERROR(-20001, 'Parallel execution failed: ' || l_status); END IF; EXCEPTION WHEN OTHERS THEN BEGIN EXECUTE IMMEDIATE 'DROP TABLE ' || l_temp_table_name; EXCEPTION WHEN OTHERS THEN NULL; END; DBMS_PARALLEL_EXECUTE.drop_task(l_task_name); RAISE_APPLICATION_ERROR(-20002, 'Error: ' || SQLERRM); END delete_records_parallel_with_comp_pk; / Key changes to fix the error: Schema-level Types: You need to create these types once in your schema (outside the procedure): CREATE TYPE t_pk_rec AS OBJECT ( pk_col1 NUMBER, pk_col2 VARCHAR2(100) ); CREATE TYPE t_pk_tab AS TABLE OF t_pk_rec; Replaced EXECUTE IMMEDIATE: Instead of using EXECUTE IMMEDIATE with USING OUT, we now use a regular SELECT with BULK COLLECT using the SQL type. Alternative approach using a cursor (if you don't want to create schema-level types): CREATE OR REPLACE PROCEDURE delete_records_parallel_with_comp_pk (p_table_name IN VARCHAR2, p_pk_columns IN VARCHAR2, p_where_clause IN VARCHAR2 DEFAULT '

Apr 9, 2025 - 08:06

The error occurs because you can't directly use a PL/SQL collection type (t_pk_tab) with EXECUTE IMMEDIATE's USING OUT clause - it expects SQL types, not PL/SQL types. To fix this, we need to either:

Use a SQL nested table type instead of a PL/SQL collection, or
Use an intermediate SQL query with a cursor

Here's the corrected version using a SQL nested table type approach:

CREATE OR REPLACE PROCEDURE delete_records_parallel_with_comp_pk
    (p_table_name IN VARCHAR2,
     p_pk_columns IN VARCHAR2,
     p_where_clause IN VARCHAR2 DEFAULT '1=1',
     p_chunk_size IN NUMBER DEFAULT 10000)
IS
    -- Define SQL types at schema level (create these once outside the procedure)
    /*
    CREATE TYPE t_pk_rec AS OBJECT (
        pk_col1 NUMBER,
        pk_col2 VARCHAR2(100)
    );
    CREATE TYPE t_pk_tab AS TABLE OF t_pk_rec;
    */

    l_sql_stmt VARCHAR2(4000);
    l_task_name VARCHAR2(100) := 'DELETE_TASK_' || TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS');
    l_status NUMBER;
    l_deleted_pks t_pk_tab;  -- Using SQL type now

    l_temp_table_name VARCHAR2(30) := 'TEMP_DEL_PKS_' || TO_CHAR(SYSDATE, 'HH24MISS');
    l_pk_col1 VARCHAR2(30);
    l_pk_col2 VARCHAR2(30);
BEGIN
    -- Parse composite PK columns
    l_pk_col1 := TRIM(REGEXP_SUBSTR(p_pk_columns, '[^,]+', 1, 1));
    l_pk_col2 := TRIM(REGEXP_SUBSTR(p_pk_columns, '[^,]+', 1, 2));

    -- Create temporary table
    EXECUTE IMMEDIATE '
        CREATE GLOBAL TEMPORARY TABLE ' || l_temp_table_name || ' (
            pk_col1 NUMBER,
            pk_col2 VARCHAR2(100)
        ) ON COMMIT PRESERVE ROWS';

    DBMS_PARALLEL_EXECUTE.create_task(task_name => l_task_name);

    DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(
        task_name    => l_task_name,
        table_owner  => USER,
        table_name   => p_table_name,
        by_row       => TRUE,
        chunk_size   => p_chunk_size
    );

    l_sql_stmt := '
        DECLARE
            TYPE t_pk_rec IS RECORD (
                pk_col1 NUMBER,
                pk_col2 VARCHAR2(100)
            );
            TYPE t_pk_tab IS TABLE OF t_pk_rec INDEX BY PLS_INTEGER;
            l_pks t_pk_tab;
        BEGIN
            FORALL i IN (
                SELECT ' || l_pk_col1 || ', ' || l_pk_col2 || '
                FROM ' || p_table_name || '
                WHERE ' || p_where_clause || '
                AND ROWID BETWEEN :start_id AND :end_id
            )
            DELETE FROM ' || p_table_name || '
            WHERE (' || l_pk_col1 || ', ' || l_pk_col2 || ') IN (
                SELECT ' || l_pk_col1 || ', ' || l_pk_col2 || '
                FROM ' || p_table_name || '
                WHERE ' || p_where_clause || '
                AND ROWID BETWEEN :start_id AND :end_id
            )
            RETURNING ' || l_pk_col1 || ', ' || l_pk_col2 || ' 
            BULK COLLECT INTO l_pks;

            FORALL i IN 1..l_pks.COUNT
                INSERT INTO ' || l_temp_table_name || ' (pk_col1, pk_col2)
                VALUES (l_pks(i).pk_col1, l_pks(i).pk_col2);
        END;';

    DBMS_PARALLEL_EXECUTE.run_task(
        task_name      => l_task_name,
        sql_stmt       => l_sql_stmt,
        language_flag  => DBMS_SQL.NATIVE,
        parallel_level => 4
    );

    l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task_name);

    IF l_status = DBMS_PARALLEL_EXECUTE.FINISHED THEN
        -- Use explicit SELECT with BULK COLLECT
        SELECT t_pk_rec(pk_col1, pk_col2)
        BULK COLLECT INTO l_deleted_pks
        FROM (SELECT pk_col1, pk_col2 
              FROM temp_del_pks_123456);  -- Replace with actual temp table name

        DBMS_PARALLEL_EXECUTE.drop_task(l_task_name);
        EXECUTE IMMEDIATE 'DROP TABLE ' || l_temp_table_name;

        DBMS_OUTPUT.put_line('Deleted ' || l_deleted_pks.COUNT || ' records');
    ELSE
        EXECUTE IMMEDIATE 'DROP TABLE ' || l_temp_table_name;
        DBMS_PARALLEL_EXECUTE.drop_task(l_task_name);
        RAISE_APPLICATION_ERROR(-20001, 'Parallel execution failed: ' || l_status);
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        BEGIN
            EXECUTE IMMEDIATE 'DROP TABLE ' || l_temp_table_name;
        EXCEPTION WHEN OTHERS THEN NULL; END;
        DBMS_PARALLEL_EXECUTE.drop_task(l_task_name);
        RAISE_APPLICATION_ERROR(-20002, 'Error: ' || SQLERRM);
END delete_records_parallel_with_comp_pk;
/

Key changes to fix the error:

Schema-level Types: You need to create these types once in your schema (outside the procedure):

CREATE TYPE t_pk_rec AS OBJECT (
    pk_col1 NUMBER,
    pk_col2 VARCHAR2(100)
);
CREATE TYPE t_pk_tab AS TABLE OF t_pk_rec;

Replaced EXECUTE IMMEDIATE: Instead of using EXECUTE IMMEDIATE with USING OUT, we now use a regular SELECT with BULK COLLECT using the SQL type.

Alternative approach using a cursor (if you don't want to create schema-level types):

CREATE OR REPLACE PROCEDURE delete_records_parallel_with_comp_pk
    (p_table_name IN VARCHAR2,
     p_pk_columns IN VARCHAR2,
     p_where_clause IN VARCHAR2 DEFAULT '1=1',
     p_chunk_size IN NUMBER DEFAULT 10000)
IS
    l_sql_stmt VARCHAR2(4000);
    l_task_name VARCHAR2(100) := 'DELETE_TASK_' || TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS');
    l_status NUMBER;

    TYPE t_pk_rec IS RECORD (
        pk_col1 NUMBER,
        pk_col2 VARCHAR2(100)
    );
    TYPE t_pk_tab IS TABLE OF t_pk_rec INDEX BY PLS_INTEGER;
    l_deleted_pks t_pk_tab;

    l_temp_table_name VARCHAR2(30) := 'TEMP_DEL_PKS_' || TO_CHAR(SYSDATE, 'HH24MISS');
    l_pk_col1 VARCHAR2(30);
    l_pk_col2 VARCHAR2(30);
    l_cursor SYS_REFCURSOR;
BEGIN
    l_pk_col1 := TRIM(REGEXP_SUBSTR(p_pk_columns, '[^,]+', 1, 1));
    l_pk_col2 := TRIM(REGEXP_SUBSTR(p_pk_columns, '[^,]+', 1, 2));

    EXECUTE IMMEDIATE '
        CREATE GLOBAL TEMPORARY TABLE ' || l_temp_table_name || ' (
            pk_col1 NUMBER,
            pk_col2 VARCHAR2(100)
        ) ON COMMIT PRESERVE ROWS';

    DBMS_PARALLEL_EXECUTE.create_task(task_name => l_task_name);

    DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(
        task_name    => l_task_name,
        table_owner  => USER,
        table_name   => p_table_name,
        by_row       => TRUE,
        chunk_size   => p_chunk_size
    );

    l_sql_stmt := '
        DECLARE
            TYPE t_pk_rec IS RECORD (
                pk_col1 NUMBER,
                pk_col2 VARCHAR2(100)
            );
            TYPE t_pk_tab IS TABLE OF t_pk_rec INDEX BY PLS_INTEGER;
            l_pks t_pk_tab;
        BEGIN
            FORALL i IN (
                SELECT ' || l_pk_col1 || ', ' || l_pk_col2 || '
                FROM ' || p_table_name || '
                WHERE ' || p_where_clause || '
                AND ROWID BETWEEN :start_id AND :end_id
            )
            DELETE FROM ' || p_table_name || '
            WHERE (' || l_pk_col1 || ', ' || l_pk_col2 || ') IN (
                SELECT ' || l_pk_col1 || ', ' || l_pk_col2 || '
                FROM ' || p_table_name || '
                WHERE ' || p_where_clause || '
                AND ROWID BETWEEN :start_id AND :end_id
            )
            RETURNING ' || l_pk_col1 || ', ' || l_pk_col2 || ' 
            BULK COLLECT INTO l_pks;

            FORALL i IN 1..l_pks.COUNT
                INSERT INTO ' || l_temp_table_name || ' (pk_col1, pk_col2)
                VALUES (l_pks(i).pk_col1, l_pks(i).pk_col2);
        END;';

    DBMS_PARALLEL_EXECUTE.run_task(
        task_name      => l_task_name,
        sql_stmt       => l_sql_stmt,
        language_flag  => DBMS_SQL.NATIVE,
        parallel_level => 4
    );

    l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task_name);

    IF l_status = DBMS_PARALLEL_EXECUTE.FINISHED THEN
        -- Use cursor to fetch results
        OPEN l_cursor FOR 'SELECT pk_col1, pk_col2 FROM ' || l_temp_table_name;
        FETCH l_cursor BULK COLLECT INTO l_deleted_pks;
        CLOSE l_cursor;

        DBMS_PARALLEL_EXECUTE.drop_task(l_task_name);
        EXECUTE IMMEDIATE 'DROP TABLE ' || l_temp_table_name;

        DBMS_OUTPUT.put_line('Deleted ' || l_deleted_pks.COUNT || ' records');
    ELSE
        EXECUTE IMMEDIATE 'DROP TABLE ' || l_temp_table_name;
        DBMS_PARALLEL_EXECUTE.drop_task(l_task_name);
        RAISE_APPLICATION_ERROR(-20001, 'Parallel execution failed: ' || l_status);
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        BEGIN
            EXECUTE IMMEDIATE 'DROP TABLE ' || l_temp_table_name;
        EXCEPTION WHEN OTHERS THEN NULL; END;
        DBMS_PARALLEL_EXECUTE.drop_task(l_task_name);
        RAISE_APPLICATION_ERROR(-20002, 'Error: ' || SQLERRM);
END delete_records_parallel_with_comp_pk;
/

Choose the approach based on your needs:

First version (schema-level types): Better for reusable code, requires type creation
Second version (cursor): No schema changes needed, self-contained but uses PL/SQL types

Make sure to adjust the data types (NUMBER, VARCHAR2(100)) to match your actual PK columns.