Hi Everyone
I am running a Oracle 9.2.0.6 EE and using Heterogeneous Services (HS) to
access a SQL-Server 2000 database. I believe that I have both sides
configured correctly and we have been using HS services for a while,
mostly to report against the SQL-Server database. I ran into a oddity the
other day. When I issue a query using bind variables that are defined as
number,decimal,or numeric the query doesn't return the expected row, but
when I issue the exact same query using a hard coded value instead of the
bind variable the query returns the expected row. I've also tried this
with character based variables and the queries work fine using both bind
variable and hard-coded values. Is just seems to the an issue with bind
variables with numeric definitions? Anyone have any ideas or suggestions.
Thanks
Harry
Here's an example of what I am doing Here's a basic query from Oracle
using HS to select all the rows.
SQL> desc "harry"@mssql
Name Null? Type
----------------------------------------- --------
----------------------------
id_name VARCHAR2(10)
name_details VARCHAR2(100)
money_test NUMBER(19,4)
decimal_test NUMBER(18,4)
number_test NUMBER(18,4)
SQL> select * from "harry"@mssql;
id_name name_details money_test decimal_test number_test
-------------- --------------- ---------- ------------ -----------
HFL test 12345.1234 67890.1234 56789.9876
HFL2 test 2 12345.67 10203.987 12345.1234
HFL3 test3 11111.11 22222.3334 33333.7777
This query works
SQL> set serveroutput on;
SQL>
SQL> declare
2
3 v_numbertest number(19,4);
4 v_theone varchar(10);
5 v_out_number number(19,4);
6
7 begin
8
9 v_out_number :='';
10 v_numbertest :=11111.11;
11 v_theone :='HFL3';
12
13
14 dbms_output.put_line ('Before 1 Select v_numbertest '||v_numbertest
|| ' v_theone = '|| v_theone );
15
16 select max("decimal_test") into v_out_number from "harry"@mssql where
"id_name" = v_theone;
17
18 dbms_output.put_line ('After 1 Select v_out_number '||v_out_number );
19
20 end;
21
22 /
Before 1 Select v_numbertest 11111.11 v_theone = HFL3
After 1 Select v_out_number 22222.3334
PL/SQL procedure successfully completed.
This query doesn't work and returns zero rows
SQL> declare
2
3 v_numbertest number(19,4);
4 v_theone varchar(10);
5 v_out_number number(19,4);
6
7 begin
8
9 v_out_number :='';
10 v_numbertest :=11111.11;
11 v_theone :='HFL3';
12
13
14 dbms_output.put_line ('Before 1 Select v_numbertest '||v_numbertest
|| ' v_theone = '|| v_theone );
15
16 select max("decimal_test") into v_out_number from "harry"@mssql where
"money_test" = v_numbertest;
17
18 dbms_output.put_line ('After 1 Select v_out_number '||v_out_number );
19
20
21 end;
22
23 /
Before 1 Select v_numbertest 11111.11 v_theone = HFL3
After 1 Select v_out_number
PL/SQL procedure successfully completed.
SQL>
This query works using a hard coded value instead of the bind variable
SQL> set serveroutput on;
SQL>
SQL> declare
2
3 v_numbertest number(19,4);
4 v_theone varchar(10);
5 v_out_number number(19,4);
6
7 begin
8
9 v_out_number :='';
10 v_numbertest :=11111.11;
11 v_theone :='HFL3';
12
13
14 dbms_output.put_line ('Before 1 Select v_numbertest '||v_numbertest
|| ' v_theone = '|| v_theone );
15
16 select max("decimal_test") into v_out_number from "harry"@mssql where
"money_test" = 11111.11;
17
18 dbms_output.put_line ('After 2 Select v_out_number '||v_out_number );
19
20 end;
21
22 /
Before 1 Select v_numbertest 11111.11 v_theone = HFL3
After 2 Select v_out_number 22222.3334
PL/SQL procedure successfully completed.
Harry F. Larsick ¨ Jones Day ¨ 901 Lakeside Ave ¨ Cleveland, Ohio 44114
Firm Technology Support Services ¨ Business Information Services
E-mail: [Email address protected] ¨ Phone: (216) 586-1136 ¨ Fax: (216)
579-0212
==========
This e-mail (including any attachments) may contain information that is
private, confidential, or protected by attorney-client or other privilege.
If you received this e-mail in error, please delete it from your system
without copying it and notify sender by reply e-mail, so that our records
can be corrected.
==========
MS Sql Server LazyDBA home page