The problem is this:
I am trying to pass an array to a pl/sql procedure, well this is my procedure(if it helps), the procedure shows no errors
Code: Select all
CREATE OR REPLACE PACKAGE Pkg_Eval
AS
TYPE ARRTYPE IS TABLE OF VARCHAR(10) INDEX BY BINARY_INTEGER;
PROCEDURE PR_EVAL_FORM_ENTITIES_DEF (
v_mode IN VARCHAR2, -- MANDATORY,
v_form_id /*VARCHAR2(10)*/ IN EVALUATION_FORM_ENTITIES_DEF.form_id%TYPE DEFAULT NULL,
v_criteria_id IN ARRTYPE,
v_metric_id /*VARCHAR2(10)*/ IN EVALUATION_FORM_ENTITIES_DEF.metric_id%TYPE DEFAULT NULL,
v_status /*CHAR(1)*/ IN EVALUATION_FORM_ENTITIES_DEF.status%TYPE DEFAULT NULL,
v_entered_by /*VARCHAR2(10)*/ IN EVALUATION_FORM_ENTITIES_DEF.entered_by%TYPE DEFAULT NULL,
v_entered_dt /*DATE(7)*/ IN VARCHAR2 DEFAULT NULL,
v_last_updated_dt /*DATE(7)*/ IN VARCHAR2 DEFAULT NULL,
v_last_updated_by /*VARCHAR2(10)*/ IN EVALUATION_FORM_ENTITIES_DEF.last_updated_by%TYPE DEFAULT NULL
);
END Pkg_Eval;
AND the body goes LIKE this:
PROCEDURE PR_EVAL_FORM_ENTITIES_DEF (
v_mode IN VARCHAR2, -- MANDATORY,
v_form_id /*VARCHAR2(10)*/ IN EVALUATION_FORM_ENTITIES_DEF.form_id%TYPE DEFAULT NULL,
v_criteria_id IN ARRTYPE,
v_metric_id /*VARCHAR2(10)*/ IN EVALUATION_FORM_ENTITIES_DEF.metric_id%TYPE DEFAULT NULL,
v_status /*CHAR(1)*/ IN EVALUATION_FORM_ENTITIES_DEF.status%TYPE DEFAULT NULL,
v_entered_by /*VARCHAR2(10)*/ IN EVALUATION_FORM_ENTITIES_DEF.entered_by%TYPE DEFAULT NULL,
v_entered_dt /*DATE(7)*/ IN VARCHAR2 DEFAULT NULL,
v_last_updated_dt /*DATE(7)*/ IN VARCHAR2 DEFAULT NULL,
v_last_updated_by /*VARCHAR2(10)*/ IN EVALUATION_FORM_ENTITIES_DEF.last_updated_by%TYPE DEFAULT NULL
) IS
/**
*
* This PL/SQL code is a procedure used to insert, update and delete the evaluation_form_entities_def records
* to the table .Here, the first argument is mode which accepts a MANDATORY value as in
* ('I' for INSERT 'U' for UPDATE 'D' for DELETE)
*
* @autoproc version 1.2
* @code version 1.0
* @author DEEWENDRA G. SHRESTHA
* @modification 06-Apr-2007, DEEWENDRA G. SHRESTHA
*/
BEGIN
IF UPPER(v_mode) = 'I' THEN
FOR criteria IN v_criteria_id.FIRST..v_criteria_id.LAST LOOP
INSERT INTO EVALUATION_FORM_ENTITIES_DEF(
form_id,
criteria_id,
metric_id,
STATUS,
entered_by,
entered_dt,
last_updated_dt,
last_updated_by
) VALUES (
v_form_id,
criteria,
v_metric_id,
v_status,
v_entered_by,
SYSDATE,
SYSDATE,
v_last_updated_by
);
END LOOP;
ELSIF UPPER(v_mode) = 'U' THEN
FOR criteria IN v_criteria_id.FIRST..v_criteria_id.LAST LOOP
UPDATE EVALUATION_FORM_ENTITIES_DEF SET
form_id=v_form_id,
criteria_id=criteria,
metric_id=v_metric_id,
STATUS=v_status,
last_updated_dt=SYSDATE,
last_updated_by=v_last_updated_by
WHERE 1=1
AND form_id=v_form_id
AND criteria_id=criteria;
END LOOP;
ELSIF UPPER(v_mode) = 'D' THEN
FOR criteria IN v_criteria_id.FIRST..v_criteria_id.LAST LOOP
DELETE FROM EVALUATION_FORM_ENTITIES_DEF
WHERE 1=1
AND form_id=v_form_id
AND criteria_id=criteria;
END LOOP;
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20099, SQLERRM);
END PR_EVAL_FORM_ENTITIES_DEF;Code: Select all
$connection=$this->getConnection();
$query = OCIParse($connection, $Sql);
oci_bind_array_by_name($query , ":criteria", $criteria, 20, 1, SQLT_CHR);
return OCIExecute($query,OCI_DEFAULT);