Oracle Notes:-
1. From the Procedure Data Result(s) OUT TYPES.cursor_type Count must be same as declared
2. Post Every Insert/Update/Delete must commit without this change will not reflect.
3. Every If() Then statement closed with End If; (Semicolumn is must)
4. For Writing select query we can wite like this OPEN L_RECORDSET2 for select 'true' as col from dual; (Here dual is default table available on oracle)
5. Insert Records like this
Insert into MY_TBL_DETAILS (ID, PRODUCTTYPE, REFNO , CREATEDDATE)
values (SEQ_MY_TBL_DETAILS.NEXTVAL,L_PRODUCTTYPE, L_REFNO, TO_CHAR(sysdate, 'DD-MON-YYYY HH:MI:SS'));
commit;
6. Update Records like this
UPDATE MY_TBL_DETAILS SET PRODUCTTYPE = 'TestProduct' WHERE REFNO = L_REFNO;
Commit;
7. Sequece Creation
CREATE SEQUENCE SEQ_MY_TBL_DETAILS
MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE ;
CREATE OR REPLACE TRIGGER MY_TBL_DETAILS_SEQ
BEFORE INSERT ON MY_TBL_DETAILS
FOR EACH ROW
BEGIN
SELECT SEQ_MY_TBL_DETAILS.NEXTVAL
INTO :new.id
FROM dual;
END;
/
ALTER TRIGGER MY_TBL_DETAILS_SEQ ENABLE;
8. Update Query with latest records
DECLARE tempVariable NUMBER;
BEGIN
SELECT Count(*) INTO tempVariable FROM MY_TBL_DETAILS Where REFNO = L_REFNO;
IF (tempVariable > 0) then
UPDATE MY_TBL_DETAILS SET STATUS = (CASE WHEN L_STATUS IS NULL THEN STATUS ELSE L_STATUS END)
,MESSAGE = (CASE WHEN L_MESSAGE IS NULL THEN MESSAGE ELSE L_MESSAGE END)
WHERE ID IN (Select Id FROM (SELECT ID, rownum RN FROM (SELECT ID from MY_TBL_DETAILS Where REFNO = L_REFNO ORDER BY ID DESC))
WHERE RN = 1);
End If;
End;
