Message-Id: <10514.107305@fatcity.com> From: "Igor Neyman" Date: Wed, 31 May 2000 11:05:27 -0400 Subject: Re: Ordered Inline View This is a multi-part message in MIME format. ------=_NextPart_000_01D4_01BFCAF0.206B2DC0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I think, I found a solution: use Dynamic (Native) SQL: declare lDate date; begin EXECUTE IMMEDIATE 'SELECT cycle_date_time=20 FROM( SELECT /*+ FIRST_ROWS */ cycle_date_time FROM CYCLE WHERE cycle_date_time < SYSDATE AND gauge_id =3D 2 ORDER BY cycle_date_time DESC) WHERE ROWNUM < 2' INTO lDate; dbms_output.put_line(to_char(lDate, 'mm/dd/yyyy hh24:mi:ss')); end; / But, is there any other option? Igor Neyman, OCP DBA Perceptron, Inc. (734)414-4627 ineyman@perceptron.com =20 ----- Original Message -----=20 From: Igor Neyman=20 To: oracledba@quickdoc.co.uk ; ORACLE-L@fatcity.com=20 Sent: Wednesday, May 31, 2000 10:14 AM Subject: Ordered Inline View I have the followingproblem, when using ordered inline view. When I execute a single sql-statement (from SQL*PLUS): SELECT /*+ FIRST_ROWS */ cycle_date_time FROM CYCLE WHERE cycle_date_time < SYSDATE AND gauge_id =3D 2 AND model_id =3D 16 ORDER BY cycle_date_time ASC) WHERE ROWNUM < 2; it works fine. But, when I put this statement into PL/SQL block, I am getting=20 an error: SQL> declare lDate date; 2 begin 3 SELECT cycle_date_time INTO lDate 4 FROM( 5 SELECT /*+ FIRST_ROWS */ cycle_date_time 6 FROM CYCLE 7 WHERE cycle_date_time < SYSDATE 8 AND gauge_id =3D 2 9 AND model_id =3D 16 10 ORDER BY cycle_date_time ASC) 11 WHERE ROWNUM < 2; 12 end; 13 / ORDER BY cycle_date_time ASC) * ERROR at line 10: ORA-06550: line 10, column 3: PLS-00103: Encountered the symbol "ORDER" when expecting one of=20 the following: ) * & - + / mod rem with an exponent (**) and or group having intersect minus start union where connect || The symbol ")" was substituted for "ORDER" to continue. ORA-06550: line 10, column 31: PLS-00103: Encountered the symbol ")" when expecting one of the=20 following: , ; for SQL>=20 It looks, like PL/SQL engine does not like ordered inline views=20 (though they work fine in SQL). Do you know, if this is a known bug? Or, there is any workaround? TIA Igor Neyman, OCP DBA Perceptron, Inc. (734)414-4627 ineyman@perceptron.com =20 ------=_NextPart_000_01D4_01BFCAF0.206B2DC0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

I think, I found a solution: use Dynamic (Native)=20 SQL:

declare lDate date;

begin

EXECUTE IMMEDIATE 'SELECT cycle_date_time

FROM(

SELECT /*+ FIRST_ROWS */ cycle_date_time

FROM CYCLE

WHERE cycle_date_time < SYSDATE

AND gauge_id =3D 2

ORDER BY cycle_date_time DESC)

WHERE ROWNUM < 2' INTO lDate;

dbms_output.put_line(to_char(lDate, 'mm/dd/yyyy hh24:mi:ss'));

end;

But, is there any other option?

Igor Neyman, OCP DBA
Perceptron, Inc.
(734)414-4627
ineyman@perceptron.com
&nbs= p;=20

----- Original Message -----

From:=20 Igor=20 Neyman

To: oracledba@quickdoc.co.uk ; ORACLE-L@fatcity.com

Sent: Wednesday, May 31, 2000 = 10:14=20 AM

Subject: Ordered Inline = View

I have the followingproblem, when using ordered = inline=20 view.
When I execute a single sql-statement (from = SQL*PLUS):

SELECT /*+ FIRST_ROWS */=20 cycle_date_time
    FROM CYCLE
    = WHERE=20 cycle_date_time < SYSDATE
      AND = gauge_id =3D=20 2
      AND model_id =3D = 16
   =20 ORDER BY cycle_date_time=20 ASC)
         WHERE ROWNUM = <=20 2;

it works fine.
But, when I put this statement = into PL/SQL=20 block, I am getting
an error:
SQL> declare lDate = date;
=20 2 begin
3 SELECT cycle_date_time INTO=20 lDate
4    FROM(
5   = SELECT /*+=20 FIRST_ROWS */ cycle_date_time
6    FROM=20 CYCLE
7    WHERE cycle_date_time <=20 SYSDATE
8      AND gauge_id =3D = 2
=20 9      AND model_id =3D=20 16
10    ORDER BY cycle_date_time=20 ASC)
11         WHERE = ROWNUM=20 < 2;
12 end;
13 /
ORDER BY=20 cycle_date_time ASC)
*
ERROR at line 10:
ORA-06550: = line 10,=20 column 3:
PLS-00103: Encountered the symbol "ORDER" when expecting = one of=20
the following:
) * & - + / mod rem with an exponent (**) = and or=20 group having
intersect minus start union where connect ||
The = symbol ")"=20 was substituted for "ORDER" to continue.
ORA-06550: line 10, column = 31:
PLS-00103: Encountered the symbol ")" when expecting one of the =
following:
, ; for

SQL>

It looks, like PL/SQL engine does not like ordered = inline=20 views
(though they work fine in SQL).
Do you know, if this is a = known=20 bug?
Or, there is any workaround?

TIA

Igor Neyman, OCP DBA
Perceptron,=20