How to change Stored procedure from SQL server to Oracle

[trungx]

I want to change Stored procedure from SQL server to Oracle, but Oracle not accept.
Exam: change If exists (select ...) in SQL server to Oracle
Help me!

 

[rcurva]

Can you post the entire SQL procedure? Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[trungx]

CREATE PROCEDURE quest2001.proc_setstatus
@id varchar(30),
@param varchar(4),
@taction varchar(10) = ''
AS
DECLARE @status varchar(30)
SET @status='Nused'

IF @param= 'BNOT' -- Set status Bloc-notes
BEGIN
IF EXISTS (SELECT q.IDTBNOT FROM quest2001.QUEST_TBNOTE q WHERE q.IDTBNOT=@id ) SET @status='Used'
UPDATE t SET t.STATUTTBNOT =@status FROM quest2001.TYPE_BLOC_NOTE t WHERE t.IDTBNOT=@id
END

 

[rcurva]

You can change this part of your procedure:

IF EXISTS (SELECT q.IDTBNOT FROM quest2001.QUEST_TBNOTE q WHERE q.IDTBNOT=@id )
SET @status='Used'
UPDATE t SET t.STATUTTBNOT =@status
FROM quest2001.TYPE_BLOC_NOTE t
WHERE t.IDTBNOT=@id
END


To an Oracle equivalent:

BEGIN
SELECT q.IDTBNOT
FROM quest2001.QUEST_TBNOTE q
WHERE q.IDTBNOT=v_id;

v_status='Used';

UPDATE t SET t.STATUTTBNOT = v_status
FROM quest2001.TYPE_BLOC_NOTE t
WHERE t.IDTBNOT= v_id
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;


I don't know if my theory is right, table quest2001.TYPE_BLOC_NOTE will be updated to 'Used' when data is found on quest2001.QUEST_TBNOTE.



Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[trungx]

thanks you,help me, It run ok. but I met problem when I create Trigger and this trigger call that procedure.

Ex:
CREATE TRIGGER quest2001.STATUTEVAFONCTi ON quest2001.EVALUATIONDECLARE
FOR INSERT,UPDATE
AS
DECLARE @id varchar(30)
(SELECT id=old.IDFONCT FROM deleted old)
EXECUTE quest2001.PROC_SETSTATUS id,'EIDX;'


this trigger created in SQL SERVER I can not change ORAC. Can you help me