| ORACLE global Temp Table / Ref cursor problem [message #25385] |
Mon, 21 April 2003 14:50  |
Sam Josh
Messages: 15
Registered: January 2003
|
Junior Member |
|
|
Hi gurus,
I am facing a ADO problem that involves ORACLE Refcursor and Global Temporary table. I am calling a Procedure from a VB application
using Oracle provided ADO. The Oracle store procedure populates a global temporary table (which is created as ON COMMIT DELETE ROWS)
and then immediately select all the rows from the table and return it via Ref Cursor.
The problem here is at the select the Global Temp Table is getting Trucated and I am getting error ORA-08103: object no longer exist.
Any help is highly appreciated. Here is the simplified version of ORACLE & VB code (user: scott/tiger) .
Thanks in advance
-------------------------------------------------------
CREATE OR REPLACE PACKAGE GlobalPkg AS
TYPE RC IS REF CURSOR;
END GlobalPkg;
-------------------------------------------------------
CREATE OR REPLACE PROCEDURE Test
(
rc1 IN OUT GlobalPKG.RC
)
AS
Begin
insert into gt_test
select ename
from emp
where deptno = 20;
open rc1 for
select *
from gt_test;
End Test;
-------------------------------------------------------
create global temporary table gt_test
( ename varchar2(10) )
on commit delete rows;
-------------------------------------------------------
Private Sub cmdADOProc_Click()
Dim objConn As New ADODB.Connection
Dim objCmd As ADODB.Command
Dim objRS As ADODB.Recordset
objConn.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=MYDELL;User ID=scott;Password=tiger;PLSQLRSet=1"
objConn.Open
Set objCmd = CreateObject("ADODB.Command") ' OR Set objCmd = New ADODB.Command
With objCmd
.ActiveConnection = objConn
.CommandText = "test"
.CommandType = adCmdStoredProc
End With
Set objRS = objCmd.Execute
Set objCmd = Nothing
Set objConn = Nothing
End Sub
|
|
|
|
| Re: ORACLE global Temp Table / Ref cursor problem [message #25386 is a reply to message #25385] |
Mon, 21 April 2003 15:29  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Sam, I was unable to recreate this error with the given code. The result set was returned successfully. Granted, I am not testing in VB, but I had no problems in either SQL*Plus or Delphi.
Are you sure there isn't a truncate of this GTT buried (incorrectly) in a call to a subroutine somewhere? That is the only way I could recreate this error but it meant artificially changing the posted code.
Do you absolutely need this GTT? Is your pre-select logic so complex that you believe you need a temp table to accomplish your goal? I only ask because at least half the time people think they need a temp table - the same result can be accomplished in a single select without all the temp table overhead.
|
|
|
|
| Thanks Todd - ORACLE global Temp Table / Ref cursor problem [message #25393 is a reply to message #25385] |
Tue, 22 April 2003 11:00 |
Sam Josh
Messages: 15
Registered: January 2003
|
Junior Member |
|
|
Thanks Todd, the Orginal procedure is very complex and the Temp Table seems desirable. I will appreciate
if you can let me know how you tested the refcursor in
SQL plus, may be then I can find out what's wrong with
ADO. Also I have one more question, what will be the
performance impact if I include the select portion from
the INSERT of GT table into the FROM clause of my main query.
Thanks once again.
Josh
|
|
|
|
| Re: ORACLE global Temp Table / Ref cursor problem [message #25396 is a reply to message #25385] |
Tue, 22 April 2003 17:30 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
To use a ref cursor in SQL*Plus, you define a variable of type refcursor, execute the procedure, and then print the contents of the cursor.
sql>create or replace package pkg_rc
2 is
3 type rc is ref cursor;
4
5 procedure p1(p_rc out rc);
6 end;
7 /
Package created.
sql>create or replace package body pkg_rc
2 is
3 procedure p1(p_rc out rc)
4 is
5 begin
6 open p_rc for
7 select object_name
8 from all_objects
9 where rownum <= 3;
10 end;
11 end;
12 /
Package body created.
sql>var rc refcursor
sql>exec pkg_rc.p1(:rc)
PL/SQL procedure successfully completed.
sql>print rc
OBJECT_NAME
------------------------------
/1001a851_ConstantDefImpl
/1005bd30_LnkdConstant
/10076b23_OraCustomDatumClosur
3 rows selected.
I don't quite understand the second question you have. It would be helpful if you could provide a simple example.
|
|
|
|
| Re: ORACLE global Temp Table / Ref cursor problem [message #25418 is a reply to message #25396] |
Wed, 23 April 2003 09:01 |
Sam Josh
Messages: 15
Registered: January 2003
|
Junior Member |
|
|
Todd,
In the previous posting I mentioned about a procedure that is inserting rows in a Global Temp table and than
uses that table in another select clause like this:
CREATE OR REPLACE PROCEDURE Test
(
rc1 IN OUT GlobalPKG.RC
)
AS
Begin
insert into gt_test
select ename
from emp
where deptno = 20;
open rc1 for
select *
from gt_test;
End Test;
Instead If I change the procedure something like below, will this be a costlier than the above ?
CREATE OR REPLACE PROCEDURE Test
(
rc1 IN OUT GlobalPKG.RC
)
AS
Begin
open rc1 for
select *
from ( select ename
from emp
where deptno = 20) ;
End Test;
Thanks
Sam
|
|
|
|
| Re: ORACLE global Temp Table / Ref cursor problem [message #25427 is a reply to message #25418] |
Wed, 23 April 2003 11:28 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Actually, that's the approach I am suggesting - bypassing the temp table entirely. It will be far less costly than using the temp table because you avoid all the DML and associated undo/redo.
I'm not sure why you used an inline view in your example. It could really be just:
open rc1 for
select ename
from emp
where deptno = 20; |
|
|
|
| Re: ORACLE global Temp Table / Ref cursor problem [message #25554 is a reply to message #25385] |
Fri, 02 May 2003 07:45 |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
As Todd said, it is right that half of the people think that they need a temporary table where the same can be achieved (unless its needed to store it for a while) by a simple or inline view.
In any case, you need to use a temporary table, did you try it out with ON COMMIT PRESERVE ROWS instead of DELETE ROWS? Again, I would rather use an inline view or subquery etc instead of using a temporary table, and I am not so used to with this temp table stuff. As per my knowledge, the ON COMMIT PRESERVE ROWS clause may resolve your issue. This PRESERVE ROWS clause keeps the data available until your session ends, where as the other keeps it available only until a transaction goes end of scope. So when you try it from a VB transaction, obviously the DELETE ROWS clause takes away the data as soon as your VB transaction done with ORACLE, where as the other keeps it available until a client/server session terminates.
Check it out.
Good luck :)
|
|
|
|
| Re: ORACLE global Temp Table / Ref cursor problem [message #27739 is a reply to message #25385] |
Mon, 27 October 2003 00:16 |
eilison
Messages: 22
Registered: August 2003
|
Junior Member |
|
|
Transaction-specific temporary tables are accessible by user transactions and their child transactions. However, a given transaction-specific temporary table cannot be used concurrently by two transactions in the same session, although it can be used by transactions in different sessions.
If a user transaction does an INSERT into the temporary table, then none of its child transactions can use the temporary table afterwards.
If a child transaction does an INSERT into the temporary table, then at the end of the child transaction, the data associated with the temporary table goes away. After that, either the user transaction or any other child transaction can access the temporary table.
=====================
The above text is originally from Oracle Docu, may be help you.
eilison
eilisonl@yahoo.com
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
