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.
CodePudding user response：
As per the error it clearly shows that this is an issue on your Database side. This error usually occurs when you either try to set a variable whose value is too big.
Check this: So error happens when:
you are using sqlplus with version less than 10.2 and the version of Database server is 10.2 or higher. For example:
- client is 10.1.0.5 and server is 12.2.9
- client is 10.1.0.5 and server is 10.2.0.5
- client is 10.1.0.5 and server is 11.2.0.1 the serveroutput on option is set
you have put a line with more than 255 characters into the dbms_output buffer.
CodePudding user response：
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