Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to call function which has input parameter type??

965816Jan 10 2014 — edited Jan 10 2014

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)??

Comments

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
1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 7 2014
Added on Jan 10 2014
#in, #of, #to
2 comments
1,068 views