Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

REF CURSOR -09004 invalid identifier, variable?

564801May 30 2007 — edited Jun 1 2007
I 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"
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 29 2007
Added on May 30 2007
4 comments
983 views