| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Workaround for using NTILE function in Oracle8i
Look into native dynamic sql -
the standard trick with SQL that pl/sql cannot
handle is to do something like:
declare
cursor variable declaration
begin
open cursor_variable for
'your sql string as a text string'
;
loop
fetch cursor variable into local variables
exit when NOT FOUND
close cursor variable
end;
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Author of:
Practical Oracle 8i: Building Efficient Databases
-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Date: 21 March 2002 10:58
|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).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: jonathan_at_jlcomp.demon.co.uk 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 - 05:08:18 CST
 |
 |