Just wrap it within 'execute immediate' and it will
run fine.
hth
connor
- Kieran Murray <kieran.murray_at_norkom.com> wrote: >
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).
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)
"Some days you're the pigeon, some days you're the statue"
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.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:18:18 CST
