Re: Temp tables??

From: Thomas Olszewicki <ThomasO_at_cpas.com>
Date: Thu, 05 Oct 2000 04:23:56 GMT
Message-ID: <wnTC5.111816$i5.4611194@news1.rdc2.on.home.com>

Rob,
It may be what you are looking for:
Oracle 8.1.6 does have a good dynamic sql handling: Example:

   PROCEDURE FindMemberSSN( sData IN OUT REPLICATION%ROWTYPE , cMkey VARCHAR2 ,cSchema VARCHAR2 ) AS

      TYPE typeCur IS REF CURSOR;
      cStmt       VARCHAR2(400);
      cSSN        VARCHAR2(9);
      curData     typeCur;
      BEGIN
         cStmt    := 'SELECT A.SIN FROM '||cSchema||'.PERSON A,' ;
         cStmt    := cStmt||cSchema||'.MEMBER B WHERE 
A.PERSONID=B.PERSONID AND B.CLNT=:cCLNT AND B.MKEY=:cMKEY';
         OPEN curData FOR cStmt USING ssCPAS.CLNT,cMkey;
         FETCH curData INTO cSSN;
         IF curData%NOTFOUND THEN
            cSSN  := '000000000';
         END IF;
         sData.OSSN := cSSN;
         sData.CSSN := cSSN;
         CLOSE curData;
      END;

And again Oracle 8.1.6 does have temp tables: Example:
CREATE GLOBAL TEMPORARY TABLE TEMP$PLPSSOURCE (

   CALCDATE DATE,

   PLANNO      VARCHAR2(12),
   CSSN        VARCHAR2(9),
   OSSN        VARCHAR2(9),
   CALCID      NUMBER(12),
   SOURCE      VARCHAR2(3),
   BENEFIT     NUMBER(12,2),
   CON RATE    VARCHAR2(200),
   ACCBAL      NUMBER(12,2),
   OCALCID     NUMBER(12)

)
ON COMMIT PRESERVE ROWS; See Oracle HTML help : search for 1) OPEN cursor FOR , 2) CREATE TABLE ...
HTH
Thomas

--

Received on Wed Oct 04 2000 - 23:23:56 CDT