Farai
Farai

Reputation: 111

I am experiencing an error with one of my oracle stored procedures with error ORA:24344

I am experiencing an error with one of my oracle stored procedures with error ORA:24344 when trying to create a stored procedure I am getting the following error message ORA-24344: success with compilation error PL/SQL: ORA-00927: missing equal sign PL/SQL: SQL Statement ignored

CREATE OR REPLACE PROCEDURE sp_comms_update_stg (
ssms_key            IN VARCHAR2,
spolicyNumber       IN VARCHAR2,
sclientKey          IN VARCHAR2,
sclientReference    IN VARCHAR2,
sresult          OUT SYS_REFCURSOR
)
IS
BEGIN

  UPDATE stg_update_email 
  SET
   sms_key := ssms_key, 
  policy_number := spolicyNumber, 
  client_key := sclientKey, 
  process_status := 'Processed'
   WHERE client_reference = sclientReference;

    INSERT INTO EVENTLOG VALUES(seq_eventlog.NEXTVAL, spolicyNumber , (select 
   to_date(sysdate) from dual),
   to_char(sysdate,'HH24:MI:SS'), 101, null, 1, 'Updated stg_update_email', 
   'stg_update_email successfully updated', 'P', var_Client, null, null);


  sresult:= true;

  COMMIT;
 EXCEPTION
     WHEN OTHERS Then
   Rollback;
   sresult := false;
   RAISE_APPLICATION_ERROR (-20000,'ERROR IN EXECUTING PROCEDURE 
SP_UNDEL_UPD_STG - '|| chr(13)||chr(10) || UPPER(SQLERRM) || 
chr(13)||chr(10));
 END;

Upvotes: 0

Views: 240

Answers (2)

APC
APC

Reputation: 146219

ORA-24344: success with compilation error
PL/SQL: ORA-00927: missing equal sign
PL/SQL: SQL Statement ignored

That was pointing out that you had used the PL/SQL assignment operator := instead of the SQL one = in your update statement. But you say despite having fixed that you still cannot compile your procedure. Maybe it's just that the compiler doesn't like assigning a Boolean to a sys_refrcursor parameter, may there's more to it.

I suggest you run this query to see what other errors the compiler has spotted.

select * from user_errors 
where name = 'SP_COMMS_UPDATE_STG'

IDEs like Allround Automations PL/SQL Developer and Oracle SQL Developer will do this for us automatically (in Orcale SQL Developer you need to click on the Errors tab) but it doesn't do any harm to know the explicit query.

Upvotes: 2

Aleksej
Aleksej

Reputation: 22949

See the comments; fixing these issues should make your procedure compile:

CREATE OR REPLACE PROCEDURE sp_comms_update_stg(
                                                ssms_key            IN     VARCHAR2,
                                                spolicyNumber       IN     VARCHAR2,
                                                sclientKey          IN     VARCHAR2,
                                                sclientReference    IN     VARCHAR2,
                                                sresult                OUT boolean      /* based on your code, you probably need a BOOLEAN */
                                               ) IS
BEGIN
    UPDATE stg_update_email
       SET sms_key           = ssms_key,        /* = and not := */
           policy_number     = spolicyNumber,   /* = and not := */
           client_key        = sclientKey,      /* = and not := */
           process_status    = 'Processed'      /* = and not := */
     WHERE client_reference  = sclientReference;

    INSERT INTO EVENTLOG
         VALUES (
                 seq_eventlog.NEXTVAL,
                 spolicyNumber,
                 sysdate,    --(SELECT TO_DATE(SYSDATE) FROM DUAL),     /* let's simplify! also, to_date(sysdate) makes no sense: sysdate already is a date */
                 /* or even trunc(sysdate) if you don't want time informations*/
                 TO_CHAR(SYSDATE, 'HH24:MI:SS'),
                 101,
                 NULL,
                 1,
                 'Updated stg_update_email',
                 'stg_update_email successfully updated',
                 'P',
                 var_Client,                    /* where is var_Client defined ? */
                 NULL,
                 NULL
                );


    sresult    := TRUE;

    COMMIT;
EXCEPTION
    WHEN OTHERS
    THEN
        ROLLBACK;
        sresult    := FALSE;
        RAISE_APPLICATION_ERROR(-20000, 'ERROR IN EXECUTING PROCEDURE SP_UNDEL_UPD_STG - '
            || CHR(13) || CHR(10) || UPPER(SQLERRM) || CHR(13) || CHR(10));
END;

Upvotes: 1

Related Questions