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.