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

Posted by Janine Ohmer on
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);


  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?

Posted by Sebastiano Pilla on
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;
  result varchar2(128);
  result := test_func(39757);
  dbms_output.put_line('result = ' || result);
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.