Re: ORDER BY in sub-select doesn't compile in Pro*C 8.1.6

From: Ravi Alluru <ravica_at_quixnet.net>
Date: Mon, 4 Jun 2001 10:48:50 -0500
Message-ID: <9fge9q$b1n$1@news.sinet.slb.com>

try
...

/* other statements */
...

sql_error()
{

char msg[200];
size_t buf_len, msg_len;

    buf_len = sizeof (msg);
    sqlglm(msg, &buf_len, &msg_len); /* note use of pointers */     printf("%.*s\n\n", msg_len, msg);
    exit(1);
}
this function to get the exact oracle error message. i.e if (sqlca.sqlcode != 0 )

      {
       sql_error();
       }

this will give you a more meaningful ora error

--
Ravi Alluru
mailto:ravica_at_quixnet.net

"Zoran Marjanski" <zoranm_at_sympatico.ca> wrote in message
news:ADtS6.14925$S2.1243969_at_newsread2.prod.itd.earthlink.net...
> Spencer, thanks for the tips. Sorry for not including text of the error
 msg.
> It is below.
>
> We ended up solving the problem by adding an index to the ORDETAIL table
> that would ensure the index be used by Oracle so that the rownum could be
> effective.
> Pro*C/C++: Release 8.1.6.0.0 - Production on Sun Jun 3 12:25:20 2001
>
> (c) Copyright 1999 Oracle Corporation. All rights reserved.
>
> System default option values taken from:
> /u01/app/oracle/product/8.1.6/precomp/admin/pcscfg.cfg
>
> Syntax error at line 96, column 30, file
> /wms/work/zmarjans/csiint/src/precomp/osf51/oescrn_dbafSelectOrDetail.pcc:
>
> Error at line 96, column 30 in file
> /wms/work/zmarjans/csiint/src/precomp/osf51/
>
> oescrn_dbafSelectOrDetail.pcc
>
>                        order by wskuskuskuid
>
> .............................1
>
> PCC-S-02201, Encountered the symbol "by" when expecting one of the
> following:
>
> , ) union, connect, group, having, intersect, minus, start,
>
> where, with,
>
> The symbol "group," was substituted for "by" to continue.
>
> Zoran.
>
> "Spencer" <spencerp_at_swbell.net> wrote in message
> news:GBkS6.222$Z42.11659_at_nnrp1.sbc.net...
> > "Zoran Marjanski" <zoranm_at_sympatico.ca> wrote in message
> > news:wYeS6.13420$S2.1037996_at_newsread2.prod.itd.earthlink.net...
> > > We have Oracle 8i (8.1.6) and Pro*C 8.1.6 pre-compiler and the
 following
 SQL
> > > is NOT pre-compiling. Pro*C doesn't like the ORDER BY clause in the
> > > sub-select. We need the order by in the sub-select to make the rownum
 clause
> > > in the outer select do it's job.
> > >
> > > EXEC SQL select order_idbase, order_idsub, line, wskuskuskuid
> > >                     into :order_idbase, :order_idsub, :line
> > >                     from (select order_idbase, order_idsub, line,
> > > wskuskuskuid
> > >                               from ordetail
> > >                               order by wskuskuskuid
> > >                              )
> > >                     where rownum < 10
> > > ;
> > > The query however is supported by Oracle 8i and executes just fine in
> > > SQL*Plus.
> > >
> > > Does anyone know, how to get the pre-compiler to like this syntax with
 the
> > > order by in the sub-select?
> > >
> > > Thanks, Zoran.
> > >
> >
> > it's strange how many problems people seem with oracle that
> > do not have any related error messages issued.
> >
> > or is Pro*C issuing a message the actually reads
> > "doesn't like the ORDER BY clause in the subselect"
> >
> > Pro*C does do some strange things, but i've not seen an error
> > message like this before.
> >
> > there may be some "compatibility" flag or some such that you
> > can turn on.  perhaps enabling or disabling semantic checking
> > will allow it to run.
> >
> > you could search metalink for a similar problem report, or open
> > a technical assistance request with oracle support.
> >
> > not all SQL constructs are supported in Pro*C or PL/SQL. these
> > products tend to trail somewhat in their support of newer features.
> > prior to 8i, the use of the ORDER BY clause was restricted to the
> > outermost SELECT of nested queries.  the ORDER BY could not
> > be used as part of an inline view, or as part of a view definition.
> >
> > how important is it that you get this exact statement to precompile?
> >
> > if oracle can't recommend any switches or flags to use, or can't
> > find any patches or later releases that address the problem, then
> > you're probably going to be looking for a workaround...
> >
> > since SQL now supports the ORDER BY in an inline view (which
> > is really you've got here, not a subselect) you might try creating a
> > view defintion that contains the order by, and then referencing the
> > new "order by" view in your Pro*C statement, in effect, moving the
> > ORDER BY from your SQL statement into a view definition:
> >
> > CREATE VIEW ORDETAIL_ORDBY_WSKUSKUSKUID AS SELECT
> > ORDER_IDBASE,ORDER_IDSUB,LINE,WSKUSKUYSKUID
> > FROM ORDETAIL ORDER BY WSKUSKUSKUID
> >
> > and replace:
> >
> > >                               from ordetail
> > >                               order by wskuskuskuid
> >
> > with:
> >
> >                                 from ORDETAIL_ORDBY_WSKUSKUSKUID
> >
> > if that doesn't work, another alternative may be to use a dynamic
> > SQL call, which should get you around the precompiler syntax
> > checks.
> >
> > otherwise, you could redesign this statement and solve this problem
> > like developers still do with Oracle 8, which still has the ORDER BY
> > restrictions
> >
> > HTH
> >
> >
> >
>
>

Received on Mon Jun 04 2001 - 10:48:50 CDT

Re: ORDER BY in sub-select doesn't compile in Pro*C 8.1.6 - why not?