New dense_rank() not working in PL/SQL, why?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: New dense_rank() not working in PL/SQL, why?

Hybrid View

  1. #1
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    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 do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  2. #2
    Join Date
    Apr 2001
    Posts
    118
    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.

  3. #3
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    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
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  4. #4
    Join Date
    May 2000
    Location
    fremont ca
    Posts
    182
    it should work , I used analystic function it works fine.
    what is the error it gives. did you try your sql in sqlplus ?

  5. #5
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    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.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width