Unable to compile procedure [message #689232] |
Thu, 26 October 2023 12:49 |
|
DorababuMeka
Messages: 11 Registered: October 2023
|
Junior Member |
|
|
I have created an OBJECT TYPE as follows and referring it in TABLE TYPE
reate or replace TYPE EMP_CSV_OBJ AS OBJECT
(
FirstName VARCHAR2(50),
LastName VARCHAR2(50),
MiddleName VARCHAR2(50)
);
create or replace TYPE EMP_CSV_TABLE
AS TABLE OF emp_csv_obj;
I am referring the table type in one of my PROC
CREATE OR REPLACE PROCEDURE INSERT_EMPLOYEE(empCsv IN EMP_CSV_TABLE) AS
BEGIN
MERGE INTO Employee pt
USING empCsv src
ON (pt.FIRSTNAME = src.FIRSTNAME)
WHEN NOT MATCHED THEN INSERT
(pt.FIRSTNAME, pt.LASTNAME, pt.MIDDLENAME)
VALUES (ps.FirstName , ps.LastName , ps.MIDDLENAME);
COMMIT;
END;
Unable to compile any can some one help me
|
|
|
Re: Unable to compile procedure [message #689234 is a reply to message #689232] |
Thu, 26 October 2023 13:27 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
There are several errors in your procedure (USING clause, "ps." instead of "src.". Here's a correct version:
SQL> create or replace TYPE EMP_CSV_OBJ AS OBJECT
2 (
3 FirstName VARCHAR2(50),
4 LastName VARCHAR2(50),
5 MiddleName VARCHAR2(50)
6 );
7 /
Type created.
SQL> create or replace TYPE EMP_CSV_TABLE
2 AS TABLE OF emp_csv_obj;
3 /
Type created.
SQL> CREATE OR REPLACE PROCEDURE INSERT_EMPLOYEE(empCsv IN EMP_CSV_TABLE) AS
2 BEGIN
3 MERGE INTO Employees pt
4 USING (select * from table(empCsv)) src
5 ON (pt.FIRST_NAME = src.FIRSTNAME)
6 WHEN NOT MATCHED THEN INSERT
7 (pt.FIRST_NAME, pt.LAST_NAME, pt.MIDDLE_NAME)
8 VALUES (src.FirstName , src.LastName , src.MIDDLENAME);
9
10 -- COMMIT;
11 END;
12 /
Procedure created.
Note: do NOT commit inside a procedure; the caller knows if he wants to commit or to rollback what the procedure does, the procedure doesn't without speaking that your procedure would also commit work that has been done before its call which may something the caller does not want.
|
|
|
|
|
|
Re: Unable to compile procedure [message #689240 is a reply to message #689234] |
Fri, 27 October 2023 05:14 |
|
DorababuMeka
Messages: 11 Registered: October 2023
|
Junior Member |
|
|
Michel Cadot wrote on Thu, 26 October 2023 13:27
Always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
There are several errors in your procedure (USING clause, "ps." instead of "src.". Here's a correct version:
SQL> create or replace TYPE EMP_CSV_OBJ AS OBJECT
2 (
3 FirstName VARCHAR2(50),
4 LastName VARCHAR2(50),
5 MiddleName VARCHAR2(50)
6 );
7 /
Type created.
SQL> create or replace TYPE EMP_CSV_TABLE
2 AS TABLE OF emp_csv_obj;
3 /
Type created.
SQL> CREATE OR REPLACE PROCEDURE INSERT_EMPLOYEE(empCsv IN EMP_CSV_TABLE) AS
2 BEGIN
3 MERGE INTO Employees pt
4 USING (select * from table(empCsv)) src
5 ON (pt.FIRST_NAME = src.FIRSTNAME)
6 WHEN NOT MATCHED THEN INSERT
7 (pt.FIRST_NAME, pt.LAST_NAME, pt.MIDDLE_NAME)
8 VALUES (src.FirstName , src.LastName , src.MIDDLENAME);
9
10 -- COMMIT;
11 END;
12 /
Procedure created.
Note: do NOT commit inside a procedure; the caller knows if he wants to commit or to rollback what the procedure does, the procedure doesn't without speaking that your procedure would also commit work that has been done before its call which may something the caller does not want.
Thanks this works for me
|
|
|
|
|