1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Reg:How to validate muliple values in table using function

Discussion in 'SQL PL/SQL' started by sadheesh, Sep 19, 2014.

  1. sadheesh

    sadheesh Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    I have try following way to validate the data but it not working
    Code (SQL):

    CREATE OR REPLACE FUNCTION test_func(i_str IN varchar2) RETURN tst_lst
      pipelined AS

      cursor c1_tst(i_str IN varchar2) IS
        SELECT emp_name AS e_nam FROM employees WHERE emp_name IN (i_str);

    BEGIN
      FOR det IN c1_tst(i_str) loop
        pipe ROW(tst(det.e_nam));
      END loop;
      RETURN;
    END test_func;

    Eg:

    SELECT * FROM TABLE (test_func('Ram,bala'))

     
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi sadheesh,

    A few points or questions related to your post:
    1) Am not sure I understand what you are trying to test.
    2) What is "tst_lst" ? You haven't included any scripts or description of this type.
    2) It's not possible to use a string in an "IN" statement - at least the way you've done it. You have to use dynamic sql.

    You'll find hereunder and example (based on your code with some corrections) of what, according to my understanding, you're trying to check or achieve.

    Code (SQL):
    -- Creation of TYPE
    CREATE OR REPLACE TYPE Emp_lst IS TABLE OF VARCHAR2(40)
    ;
    /

    CREATE OR REPLACE FUNCTION test_func(
        p_str IN VARCHAR2)
      RETURN Emp_lst pipelined
    AS
      cv sys_refcursor;
      l_name VARCHAR2(40);
    BEGIN
      OPEN cv FOR 'SELECT last_name AS e_nam FROM employees  
    WHERE last_name IN ('
    || p_str || ')';
      LOOP
        FETCH cv INTO l_name;
        EXIT WHEN cv%NOTFOUND;
        pipe ROW(l_name);
      END LOOP;
      RETURN;
    END test_func;
    /

    -- To test: you need to put proper ''' in your call
    SELECT * FROM TABLE (test_func( '''Abel'',''Ande'''));
     
    Hope this helps...
     
    sadheesh likes this.