Solved

Problems migrating to Oracle ODBC Driver

Posted on 2014-10-11
41
368 Views
Last Modified: 2015-04-26
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
0
Comment
Question by:JeffStone
  • 21
  • 17
41 Comments
 
LVL 7

Expert Comment

by:Stampel
ID: 40374554
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 ?
0
 

Author Comment

by:JeffStone
ID: 40374922
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

0
 
LVL 32

Accepted Solution

by:
Big Monty earned 500 total points
ID: 40374936
On Error Resume Next is recommended by Microsoft and oracle when calling the db.

actually, it's the only way to handle errors in classic asp, and can/should be used with more than database calls. since your capturing the Err.Description, commenting it out won't yield much more info so you can leave it as is.

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done

this message indicates that one of the values provided is invalid. It could be blank/null or an invalid data type. also, make sure the constants bolded below have been defined somewhere (I assume you have an adovbs.inc file included)

objCM.Parameters.Append objCM.CreateParameter("P_USER_INFO_ID", adNumeric, adParamInput)
0
 

Author Comment

by:JeffStone
ID: 40374947
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
0
 

Author Comment

by:JeffStone
ID: 40374963
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

0
 
LVL 32

Expert Comment

by:Big Monty
ID: 40374965
if you plug those values directly into the SP does it run successfully?
0
 

Author Comment

by:JeffStone
ID: 40374973
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 = ""
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 40374975
I'm heading out for a bit, I'll try to have a look later
0
 

Author Comment

by:JeffStone
ID: 40374981
thanks for you help so far.
0
 

Author Comment

by:JeffStone
ID: 40375003
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

0
 
LVL 32

Expert Comment

by:Big Monty
ID: 40375280
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.
0
 

Author Comment

by:JeffStone
ID: 40375563
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
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 40375658
Can you post your full test code so I can have a look?
0
 

Author Comment

by:JeffStone
ID: 40375663
OK I am gone for the day . Will need to get to it tomorrow
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 40375671
Sure no prob.  When you do get to it post both the asp code and any changes you made to the  sp
0
 

Author Comment

by:JeffStone
ID: 40376859
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

0
 

Author Comment

by:JeffStone
ID: 40376897
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.
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 40377159
can you call a simple select statement and read from the recordset the results? if so, it's not a configuration issue
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 40377165
also, I presume the error is occurring on the .Execute line?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:JeffStone
ID: 40377186
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
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 40377224
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?
0
 

Author Comment

by:JeffStone
ID: 40377277
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"
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 40377327
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.
0
 

Author Comment

by:JeffStone
ID: 40377336
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

0
 
LVL 32

Expert Comment

by:Big Monty
ID: 40377341
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
0
 

Author Comment

by:JeffStone
ID: 40377357
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.
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 40377378
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
0
 

Author Comment

by:JeffStone
ID: 40377483
ok, I am now thoroughly confused ... the page processed cleanly.

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

Expert Comment

by:Big Monty
ID: 40377494
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
0
 

Author Comment

by:JeffStone
ID: 40377577
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.
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 40377785
did the data get deleted?
0
 

Author Comment

by:JeffStone
ID: 40379037
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.
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 40380003
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
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 40380014
can you also post the latest version of your code?
0
 

Author Comment

by:JeffStone
ID: 40385860
sorry for the  delay, I have been dealing with  another client.   I will try to get back on this Friday AM.
0
 

Author Comment

by:JeffStone
ID: 40389367
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.
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 40392437
can you post the latest version of your code?
0
 

Author Comment

by:JeffStone
ID: 40400248
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.
0
 

Author Comment

by:JeffStone
ID: 40473753
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
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now