How to send a string to the IN clause in the stored procedure (oracle)

0

I find myself doing select to a table by means of where campo in (id1,id2...) . My doubt arises in how to send the string as an input parameter to the Oracle stored procedure. Then I present what I have tried so far.

From C # I select the different ids by means of a listbox in the quadrant catalog in order to form a chain. An example of what would be generated would be

  

string sent to pi_cuadrantes: 1,5,7

I used replace since I send a string but the field CUA.CVE_CUADRANTE is numeric in its table so I try to eliminate the " "

Procedure

create PROCEDURE SP_SEL_AG_CUADRANTE
( 
   PI_CRITERIO IN NUMBER
,  PI_CUADRANTES in VARCHAR2
,  V_REP OUT sys_refcursor
)
AS 
V_CUADRANTES VARCHAR2(30);
/*
*/
BEGIN

V_CUADRANTES:= REPLACE(PI_CUADRANTES,'"',''); 

IF PI_CRITERIO = 6 THEN
  OPEN V_REP  FOR
    SELECT 
    DR.FOLIO,
    DR.NOMINA,
    TRIM(EMP.AP_PATERNO)||' '||TRIM(EMP.AP_MATERNO)||' '||TRIM(EMP.NOMBRE)NOMBREFULL
    FROM DETALLE_R DR 
      INNER JOIN ROL R ON R.FOLIO = DR.FOLIO
      INNER JOIN EMPLEADOS EMP ON EMP.NOMINA = DR.NOMINA
      INNER JOIN AG_CUADRANTE AC ON AC.AGENTE = DR.NOMINA
      INNER JOIN CUADRANTES CUA ON CUA.CVE_REL_CUADRANTE = AC.CVE_REL_CUADRANTE
    WHERE R.CVE_ESTATUS = 2
      AND TO_CHAR(R.FECHA_REGISTRO, 'DD/MM/YYYY') = TO_CHAR(SYSDATE, 'DD/MM/YYYY')
      AND CUA.CVE_CUADRANTE IN (V_CUADRANTES);
END IF;
END SP_SEL_AG_CUADRANTE;

but when I get the information, it only sends me data when I select only one, if I select two id it does not send data and if I select 3 oracle it throws me the error:

  

{"ORA-01722: invalid number \ n"}

For what I researched a little more I found that I should send by means of a type which is not completely clear to me and generates the following:

create or replace TYPE cadena_cua_typ as TABLE OF VARCHAR2(50);


create PROCEDURE SP_SEL_AG_CUADRANTE
( 
   PI_CRITERIO IN NUMBER
,  PI_CUADRANTES in CADENA_CUA_TYP
,  V_REP OUT sys_refcursor
)
AS 

/*
*/
BEGIN

IF PI_CRITERIO = 6 THEN
  OPEN V_REP  FOR
    SELECT 
    DR.FOLIO,
    DR.NOMINA,
    TRIM(EMP.AP_PATERNO)||' '||TRIM(EMP.AP_MATERNO)||' '||TRIM(EMP.NOMBRE)NOMBREFULL
    FROM DETALLE_R DR 
      INNER JOIN ROL R ON R.FOLIO = DR.FOLIO
      INNER JOIN EMPLEADOS EMP ON EMP.NOMINA = DR.NOMINA
      INNER JOIN AG_CUADRANTE AC ON AC.AGENTE = DR.NOMINA
      INNER JOIN CUADRANTES CUA ON CUA.CVE_REL_CUADRANTE = AC.CVE_REL_CUADRANTE
    WHERE R.CVE_ESTATUS = 2
      AND TO_CHAR(R.FECHA_REGISTRO, 'DD/MM/YYYY') = TO_CHAR(SYSDATE, 'DD/MM/YYYY')
      AND CUA.CVE_CUADRANTE IN (select column_value from table(PI_CUADRANTES));
END IF;
END SP_SEL_AG_CUADRANTE;

getting the following:

  

ORA-06550: line 1, column 7: \ nPLS-00306: number or types of erroneous arguments when calling 'SP_SEL_AG_CUADRANTE'.

I would appreciate your help as I can not find the way to send that string and search the selected quadrants in the catalog.

I use Oracle 11g.

    
asked by Yeraldine 23.02.2018 в 02:03
source

0 answers