How can i insert multiple record into a table at runtime PL SQL on (apex.oracle.com)-CodePudding

I want to insert new records into the table at runtime. and I'm using apex.oracle.com platform.

I have created the following table:-

CREATE TABLE STUDENTS(ID VARCHAR2(10),NAME VARCHAR2(50),COURSE VARCHAR2(10),MOBILE NUMBER(10),EMAIL VARCHAR2(50));

And create this procedure to insert record into the table:-

CREATE OR REPLACE PROCEDURE "INSERTRECORD" (ID IN VARCHAR2,NAME IN VARCHAR2,COURSE IN VARCHAR2,MOBILE IN NUMBER,EMAIL IN VARCHAR2)
IS
BEGIN
INSERT INTO STUDENTS VALUES(ID,NAME,COURSE,MOBILE,EMAIL);
END;

DECLARE
RECORD_SIZE NUMBER := 2;
ID VARCHAR2(10);
NAME VARCHAR2(50);
COURSE VARCHAR2(10);
MOBILE NUMBER(10);
EMAIL VARCHAR2(50);
I NUMBER;
BEGIN
FOR I IN 1..RECORD_SIZE LOOP
ID:=:ID;
NAME:=:NAME;
COURSE:=:COURSE;
MOBILE:=:MOBILE;
EMAIL:=:EMAIL;
INSERTRECORD(ID,NAME,COURSE,MOBILE,EMAIL);
END LOOP;
END;

I'm not able to insert record at runtime given by the user....I am getting this error. Can someone fix this ? Or can help me out in inserting records into table at runtime.

Error:-

ORA-01722: invalid number
ORA-06512: at "WKSP_CUPLSQL.INSERTRECORD", line 4
ORA-06512: at line 16
ORA-06512: at "SYS.DBMS_SQL", line 1721


2. RECORD_SIZE NUMBER := 2;
3. ID VARCHAR2(10);
4. NAME VARCHAR2(50);
5. COURSE VARCHAR2(10);
6. MOBILE NUMBER(10);

CodePudding user response：

I'm unsure why you need a procedure. In Oracle, to insert multiple rows into table t with columns col1, col2 and col3 you can use the following syntax:

INSERT ALL
   INTO t (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')
   INTO t (col1, col2, col3) VALUES ('val2_1', 'val2_2', 'val2_3')
   INTO t (col1, col2, col3) VALUES ('val3_1', 'val3_2', 'val3_3')
   .
   .
   .
SELECT 1 FROM DUAL;

Or

INSERT  INTO t (col1, col2, col3) 
   SELECT 'val1_1', 'val1_2', 'val1_3' from dual UNION ALL
SELECT 'val1_4', 'val1_5', 'val1_6'
FROM DUAL

The thing to remember here is to use the from dual statement.

CodePudding user response：

Try this. It works for me.


DECLARE
    record_count NUMBER := 10;
BEGIN
    FOR i IN 1..record_count
    LOOP
        insertrecord (:id, :student_name, :course, :mobile, :email);
    END LOOP;
END;
/

NAME is an Oracle keyword, so it's not a good column or variable name. I changed it to student_name.

CodePudding user response：

This code worked for me... But It is asking input only for 1 time. and that one record is being inserted for RECORD_SIZE := 10 times. I want, it must ask for input for every new record i want to insert... !!! Any Help ?

CREATE OR REPLACE PROCEDURE "INSERTRECORD" (ID IN VARCHAR2,NAME IN VARCHAR2,COURSE IN VARCHAR2,MOBILE IN NUMBER,EMAIL IN VARCHAR2)
IS
BEGIN
INSERT INTO STUDENTS(ID,NAME,COURSE,MOBILE,EMAIL) VALUES(ID,NAME,COURSE,MOBILE,EMAIL);
END;

DECLARE
RECORD_SIZE NUMBER := 10;
ID VARCHAR2(10);
NAME VARCHAR2(50);
COURSE VARCHAR2(10);
MOBILE NUMBER(10);
EMAIL VARCHAR2(50);
I NUMBER;
BEGIN
FOR I IN 1..RECORD_SIZE LOOP
ID:=:ID;
NAME:=:NAME;
COURSE:=:COURSE;
MOBILE:=:MOBILE;
EMAIL:=:EMAIL;
INSERTRECORD(ID,NAME,COURSE,MOBILE,EMAIL);
END LOOP;
END;