logo
down
shadow

Reading from a oracle temp table in a separate procedure than the one it was populted in


Reading from a oracle temp table in a separate procedure than the one it was populted in

By : user3860453
Date : January 02 2021, 06:48 AM
like below fixes the issue There are a few ways to connect to Oracle from .Net, but in general I assume that at some point you're creating a Connection object and using that to generate commands, etc. You need to re-use that Connection object between the two calls. This may mean passing the Connection object around, or using some kind of singleton pattern to retrieve it.
code :


Share : facebook icon twitter icon
how to convert global temp table to collection/type in oracle procedure

how to convert global temp table to collection/type in oracle procedure


By : user2138012
Date : March 29 2020, 07:55 AM
wish of those help I have a procedure that is using a temp table. I want to get rid of the temp table and use a collection to remove I/O. it has about 5000 records. , You are heading in the right direction - first create your types
code :
CREATE TYPE myEntry
AS
OBJECT
  (attr1 NUMBER,
   attr2 VARCHAR2(20)
  );

CREATE TYPE myCollection AS TABLE OF myEntry;
CREATE OR REPLACE FUNCTION ExampleMyCollection1
RETURN myCollection  
IS
   lCol myCollection := myCollection(); /* Must initialise empty collection */
BEGIN
    lCol.EXTEND(1000);
    /* Populate the collection entries with objects */
    FOR i IN 1..1000 LOOP
        lCol(i) := myEntry(i,'An entry for '||i);
    END LOOP;
    RETURN lCol;
END ExampleMyCollection1;

SELECT * FROM TABLE(ExampleMyCollection1);
CREATE OR REPLACE FUNCTION ExampleMyCollection2
RETURN myCollection PIPELINED
IS
BEGIN
    FOR i IN 1..1000 LOOP
        PIPE ROW(myEntry(i,'An entry for '||i));
    END LOOP;
END ExampleMyCollection2;

SELECT * FROM TABLE(ExampleMyCollection2);
CREATE OR REPLACE PACKAGE pMyCollection
AS
   PROCEDURE AddEntry(entry IN myEntry);

   FUNCTION fCurrentCollection RETURN myCollection;

   PROCEDURE ClearEntries;

END pMyCollection;

CREATE OR REPLACE PACKAGE BODY pMyCollection
AS
   /* Stateful variable to hold the collection */
   pCollection myCollection := myCollection();

   PROCEDURE AddEntry(entry IN myEntry)
   IS
   BEGIN
      pCollection.EXTEND;
      pCollection(pCollection.LAST) := entry;
   END;

   PROCEDURE ClearEntries 
   IS
   BEGIN
      pCollection.DELETE;
   END ClearEntries;

   FUNCTION fCurrentCollection
   RETURN myCollection
   IS
   BEGIN
      /* Return whole collection - we could use pipelining and parameters to return partial elements */
      RETURN pCollection;
   END fCurrentCollection;

END pMyCollection;
How to pass a temp table as a parameter into a separate stored procedure

How to pass a temp table as a parameter into a separate stored procedure


By : Lilly An
Date : March 29 2020, 07:55 AM
wish of those help When you create a #TEMP table, the "scope" is bigger than just the procedure it is created in.
Below is a sample:
code :
IF EXISTS 
    (
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' and ROUTINE_NAME = N'uspProc002'  
    )
BEGIN
    DROP PROCEDURE [dbo].[uspProc002]
END


GO

CREATE Procedure dbo.uspProc002 
AS

BEGIN

/* Uncomment this code if you want to be more explicit about bad "wiring" */
/*
IF OBJECT_ID('tempdb..#TableOne') IS NULL
begin
        THROW 51000, 'The procedure expects a temp table named #TableOne to already exist.', 1;  
end
*/

    /* Note, I did not Create #TableOne in this procedure.  It "pre-existed".  An if check will ensure that it is there.  */
    IF OBJECT_ID('tempdb..#TableOne') IS NOT NULL
    begin
        Insert into #TableOne ( SurrogateKey , NameOf ) select 2001, 'uspProc002'
    end

END


GO



IF EXISTS 
    (
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' and ROUTINE_NAME = N'uspProc001'  
    )
BEGIN
    DROP PROCEDURE [dbo].[uspProc001]
END


GO

CREATE Procedure dbo.uspProc001 (
@Param1 int
)
AS

BEGIN


    IF OBJECT_ID('tempdb..#TableOne') IS NOT NULL
    begin
            drop table #TableOne
    end


    CREATE TABLE #TableOne
    ( 
    SurrogateKey int , 
    NameOf varchar(12)
    )

    Insert into #TableOne ( SurrogateKey , NameOf ) select 1001, 'uspProc001'

    Select * from #TableOne

    EXEC dbo.uspProc002 

    Select * from #TableOne

    IF OBJECT_ID('tempdb..#TableOne') IS NOT NULL
    begin
            drop table #TableOne
    end


END


GO




exec dbo.uspProc001 0
Not able to create temp table in stored procedure in Oracle

Not able to create temp table in stored procedure in Oracle


By : user3283951
Date : March 29 2020, 07:55 AM
fixed the issue. Will look into that further I am trying to create a temp table in Oracle.
code :
begin
      create global temporary table temptable (mgr number);
    end;
    /
    begin
      execute immediate 'create global temporary table temptable (mgr number)';
   execute immediate 'insert into   temptable select pct_free from dba_tables';
   execute immediate 'insert into   temptable  values(-99)'; 
    end;
    /
select count(*) from temptable;

     0
Oracle Stored Procedure - Can I empty temp table after cursor is created

Oracle Stored Procedure - Can I empty temp table after cursor is created


By : oser
Date : March 29 2020, 07:55 AM
it fixes the issue I haven't really tested that, but from general ORACLE knowledge perspective, as soon as you open a cursor, you are no longer dealing with stored data. Instead you are iterating an in-memory snapshot. So I believe it should work. Unless there's a huge amount of data and oracle tries to page the results (not sure if it actually happens though)...
As a simple/safe option you can delete the records that are a day/hour/minute old (depending on the utilization).
Reading from Global Temp Table With Oracle.ManagedDataAccess

Reading from Global Temp Table With Oracle.ManagedDataAccess


By : Jamie Anderson
Date : March 29 2020, 07:55 AM
Hope this helps As stated in the error message, isolation level IsolationLevel.ReadUncommitted is not allowed. You must use ReadCommitted (the default) or Serializable.
Did you create the global temporary table with ON COMMIT DELETE ROWS (the default)?
shadow
Privacy Policy - Terms - Contact Us © 35dp-dentalpractice.co.uk