Hi Everyone
I am running a Oracle 9.2.0.6 EE on Solaris 8, that is 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 for using Discoverer to run reports 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 sugestions.?????
Thanks again
Harry
Here's some examples below and output below
Here the table ddl from SQL-Server
CREATE TABLE [dbo].[harry] (
[id_name] [varchar] (10) COLLATE Latin1_General_CS_AS NULL ,
[name_details] [varchar] (100) COLLATE Latin1_General_CS_AS NULL ,
[money_test] [money] NULL ,
[decimal_test] [decimal](18, 4) NULL ,
[number_test] [numeric](18, 4) NULL
) ON [PRIMARY]
GO
Here's a bais 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_theon
e );
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.
SQL>
This query doesn't work and returns zero rows
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_theon
e );
15
16 select max("decimal_test") into v_out_number from "harry"@mssql where
"money_test" = v_numberte
st;
17
18 dbms_output.put_line ('After 1 Select v_out_number '||v_out_number );
19
20
21
22
23
24 end;
25
26 /
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_theon
e );
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.
SQL>
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