AndroidToday I oddly found the different between using non-cursor and cursor in Oracle. Generally, using these two usages are same but kind of different memory usage and error handling.
Advantage and disadvantage Of Cursor
Using so many cursors in stored procedures, functions and views can be high memory usage in Server. Because every cursors take individual space in memory while creating and processing. If return value is NULL from this cursor, Oracle will not throw error message to application.
Example coding of using cursor
2 (P_PERSON_ID IN NUMBER)
3IS
4 L_MENU VARCHAR2(50);
5 L_ERR_MSG VARCHAR2(200);
6
7 CURSOR C_PP (P_PERSON_ID NUMBER) IS
8 SELECT NAME FROM PERSON
9 WHERE PERSON_ID = P_PERSON_ID;
10
11BEGIN
12 OPEN C_PP(P_PERSON_ID);
13 FETCH C_PP INTO L_MENU;
14 CLOSE C_PP;
15EXCEPTION
16 WHEN OTHERS THEN
17 L_ERR_MSG := SQLERRM;
18 RAISE_APPLICATION_ERROR(-20000, L_ERR_MSG);
19END;
Advantage and disadvantage Of Cursor
If we use non-cursor query in Oracle, we don't need to consider for memory usage. But the important thing we're gonna be aware how to handle error when return value is NULL. That's what I've found is Oracle throws error if return value is NULL. Please check the following error image of using non-cursor query.
Example coding of using non-cursor
2 (P_PERSON_ID IN NUMBER)
3IS
4 L_MENU VARCHAR2(50);
5 L_ERR_MSG VARCHAR2(200);
6BEGIN
7 SELECT NAME INTO L_MENU FROM PERSON
8 WHERE PERSON_ID = P_PERSON_ID;
9EXCEPTION
10 WHEN OTHERS THEN
11 L_ERR_MSG := SQLERRM;
12 RAISE_APPLICATION_ERROR(-20000, L_ERR_MSG);
13END;
Top of Page