How to call function which has input parameter type??

965816

Hi all,

I am facing problem while

executing select ftest1(123) from dual;

and i am getting error

ORA-006553:PLS-306 : wrong number or types of arguments in call to ftest1

and this is the code for function;

create package body pkg1 is

type no_array is table of NUMBER index by binary_integer;

end pkg1;

CREATE OR REPLACE FUNCTION ftest1(lvdriver pkg1.No_arrray)

    RETURN VARCHAR2

IS

    lv VARCHAR2(100);

BEGIN

    FOR i IN lvdriver.first .. lvdriver.last LOOP

        SELECT last_nm

          INTO lv

          FROM person

         WHERE person_id = lvdriver(i);

        dbms_output.put_line(lv);

    END LOOP;

    RETURN lv;

END;

how to pass array values to function

i need to pass multiple values to array here it is lvdriver(1,2,3,123,4,5)??

BluShadow

a) you cannot use PL/SQL declared types in your SQL statements.  The type must be declared as a database object for SQL to recognise it.

b) passing a parameter of 123 is just passing a number, it's not passing something of the type declared which is an array of numbers.

BluShadow

Example...

create or replace type tNums as table of number;

create or replace function ftest1(nms tNums) return number is
  vResult number := 0;
begin
  for i in 1 .. nms.count
  loop
    vResult := vResult + nms(i);
  end loop;
  return vResult;
end;
/

SQL> select ftest1(tNums(1,2,3,4)) from dual;

FTEST1(TNUMS(1,2,3,4))
----------------------
                    10