Start Free Trial

asked on

Problems migrating to Oracle ODBC Driver

I am migrating a classic ASP website that connects to an Oracle database via ODBC from windows 2003 (32-bit) to windows 2012 R2 (64-bit)

On the original website we are using the Microsoft ODBC driver for Oracle and since this is no longer available we are using the 12c Client - Oracle ODBC driver.

I am able to setup a tsn and successfully connect to the database
I am also able to setup a 64-bit ODBC connection and successfully connect to the database
On the website, I am able to successfully query the db and retrieve data.
When I insert / update / delete data on the website it is done via stored procedure calls. The problem that I am having appears to be executing a stored procedure.

My connection string is objCONN.Open "Data Source=dsn_name", "username", "password"

My call to execute the stored procedure is ...

Sub prc_delete_stale_right(P_USER_INFO_ID)
Dim objCM

On Error Resume Next

If ErrNum = 0 Then
Set objCM = Server.CreateObject("ADODB.Command")
objCM.ActiveConnection = objCONN
objCM.CommandText = "prc_delete_stale_right"
objCM.CommandType = adCmdStoredProc

objCM.Parameters.Append objCM.CreateParameter("P_USER_INFO_ID", adNumeric, adParamInput)
objCM.Parameters.Append objCM.CreateParameter("P_RET_CODE", adNumeric, adParamInputOutput)
objCM.Parameters.Append objCM.CreateParameter("P_ERROR_MESSAGE", adVarChar, adParamInputOutput, 4000)

objCM.Parameters("P_USER_INFO_ID").value = P_USER_INFO_ID
objCM.Parameters("P_RET_CODE").value = 0
objCM.Parameters("P_ERROR_MESSAGE").value = "Nothing"

objCM.Execute
ErrNum = Err.Number
ErrStr = Err.Description

If ErrNum = 0 Then
If Not objCM.Parameters("P_RET_CODE").value = 0 Then
ErrNum = objCM.Parameters("P_RET_CODE").value
ErrStr = objCM.Parameters("P_ERROR_MESSAGE").value
Else
End If
End If
Set objCM = Nothing
End If
End Sub

I am getting the following error
ErrNum: -2147217887
ErrStr: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done

any / all help is appreciated

Is it possible for you to narrow down your code i.e, reduce this procedure by commenting out most of the code.
Remove everything to see if it can pass when doing nothing. Than you may be able to understand where it fails by adding lines one after the other.

Also, but not sure on this, maybe the "On Error Resume Next" could mask some error ?

ASKER

a. On Error Resume Next is recommended by Microsoft and oracle when calling the db.
b. the reduction of code was my next option but was hoping to avoid it ... the db code is pretty simple actually

CREATE OR REPLACE PROCEDURE PRC_DELETE_STALE_RIGHT(
  P_USER_INFO_ID    USER_INFO.ID%type
 ,P_RET_CODE IN OUT NUMBER
 ,P_ERROR_MESSAGE IN OUT VARCHAR2
 )
 IS

BEGIN
  dbms_output.put_line('Entering update_user');

  p_ret_code :=0;
  p_error_message := null;

   delete assigned_rights
   where user_rights_id = (select id from user_rights where name = 'bolurStale')
   and user_info_id = p_user_info_id;

      COMMIT;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
      p_ret_code := -1;
      p_error_message := 'User Not found ';

    WHEN OTHERS THEN
       ROLLBACK;
       p_ret_code := -1;
       p_error_message := substr(SQLERRM,1,255);


END ;

Open in new window

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

I added some code to print out the values of what I am passing in
- P_USER_INFO_ID - 341
- P_RET_CODE - 0
- P_ERROR_MESSAGE - Nothing

as for the constants, that is part of today's work to see if they changed but I wouldn't think so otherwise it would be all over the searches
Const adNumeric = 131
Const adVarChar = 200

Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adParamInputOutput = &H0003

ASKER

I made the stored procedure nothing more than the setting of the outbound parameters and no change ... this appears to be a preprocessor error

CREATE OR REPLACE PROCEDURE PRC_DELETE_STALE_RIGHT(
  P_USER_INFO_ID    USER_INFO.ID%type
 ,P_RET_CODE IN OUT NUMBER
 ,P_ERROR_MESSAGE IN OUT VARCHAR2
 )
 IS

BEGIN

  p_ret_code :=0;
  p_error_message := null;


EXCEPTION
    WHEN NO_DATA_FOUND THEN
      p_ret_code := -1;
      p_error_message := 'User Not found ';

    WHEN OTHERS THEN
       ROLLBACK;
       p_ret_code := -1;
       p_error_message := substr(SQLERRM,1,255);


END ;

Open in new window

if you plug those values directly into the SP does it run successfully?

ASKER

The stored procedure is currently working in our production environment.
I also manually executed it in the database and it worked

In the asp call to setup the db call, I replaced the ado constants with their values and also hardcoded the 1 passed in value and I get the same error / result.

objCM.Parameters.Append objCM.CreateParameter("P_USER_INFO_ID", 131, &H0001)
objCM.Parameters.Append objCM.CreateParameter("P_RET_CODE", 131, &H0003)
objCM.Parameters.Append objCM.CreateParameter("P_ERROR_MESSAGE", 200, &H0003, 4000)

objCM.Parameters("P_USER_INFO_ID").value = 341 'P_USER_INFO_ID
objCM.Parameters("P_RET_CODE").value = 0
objCM.Parameters("P_ERROR_MESSAGE").value = ""

I'm heading out for a bit, I'll try to have a look later

ASKER

thanks for you help so far.

ASKER

I removed all parameters from the call and hardcoded the sql within the procedure ... I am getting an err.number of 13 and description of type mismatch

CREATE OR REPLACE PROCEDURE PRC_DELETE_STALE_RIGHT
 IS
BEGIN
   delete assigned_rights
   where user_rights_id = 24
     and user_info_id = 341;

  COMMIT;

EXCEPTION
    WHEN OTHERS THEN
       ROLLBACK;

END ;

Open in new window

a type mismatch error indicates an incorrect data type. it's been a loooong time since I've dealt with Oracle, so I'm a little rusty on that end of things, but I imagine thats where the problem lies.

ASKER

But there is nothing left to be a data type error. There are no parameters no local variables in the stored procedure

I think where I have it it is clearly an odbc cfg or driver issu . It has to be something stupid at this point

Can you post your full test code so I can have a look?

ASKER

OK I am gone for the day . Will need to get to it tomorrow

Sure no prob. When you do get to it post both the asp code and any changes you made to the sp

ASKER

Here is the asp code, which is calling the stored procedure where I am getting the err.number 13 err.description Type mismatch

Sub prc_delete_stale_right(P_USER_INFO_ID)
  Dim objCM

  On Error Resume Next

  If ErrNum = 0 Then
    Set objCM = Server.CreateObject("ADODB.Command")
    objCM.ActiveConnection = objCONN
    objCM.CommandText = "prc_delete_stale_right"
    objCM.CommandType = adCmdStoredProc


    objCM.Execute
    ErrNum = Err.Number
    ErrStr = Err.Description

    response.write "<br>ErrNum:" & ErrNum
    response.write "<br>ErrStr:" & ErrStr
    response.end


    If ErrNum = 0 Then
      If Not objCM.Parameters("P_RET_CODE").value = 0 Then
        ErrNum = objCM.Parameters("P_RET_CODE").value
        ErrStr = objCM.Parameters("P_ERROR_MESSAGE").value
      Else
      End If
    End If
    Set objCM = Nothing
  End If
End Sub

Open in new window

Here is the stored procedure code

CREATE OR REPLACE PROCEDURE PRC_DELETE_STALE_RIGHT

 IS

BEGIN
   delete from assigned_rights
   where user_rights_id = 24
   and user_info_id = 341;

  COMMIT;

EXCEPTION

    WHEN OTHERS THEN
       ROLLBACK;

END ;

Open in new window

ASKER

As I was thinking about this ...
this is currently working in the production environment

Microsoft ODBC for Oracle driver

Microsoft windows 2003 server - 32 bit

Oracle 10g db

The new environment is

Oracle ODBC driver (64-bit) w. Oracle 12c client install

Windows 2012 R2 - 64-bit

Oracle 11g database

let me know if this helps.

can you call a simple select statement and read from the recordset the results? if so, it's not a configuration issue

also, I presume the error is occurring on the .Execute line?

ASKER

yes, it appears that we are only having the issue w/ execute

I was going to search for other ways to call the stored procedure and / or sql within to further track it down ... but we have 300+ stored procedures and so we need to figure this out.

before the call to this asp function ErrNum = 0
after the execute it is the error described and is displayed in the browser by these lines within the asp function.
objCM.Execute
ErrNum = Err.Number
ErrStr = Err.Description
response.write "<br>ErrNum:" & ErrNum
response.write "<br>ErrStr:" & ErrStr
response.end

just to make sure we have everything covered, try changing

objCM.CommandType = adCmdStoredProc

to

objCM.CommandType = 4

also, I assume objCONN is a global variable?

ASKER

in my ado include file
Const adCmdStoredProc = &H0004

but I put in objCM.CommandType = 4
and I get the same error

yes objCONN is the global connection obj

Set objCONN = Server.CreateObject("ADODB.Connection")
objCONN.Open "Data Source=dsn_name", "username", "password"

the code looks fine, can you verify it's being caused by the .Execute command by commenting out

on error resume next

you'll also need to change your if statement to make sure it executes.

ASKER

Same error ... this is what the function looks like now

Sub prc_delete_stale_right(P_USER_INFO_ID)
  Dim objCM

    Set objCM = Server.CreateObject("ADODB.Command")
    objCM.ActiveConnection = objCONN
    objCM.CommandText = "prc_delete_stale_right"
    objCM.CommandType = 4 'adCmdStoredProc

    objCM.Execute
    ErrNum = Err.Number
    ErrStr = Err.Description

    response.write "<br>ErrNum:" & ErrNum
    response.write "<br>ErrStr:" & ErrStr
    response.end


    If ErrNum = 0 Then
      If Not objCM.Parameters("P_RET_CODE").value = 0 Then
        ErrNum = objCM.Parameters("P_RET_CODE").value
        ErrStr = objCM.Parameters("P_ERROR_MESSAGE").value
      Else
      End If
    End If
    Set objCM = Nothing
End Sub

Open in new window

what line number is the error occurring? with the on error resume next statement gone it should display exactly what line the error is happening on

ASKER

the error is getting reported online 13 & 14 above which is simply the error number from the Err object which gets populated after the execute call ... if I comment out the response.end the page continues.

ok so you still have error handling on somewhere else, let's change your test code to:

Sub prc_delete_stale_right(P_USER_INFO_ID)
  Dim objCM

on error goto 0

    Set objCM = Server.CreateObject("ADODB.Command")
    objCM.ActiveConnection = objCONN
    objCM.CommandText = "prc_delete_stale_right"
    objCM.CommandType = 4 'adCmdStoredProc

    objCM.Execute
    ErrNum = Err.Number
    ErrStr = Err.Description

    response.write "<br>ErrNum:" & ErrNum
    response.write "<br>ErrStr:" & ErrStr
    response.end


    If ErrNum = 0 Then
      If Not objCM.Parameters("P_RET_CODE").value = 0 Then
        ErrNum = objCM.Parameters("P_RET_CODE").value
        ErrStr = objCM.Parameters("P_ERROR_MESSAGE").value
      Else
      End If
    End If
    Set objCM = Nothing
End Sub

Open in new window

this should turn off all error handling and verify that the error occurs on line 11 (objCM.Execute). if it doesn't, the error could be from somewhere else

ASKER

ok, I am now thoroughly confused ... the page processed cleanly.

let me put some stuff back and see what is happening.

one more thing to check is to see if the actual data is being deleted by the stored procedure

i think what's happening is that an error is occurring somewhere else and is being kept in memory, and when you finally check the err object, it's making you think it's being caused by the sp

ASKER

at the start of all this, I checked the errnum variable before and after the asp function call and that is how it was narrowed down to that call which is the first db sproc call ... regardless, I will check and verify again.

did the data get deleted?

ASKER

ok, here is an update on where we are.

When I do not pass in any Input parameters everything seems to work. So I can pass in the output parameters and it works.

When I attempt to pass in the input parameter I get the following error -- line 35 is the execute.
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Oracle][ODBC][Ora]ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'PRC_DELETE_STALE_RIGHT' ORA-06550: line 1, column 7: PL/SQL: Statement ignored
/data/V_prc_delete_stale_right.inc, line 35

The stored procedure's parameters are
CREATE OR REPLACE PROCEDURE PRC_DELETE_STALE_RIGHT(
P_USER_INFO_ID USER_INFO.ID%type -- this is a number datatype (sequence - primary key)
,P_RET_CODE IN OUT NUMBER
,P_ERROR_MESSAGE IN OUT VARCHAR2
)

The code in the asp function relating to the inpout parameter is
objCM.Parameters.Append objCM.CreateParameter("P_USER_INFO_ID", adNumeric, adParamInput)
objCM.Parameters("P_USER_INFO_ID").value = P_USER_INFO_ID

With this new information I am wondering how much this might be related to 32-bit vs. 64 bit ODBC drivers.

any thoughts there.

have a look here and try implementing thr line of code below:

objCmd.Properties("PLSQLRSet") = TRUE

we've pretty much tackled everything else, it very well could be a driver issue, so maybe this'll help

can you also post the latest version of your code?

ASKER

sorry for the delay, I have been dealing with another client. I will try to get back on this Friday AM.

ASKER

Ok, I have tracked down the type mismatch and appears to be tied to checking the stored procedure's return value

this line
If Not objCM.Parameters("P_RET_CODE").value = 0 Then

became

this line
If Not clng(objCM.Parameters("P_RET_CODE").value) = 0 Then

Now when I put back the original code to pass in a parameter, I still get the following ... line 35 is the execute.

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
/data/V_prc_delete_stale_right.inc, line 35

And just as the error states no work was done, the sproc didnt' execute successfully.

so what is the "magic" about passing in a parameter.

can you post the latest version of your code?

ASKER

sorry for the delay ... currently trying to use the 32-bit instant client to see if this is any different. I will repost my code shortly after this latest test.

ASKER

part of the problem w/ stored procedure calls was that I needed to provide adNumeric parameter types
Set Parameter = objCM.CreateParameter("P_ID", adNumeric, adParamInput)
Parameter.Precision = 10
Parameter.NumericScale = 0

This allowed the stored procedure to execute, however, if I was passing back a parameter, I never got this to work with odbc and so I switched to an OraOLEDB connection.

I consider this closed and Big Monty can get the points for his help