Sign in to follow this  
Followers 0
mitchellm44

Oracle count result - wrong

6 posts in this topic

I have a query that returns a result, however the COUNT value that is returned is completely different than what I see when running in a Query Tool.

I am running AutoIT 3.2.4.9

The query should return 2 rows, with value of count in the first row currently @ 549 , but the return value from AutoIT is like 234596 (not exact data here - but in that range)

I have spent a good amount of time trying to get this to work and am turning to the forum for some advice on what is wrong here.

I have also had to modify some parts of the code to obfuscate it. - you understand.

I found another post on the forum about an issue withn Numbers coming back and using the TO_CHAR() function - in Oracle - but that had zero effect -

I had previously used an object to get at the value of $rs(1).Value and got the same result (I added the $rs.Fields(1).Value) after with zero effect).

;_________________________CODE START______________

$Connection = ObjCreate("ADODB.Connection")

$Connection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = computerd016)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = servicename.redacted.com) ));User Id=myusername;Password=redacted;"

$Connection.Open

if $ExitFlag == 0 Then

$rs = objcreate("ADODB.Recordset")

$cmd = ObjCreate("ADODB.Command")

$cmd.ActiveConnection = $Connection

$cmd.CommandType = $adCmdText

$cmd.CommandText = "select distinct HIST_DATA2, count(1) from IMPC_HIST_TB where HIST_CD_FK = 'MEDBPLMUPD' and HIST_DT > '" & @MDAY & "-" & CurrentMonthLiteral() & "-" & @YEAR & "' GROUP BY HIST_DATA2"

;** RUNNING THIS IN TOAD TOOK 5 MINUTES TO GET BACK VALUES

SplashTextOn("Oracle Reporting","This process to retrieve New and Updated Counts takes over 5 minutes Please wait" & @CRLF & $cmd.CommandText & @CRLF & @CRLF & "Start Time " & @HOUR & ":" & @MIN & ":" & @SEC ,400,200,300,300)

$rs = $cmd.Execute

SplashOff();

;MsgBox(0,"TEST", $rs(0).Value)

for $i = 0 to $rs.Fields.Count - 1

$values = $values & $rs.Fields($i).Value() & " : "

next

$newreccount = $rs.Fields(1).Value

if @error Then

msgbox(0,"Error ","After Recordset Value Error",10)

Exit(1)

EndIf

$rs.MoveNext()

$values = $values & @CRLF

for $i = 0 to $rs.Fields.Count - 1

$values = $values & $rs.Fields($i).Value()

next

if @error Then

msgbox(0,"Error ","After Recordset MoveNext Error",10)

Exit(1)

EndIf

$updatedcount = $rs.Fields(1).Value

MsgBox(0,"TEST", "New rec Count = " & $newLoans & " Updated count = " & $updatedcount & @CRLF & $values)

if @error Then

msgbox(0,"Error ","After Recordset Error",10)

Exit(1)

EndIf

$rs.Close()

EndIf

$Connection.Close()

$rs = ""

$cmd = ""

$Connection = ""; Destroy the object

Share this post


Link to post
Share on other sites



Why use a Command Object, can't you directly use a $adoRs.Open("SELECT ....") ?

Also, what is CurrentMonthLiteral().

Try displaying the SQL in a MsgBox and verify it is the exact same SQL you see returning the correct COUNT

Share this post


Link to post
Share on other sites

Why use a Command Object, can't you directly use a $adoRs.Open("SELECT ....") ?

Also, what is CurrentMonthLiteral().

Try displaying the SQL in a MsgBox and verify it is the exact same SQL you see returning the correct COUNT

Yes - you could go that way -- The SQL, is put into the splash screen specifically to verify that what I put in there is what I am running. The results are very different.

I will try putting it in an $adoRs. to see if there is a different behavior.

Share this post


Link to post
Share on other sites

I changed the code to Eliminate the Command - and went directly to the $rs = $Connection.Execute($strSQL)

The same result is happening.

I tried playing around with other was of putting in the date, and still no luck.

The standard format string for an Oracle date DD-MON-YYYY -- is used within the quotes '13-FEB-2009'

and that gives me records that are from Midnight onward. Time portion starts at midnight -- which fits my needs for the processing.

If you have another way of doing the date -that is more efficient - I would like to see that.

The extact numbers coming back from the AutoIt version of this 269349 - Should be 549

the second row is returning 31315516 - should be 83479

The first column value is correct - which is text - the count value is what is screwy.. I am just not seeing why

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

I changed the code to Eliminate the Command - and went directly to the $rs = $Connection.Execute($strSQL)

The same result is happening.

I tried playing around with other was of putting in the date, and still no luck.

The standard format string for an Oracle date DD-MON-YYYY -- is used within the quotes '13-FEB-2009'

and that gives me records that are from Midnight onward. Time portion starts at midnight -- which fits my needs for the processing.

If you have another way of doing the date -that is more efficient - I would like to see that.

The extact numbers coming back from the AutoIt version of this 269349 - Should be 549

the second row is returning 31315516 - should be 83479

The first column value is correct - which is text - the count value is what is screwy.. I am just not seeing why

I don't know if this will help you but I also make queries in ORACLE thru ODBC. Here's a couple lines I use:

; ADO cursortype and locktype
Global Const $i_adOpenDynamic   = 2
Global Const $i_adLockOptimistic = 3

;=== Objet Connection Oracle ==================
$ObjConn = ObjCreate("ADODB.Connection")
$ObjConn.Open("DSN=oracle_dsn;UID=user;PWD=password")

;=== Recordsets =============================================
$rstTESTdata = ObjCreate ("ADODB.Recordset")
$rstTESTdata.CursorType = $i_adOpenDynamic
$rstTESTdata.LockType = $i_adLockOptimistic
;============================================================


$SQL_ = "SELECT SomeColumn1, SomeColumn2 FROM SomeWhere WHERE SomeColumn = 'Value'"
$rstTESTdata.Open ($SQL_, $ObjConn)

$SomeColum1 = $rstTESTdata("SomeColum1").Value
$SomeColum2 = $rstTESTdata("SomeColum2").Value

MsgBox(0,"ORACLE Result","SomeColum1 Value: " & $SomeColum1 & @CRLF & "SomeColum2 Value: " & $SomeColum2)

$rstTESTdata.Close

  • Try showing just one record with the date field
  • Maybe some EPOCH time conversion
  • Does your user has privilege for count(*) queries

Your query in your 'ORACLE tool' should be the pretty similiar to the query in $SQL_

Edited by M a k a v e l !

[font="Lucida Sans Unicode"]M a k. a v e L ![/font]

Share this post


Link to post
Share on other sites

I don't know if this will help you but I also make queries in ORACLE thru ODBC. Here's a couple lines I use:

; ADO cursortype and locktype
Global Const $i_adOpenDynamic   = 2
Global Const $i_adLockOptimistic = 3

;=== Objet Connection Oracle ==================
$ObjConn = ObjCreate("ADODB.Connection")
$ObjConn.Open("DSN=oracle_dsn;UID=user;PWD=password")

;=== Recordsets =============================================
$rstTESTdata = ObjCreate ("ADODB.Recordset")
$rstTESTdata.CursorType = $i_adOpenDynamic
$rstTESTdata.LockType = $i_adLockOptimistic
;============================================================


$SQL_ = "SELECT SomeColumn1, SomeColumn2 FROM SomeWhere WHERE SomeColumn = 'Value'"
$rstTESTdata.Open ($SQL_, $ObjConn)

$SomeColum1 = $rstTESTdata("SomeColum1").Value
$SomeColum2 = $rstTESTdata("SomeColum2").Value

MsgBox(0,"ORACLE Result","SomeColum1 Value: " & $SomeColum1 & @CRLF & "SomeColum2 Value: " & $SomeColum2)

$rstTESTdata.Close

  • Try showing just one record with the date field
  • Maybe some EPOCH time conversion
  • Does your user has privilege for count(*) queries

Your query in your 'ORACLE tool' should be the pretty similiar to the query in $SQL_

The user for both queries -- is the same -- and the Statements are the same

For performance - the Ordinal numbers instead of names are used.

The Epoch time issue - should have shown up in the TOAD/SQL query - but did not..

I will have to review your code example more and see if there might be some specific I can glean from it. - Way past my end of work day - but will have to look at it again in the morning.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0