CODEWITHSUNDEEP
oracle-databasefunctionplsql
don_julian
don_julian

Reputation: 55

Why I get error message when transfer INSERT method to Function

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 ?

Upvotes: 0

Views: 35

Answers (1)

MT0
MT0

Reputation: 167972

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

Upvotes: 1

Related Questions