How to call a function that fills all table columns with random values?-CodePudding

I have created a function called customers_data tha returns random values in the customers table. As far everthing compiled successfully. But when i call this

DECLARE

X_C NUMBER NOT NULL :=5 ;
Y_C NUMBER NOT NULL :=8 ;

BEGIN 

 FOR LOOP_COUNTER IN X_C..Y_C LOOP

   select * into CUSTOMERS from CUSTOMERS_DATA;

 END LOOP;

END;

THE ERROR IS:

ORA-06550

AND THE CUSTOMERS_DATA FUNCTION :

CREATE OR REPLACE FUNCTION  CUSTOMERS_DATA

RETURN NUMBER AS CR_ID NUMBER;

   BEGIN

      CR_ID := CUSTOMERS_ID_SEQ.NEXTVAL;

     INSERT INTO CUSTOMERS(CUSTOMERS_ID, CUSTOMERS_CODE )

          VALUES(CR_ID, dbms_random.string('X',10));

     
     RETURN CR_ID;

     
END CUSTOMERS_DATA;

CodePudding user response：

To me, it looks as if you wanted to run this:

Function:

SQL> CREATE OR REPLACE FUNCTION CUSTOMERS_DATA
  2     RETURN NUMBER
  3  AS
  4     CR_ID  NUMBER;
  5  BEGIN
  6     CR_ID := CUSTOMERS_ID_SEQ.NEXTVAL;
  7
  8     INSERT INTO CUSTOMERS (CUSTOMERS_ID, CUSTOMERS_CODE)
  9          VALUES (CR_ID, DBMS_RANDOM.string ('X', 10));
 10
 11     RETURN CR_ID;
 12  END CUSTOMERS_DATA;
 13  /

Function created.

PL/SQL block:

SQL> DECLARE
  2     X_C   NUMBER NOT NULL := 5;
  3     Y_C   NUMBER NOT NULL := 8;
  4     l_id  NUMBER;
  5  BEGIN
  6     FOR LOOP_COUNTER IN X_C .. Y_C
  7     LOOP
  8        l_id := customers_data;
  9     END LOOP;
 10  END;
 11  /

PL/SQL procedure successfully completed.

Result:

SQL> SELECT * FROM customers;

CUSTOMERS_ID CUSTOMERS_CODE
------------ --------------------
           1 465MT9ECDL
           2 4TY1QS8S75
           3 VNN0ZVP12W
           4 HSP5RIQSIE

SQL>