REF CURSOR -09004 invalid identifier, variable?
564801May 30 2007 — edited Jun 1 2007I am using a REF CURSOR because within my procedure, I need to alter the ORDER BY clause.
So I take a string that contains the main SELECT stmt and append whichever ORDER BY clause is needed based on other conditions.
This was working great, until I realized that I needed to have the REF CURSOR, limit the results set and I added a line to the WHERE clause:
"AND AD.DST = V_DST", whereV_DST is a variable within the procedure
Now I am getting the following ERROR: "-ORA-00904: "V_DST": invalid identifier"
So my question is - are you or are you not allowed to use a local variable with the REF CURSOR within a procedure?
TIA.
A sample of the Main REF CURSOR is shown here:
"SELECT AD.DST, AD.DISB, MIN(FISCAL_YEAR)
FROM AST_DISB AD, DH_XMIT DX
WHERE DX.UNIQUE_ID IN (''IN_PROGRESS'')
AND DX.DST = V_DST <<<this is what I added >>>
AND AD.RGSN_ID = DX.RGSN_ID
AND AD.DST = DX.DST
AND AD.DISB = DX.DISB
group by AD.DST, AD.DISB ; "
and a sample ORDER BY is appended "ORDER BY AD.DISB"