Home » SQL & PL/SQL » SQL & PL/SQL » global variable multiple session (oracle 10g)
global variable multiple session [message #400112] Sun, 26 April 2009 04:45 Go to next message
mylraj
Messages: 16
Registered: January 2009
Location: Manama
Junior Member
Hi,

i have a package called p_tcp with the with the following procedure

p_open
p_close
p_read

user 1 will call p_tcp.p_open inside this i open the connection

user 2 will call p_tcp.p_read i read the message, for which i have already had the connection was established by user 1

user 2 will close the connection , which was already opened by user 1

How to achieve this ? can someone please help
Re: global variable multiple session [message #400115 is a reply to message #400112] Sun, 26 April 2009 04:59 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
What have you tried?
Re: global variable multiple session [message #400117 is a reply to message #400115] Sun, 26 April 2009 05:15 Go to previous messageGo to next message
mylraj
Messages: 16
Registered: January 2009
Location: Manama
Junior Member

we have requirement to use the socket level program
i am using UTL_TCP.in my package

The scenario is this

user 1 will open the connection.

user 2 will read the message on the available port


i want to declare the below variable as global

v_Connection UTL_TCP.CONNECTION;

so that when user 1 will open the connection using the above variable in package p_tcp.p_open( )

UTL_TCP.OPEN_CONNECTION(remote_host => v_IP, remote_port => v_Port, tx_timeout => v_TimeOut);


user 2 will check whether the connection is available, if it is available, he will use the same connection and read the message

rc := utl_tcp.read_line(v_Connection, value, true);



Re: global variable multiple session [message #400121 is a reply to message #400117] Sun, 26 April 2009 05:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Package variables are private to a session.

Regards
Michel
Re: global variable multiple session [message #400124 is a reply to message #400121] Sun, 26 April 2009 05:38 Go to previous messageGo to next message
mylraj
Messages: 16
Registered: January 2009
Location: Manama
Junior Member
how to achieve this ? is there any trick is there to declare a variable and used by multiple sessions




Re: global variable multiple session [message #400127 is a reply to message #400124] Sun, 26 April 2009 06:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for global context variable.

Regards
Michel
Re: global variable multiple session [message #400445 is a reply to message #400127] Tue, 28 April 2009 04:26 Go to previous messageGo to next message
mylraj
Messages: 16
Registered: January 2009
Location: Manama
Junior Member
hi,

i am using the global context. But i can not use the UTL_TCP as a variable in DBMS_SESSION.SET_CONTEXT.


DBMS_SESSION.SET_CONTEXT (
namespace VARCHAR2,
attribute VARCHAR2,
value VARCHAR2,
username VARCHAR2,
client_id VARCHAR2 );


attribute and the value is a character value.

please find the attached package for your info.

  • Attachment: tcp_pkg.sql
    (Size: 1.04KB, Downloaded 164 times)
Re: global variable multiple session [message #400448 is a reply to message #400445] Tue, 28 April 2009 04:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But i can not use the UTL_TCP as a variable in DBMS_SESSION.SET_CONTEXT.

I don't know the reason why you can't do it as you didn't explain your problem.

Anyway, I think tcp connection is private to a session.

Regards
Michel
Re: global variable multiple session [message #400452 is a reply to message #400448] Tue, 28 April 2009 04:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm pretty sure you're right there Michel, and calls to UTL_TCP are like UTL_SMTP - private to your session.

What you'd need to do would be to set up a daemon process that any user could pass/recieve information from, that would handle the UTL_TCP calls.

You could do this with a DBMS_JOB process and a table to handle the interface:

User 1 adds a row to the table TCP_INTERFACE that says 'Open connection to address x';

Next time the Dbms_Job process runs, it gets this message, opens the connection, and writes a message to the table with the connection handle.

User 2 can now add a row to the table saying 'Read from connection x'

When the Dbms_Job process runs, it gets this message, performs the required processing and writes the results to the table.


You could do something similar with a package that loops round, and waits on DBMS_ALERT to tell it when to activate.
Re: global variable multiple session [message #400474 is a reply to message #400452] Tue, 28 April 2009 05:52 Go to previous messageGo to next message
mylraj
Messages: 16
Registered: January 2009
Location: Manama
Junior Member

hi thanks for the reply, i will try to use DBMS_JOB process
Re: global variable multiple session [message #400481 is a reply to message #400452] Tue, 28 April 2009 06:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Daemon process.. That sounds like AQ, instead of a polling DBMS_JOB?
Re: global variable multiple session [message #400483 is a reply to message #400481] Tue, 28 April 2009 06:35 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yes, but AQ is (IMHO) a finicky pain in the neck.
Previous Topic: prob
Next Topic: Wroking with DENSE_RANK function
Goto Forum:
  


Current Time: Tue Dec 06 06:42:54 CST 2016

Total time taken to generate the page: 0.09138 seconds