CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqlcursor
Iishfaaq Ismath
Iishfaaq Ismath

Reputation: 77

binding input parameter to a like clause in a cursor in PLSQL

I am trying to writing a PLSQL Procedure with an input parameter which will be assign to the like clause inside a cursor. BUt binding is not working properly. Please help me to solve it.

PROCEDURE SEARCH_DONOR (p_nic_ VARCHAR2,
                        records_ OUT Sys_Refcursor) 

  AS

  v_stmt_str      VARCHAR2(200);
  v_stmt_str2      VARCHAR2(4000);

  BEGIN

       OPEN  records_  FOR
      SELECT DONOR_ID_        Id,
             FIRST_NAME_      First_Name,
             LAST_NAME_       Last_Name,
             DOB_             Date_of_Birth,
             NIC_             NIC,
             CONTACT_NUMBER_  Contact_Number,
             BLOOD_TYPE_ID_   Blood_type

      FROM MOISLK_DONOR_INFO_TAB
      WHERE NIC like '%p_nic_%'
      AND CLIENT_STATUS_ = 0
      ORDER BY DONOR_ID_;

  END SEARCH_DONOR;

Upvotes: 0

Views: 867

Answers (1)

Littlefoot
Littlefoot

Reputation: 142710

Should be

WHERE NIC like '%' || p_nic_ || '%'

because you were searching for a string p_nic, while you want to search for a string that contains that parameter's value.

Upvotes: 4

Related Questions