about forms

Posted on 2003-03-26
Medium Priority
Last Modified: 2013-12-12
hi experts,
i have a secretary application i have made and i want to make the following.
there are a person enter the job tasks to the database and one of the fields is the person who must do thie job. what i want that if a task enterd to the table an alert appear to the user who must do the work such as plenking on the icon of the application if it was minimize on th tool bar or any thing else

so please can any body tell me how can i do it

thanx alot
Question by:loay
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
LVL 22

Expert Comment

by:Helena Marková
ID: 8209128
One option is sending message to the user. There is dbms_pipe package in Oracle database there. You can see a description of it in a dbmspipe.sql file on your PC.


Author Comment

ID: 8209282
then can you help me more and send me how can i use it and if there are an option that allow me not to send to a database user but to aa computer name coz the i have many emoplyees connects on the same user but i have another sequrity system contolling the users
LVL 22

Expert Comment

by:Helena Marková
ID: 8210019
Here is an article from Metalink (Note:116668.1):

Definitions and Examples of how Oracle Pipes works

This bulletin will explain how the Oracle Pipes work, how to use  them and will show examples of it.
This feature will be useful for programmers in the development of their applications and for DBAs in the development of debugging tools. This article does not include any troubleshooting. Oracle Pipes was introduced in Oracle7. Some of the procedures, functions and views might change between versions, specially in earlier versions of Oracle7.


The Oracle Pipe is a mean of communication between sessions in the same instance of a database. Messages are put inside the pipe by one session and other takes the message out. The type of the messages can be numbers, text or dates or combinations of these. Its characteristics are:
- Is asynchronous, meaning that the sessions don't have to be in sync.
- Transaction independent, that is, it does not need, depend, or change it's behavior because of a commit or rollback.
- It uses a temporary buffer to hold the message before sending it and to receive it.
- It resides in the Shared Pool inside the System Global Area (SGA) and it's deallocated after it is emptied if not reused.
- If the session that extracts the message from the pipe is connected thru Multi-Threaded Server (MTS) then the shared server serving the petition will be 'slaved' and remain idle until the message arrives.
- Can either be public or private.
- Multiple sessions can put and extract messages from it.

Let's elaborate,

By asynchronous we mean that the sessions can put messages without waiting for and answer of the receptor and receptors can define how long they'll wait for a message to arrive through the pipe up to 1000 days. Because a pipe is Transaction independent it can be done during the lifespan of a transaction without disturbing it, this also means that what ever you put in the pipe its going to be available immediately to whom ever is waiting on it and there is no way to "rollback" it.
The next three characteristics explain how Oracle manages that pipe. Before creating the pipe, the session must put all the messages in a private buffer and then all are send on their way through the pipe. Also when the messages are received they are placed on the private buffer and then the session can read of them. The current size of the buffer is 4096 bytes and each type a message is placed on the buffer 4 additional bytes are added for bookkeeping.

A pipe once emptied, its memory became a candidate for reuse in the shared pool but if the pipe is used again it will use the same memory it used before. One aspect that is influence by the pipe is the way the server processes behave when waiting for a pipe message. When a process is waiting on the message it will stop there until it either timeout or a message is received. This will have a repercussion on a MTS environment because the shared server will become 'slaved', and it will not be a shared among the rest of the sessions until the complete session's request is finished, that is, not only the receipt of the pipe but the rest of the program that included the request. if the message is already in the pipe then there is no wait and the shared server continues its operations normally.

When a pipe is public, it means everybody that knows of its existence can receive or send messages to it. When is private only the session with the same user that created the pipe can access it. The only way other users can access this pipe is through packages, procedures or functions owned by the creating user and having the invokers rights set to definer.

The last characteristic will be better explain with an example: Let's assume we have sessions A, B and C.

Case 1
Let's say A and B send messages through the pipe and C is waiting on them. If A sends a message first and then B sends another message, C will receive A's message first and then B's message

Case 2
Now, Let's put A and B to receive and C to send. A will wait for message first and B will wait for a message after A did it. When C sends a message, B is the one who receives it but A will remain waiting. If C sends another message now A receives the message.

This shows that the queue for waiting a pipe behaves in a "stack" manner. Which ever waits first receives the message last and whichever waits last will receives it first.

The DBMS_PIPE package
Here we will briefly show and explain some of the procedures and functions of the package.

See the $ORACLE_HOME/rdbms/admin dbmspipe.sql script for a complete reference on the procedure and functions of the package.

procedure pack_message(item in varchar2 character set any_cs);
                      (item in number);
                      (item in date);
procedure pack_message_raw(item in raw);
procedure pack_message_rowid(item in rowid);

These procedures put the messages inside the private buffer. They can be executed multiple times to put several messages in the buffer (up to 4096 bytes less bookkeeping) and the messages can be of different types.

function send_message(pipename in varchar2,
        timeout in integer default maxwait,
        maxpipesize in integer default 8192)

This function sends the messages stored in the private buffer thru the pipe. Returns error number or 0 if successful. Pipename cannot be longer than 128 characters and should not have the prefix 'ORA$'. timeout is how long the process is willing to wait to put the message in the pipe, usually is instantaneous but heavy concurrency can influence. maxpipesize defines how big the pipe will be. If the space the messages take are bigger than the maxpipesize then the session sending the messages will block until enough space is available in the pipe or a timeout is signaled.

function receive_message(pipename in varchar2,
            timeout in integer default maxwait)
    return integer;

This function waits for messages to appear in the pipe up to the value of timeout in seconds and place them on the private buffer. Returns error number or 0 if successful.

procedure unpack_message(item out varchar2 character set any_cs);
                        (item out number);
                        (item out date);
procedure unpack_message_raw(item out raw);
procedure unpack_message_rowid(item out rowid);

These procedures retrieve the messages from the buffer and place them in variables according to type.

function next_item_type return integer;

If the type and ammount of information that is going to be received from the pipe is unknown then this function came in handy. It returns the type of message to be next unpacked following this table:
   0    no more items
   9    varchar2
   6    number
  11    rowid
  12    date
  23    raw

function unique_session_name return varchar2;

This function will give each session a unique identifier. If several users connect with the same username, each will have their own session identified. It will be up to 30 chars long.

procedure purge(pipename in varchar2);

When a message is send to a pipe, if the pipe does not exist then it's created in the shared pool and the message place on it.When the pipe is emptied then it's a candidate to be removed from the Shared pool but it's not removed immediately. A way to empty the pipe without reading all it's contents is by using this procedure.

function remove_pipe(pipename in varchar2)

If it at some point the pipe's memory needs to be reclaimed then this function can be used to free that memory byt removing the pipe.If the pipe is public anybody can do it but if it's private then only a user connected with the same username as the creator of the pipe can remove it.


This view allows us to see what pipes currently exist in the shared pool,to who they belong to and how much space they are using.

Name        Type

NAME        VARCHAR2(1000)
TYPE        VARCHAR2(7)

The following will be used in the examples below.

User(s) must have grant execute on dbms_pipe,dbms_output. Up to four different sqlplus are needed to see all the examples.The examples need different session but they can be connected with the same user.

This script is provided for educational purposes only. It is NOT supported by Oracle World Wide Technical Support.  
The script has been tested and appears to work as intended.However,you should always test any script before relying on it.

Due to differences in the way text editors, email packages and operating systems handle text formatting (spaces,tabs and carriage returns),this script may not be in an executable state when you first receive it.
Check over the script to ensure that errors of this type are corrected.

create or replace procedure send_pipe as
 result number;
 my_pipe varchar2(30);
 user user_users%rowtype;
 select * into user from user_users;
-- this message will have 3 parts. A varchar2, a number and a date.

create or replace procedure read_pipe as
 result number;
 v varchar2(4000);
 d date;
 n number;
 w raw(32767);
 r rowid;
 his_pipe varchar2(30);
 type_not_handled exception;
 dbms_output.put_line('Pipe :'||his_pipe);
 while result<>0 loop
  dbms_output.put_line('type =>'||to_char(result));
  if result=9 then -- varchar2
  elsif result=6 then -- number
  elsif result=12 then -- date
  elsif result=11 then -- rowid
  elsif result=23 then -- raw
   raise type_not_handled;
  end if;
 end loop;
 when type_not_handled then
  dbms_output.put_line('Type '||to_char(result)||' not handled');
 when others then
  dbms_output.put_line('error: '||to_char(result));

create or replace procedure remove_all_pipes is
 result number;
for i in (select * from v$db_pipes) loop
 dbms_output.put('Pipe '||i.name);
   dbms_output.put_line(' removed.');
   when others then
   dbms_output.put_line(' not removed.');
end loop;

drop table any_table;
create table any_table(dummy varchar2(30));

create or replace trigger audit_any_table after insert on any_table for each row
 name varchar2(30);
 result number;
 usid varchar2(30);
 select username into name from user_users;

Note: remember to issue "set serveroutput on" at the beginning of the session.Otherwise the data will not be printed to the screen.

Case 1
Session: A
Explanation: The same user can read from it's own pipe. Two pipes are created. One is 'waiter' the other has the name of the unique_session_name.

1) A: exec send_pipe
2) A: exec read_pipe
3) A: Select * from v$db_pipes;

Case 2
Sessions: A & B
Explanation: A sends info, B receives it. No new pipes.

1) A: exec send_pipe
2) B: exec read_pipe
3) A: Select * from v$db_pipes;

Case 3
Sessions: A & B
Explanation: A will hold until a message is received. A new pipe is created.

1) A: exec read_pipe
2) B: exec send_pipe
3) A: Select * from v$db_pipes;

Case 4
Sessions: A & B
Notes: A must have the table any_table and the trigger audit_any_table in its schema.
Explanation: B will hold until a message from the trigger is recieved which is sent when A inserts a new value. Commit or rollback doesn't have any effect, the message was already been received. No new pipes.

1) B: exec read_pipe
2) A: insert into any_table values('New Value');
3) A: try both commit and rollback.

Case 5
Sessions: A, B & C
Explanation: On step 3, B will receive the message.On Step 4 A receives it. A new pipe is created.

1) A: exec read_pipe
2) B: exec read_pipe
3) C: exec send_pipe
4) C: exec send_pipe

Case 6
Sessions: A, B & C
Explanation: On step 3, B will receive the message. On Step 5 B again receives it. On step 6 A receives it.

1) A: exec read_pipe
2) B: exec read_pipe
3) C: exec send_pipe
4) B: exec read_pipe
5) C: exec send_pipe
6) C: exec send_pipe

Case 7
Sessions: A, B & C
Explanation: On Step 3, A receives C's message. On Step 4 A received B's Message.

1) C,B: exec dbms_output.put_line(dbms_pipe.unique_session_name)
2) C: exec send_pipe
3) B: exec send_pipe
4) A: exec read_pipe
5) A: exec read_pipe

Case 8
Sessions: A,B,C & D
Explanation: On Step 4, B receives D' message. On step 5, A receives C's message. A new pipe is created.

1) C,D: exec dbms_output.put_line(dbms_pipe.unique_session_name)
2) D: exec send_pipe
3) C: exec send_pipe
4) B: exec read_pipe
5) A: exec read_pipe

Case 9
Session: Any
Explanation: Cleaning up.

1) exec remove_all_pipes
2) select * from v$db_pipes;
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.


Expert Comment

ID: 8210515
 The pipes are ok, but my understanding is that you have to have a mechanism to read the pipes in the forms. This makes the things the same if you support a communication table and insert messages into it (based on user id).
  In the forms, especially if you use only one form, you can create a timer to fire on each, lets say 1 min. When timer fire it will fire WHEN-TIMER-EXPIRED trigger in forms. In this trigger you can put together some code to check your communication table and to display a pop-up message on the screen.
  You can see example how to use timers if go to the online help in Oracle Forms and search for CREATE_TIMER built in (for example)


Expert Comment

ID: 8215385
The technique(dbms_pipe,normal tables,dbms_alerts,dbms_aq) used to transmit the notification is just the smaller of the problems!

The main problem will be that you cannot flash the icon of a minimized screen and also it will get really nasty to have a running system then to interrupt any pending work and do bring up this record, etc ...

also another nasty point of pipes is that they have only an allocated size of memory and if no listener process reads out data (because the assigned person doesn't have the application open!) the pipe will overrun and the writing process will be blocked until the reader makes place by removing messages!

what about redesigning your approach !? - maybe the easiest is just to send an email (UTL_SMTP) to the person, notifying it that there is something for her to do! - maybe thats the more efficient and easier way then to try raping Oracle Forms for something it's not build for!

Cheers, Stefan

Author Comment

ID: 8218128
ok stemu2000 how can i do this thing.

Expert Comment

ID: 8258639
stemu2000 seems to have scarpered so I'll help:

PROCEDURE send_email
  (p_sender IN VARCHAR2
  ,p_recipient IN VARCHAR2
  ,p_subject IN VARCHAR2
  ,p_message IN VARCHAR2) IS
   c_mailhost CONSTANT VARCHAR2(30) := 'your.mail.server';
     c_crlf CONSTANT VARCHAR2(2) := CHR(13) || CHR(10);
   v_conn utl_smtp.connection;
   -- start setting up the email    
   v_conn := utl_smtp.open_connection(c_mailhost, 25);
   utl_smtp.helo(v_conn, c_mailhost);
   -- identify the sender
   utl_smtp.mail(v_conn, p_sender);
   -- identify the recipient
   utl_smtp.rcpt(v_conn, p_recipient);
   -- build the message
     'Date: '    || TO_CHAR( SYSDATE, 'dd Mon yyyy hh24:mi:ss' ) || c_crlf
      || 'From: '    || p_sender || c_crlf
      || 'Subject: ' || p_subject || c_crlf
      || 'To: '      || p_recipient || c_crlf || c_crlf);
   utl_smtp.write_data(v_conn, p_message || c_crlf);
   utl_smtp.write_data(v_conn, '.' || c_crlf);
   utl_smtp.close_data(v_conn); -- this will cause the email to be sent
   -- end the mail connection
END send_email;


Expert Comment

ID: 8258701
Thanks Jeff!

I am not anymore so much into answering questions on this forum since they renamed it and killed the stream of new questions/points ;-/

Cheers, Stefan

Expert Comment

ID: 8258830
No problem Stefan.

I think it's always a big problem for newbies asking questions, they never know where to ask the question (it appears some don't even think about where to ask it) because they might not have enough technical knowledge to know what field is required.

I usually just frequent the Databases and Databases/Oracle TAs. That's where most Oracle questions seem to get asked.

The Databases TA is the most annoying, however; only one in a thousand questions is actually not related to any particular database, or related to a database which doesn't have its own TA; therefore, it's up to the experts to try to guess what DBMS the questioner is using, if they don't specify up front.

I wonder what "Oracle/Product Info" is supposed to be for?

LVL 22

Expert Comment

by:Helena Marková
ID: 10154493
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

PAQ/No Refund.

Please leave any comments here within the next seven days.


EE Cleanup Volunteer

Accepted Solution

Computer101 earned 0 total points
ID: 10214635
PAQed - no points refunded (of 100)

E-E Admin

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question