-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|