Error : ORA-06502: PL/SQL: numeric or value error: character string buffer too small

[FLA]

Hello Group,

I have been working on this error for almost a week now and I need
your assistance if possible. I am making a call to a COM object on an
ASP page. Inside the COM object, I call an oracle DB procedure. I am
getting this error "Error : ORA-06502: PL/SQL: numeric or value error:
character string buffer too small".

The page set up is this way. I have a basic query page that returns
some values from the database. Say for instance I enter "Ba" for the
first name waildcard search and "L" for the lastname wildcard search,
I get the error message. If I change it to "Ba" and "J" for instance I
dont get the error. I have checked the results that come back for the
"L" search and none of the values are too long for the variables.
Also, if I enter "Ba" and "Li", it works. There are several
combonations that work and dont work. I am at my witts end over this.
Here is the code that this involves. If anyone sees anything please
let me know. Sorry for the long read but I thought it would be needed
if anyone had a question about something. I did step through the
ORACLE Procedure in Debug mode and it checked out ok. It seems to be
something with the way the parameters are being passed or something
between the COM file and the package maybe??? Any help would be
GREATLY appreciated.

the ASP page portion:
Dim l_istrFirstName, l_iintFirstNameID, l_istrLastName,
l_iintLastNameID, l_istrUserName, l_iintUserNameID
Dim l_ilngBureau, l_ilngRoleID, l_iPageNumber, l_iSortField,
l_iSortDirection, l_ostrPageCount, l_ostrRecordCount
Dim l_oobjInternalUsersArray, l_objFetchInternalUsers
Dim l_intstepArray, l_strStyle

Set l_objFetchInternalUsers = Server.CreateObject("CORE.Search")

l_istrFirstName = Replace(CStr(Request.Form("txtFirstName")),"'","''")
l_istrFirstName = CStr(l_istrFirstName)
l_iintFirstNameID = CInt(Request.Form("rdFirstName"))
l_istrLastName = Replace(CStr(Request.Form("txtLastName")),"'","''")
l_istrLastName = Cstr(l_istrLastName)
l_iintLastNameID = CInt(Request.Form("rdLastName"))
l_istrUserName = CStr(Request.Form("txtUserName"))
l_istrUserName = Cstr(l_istrUserName)
l_iintUserNameID = CInt(Request.Form("rdUserName"))
l_ilngBureau = CStr(Request.Form("cboBureau"))
l_ilngBureau = Cstr(l_ilngBureau)
l_ilngRoleID = CInt(Request.Form("cboRoles"))

'Calling COM method to retrieve the data for ALL PRIVILEGES
Set l_objErrorCollection = l_objFetchInternalUsers.FetchInternalUsers
_
(g_objSecurityContext,_
l_istrFirstName,_
l_iintFirstNameID,_
l_istrLastName,_
l_iintLastNameID,_
l_istrUserName,_
l_iintUserNameID,_
l_ilngBureau,_
l_ilngRoleID,_
False,_
m_intPageNumber,_
7,_
0,_
l_oobjInternalUsersArray,_
l_ostrPageCount,_
l_ostrRecordCount)

If Not l_objErrorCollection Is Nothing Then
***** ERROR GETS WRITTEN OUT ******

Here is the .COM code:
Public Function FetchInternalUsers( _
ByVal v_objSecCtx As SecurityContext, _
ByVal v_strFirstNm As String, _
ByVal v_intFirstNmInd As Integer, _
ByVal v_strLastNm As String, _
ByVal v_intLastNmInd As Integer, _
ByVal v_strUserNm As String, _
ByVal v_intUserNmInd As Integer, _
ByVal v_strBureau As String, _
ByVal v_lngRoleId As Long, _
ByVal v_blnExistingUser As Boolean, _
ByVal v_intPageNumber As Integer, _
ByVal v_intSortField As Integer, _
ByVal v_intSortDirection As Integer, _
ByRef r_varResultArray As Variant, _
ByRef r_varPageCount As Variant, _
ByRef r_varRecordCount As Variant) As CoreErrors

On Error GoTo ERROR_HANDLER

Dim l_cnnCore As ADODB.Connection
Dim l_rstCore As ADODB.Recordset
Dim l_cmdCore As ADODB.Command
Dim l_objErrors As CoreErrors
Dim l_lngErrorNumber As Long
Dim l_strErrorMessage As String
Dim l_intGranted As Integer
Dim l_strDecryptedPassword As String
Dim intStepRow As Integer

' Ensure the user has the required privilege
Set l_objErrors = _
v_objSecCtx.HasPrivilege("VIEW USERS", l_intGranted)
If Not l_objErrors Is Nothing Then _
If l_objErrors.Count > 0 Then GoTo CLEAN_UP
If l_intGranted = 0 Then
Err.Raise _
vbObjectError + 10000, _
"Search.FetchInternalUsers", _
"Insufficient privilege for requested operation"
End If

' Establish a connection
Set l_cnnCore = New ADODB.Connection
l_cnnCore.ConnectionString = g_strConnectionString
l_cnnCore.Open

' Check the input for validity. Change single quote to double
quote
If v_strFirstNm = Chr(39) Then v_strFirstNm = Chr(34)
If InStr(CStr(v_strFirstNm), Chr(39)) Then
v_strFirstNm = Replace(v_strFirstNm, Chr(39), Chr(34))
End If

If v_strLastNm = Chr(39) Then v_strLastNm = Chr(34)
If InStr(CStr(v_strLastNm), Chr(39)) Then
v_strLastNm = Replace(v_strLastNm, Chr(39), Chr(34))
End If

If v_strBureau = Chr(39) Then v_strBureau = Chr(34)
If InStr(CStr(v_strBureau), Chr(39)) Then
v_strBureau = Replace(v_strBureau, Chr(39), Chr(34))
End If

' Create the command
Set l_cmdCore = New ADODB.Command

' Assign the command's properties and parameters
With l_cmdCore

.ActiveConnection = l_cnnCore
.CommandType = adCmdText
.CommandText = _
"{call COREN.PKG_SEARCH.FETCH_INTERNAL_USERS" & _
"(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,{resultset 50, " & _
"out_udtUserId, " & _
"out_udtEmployeeId, " & _
"out_udtUserNm, " & _
"out_udtPassword, " & _
"out_udtFirstNm, " & _
"out_udtMiddleNm, " & _
"out_udtLastNm, " & _
"out_udtLocation, " & _
"out_udtBureau, " & _
"out_udtEmail, " & _
"out_udtRoleNm})}"

' Assign the command's input parameters
.Parameters.Append .CreateParameter("FirstNm", adBSTR, _
adParamInput, , v_strFirstNm)
.Parameters.Append .CreateParameter("FirstNmInd", adInteger, _
adParamInput, , v_intFirstNmInd)
.Parameters.Append .CreateParameter("LastNm", adBSTR, _
adParamInput, , v_strLastNm)
.Parameters.Append .CreateParameter("LastNmInd", adInteger, _
adParamInput, , v_intLastNmInd)
.Parameters.Append .CreateParameter("UserName", adBSTR, _
adParamInput, , v_strUserNm)
.Parameters.Append .CreateParameter("UserNameInd", adInteger,
_
adParamInput, , v_intUserNmInd)
.Parameters.Append .CreateParameter("Bureau", adBSTR, _
adParamInput, , v_strBureau)
.Parameters.Append .CreateParameter("RoleId", adNumeric, _
adParamInput, , v_lngRoleId)
If v_blnExistingUser Then
.Parameters.Append .CreateParameter("ExistingUser",
adInteger, _
adParamInput, , 1)
Else
.Parameters.Append .CreateParameter("ExistingUser",
adInteger, _
adParamInput, , -1)
End If
.Parameters.Append .CreateParameter("PageNumber", adInteger, _
adParamInput, , v_intPageNumber)
.Parameters.Append .CreateParameter("SortField", adInteger, _
adParamInput, , v_intSortField)
.Parameters.Append .CreateParameter("SortDirection",
adInteger, _
adParamInput, , v_intSortDirection)

' Assign the command's output parameters
.Parameters.Append .CreateParameter _
("PageCount", adNumeric, adParamOutput)
.Parameters.Append .CreateParameter _
("RecordCount", adNumeric, adParamOutput)
.Parameters.Append .CreateParameter _
("ErrorNumber", adNumeric, adParamOutput)
.Parameters.Append .CreateParameter _
("ErrorMessage", adVariant, adParamOutput)

End With

' Execute the stored procedure and populate a recordset
Set l_rstCore = New ADODB.Recordset
Set l_rstCore = l_cmdCore.Execute

' Assign the output parameters
If l_rstCore.BOF And l_rstCore.EOF Then
ReDim r_varResultArray(0, 0) As Variant
r_varResultArray(0, 0) = ""
Else
r_varResultArray = l_rstCore.GetRows

' This loop will decrypt each of the passwords retrieved
' from the database
If v_blnExistingUser = True Then
For intStepRow = 0 To UBound(r_varResultArray, 2)
Set l_objErrors = DecryptString(NVL(r_varResultArray
(3, intStepRow), ""), _
l_strDecryptedPassword)
If Not l_objErrors Is Nothing Then _
If l_objErrors.Count > 0 Then GoTo CLEAN_UP
r_varResultArray(3, intStepRow) = CVar
(l_strDecryptedPassword)
Next intStepRow
End If

End If
r_varPageCount = CLng(NVL(l_cmdCore.Parameters("PageCount").value,
0))
r_varRecordCount = CLng(NVL(l_cmdCore.Parameters
("RecordCount").value, 0))
l_lngErrorNumber = CLng(NVL(l_cmdCore.Parameters
("ErrorNumber").value, 0))
l_strErrorMessage = CStr(NVL(l_cmdCore.Parameters
("ErrorMessage").value))

' If we encountered an error while processing the stored
procedure,
' raise an error that will be added to the collection
If l_lngErrorNumber <> 0 Then
Err.Raise _
vbObjectError + l_lngErrorNumber, _
"COREN.PKG_SEARCH.FETCH_INTERNAL_USERS", _
l_strErrorMessage
End If

CLEAN_UP:

' Assign the function return value
Set FetchInternalUsers = l_objErrors

' Free memory
If Not l_objErrors Is Nothing Then Set l_objErrors = Nothing
If Not l_cmdCore Is Nothing Then Set l_cmdCore = Nothing
KillADORecordset l_rstCore
KillADOConnection l_cnnCore

Exit Function

ERROR_HANDLER:

' Assign/Initialize output parameters
ReDim r_varResultArray(0, 0) As Variant
r_varResultArray(0, 0) = ""
r_varPageCount = 0
r_varRecordCount = 0

' Assemeble any runtime or ADO Errors
Set l_objErrors = BuildErrorCollection(Err, l_cnnCore)

GoTo CLEAN_UP

End Function


Lastly the Oracle Proc:
PROCEDURE Fetch_Internal_Users
(in_strFirstNm VARCHAR2,
in_intFirstNmInd INT,

in_strLastNm VARCHAR2,
in_intLastNmInd INT,

in_strUserNm VARCHAR2,
in_intUserNmInd INT,

in_strBureau IN VARCHAR2,
in_numRoleID IN NUMBER,
in_intExistingUser IN INT,

in_intPageNumber IN INT,
in_intSortField IN INT,
in_intSortDirection IN INT,

out_numPageCount OUT NUMBER,
out_numRecordCount OUT NUMBER,
out_numErrorNumber OUT NUMBER,
out_strErrorMessage OUT VARCHAR2,

out_udtUserId OUT udt_id,
out_udtEmployeeId OUT udt_id,
out_udtUserNm OUT udt_UserName,
out_udtPassword OUT udt_password,
out_udtFirstNm OUT udt_name,
out_udtMiddleNm OUT udt_name,
out_udtLastNm OUT udt_name,
out_udtLocation OUT udt_location,
out_udtBureau OUT udt_bureau,
out_udtEmail OUT udt_email,
out_udtRoleNm OUT udt_name)
IS
strSelect VARCHAR2(1000);
strFrom VARCHAR2(1000);
strWhere VARCHAR2(1000);
strJoin VARCHAR2(1000);
strOrder VARCHAR2(100);
strSearchValue VARCHAR(75);

numUserId NUMBER(9);
numEmployeeId NUMBER(9);
strUserNm VARCHAR2(100);
strUserPassword VARCHAR2(510);
strFirstNm VARCHAR2(25);
strMiddleNm VARCHAR2(25);
strLastNm VARCHAR2(25);
strLocation VARCHAR2(50);
strBureau VARCHAR2(45);
strEmail VARCHAR2(100);
strRoleNm VARCHAR2(100);

curSearch INTEGER;
intIndex INTEGER;
intPos INTEGER;
intTableIndex INTEGER;
intResult INTEGER;
intLen INTEGER;
strTemp VARCHAR2(100);
intPageNumber INT;
expInvalidArg EXCEPTION;

BEGIN

-- Initialize output parameters
out_numPageCount := 0;
out_numRecordCount := 0;
out_numErrorNumber := 0;
out_strErrorMessage := '';

-- Test for requested Page number
If in_intPageNumber is null or in_intPageNumber = 0 Then
intPageNumber := 1;
else
intPageNumber := in_intPageNumber;
End If;

If in_intExistingUser <> 1 and in_intExistingUser <> -1 Then
out_numErrorNumber := 5102;
out_strErrorMessage := 'Please specify if this is a request
for an existing CORE user ' ||
'(in_intExistingUser = 1) or a user not in the CORE system
(in_intExistingUser = -1).';
RAISE expInvalidArg;
End If;

IF in_intExistingUser = 1 THEN

-- Compose SELECT clause
strSelect := 'SELECT DISTINCT u.core_user_id, ' ||
'i.doi_employee_id, ' ||
'u.user_nm, ' ||
'u.user_password, ' ||
'i.first_name, ' ||
'i.middle_name, ' ||
'i.last_name, ' ||
'i.office_location, '||
'i.bureau, '||
'i.email, '||
'r.role_nm ';

-- Compose the FROM clause
strFrom := 'FROM DOI_EMPLOYEE i, ' ||
'COREN.core_user u, ' ||
'COREN.role r ';

-- Compose Join part of WHERE clause
strWhere := 'WHERE i.doi_employee_id = u.doi_employee_id '
||
'AND u.role_id = r.role_id ' ||
'AND u.active_ind = 1 ';

ELSIF in_intExistingUser = -1 THEN
-- Compose SELECT clause
strSelect := 'SELECT DISTINCT null, ' ||
'i.doi_employee_id, ' ||
'null, ' ||
'null, ' ||
'i.first_name, ' ||
'i.middle_name, ' ||
'i.last_name, ' ||
'i.office_location, '||
'i.bureau, '||
'i.email, '||
'null ';

-- Compose the FROM clause
strFrom := 'FROM DOI_EMPLOYEE i ';
--DBMS_OUTPUT.PUT_LINE(strFrom);

-- Compose Join part of WHERE clause
strWhere := 'WHERE i.doi_employee_id in ( ' ||
'SELECT DISTINCT e.doi_employee_id ' ||
'FROM DOI_EMPLOYEE e ' ||
'MINUS ' ||
'SELECT DISTINCT u.doi_employee_id ' ||
'FROM COREN.core_user u ' ||
'WHERE u.active_ind = 1) ';

END IF;

-- If the user specified a first name...
If NOT (in_strFirstNm is null or LENGTH(in_strFirstNm) = 0) THEN
IF in_intFirstNmInd = 1 THEN -- Start with
strWhere := strWhere || ' and upper(first_name) like
'''||
upper(in_strFirstNm) || '%''';

ELSIF in_intFirstNmInd = 2 THEN -- Exact match
strWhere := strWhere || ' and upper(first_name) = ''' ||
upper(in_strFirstNm) || '''';

ELSE -- Anywhere
strWhere := strWhere ||' and upper(first_name) like
''%'||
upper(in_strFirstNm) || '%''';
END IF;
End If;

-- If the user specified a last name...
If NOT (in_strLastNm is null or LENGTH(in_strLastNm) = 0) THEN
IF in_intLastNmInd = 1 THEN -- Start with
strWhere := strWhere || ' and upper(Last_name) like
'''||
upper(in_strLastNm) || '%''';

ELSIF in_intLastNmInd = 2 THEN -- Exact match
strWhere := strWhere || ' and upper(Last_name) = ''' ||
upper(in_strLastNm) || '''';

ELSE -- Anywhere
strWhere := strWhere ||' and upper(Last_name) like
''%'||
upper(in_strLastNm) || '%''';
END IF;
End If;

-- If the user specified a user name...
If in_intExistingUser = 1 THEN
If NOT (in_strUserNm is null or LENGTH(in_strUserNm) = 0)
THEN
IF in_intUserNmInd = 1 THEN -- Start with
strWhere := strWhere || ' and upper(user_nm) like '''||
upper(in_strUserNm) || '%''';
ELSIF in_intUserNmInd = 2 THEN -- Exact match
strWhere := strWhere || ' and upper(user_nm) = ''' ||
upper(in_strUserNm) || '''';
ELSE -- Anywhere
strWhere := strWhere ||' and upper(user_nm) like ''%'||
upper(in_strUserNm) || '%''';
END IF;
End If;
End if;

-- If the user specified a bureau...
If NOT (in_strBureau is null or LENGTH(in_strBureau) = 0) THEN
strWhere := strWhere || ' and upper(bureau) like ''' ||
'%' || upper(in_strBureau) || '%' || '''';
End If;

-- If the user specified a role id...
If in_intExistingUser = 1 AND NOT (in_numRoleId is null or
in_numRoleId = 0) THEN
strWhere := strWhere || ' and r.role_id = ' ||
to_char(in_numRoleId);
End If;

-- Sort the search result based on the field requested
-- 1 = UserId
-- 2 = EmployeeId
-- 3 = UserNm
-- 4 = Password
-- 5 = FirstNm
-- 6 = MiddleNm
-- 7 = LastNm (Default)
-- 8 = Location
-- 9 = Bureau
-- 10 = Email
-- 11 = RoleNm

IF in_intSortField IS NULL OR in_intSortField > 11 OR
in_intSortField < 1 THEN
strOrder := ' ORDER BY 7';
ELSE
strOrder := ' ORDER BY '||to_char(in_intSortField);
END IF;

-- If the user wants decending sort order...
IF in_intSortDirection = 1 THEN
strOrder := strOrder || ' DESC';
END IF;

DBMS_OUTPUT.PUT_LINE(strSelect);
DBMS_OUTPUT.PUT_LINE(strFrom);
DBMS_OUTPUT.PUT_LINE(strWhere);
DBMS_OUTPUT.PUT_LINE(strOrder);

-- Send the query to Oracle
curSearch := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(curSearch, strSelect || strFrom || strWhere ||
strOrder, DBMS_SQL.V7);

-- Define the column of the result set
DBMS_SQL.DEFINE_COLUMN(curSearch, 1, numUserId);
DBMS_SQL.DEFINE_COLUMN(curSearch, 2, numEmployeeId);
DBMS_SQL.DEFINE_COLUMN(curSearch, 3, strUserNm,100);
-- DBMS_SQL.DEFINE_COLUMN(curSearch, 4, strUserPassword,512);
DBMS_SQL.DEFINE_COLUMN(curSearch, 4, strUserPassword,510);
DBMS_SQL.DEFINE_COLUMN(curSearch, 5, strFirstNm,25);
DBMS_SQL.DEFINE_COLUMN(curSearch, 6, strMiddleNm,25);
DBMS_SQL.DEFINE_COLUMN(curSearch, 7, strLastNm,25);
DBMS_SQL.DEFINE_COLUMN(curSearch, 8, strLocation,50);
DBMS_SQL.DEFINE_COLUMN(curSearch, 9, strBureau,45);
-- DBMS_SQL.DEFINE_COLUMN(curSearch, 10, strEmail,255);
DBMS_SQL.DEFINE_COLUMN(curSearch, 10, strEmail,100);
DBMS_SQL.DEFINE_COLUMN(curSearch, 11, strRoleNm,100);

-- strUserPassword VARCHAR2(510);
-- strEmail VARCHAR2(100);


-- Go throught the result set row by row and fetch the value of
each column then
-- insert this value into the pl/sql tables (0utput parameters)
intIndex := 0;
intTableIndex := 0;
intResult := DBMS_SQL.EXECUTE(curSearch);

LOOP
EXIT WHEN DBMS_SQL.FETCH_ROWS(curSearch) = 0;
intIndex := intIndex + 1;
IF (intIndex > (intPageNumber - 1) * g_intRecordsPerPage AND
intIndex < (intPageNumber * g_intRecordsPerPage + 1)) THEN
intTableIndex := intTableIndex +1;

DBMS_SQL.COLUMN_VALUE(curSearch, 1, numUserId);
DBMS_SQL.COLUMN_VALUE(curSearch, 2, numEmployeeId);
DBMS_SQL.COLUMN_VALUE(curSearch, 3, strUserNm);
DBMS_SQL.COLUMN_VALUE(curSearch, 4, strUserPassword);
DBMS_SQL.COLUMN_VALUE(curSearch, 5, strFirstNm);
DBMS_SQL.COLUMN_VALUE(curSearch, 6, strMiddleNm);
DBMS_SQL.COLUMN_VALUE(curSearch, 7, strLastNm);
DBMS_SQL.COLUMN_VALUE(curSearch, 8, strLocation);
DBMS_SQL.COLUMN_VALUE(curSearch, 9, strBureau);
DBMS_SQL.COLUMN_VALUE(curSearch, 10, strEmail);
DBMS_SQL.COLUMN_VALUE(curSearch, 11, strRoleNm);

IF strUserNm IS NULL THEN
IF NOT (strEmail IS NULL) THEN
intPos := INSTR(strEmail, '@');
strUserNm := UPPER(SUBSTR(strEmail, 1, intPos-1));
ELSE
strUserNm := UPPER(strLastNm || SUBSTR(strFirstNm,
1, 1));
SELECT COUNT(*)
INTO intResult
FROM core_user
WHERE UPPER(user_nm) = strUserNm;

intPos := 1;
strTemp := strUserNm;
intLen := Length(strTemp);
WHILE intResult > 0 LOOP
intPos := intPos + 1;
strTemp := SUBSTR(strTemp, 1, intLen) || to_char
(intPos);

intResult := 0;
SELECT COUNT(*)
INTO intResult
FROM core_user
WHERE UPPER(user_nm) = strTemp;
END LOOP;

strUserNm := strTemp;
END IF;
END IF;

out_udtUserId(intTableIndex) := numUserId;
out_udtEmployeeId(intTableIndex) := numEmployeeId;
out_udtUserNm(intTableIndex) := strUserNm;
out_udtPassword(intTableIndex) := strUserPassword;
out_udtFirstNm(intTableIndex) := strFirstNm;
out_udtMiddleNm(intTableIndex) := strMiddleNm;
out_udtLastNm(intTableIndex) := strLastNm;
out_udtLocation(intTableIndex) := strLocation;
out_udtBureau(intTableIndex) := strBureau;
out_udtEmail(intTableIndex) := strEmail;
out_udtRoleNm(intTableIndex) := strRoleNm;

END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(curSearch);

-- assign output parameters
out_numRecordCount := intIndex;
out_numPageCount := ceil(out_numRecordCount /
g_intRecordsPerPage);

-- Test for out of range page number
IF out_numRecordCount > 0 and in_intPageNumber >
out_numPageCount THEN
out_numErrorNumber := 5001;
out_strErrorMessage := 'The requested page exceeds the pages
available.';
RAISE expInvalidArg;
END IF;

EXCEPTION
WHEN expInvalidArg THEN
IF dbms_sql.is_open(curSearch) THEN
dbms_sql.close_cursor(curSearch);
END IF;
IF out_numErrorNumber = 0 THEN
out_numErrorNumber := -20051;
out_strErrorMessage := 'An invalid argument value was
specified';
END IF;

WHEN OTHERS THEN
IF dbms_sql.is_open(curSearch) THEN
dbms_sql.close_cursor(curSearch);
END IF;
out_numErrorNumber := SQLCODE;
out_strErrorMessage := SQLERRM;

END Fetch_Internal_Users;

[rgvguplb]

On Mar 9, 8:03 am, FLA <lee.whee...@fldfs.com> wrote:
> Hello Group,
>
> I have been working on this error for almost a week now and I need
> your assistance if possible.  I am making a call to a COM object on an
> ASP page. Inside the COM object, I call an oracle DB procedure.  I am
> getting this error "Error : ORA-06502: PL/SQL: numeric or value error:
> character string buffer too small".
>
> The page set up is this way. I have a basic query page that returns
> some values from the database.  Say for instance I enter "Ba" for the
> first name waildcard search and "L" for the lastname wildcard search,
> I get the error message. If I change it to "Ba" and "J" for instance I
> dont get the error. I have checked the results that come back for the
> "L" search and none of the values are too long for the variables.
> Also, if I enter "Ba" and "Li", it works.  There are several
> combonations that work and dont work. I am at my witts end over this.
> Here is the code that this involves. If anyone sees anything please
> let me know. Sorry for the long read but I thought it would be needed
> if anyone had a question about something.  I did step through the
> ORACLE Procedure in Debug mode and it checked out ok. It seems to be
> something with the way the parameters are being passed or something
> between the COM file and the package maybe??? Any help would be
> GREATLY appreciated.
>

well, in my relatively short experience, i'd say that error message
means exactly what it says. Why not up all the lengths of your
character variables and run it again? Even if it seems unlikely to you
at the moment, the fact that different parameters produce different
results seems entirely reasonable to me for this kind of error.

Or it could well be you are looking at the wrong variable when it's
another variable that's too short.

I'd up them all up in your pl/sql procedure and give it a whirl. But
that's just me.

[Rob Wolfe]

I know that i always say to include a good example but there is no way
that anyone (ok I am surely not going to) is going to read the 650+
lines of code that you pasted in this post.

Just saying.

Rob

[FLA]

Well, in order to understand the code, I had too... and I applogized
at the top for the long read. No need to be a jerk.

[Rob Wolfe]

My point was, and still is, that putting over 600 lines of code out and
expecting people to read, understand and fix it is unrealistic. I make no
apology for saying that.

You can take it as me being a jerk if you like but you have to bear in
mind that everyone on here does this on a voluntary basis. I am presuming
that you are getting paid for what you do, sadly (for us) we are not paid
to help you. We help out on this board because we remember what it was
like when we started out and were trying to grope our way through the
tangle that is Oracle.

If I recall, what you gave us was really a multi-part problem. It could
have been in your calling (non-Oracle)code, or it could have been in your
PL/SQL. What would have been helpful, instead of calling me a jerk, would
have been for you to pare down the example as much as you could and still
be getting the error. At that point you would be in a position to pinpoint
exactly where the error was happening and you would either have your own
eureka moment (happens all the time to me) or you would have a compact
example that was far less onerous for someone to analyse.

Of course if it feels better to just think I am a jerk, that is your
privilege. I think that you will find that my reaction to your code dump
was very mild compared to the response you would get on most other
technical groups. I have no desire to get into a flame war here, I think
that I am merely stating the obvious. A request to debug a 600 line chunk
of code is not going to get the same positive attention as a concise
example that demonstrates the same error.

YMMV, etc etc

Rob