I am trying to read a string array from Oracle stored procedure and I am getting this error
ORA-06502: PL/SQL: numeric or value error: host bind array too small.
The stored procedure is working but when I call it from c# the error happened so I think something in my C# code is not right. Here is my code calling the stored procedure
string[] myArray2 = new string[NoOfDay];
OracleString[] arrDays = null;
OracleParameter P_result2 = new OracleParameter("UTILDAYS", OracleDbType.Varchar2, 20);
P_result2.Direction = ParameterDirection.Output;
P_result2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
P_result2.Size = myArray2.Count();
P_result2.ArrayBindSize = new int[myArray2.Count()];
cmd.Parameters.Add(P_result2);
//cmd.Parameters["L"].Value = myArray;
try
{
on.Open();
//cmd.CommandText = sSQL;
cmd.ExecuteNonQuery(); <---- Error happened here after executing this line!!!!!!!!!!!!!!!
arrDays = (OracleString[]) cmd.Parameters["UTILDAYS"].Value;
//OracleString[] arrDay = (OracleString[])cmd.Parameters["DAYS"].Value;
con.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Here is my stored procedure. I am using Toad.
TYPE myArray2 IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
CREATE OR REPLACE PROCEDURE GETUTILDAYS (
"DATE1" IN VARCHAR2,
"DATE2" IN VARCHAR2,
"TESTER" IN VARCHAR2,
"UTILDAYS" OUT GETUTIL.myArray2
)
IS
currDate VARCHAR2 (20);
prevDate VARCHAR2 (20);
total NUMBER;
total0 NUMBER;
cnt NUMBER;
firstR NUMBER;
CURSOR V_CUR
IS
SELECT idle_category,
TO_CHAR (CAST (probed_time AS DATE), 'MM-DD-YYYY') AS pd
FROM inprogress
WHERE tester_id = TESTER
AND probed_time >
TO_TIMESTAMP (CONCAT (DATE1, ' 00:00:00'),
'mm-dd-yyyy hh24:mi:ss')
AND probed_time <
TO_TIMESTAMP (CONCAT (DATE2, ' 23:59:59'),
'mm-dd-yyyy hh24:mi:ss')
ORDER BY probed_time ASC;
/******************************************************************************
NAME: GetUtilisation
PURPOSE:
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 12/12/2022 jyow 1. Created this procedure.
NOTES:
Automatically available Auto Replace Keywords:
Object Name: GetUtilisation
Sysdate: 12/12/2022
Date and Time: 12/12/2022, 12:03:01 AM, and 12/12/2022 12:03:01 AM
Username: jyow (set in TOAD Options, Procedure Editor)
Table Name: (set in the "New PL/SQL Object" dialog)
******************************************************************************/
BEGIN
total := 0;
total0 := 0;
cnt := 1;
firstR:=1;
--UTILBYDAY:= new GETUTIL.myArray();
--UTILBYDAY.EXTEND(14);
--UTILBYDAY (1) := 1;
--UTILBYDAY (2) := 2;
--THEDAYS (1) :='TESTING';
--select idle_category from inprogress where tester_id=TESTER and probed_time>to_timestamp(CONCAT(DATE1, ' 00:00:00'), 'dd-mm-yyyy hh24:mi:ss') and probed_time<to_timestamp(CONCAT(DATE2, ' 23:59:59'), 'dd-mm-yyyy hh24:mi:ss');
FOR V_REC IN V_CUR
LOOP
currDate := V_REC.pd;
IF firstR = 1
THEN
firstR:=99;
prevDate := V_REC.pd;
END IF;
IF prevDate = currDate
THEN
--prevDate:=currDate;
total := total + 1;
IF V_REC.idle_category = 0
THEN
total0 := total0 + 1;
END IF;
ELSE
--UTILBYDAY (cnt) := total0 / total * 100;
UTILDAYS (cnt) := prevDate;
--DBMS_OUTPUT.PUT_LINE('Values' || UTILBYDAY (cnt));
--DBMS_OUTPUT.PUT_LINE('Days' || UTILDAYS (cnt));
cnt := cnt + 1;
total := 0;
total0 := 0;
prevDate := currDate;
END IF;
--DBMS_OUTPUT.PUT_LINE('Values from v_var that are not null' || V_REC.idle_category);
END LOOP;
--UTILBYDAY (cnt) := total0 / total * 100;
UTILDAYS (cnt) := currDate;
--DBMS_OUTPUT.PUT_LINE('Values' || UTILBYDAY (cnt));
--DBMS_OUTPUT.PUT_LINE('Days' || UTILDAYS (cnt));
END GETUTILDAYS;
Any help is appreciated. Thanks.
Reverse engineering your procedure, you appear to want to count the number of entries per day for a given tester and what percentage of those have idle_category of 0. If so, then you can simplify the procedure to:
CREATE PACKAGE getutil IS
TYPE myArray1 IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE myArray2 IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
END;
/
CREATE OR REPLACE PROCEDURE GETUTILDAYS (
DATE1 IN VARCHAR2,
DATE2 IN VARCHAR2,
TESTER IN INPROGRESS.TESTER_ID%TYPE,
UTILBYDAYS OUT GETUTIL.myArray1,
UTILDAYS OUT GETUTIL.myArray2
)
IS
cnt PLS_INTEGER := 0;
CURSOR V_CUR
IS
SELECT TO_CHAR(TRUNC(probed_time), 'MM-DD-YYYY') AS pd,
COUNT(CASE WHEN idle_category = 0 THEN 1 END) AS total0,
COUNT(*) AS total
FROM inprogress
WHERE tester_id = TESTER
AND probed_time >= TO_TIMESTAMP(DATE1, 'mm-dd-yyyy')
AND probed_time < TO_TIMESTAMP(DATE2, 'mm-dd-yyyy') + INTERVAL '1' DAY
GROUP BY TRUNC(probed_time)
ORDER BY TRUNC(probed_time) ASC;
BEGIN
FOR V_REC IN V_CUR
LOOP
cnt := cnt + 1;
UTILDAYS(cnt) := v_rec.pd;
UTILBYDAYS(cnt) := v_rec.total0 / v_rec.total * 100;
END LOOP;
END GETUTILDAYS;
/
and then removing the cursor, you can simplify it further to:
CREATE OR REPLACE PROCEDURE GETUTILDAYS (
DATE1 IN VARCHAR2,
DATE2 IN VARCHAR2,
TESTER IN INPROGRESS.TESTER_ID%TYPE,
UTILBYDAYS OUT GETUTIL.myArray1,
UTILDAYS OUT GETUTIL.myArray2
)
IS
BEGIN
SELECT TO_CHAR(TRUNC(probed_time), 'MM-DD-YYYY'),
COUNT(CASE WHEN idle_category = 0 THEN 1 END) / COUNT(*) * 100
BULK COLLECT INTO
utildays,
utilbydays
FROM inprogress
WHERE tester_id = TESTER
AND probed_time >= TO_TIMESTAMP(DATE1, 'mm-dd-yyyy')
AND probed_time < TO_TIMESTAMP(DATE2, 'mm-dd-yyyy') + INTERVAL '1' DAY
GROUP BY TRUNC(probed_time)
ORDER BY TRUNC(probed_time) ASC;
END GETUTILDAYS;
/
Which, for the sample data:
CREATE TABLE inprogress(probed_time, tester_id, idle_category) AS
SELECT TIMESTAMP '2022-01-01 00:00:00' + (LEVEL - 1) * INTERVAL '1' HOUR,
1,
CASE
WHEN EXTRACT(HOUR FROM TIMESTAMP '2022-01-01 00:00:00' + (LEVEL - 1) * INTERVAL '1' HOUR)
< EXTRACT(DAY FROM TIMESTAMP '2022-01-01 00:00:00' + (LEVEL - 1) * INTERVAL '1' HOUR)
THEN 0
ELSE 1
END
FROM DUAL
CONNECT BY LEVEL <= 24 * 24;
Note: there should be 24 days of data and the number of rows with idle_category = 0 should increase by 1 each day.
Then you can test it with:
DECLARE
arr1 GETUTIL.MYARRAY1;
arr2 GETUTIL.MYARRAY2;
idx PLS_INTEGER;
BEGIN
getutildays('01-01-2022', '01-24-2022', 1, arr1, arr2);
idx := arr1.FIRST;
WHILE idx IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE(arr2(idx) || ': ' || arr1(idx));
idx := arr1.NEXT(idx);
END LOOP;
END;
/
Which outputs:
01-01-2022: 4.16666666666666666666666666666666666667 01-02-2022: 8.33333333333333333333333333333333333333 01-03-2022: 12.5 01-04-2022: 16.66666666666666666666666666666666666667 01-05-2022: 20.83333333333333333333333333333333333333 01-06-2022: 25 01-07-2022: 29.16666666666666666666666666666666666667 01-08-2022: 33.33333333333333333333333333333333333333 01-09-2022: 37.5 01-10-2022: 41.66666666666666666666666666666666666667 01-11-2022: 45.83333333333333333333333333333333333333 01-12-2022: 50 01-13-2022: 54.16666666666666666666666666666666666667 01-14-2022: 58.33333333333333333333333333333333333333 01-15-2022: 62.5 01-16-2022: 66.66666666666666666666666666666666666667 01-17-2022: 70.83333333333333333333333333333333333333 01-18-2022: 75 01-19-2022: 79.16666666666666666666666666666666666667 01-20-2022: 83.33333333333333333333333333333333333333 01-21-2022: 87.5 01-22-2022: 91.66666666666666666666666666666666666667 01-23-2022: 95.83333333333333333333333333333333333333 01-24-2022: 100