Forum Stats

  • 3,872,047 Users
  • 2,266,372 Discussions
  • 7,911,040 Comments

Discussions

How to call function which has input parameter type??

965816
965816 Member Posts: 27
edited Jan 10, 2014 5:16AM in SQL & PL/SQL

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

Tagged:

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 42,540 Red Diamond

    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
    BluShadow Member, Moderator Posts: 42,540 Red Diamond

    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
This discussion has been closed.