Home » SQL & PL/SQL » SQL & PL/SQL » Calls over gateways
Calls over gateways [message #190642] Thu, 31 August 2006 09:10 Go to next message
falken666
Messages: 4
Registered: August 2006
Location: Sweden
Junior Member
Hi have a problem using oracle 9!

When i make the following calls with an argument over a gateway the answers is like follow.

select mont_unit_util_api.get_xxx_desc('test') from dual;
returns: test (OK!)
When i make another call

select mont_unit_util_api.get_xxx_desc('') from dual;
Or
select mont_unit_util_api.get_xxx_desc('null') from dual;

The result is also test?!?! (WRONG!?!)

It seems like the answer is the latest answer with a none null parameter is returning when I use '' Or null as argument?

Is this a bug or what?
Anyone whos has any suggests?

//Jocke

Re: Calls over gateways [message #190645 is a reply to message #190642] Thu, 31 August 2006 09:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd be very tempted to suspect a bug in the package procedure mont_unit_util_api.get_xxx_desc. This is largely because you try passing in the string 'null'. This is nothing special in Oracle, being simply a 4 chr text string.

Repeat these tests from SQL*Plus and see what happens.
Re: Calls over gateways [message #190647 is a reply to message #190645] Thu, 31 August 2006 09:22 Go to previous messageGo to next message
falken666
Messages: 4
Registered: August 2006
Location: Sweden
Junior Member
I get the same result from sql plus

And it doens't mather which package procedure I use have alot of them. When the argument is null or '' it returns the last none null argument?!?

[Updated on: Thu, 31 August 2006 09:26]

Report message to a moderator

Re: Calls over gateways [message #190648 is a reply to message #190647] Thu, 31 August 2006 09:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Compile this onto the Db and try the same test.

CREATE OR REPLACE PACKAGE orafaq_test AS
  FUNCTION ret_string  (p_string  in  varchar2) return varchar2;
END orafaq_test;
/

CREATE OR REPLACE PACKAGE BODY orafaq_test AS
  FUNCTION ret_string  (p_string  in  varchar2) return varchar2 IS
  BEGIN
    return nvl(p_string,'**null**');
  END ret_string;
END orafaq_test;
/

SQL> select orafaq_test.ret_string('Non null value') from dual;

ORAFAQ_TEST.RET_STRING('NONNULLVALUE')
----------------------------------------------------------------
Non null value

SQL> select  orafaq_test.ret_string('') from dual;

ORAFAQ_TEST.RET_STRING('')
----------------------------------------------------------------
**null**
icon14.gif  Re: Calls over gateways [message #190650 is a reply to message #190648] Thu, 31 August 2006 09:41 Go to previous messageGo to next message
falken666
Messages: 4
Registered: August 2006
Location: Sweden
Junior Member
Thanks!
I'll give it a Try tomorrow.
Now i have a meeting and then my day ends.

//Jocke
Re: Calls over gateways [message #190655 is a reply to message #190650] Thu, 31 August 2006 09:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I suspect the api package as well. Might very well be that a global package variable does not get initialized correctly. Hard to say without code..
Re: Calls over gateways [message #190656 is a reply to message #190655] Thu, 31 August 2006 09:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That would be my guess too.
We shall see.
Re: Calls over gateways [message #190736 is a reply to message #190656] Fri, 01 September 2006 01:29 Go to previous message
falken666
Messages: 4
Registered: August 2006
Location: Sweden
Junior Member
Thanks for your answers.
But maybe this isn't the right forum for my question.

After consulting my college who had the problem.
I turned up that I have misunderstand the problem. From Oracle to Oracle over a gateway it isn't any problem.
The problem was from an oracle to an RDB database over a gateway.
So sorry if I have mislead you.

And thanks again.
Previous Topic: flat files......
Next Topic: compute sum of balances based on year
Goto Forum:
  


Current Time: Sat Dec 03 13:46:24 CST 2016

Total time taken to generate the page: 0.10879 seconds