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

the PCC-S- error message is a message from the Pro*C precompiler, and it seems fairly meaningful to me. i was not aware that you could have the precompiler call another routine to provide message text for error messages.

how exactly would you prepare this source code? it's not clear exactly what modifications would need to be made to the makefile to enable the precompiler to call this routine.

actually, this code looks like an error handler that would be used to retrieve message text in the runtime environment...

"Ravi Alluru" <ravica_at_quixnet.net> wrote in message news:9fge9q$b1n$1_at_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 - 21:25:27 CDT