I get error message which a little bit confusing me and have no idea what I made wrong here
FUNCTION ADD_TOKEN(p_userID IN INT,p_token IN VARCHAR2,p_type IN VARCHAR2 )
RETURN SYS_REFCURSOR IS
rc SYS_REFCURSOR;
/*TokenExists*/
BEGIN
INSERT INTO user_notifications_token (userId, token, type)
VALUES (p_userID, p_token , p_type) ON DUPLICATE KEY
UPDATE token = p_token, created_date = SYSTIMESTAMP;
END ADD_TOKEN;
I get error message
Error(51,3): PL/SQL: SQL Statement ignored
I put this function inside PACKAGE where I store all my function (arround 50 functions) Does anyone know what is wrong here ? Where did I made mistake ?
Oracle does not support the SQL Server syntax INSERT ... ON DUPLICATE KEY UPDATE .... Use a MERGE statement instead.
FUNCTION ADD_TOKEN(
p_userID IN user_notifications_token.userID%TYPE,
p_token IN user_notifications_token.token%TYPE,
p_type IN user_notifications_token.type%TYPE
) RETURN SYS_REFCURSOR
IS
rc SYS_REFCURSOR;
BEGIN
MERGE INTO user_notifications_token dst
USING (
SELECT p_userID AS userID,
p_token AS token,
p_type AS type
FROM DUAL
) src
ON ( src.userId = dst.userID )
WHEN MATCHED THEN
UPDATE SET token = src.token, type = src.type
WHEN NOT MATCHED THEN
INSERT ( userId, token, type ) VALUES ( src.userId, src.token, src.type );
-- Do stuff
RETURN rc;
END ADD_TOKEN;
Then if you do:
DECLARE
rc SYS_REFCURSOR;
BEGIN
rc := package_name.ADD_TOKEN( 1, 'ABC', 'A' );
rc := package_name.ADD_TOKEN( 1, 'DEF', 'D' );
END;
/
The table will contain:
USERID | TOKEN | TYPE
-----: | :---- | :---
1 | DEF | D
db<>fiddle here
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments