CREATE TABLE INFO ( 
  PRV_ID    NUMBER (6)    NOT NULL, 
  PRV_NAME  VARCHAR2 (32));

CREATE TABLE PRV_SERVICES_T ( 
  SERVICE_ID    NUMBER (6)    NOT NULL, 
  SERVICE_NAME  VARCHAR2 (32), 
  HELP_URL      VARCHAR2 (128));

CREATE TABLE PRV_SERVICES ( 
  PRV_ID      NUMBER (6)    NOT NULL, 
  SERVICE_ID  NUMBER (6) );

@abc_xyz_interface_provider.sql
SET DEFINE ON
SET ESCAPE \
SET VERIFY OFF
VARIABLE p_prv_name varchar2;
DEFINE p_default = 'ALL';

DECLARE
   n_Prv_id       info.Prv_id%TYPE;
   n_Srv_id       Prv_Services_t.Service_id%TYPE;
   n_Srv_name     Prv_Services_t.Service_name%TYPE;
  -- n_prv_name     info.prv_name%type := '&l_prv_name';
   n_help_url     prv_services_t.help_url%TYPE;

   cursor c_info is 
      select prv_id 
      from info 
      where prv_name in &l_prv_name
          or ('&p_default' in &l_prv_name and prv_name <> 'ttt')
      order by prv_id;
BEGIN
   -- ensure the service is defined in the prv_services_T table.
   BEGIN
      SELECT SERVICE_ID
        INTO n_Srv_ID
        FROM Prv_Services_t
       WHERE Service_ID = 92;          
   EXCEPTION
      WHEN OTHERS THEN
         INSERT INTO Prv_Services_T
                     ( SERVICE_ID, SERVICE_NAME, HELP_URL )
              VALUES (92, 'xyzInterface', NULL );
   END;

   -- ensure the service is defined in the prv_services table.
    FOR r_info IN c_info LOOP
     --- give downstream service : 92 for given provider or providers
        BEGIN
      SELECT PRV_ID, Service_ID
        INTO n_prv_ID, n_Srv_ID
        FROM Prv_Services
       WHERE Prv_id = r_info.prv_id 
         AND Service_ID = 92;
   EXCEPTION
      WHEN OTHERS THEN
         INSERT INTO Prv_Services
                     (Prv_id,
                      Service_ID
                     )
              VALUES (r_info.prv_id,
                      92
                     );
   END;
    END LOOP;
END;
/
commit;

SP2-0311: string expected but not found

PL/SQL procedure successfully completed.


Commit complete.

SQL> define l_prv_name = ('eee')
SQL> def l_prv_name
DEFINE L_PRV_NAME      = "('eee')" (CHAR)

SQL> SET DEFINE ON
SQL> SET ESCAPE \
SQL> SET VERIFY OFF
SQL> VARIABLE p_prv_name varchar2;
SQL> DEFINE p_default = 'ALL';
SQL>