We're using the new dense_rank function for some sql.
Unfortunately when we stick it in a procedure it doesn't like it! Any ideas?
-Ken
Printable View
We're using the new dense_rank function for some sql.
Unfortunately when we stick it in a procedure it doesn't like it! Any ideas?
-Ken
I'm assuming that you're not running 9i.
You have to use dynamic SQL (DBMS_SQL or execute immediate) to run those functions in queries in the PL/SQL engine.
We're on 8.1.7 on this instance.
I'll give your advice a try. Thanks.
The developer is working on putting the function in a view and querying the view from pl/sql. Would that really be any different?
-Ken
it should work , I used analystic function it works fine.
what is the error it gives. did you try your sql in sqlplus ?
It worked in SQL*Plus in a straight query, but when they put it in a pl/sql block .. they got the error.
The developer(s) cluged together a solution using pl/sql tables and vars.
I think we'll be on 9i before we figure that one out.
Thanks folks for your input.
As Heath said, you need to use dynamic SQL to use any Analytical functions prior to 9i. This is not a big deal. Simply wrap the statement in quotes; stick it in a variable and do an OPEN or EXECUTE IMMEDIATE on the variable. Note that this would be *much* faster than hacking it using cursors, etc.
- Chris