Re: Temp tables??

From: Rob Gravereaux <rgravereaux_at_mbcnet.com>
Date: Thu, 5 Oct 2000 08:59:17 -0400
Message-ID: <39dc7bb2$0$4928@wodc7nh0.news.uu.net>

Cool. I'll take a look.
  Thomas Olszewicki <ThomasO_at_cpas.com> wrote in message news:wnTC5.111816$i5.4611194_at_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 Thu Oct 05 2000 - 07:59:17 CDT