Re: Ordered Inline View

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Wed, 31 May 2000 11:05:27 -0400
Message-Id: <10514.107305@fatcity.com>

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_at_perceptron.com
=20

Original Message -----=20 From: Igor Neyman=20 To: oracledba_at_quickdoc.co.uk ; ORACLE-L_at_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_at_perceptron.com
   =20

------=_NextPart_000_01D4_01BFCAF0.206B2DC0
Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" =

http-equiv=3DContent-Type>

<META content=3D"MSHTML 5.00.2314.1000" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT size=3D2>I think, I found a solution: use Dynamic (Native)=20

SQL:</FONT></DIV>

<DIV>&nbsp;</DIV>
<DIV>
<P>declare lDate date;</P>
<P>begin</P>
<P>EXECUTE IMMEDIATE 'SELECT cycle_date_time </P>
<P>FROM(</P>
<P>SELECT /*+ FIRST_ROWS */ cycle_date_time</P>
<P>FROM CYCLE</P>
<P>WHERE cycle_date_time &lt; SYSDATE</P>
<P>AND gauge_id =3D 2</P>
<P>ORDER BY cycle_date_time DESC)</P>
<P>WHERE ROWNUM &lt; 2' INTO lDate;</P>
<P>dbms_output.put_line(to_char(lDate, 'mm/dd/yyyy hh24:mi:ss'));</P>
<P>end;</P>
<P>/</P>
<P>But, is there any other option?</P></DIV>
<DIV>&nbsp;</DIV>
<DIV>Igor Neyman, OCP DBA<BR>Perceptron, Inc.<BR>(734)414-4627<BR><A=20

href=3D"mailto:ineyman_at_perceptron.com">ineyman_at_perceptron.com</A><BR>&nbs= p;=20
<BR></DIV>
<BLOCKQUOTE=20
style=3D"BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: = 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px">   <DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV>   <DIV=20
  style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: = black"><B>From:</B>=20
  <A href=3D"mailto:ineyman_at_perceptron.com" = title=3Dineyman_at_perceptron.com>Igor=20
  Neyman</A> </DIV>
  <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A=20   href=3D"mailto:oracledba_at_quickdoc.co.uk"=20   title=3Doracledba_at_quickdoc.co.uk>oracledba_at_quickdoc.co.uk</A> ; <A=20   href=3D"mailto:ORACLE-L_at_fatcity.com"=20   title=3DORACLE-L_at_fatcity.com>ORACLE-L_at_fatcity.com</A> </DIV>   <DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Wednesday, May 31, 2000 = 10:14=20
  AM</DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Subject:</B> Ordered Inline = View</DIV>
  <DIV><BR></DIV>
  <DIV><FONT size=3D2>I have the followingproblem, when using ordered = inline=20
  view.<BR>When I execute a single sql-statement (from = SQL*PLUS):</FONT></DIV>
  <DIV> </DIV>
  <DIV><FONT size=3D2>SELECT /*+ FIRST_ROWS */=20   cycle_date_time<BR>    FROM CYCLE<BR>    = WHERE=20
  cycle_date_time < SYSDATE<BR>      AND = gauge_id =3D=20
  2<BR>      AND model_id =3D = 16<BR>   =20
  ORDER BY cycle_date_time=20
  ASC)<BR>         WHERE ROWNUM = <=20
  2;</FONT></DIV>
  <DIV> </DIV>
  <DIV><FONT size=3D2>it works fine.<BR>But, when I put this statement = into PL/SQL=20
  block, I am getting <BR>an error:<BR>SQL> declare lDate = date;<BR> =20
  2  begin<BR>  3  SELECT  cycle_date_time INTO=20   lDate<BR>  4    FROM(<BR>  5   = SELECT /*+=20
  FIRST_ROWS */ cycle_date_time<BR>  6    FROM=20   CYCLE<BR>  7    WHERE cycle_date_time <=20   SYSDATE<BR>  8      AND gauge_id =3D = 2<BR> =20
  9      AND model_id =3D=20   16<BR> 10    ORDER BY cycle_date_time=20   ASC)<BR> 11         WHERE = ROWNUM=20
  < 2;<BR> 12  end;<BR> 13  /<BR>  ORDER BY=20   cycle_date_time ASC)<BR>  *<BR>ERROR at line 10:<BR>ORA-06550: = line 10,=20
  column 3:<BR>PLS-00103: Encountered the symbol "ORDER" when expecting = one of=20
  <BR>the following:<BR>) * & - + / mod rem with an exponent (**) = and or=20
  group having<BR>intersect minus start union where connect ||<BR>The = symbol ")"=20
  was substituted for "ORDER" to continue.<BR>ORA-06550: line 10, column =

31:<BR>PLS-00103: Encountered the symbol ")" when expecting one of the =

  <BR>following:<BR>, ; for</FONT></DIV>
  <DIV>&nbsp;</DIV>
  <DIV><FONT size=3D2>SQL&gt; </FONT></DIV>
  <DIV>&nbsp;</DIV>
  <DIV><FONT size=3D2>It looks, like PL/SQL engine does not like ordered =

inline=20
views <BR>(though they work fine in SQL).<BR>Do you know, if this is a = known=20
bug?<BR>Or, there is any workaround?</FONT></DIV>

  <DIV>&nbsp;</DIV>
  <DIV><FONT size=3D2>TIA<BR></FONT></DIV>
  <DIV><FONT size=3D2>Igor Neyman, OCP DBA<BR>Perceptron,=20

Received on Wed May 31 2000 - 10:05:27 CDT