Reputation: 111
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
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
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