| temporary table ?? [message #38900] |
Fri, 24 May 2002 07:44  |
vipin
Messages: 40
Registered: May 2002
|
Member |
|
|
Hello guys,
Is it possible to define a temporary table( collection) which is accessible across procedures/packages(session level) in oracle ?
Thanks in Advance.
Vipin.
|
|
|
|
| Re: temporary table ?? [message #38901 is a reply to message #38900] |
Fri, 24 May 2002 08:14  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
If you define a variable (of any type) in a package spec, that variable will be visible to any procs/packages and the value is session-specific.
You can also define a global temporary table that can contain data visible only to the current session.
|
|
|
|
| Re: temporary table ?? [message #38911 is a reply to message #38900] |
Mon, 27 May 2002 02:05 |
vipin
Messages: 40
Registered: May 2002
|
Member |
|
|
Hello ,
Thanks for the reply. I am looking for a way to effectively log error information into a table or variable. The error log information is a collection of messages collected through calls to several procedures and inserted into the error table. The issue is when there is rollback, I am not sure how to log them. If you have an alternative solution please let me know.
Thanks in Advance.
vipin
|
|
|
|
| Re: temporary table ?? [message #38915 is a reply to message #38900] |
Mon, 27 May 2002 09:33 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
The best solution to this is to define an autonomous transaction. You write a procedure and declare this special pragma, and the DML performed in the procedure is autonomous, or separate, from your main transaction. This means that even if your main transaction has to rollback, you can still capture the error messages.
create or replace procedure log_errors(
p_error_message in varchar2)
is
pragma autonomous_transaction;
begin
insert into error_log values (p_error_message);
commit;
end;
See here for a discussion on autonomous transactions:
http://osi.oracle.com/~tkyte/autonomous/index.html
|
|
|
|
| Re: temporary table ?? [message #38920 is a reply to message #38915] |
Tue, 28 May 2002 00:00 |
vipin
Messages: 40
Registered: May 2002
|
Member |
|
|
Hello Todd,
Thanks for your reply. I have tried autonomous before. But, my application sometimes uses database link. Any call to database link followed by an autonomous call fails. ora-600 error is reported. It works fine the other way around as oracle does not start a
distributed transaction. So with great disappointment, i have shelfed that approach.
Rather, i have a solution for rollback situations which is not elegant . select the inserted errolog rows into a array - do a rollback - and insert it back with a commit.
pls, keep me posted if they is an alternative.
thanks
vipin.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
