Forum OpenACS Q&A: Re: Tricky query port (pg -> oracle)

Collapse
3: Re: Tricky query port (pg -> oracle) (response to 1)
Posted by Janine Ohmer on 05/14/03 10:31 PM
Well, what I have exactly is this:

function revision_name(
  revision_id IN cr_revisions.revision_id%TYPE
) return varchar2 is

  v_text varchar2(500);

begin

  select
    'Revision ' || content_revision.get_number(r.revision_id) || ' of ' ||
    (select count(*) from cr_revisions where item_id = r.item_id)
    || ' for item: ' || content_item.get_title(item_id)
  into v_text
  from cr_revisions r
  where r.revision_id = revision_name.revision_id;

  return v_text;

end revision_name;

and it's giving me this:

Errors for PACKAGE BODY CONTENT_REVISION:

LINE/COL ERROR
-------- -----------------------------------------------------------------
322/6    PLS-00103: Encountered the symbol "SELECT" when expecting one of
        the following:
        ( - + mod not null others <an identifier>
        <a double-quoted delimited-identifier> <a bind variable> avg
        count current exists max min prior sql stddev sum variance
        execute forall time timestamp interval date
        <a string literal with character set specification>
        <a number> <a single-quoted SQL string>

323/5    PLS-00103: Encountered the symbol "|" when expecting one of the
        following:

LINE/COL ERROR
-------- -----------------------------------------------------------------
        ; return returning and or

324/3    PLS-00103: Encountered the symbol "INTO" when expecting one of
        the following:
        . ( * % & = - + ; < / > at in mod not rem return returning
        <an exponent (**)> <> or != or ~= >= <= <> and or like
        between is null is not || is dangling

Maybe you just can't do it in PL/SQL??? This is Oracle 8.1.7.4.  Thanks!

Collapse
9: Re: Tricky query port (pg -&amp;gt; oracle) (response to 3)
Posted by Vinod Kurup on 05/15/03 03:29 AM
Weird... I have no idea why you can't use subqueries as columns in PL/SQL. If you still want to make it all 1 query and you don't mind making it *really* ugly, you can use this: 

select                                                                      
  'Revision ' ||
  max(content_revision.get_number(r1.revision_id)) || ' of ' || 
  count(r2.revision_id) || ' for item: ' ||
  max(content_item.get_title(r1.item_id))               
  into v_text                                                                 
from cr_revisions r1, cr_revisions r2                                       
where r1.revision_id = revision_name.revision_id 
  and r1.item_id = r2.item_id;
Collapse
10: Re: Tricky query port (pg -&amp;amp;gt; oracle) (response to 9)
Posted by Sebastiano Pilla on 05/15/03 09:51 AM
Weird... I have no idea why you can't use subqueries as columns in PL/SQL.
AFAIK, in Oracle 8i PL/SQL and SQL use different parsers, and the PL/SQL parser lacks some of the features of the SQL one. The 2 parsers should be synchronized from Oracle 9i onwards.

For the original problem, maybe executing the problematic query with the 'execute immediate' construct may help: documentation for 'execute immediate' in Oracle 8i can be found here. Please let me know if it solves your problem.