hello
I have a function wherein I am using nested functions (because I dont want create explicit/ external objects, this is a requirement I do not have effect on/ I dont want to change).
In my outer function I am using cursor by declaring them within IS-section and open then by using loop for statement.
In my inner/ nested function I also want to use a cursor.
There I also declared one just like I did in outer function within IS-section of the nested function.
SQL Developer complains syntax error ("; : expect").
When try do compile the whole procedure I get the following error:
"Error(97,14): PLS-00201: Identifier 'CURSOR' must be declared"
This is my Outer function starts like:
CREATE OR REPLACE FUNCTION FN_GENERATE_QUERY_PMT (instance_id_in IN NUMBER, language_in IN VARCHAR2)
RETURN CLOB
IS
CURSOR c_outer IS
SELECT * FROM BLA;
Where this is my inner function with stands at the and of the IS-section of the outer function (Inner functions must be at the end of IS-Section to stand behind variable and cursor declarations, else there will be an error):
FUNCTION nfn_get_value (num_in IN NUMBER)
RETURN SYS_REFCURSOR
IS
SYS_REFCURSOR c_inner IS
SELECT *
FROM BLUB;
BEGIN
RETURN c_where_in;
END;
After that there will be another nested function and then the BEGIN-section will appear.
When looking for "function returns cursor" I only can find solutions where Cursor is declared in IS-section (sometimes its an AS-section o_O *worried*) but wihtoug select, just type-declared.
The Select of the cursor is added in Begin-block by using open for statement. But I dont want to open it yet. I want to open the cursor using for loop statement. Am I able to reopen it? wont then there be two cursors while the first one wont be closed correctly?
How to solve it to return a cursor which I can use in my outer function.
Any hints would be appreciated.
Thanks in advance.