when I run below code in oracle developer, I get error:
7/5 PL/SQL: SQL Statement ignored 14/2 PL/SQL: ORA-00933: SQL command not properly ended Errors: check compiler log
CREATE TABLE table_prc4 (
id NUMBER,
name VARCHAR2(20)
);
/
CREATE SEQUENCE pp_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;
/
CREATE OR REPLACE PROCEDURE addnewmembe (
str IN VARCHAR2
) AS
BEGIN
INSERT INTO table_prc4 (id)
VALUES (pp_seq) ;
INSERT INTO table_prc4 (name)
SELECT
regexp_substr(str, '[^,]+', 1, level) AS parts
FROM
dual
CONNECT BY
regexp_substr(str, '[^,]+', 1, level) IS NOT NULL
COMMIT ;
END ;
/
BEGIN
addnewmembe('faezeh,mina,pari');
END;
/
New contributor
Faezehprogrammer is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
This is error you got:
SQL> CREATE OR REPLACE PROCEDURE addnewmembe (
2 str IN VARCHAR2
3 ) AS
4 BEGIN
5 INSERT INTO table_prc4 (id)
6 VALUES (pp_seq) ;
7 INSERT INTO table_prc4 (name)
8 SELECT
9 regexp_substr(str, '[^,]+', 1, level) AS parts
10 FROM
11 dual
12 CONNECT BY
13 regexp_substr(str, '[^,]+', 1, level) IS NOT NULL;
14
15 COMMIT ;
16 END ;
17 /
Warning: Procedure created with compilation errors.
SQL> show err
Errors for PROCEDURE ADDNEWMEMBE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/5 PL/SQL: SQL Statement ignored
6/18 PL/SQL: ORA-00984: column not allowed here
SQL>
Lines #5 and 6:
5 INSERT INTO table_prc4 (id)
6 VALUES (pp_seq) ;
------
this is a culprit
What is pp_seq? You can’t reference a sequence only by its name; should’ve been pp_seq.nextval:
SQL> CREATE OR REPLACE PROCEDURE addnewmembe (
2 str IN VARCHAR2
3 ) AS
4 BEGIN
5 INSERT INTO table_prc4 (id)
6 VALUES (pp_seq.nextval) ;
7 INSERT INTO table_prc4 (name)
8 SELECT
9 regexp_substr(str, '[^,]+', 1, level) AS parts
10 FROM
11 dual
12 CONNECT BY
13 regexp_substr(str, '[^,]+', 1, level) IS NOT NULL;
14
15 COMMIT ;
16 END ;
17 /
Procedure created.
SQL>
(I also fixed a missing semi-colon terminator in the 2nd INSERT statement.)
However, what you’re doing is wrong because this is the result:
SQL> BEGIN
2 addnewmembe('faezeh,mina,pari');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> select * from table_prc4;
ID NAME
---------- --------------------
1
faezeh
mina
pari
SQL>
Apparently, that should’ve been a single insert statement:
5 INSERT INTO table_prc4 (id, name)
6 SELECT
7 pp_seq.nextval,
8 regexp_substr(str, '[^,]+', 1, level) AS parts
9 FROM
10 dual
11 CONNECT BY
12 regexp_substr(str, '[^,]+', 1, level) IS NOT NULL;