CODEWITHSUNDEEP
sqlstored-proceduresoracle-sqldeveloper
dimas
dimas

Reputation: 2597

SQL Procedure error exact fetch returns more than requested number of rows

I have the SQL Procedure below, which displays the error below when run from SQL DEveloper

ERROR Message: 
    ORA-01422: exact fetch returns more than requested number of rows



PROCEDURE SP_ENABLE_CONST(P_ARCH_BATCH_ID VARCHAR) IS


TABLELIST VARCHAR2(100);
SQL_QUERY VARCHAR2(4000);
RESULT_COL INT;
RESULT_CONST_COL VARCHAR2(1000);


BEGIN
/*LOOP will return three tables designated in table_name */
FOR TEMP2_VAR_TEST IN (SELECT TABLE_NAME FROM SYS.ALL_TABLES WHERE TABLE_NAME LIKE 'TEST%')
 
/*each loop the table_name returned will be used in SQL_QUERY */
  LOOP

      SQL_QUERY := 'SELECT CONSTRAINT_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME = :1';
     
      DBMS_OUTPUT.PUT_LINE(SQL_QUERY); 

/*select statement will return more than one row containing table constraints*/
**ERROR on this part since RESULT_CONST_COL cannot contain more than one row
      EXECUTE IMMEDIATE SQL_QUERY INTO RESULT_CONST_COL USING TEMP2_VAR_TEST.TABLE_NAME;
      
      DBMS_OUTPUT.PUT_LINE('CONSTRAINT_NAME' || RESULT_CONST_COL);
          
  END LOOP;     
  END SP_ENABLE_CONST;
END PK_ARCHIVE_PROCESS_TEST;

I understand that the error is caused by result_const_col which is of type VARCHAR2 but am not sure what to use to store multiple rows returned from execute immediate command. I have read about cursor and attempted to use it but still getting errors. Thanks in advance.

Update 1

Modified my code as you have suggested but still encountered errors.

TYPE RESULT_CONST_COL IS TABLE OF VARCHAR2(1000);
VAR_OF_RESULT_CONST RESULT_CONST_COL;

TBL_CONSTRAINTS VARCHAR2(100);
TABLELIST VARCHAR2(100);
SQL_QUERY VARCHAR2(4000);
RESULT_COL INT;


BEGIN

FOR TEMP2_VAR_TEST IN (SELECT TABLE_NAME FROM SYS.ALL_TABLES WHERE TABLE_NAME LIKE 'TEST%')
 
  LOOP      
      SQL_QUERY := 'SELECT CONSTRAINT_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME = :1';          
      DBMS_OUTPUT.PUT_LINE(SQL_QUERY); 

/*ERROR line 92*/
        EXECUTE IMMEDIATE SQL_QUERY USING TEMP2_VAR_TEST.TABLE_NAME
          RETURNING BULK COLLECT INTO VAR_OF_RESULT_CONST;      
  
        FOR I IN 1 .. VAR_OF_RESULT_CONST.COUNT LOOP
          DBMS_OUTPUT.PUT_LINE(VAR_OF_RESULT_CONST(i));
        END LOOP;       
  END LOOP;

When I try to run the code above encountered this error

ORA-06547: RETURNING clause must be used with INSERT, UPDATE, or DELETE statements
**ORA-06512: at "HSNIAPP.PK_ARCHIVE_PROCESS_TEST", line 92**
ORA-06512: at line 6
SELECT CONSTRAINT_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME = :1

Upvotes: 0

Views: 3352

Answers (1)

Yaroslav Shabalin
Yaroslav Shabalin

Reputation: 1644

You have to declare RESULT_CONST_COL as collection:

RESULT_CONST_COL IS TABLE OF VARCHAR2(1000);

And then use BULK COLLECT INTO to return multiple rows:

EXECUTE IMMEDIATE SQL_QUERY BULK COLLECT INTO RESULT_CONST_COL
 USING TEMP2_VAR_TEST.TABLE_NAME;

Then iterate through returned results in collection to process all values:

FOR i IN 1 .. RESULT_CONST_COL.COUNT LOOP
 DBMS_OUTPUT.PUT_LINE(RESULT_CONST_COL(i));
 EXECUTE IMMEDIATE 'ALTER TABLE '|| TEMP2_VAR_TEST ||
  ' DISABLE CONSTRAINT ' || RESULT_CONST_COL(i);
END LOOP;

Upvotes: 2

Related Questions