Workaround for using NTILE function in Oracle8i

From: Kieran Murray <kieran.murray_at_norkom.com>
Date: Thu, 21 Mar 2002 02:03:25 -0800
Message-ID: <F001.0042F74C.20020321020325@fatcity.com>

Hi Everyone,
I'm writing PL/SQL code which utilizes the NTILE function but have just found out that due to a bug in Oracle8i the function works from Sqlplus but not when called from PL/SQL. I've run the following code in Oracle 8i and 9i and the results are given below.
Unfortunately the database I need to run this on is Oracle 8i. Does anyone know of
a workaround I could use to get this to work.

Cheers,
Kieran Murray
Database Administrator,
Norkom Technologies,
Dublin 2, Ireland
Tel:- +3531 2403221

 declare
 cursor cr_main is
 select customer_id,

    work_tn_extno,
    ntile(4) over (order by work_tn_extno desc ) as quartile  from customer;
 begin
 for r1 in cr_main loop

 dbms_output.put_line('customer_id = ' || r1.customer_id);
 dbms_output.put_line('work_tn_extno = ' || r1.work_tn_extno);
 dbms_output.put_line('quartile = ' || r1.quartile);

 end loop;
 exception
 WHEN OTHERS THEN
 dbms_output.put_line('SQLERRM = ' || SQLERRM);  END ;
 /

Oracle8i

                                                    *

ERROR at line 6:
ORA-06550: line 6, column 53:
PLS-00103: Encountered the symbol "(" when expecting one of the following: , from into bulk

Oracle9i

customer_id = 1
work_tn_extno =
quartile = 1

PL/SQL procedure successfully completed.

The information contained in this e-mail transmission is confidential and may be privileged. It is intended only for the addressee(s) stated above. If you are not an addressee, any use, dissemination, distribution, publication, or copying of the information contained in this e-mail is strictly prohibited. If you have received this e-mail in error, please immediately notify our IT Department by telephone at 353-1-6769333 or e-mail internal.support_at_norkom.com and delete the e-mail from your system.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Kieran Murray
  INET: kieran.murray_at_norkom.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Mar 21 2002 - 04:03:25 CST