Re: Getting IP address of client

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 12 Oct 1998 01:03:30 GMT
Message-ID: <362753b6.5913893@192.86.155.100>

A copy of this was sent to "Mike Layng" <mlayng_at_micomine.com.au> (if that email address didn't require changing) On Mon, 12 Oct 1998 08:32:27 +0800, you wrote:

>Hi Russ,
>
>Thanks for the reply.
>
>The code I tried was almost identical to what you suggest (i think i had
>varchar2(20) instead of varchar2(15)), but I still get that exception.
>
>Can anyone tell me if exception ORA06502 would fire if the get_cgi_env was
>returning NULL?
>

No, it'll give you that from sqlplus (or any other environment other the the PL/SQL cartridge). for example:

SQL> declare
  2 tmp varchar2(15);
  3 begin
  4 tmp := owa_util.get_cgi_env( 'remote_addr' );   5 end;
  6 /
declare
*
ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "OWS2.OWA_UTIL", line 160
ORA-06512: at line 4

You see, owa_util is initialized by the webserver -- if you are not using the webserver then owa_util has NULLS all over it and it will attempt to:

....

for i in 1 .. NUM_CGI_VARS loop
...

but num_cgi_vars is NULL and that throws the exception...

If you are not using the webserver you can use the AUDIT_TRAIL feature to get this info in 7.2 and up. Here is a sample:

IP package
AUDIT_TRAIL = TRUE must be set in the init.ora file
AUDIT CONNECT; must have been executed by DBA for this to work --
the ip package has two external interfaces:
address; 'pure' pl/sql function returning ip address
into_v$session; moves ip address into v$session
so you can "select client_info from v$session" --
USAGE: --
Most commonly you would use the address function directly.
For performance reasons I will suggest it is always used in views
in the following manner: --
create view my_view
as
select *
from my_table
where exists ( select NULL
from allowed_ip_addresses
where ip_address = ( SELECT IP.ADDRESS FROM DUAL )
) --
create view my_view
as
select * from my_other_table
where ip_address = ( SELECT IP.ADDRESS FROM DUAL ) --
and so on. Basically I am suggesting the IP.ADDRESS be selected
from DUAL. This will cause the PL/SQL routine IP.ADDRESS to be
called ONCE per query, instead of once PER ROW per query. The
overhead associated with calling pl/sql for every row will be
too large to use this successfully if you don't employ the above
'trick'. --
In short, instead of saying
x = ip.address
always say
x = ( select ip.address from dual ).
Instead of saying
ip.address in ( select x from T .... )
always say
where exists ( select NULL from T ....
AND x = ( select ip.address from DUAL ) --

create or replace package ip
as

pragma restrict_references( ip, wnds, wnps, rnps );

function address return varchar2;
pragma restrict_references( address, wnds, wnps );

procedure into_v$session;
end ip;
/
show errors

create or replace package body ip
as

theAddress varchar2(2000);

    function address return varchar2
    is
    begin

return theAddress;
end;

    procedure into_v$session
    is
    begin

dbms_application_info.set_client_info( theAddress ); end;
begin

select comment_text

      into theAddress
      from user_audit_trail
     where sessionid = userenv('SESSIONID');

    declare
        n   number;
    begin
        n := instr( theAddress, '(HOST=' );
        theAddress := substr( theAddress, n+6 );
        n := instr( theAddress, ')' );
        theAddress := substr( theAddress, 1, n-1 );

end;
end ip;
/
show errors

grant execute on ip to public
/
create public synonym ip for ip
/

>Thanks,
>Mike.
>
>
>R Fray wrote in message <361e069d.14705422_at_news.u-net.com>...
>>
>>Hi, If you define a variable as :
>> vipaddress varchar2(15);
>>
>>You should then be able to insert the IP address in to that variable
>>using :
>>
>> vipaddress := owa_util.get_cgi_env('remote_addr');
>>
>>This is using Oracle Web Server & PL/SQL but the format is correct.
>>You are getting a type-mismatch, what are you trying to insert the IP
>>into? Make sure it's a character type.
>>
>>Russ.
>>
>
>
>mlayng_at_micromine.com.au
>
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sun Oct 11 1998 - 20:03:30 CDT