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

And of course keep it in one select, and I'm a bit stumped.  As far as I can tell, Oracle doesn't allow a subquery as a column specification.

I already tried this:

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

But it does not work;  Oracle complains when it sees the second "select".

Any suggestions?  Thanks in advance!

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!

but I'm getting the same error.  I tried putting it *all* on one line, too, but that didn't help either.

I can split it up, but it will have to be two selects then and I'd rather not have to do that.  I'm beginning to think I will have to, though.

PL/SQL can't call Tcl and the overriding logic is in the generic object PL/SQL code.

Calling these isn't bad if you have a small number of rows being returned, it's only when you're processing lots of rows that they're a problem.  Though as I'm sure you know we're talking about denormalizing this data.

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;

create or replace function test_func (
  revision_id IN cr_revisions.revision_id%TYPE
) return varchar2 is

  v_text varchar2(500);
  v_sql  varchar2(500);

begin

  v_sql := '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)
  from cr_revisions r
  where r.revision_id = test_func.revision_id';

  execute immediate v_sql into v_text;

  return v_text;

end test_func;

But when I try to execute it, using a value I know is a vaild revision_id:

SQL> select test_func(39757) from dual;
select test_func(39757) from dual
      *
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'TEST_FUNC'
ORA-06512: at "BP_STAGING.TEST_FUNC", line 14
ORA-06512: at line 1

??? Once again, I am mystified.  What am I doing wrong now?

I think the problem is with the function definition, not the way you are running it.
The definition of v_sql needs changing at the end:

  where r.revision_id = test_func.revision_id';

should be:

  where r.revision_id = ' || test_func.revision_id;

unless I'm very much mistaken (has been known! :D ).

You should be able to check it using your original method of selecting from dual (works on my Oracle 817 setup).

Hope that helps.

Raad