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

Collapse
11: Re: Tricky query port (pg -> oracle) (response to 1)
Posted by Janine Ohmer on 05/15/03 03:57 PM
I'm happy to report that Sebastiano's suggestion worked.  However, I'm having trouble executing my function to make sure it works.  Here is the function in it's entirety:

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?

Collapse
12: Re: Tricky query port (pg -> oracle) (response to 11)
Posted by Sebastiano Pilla on 05/16/03 10:29 AM
SQL> select test_func(39757) from dual;
You seem to be testing from SQL*Plus... You need to invoke the PL/SQL engine from SQL*Plus, so try something like: 
set serveroutput on;
declare
  result varchar2(128);
begin
  result := test_func(39757);
  dbms_output.put_line('result = ' || result);
end;
/
Don't forget the slash at the end, it is very important; also, dbms_output.put_line accepts no more than 255 characters, so if your result variable is longer than that you'll get an error. On Tom Kyte's site you can find alternative procedures that allow you to print more than 255 characters on the dbms_output buffer.