3 Replies Latest reply: May 7, 2012 3:35 AM by Billy~Verreynne RSS

    arranging  DBMS_OUTPUT.PUT_LINE  in descending order

    875962
      How can i arrange DBMS_OUTPUT.PUT_LINE by descending order. I am calculating correlation coeff of closing price of few stocks . i need to arrange them into order from high to low based on their corr coeff . pleas help me with it.

      listed below is my complete program

      ====Merck & Company           MRK          X
      ====Pfizer Inc.               PFE          A
      ====Proctor and Gamble          PG          B
      ====Johnson and Johnson          JNJ          C
      ====Altria Group Inc          MO          D
      ====Caterpillar               CAT          E
      ====Intel Corporation          INTC          F






      DECLARE
      vPopulation NUMBER;
      vSumX NUMBER;
      vSumA NUMBER;
      vSumB     NUMBER;
      vSumC NUMBER;
      vSumD NUMBER;
      vSumE NUMBER;
      vSumF NUMBER;
      vMeanX NUMBER;
      vMeanA NUMBER;
      vMeanB NUMBER;
      vMeanC NUMBER;
      vMeanD NUMBER;
      vMeanE NUMBER;
      vMeanF NUMBER;
      vDistMeanX NUMBER;
      vDistMeanA NUMBER;
      vDistMeanB NUMBER;
      vDistMeanC NUMBER;
      vDistMeanD NUMBER;
      vDistMeanE NUMBER;
      vDistMeanF NUMBER;
      vProdXA NUMBER;
      vProdXB NUMBER;
      vProdXC NUMBER;
      vProdXD NUMBER;
      vProdXE NUMBER;
      vProdXF NUMBER;
      vSumProdXA NUMBER:=0;
      vSumProdXB NUMBER:=0;
      vSumProdXC NUMBER:=0;
      vSumProdXD NUMBER:=0;
      vSumProdXE NUMBER:=0;
      vSumProdXF NUMBER:=0;
      aSqr NUMBER;
      aSumSqrX NUMBER:=0;
      aSumSqrA NUMBER:=0;
      aSumSqrB NUMBER:=0;
      aSumSqrC NUMBER:=0;
      aSumSqrD NUMBER:=0;
      aSumSqrE NUMBER:=0;
      aSumSqrF NUMBER:=0;
      vCurValX NUMBER;
      vCurValA NUMBER;
      vCurValB NUMBER;
      vCurValC NUMBER;
      vCurValD NUMBER;
      vCurValE NUMBER;
      vCurValF NUMBER;
      vXACorrel NUMBER;
      vXBCorrel NUMBER;
      vXCCorrel NUMBER;
      vXDCorrel NUMBER;
      vXECorrel NUMBER;
      vXFCorrel NUMBER;

      -- Call this guy our X
      CURSOR aCurX is SELECT closed
      FROM npe_enrollment
      WHERE TIC = 'MRK' AND TO_CHAR(close_date,'YYYY') = '2008'
      ORDER BY close_date;

      -- Call this guy our A
      CURSOR aCurA is SELECT closed
      FROM npe_enrollment
      WHERE TIC = 'PFE' AND TO_CHAR(close_date,'YYYY') = '2008'
      ORDER BY close_date;

      -- Call this guy our B
      CURSOR aCurB is SELECT closed
      FROM npe_enrollment
      WHERE TIC = 'PG' AND TO_CHAR(close_date,'YYYY') = '2008'
      ORDER BY close_date;
      -- Call this guy our C
      CURSOR aCurC is SELECT closed
      FROM npe_enrollment
      WHERE TIC = 'JNJ' AND TO_CHAR(close_date,'YYYY') = '2008'
      ORDER BY close_date;

      -- Call this guy our D
      CURSOR aCurD is SELECT closed
      FROM npe_enrollment
      WHERE TIC = 'MO' AND TO_CHAR(close_date,'YYYY') = '2008'
      ORDER BY close_date;

      -- Call this guy our E
      CURSOR aCurE is SELECT closed
      FROM npe_enrollment
      WHERE TIC = 'CAT' AND TO_CHAR(close_date,'YYYY') = '2008'
      ORDER BY close_date;
      -- Call this guy our F
      CURSOR aCurF is SELECT closed
      FROM npe_enrollment
      WHERE TIC = 'INTC' AND TO_CHAR(close_date,'YYYY') = '2008'
      ORDER BY close_date;
      BEGIN
      -- Get the population (N).
      SELECT COUNT(DISTINCT(close_date))
      INTO vPopulation
      FROM npe_enrollment
      WHERE TO_CHAR(close_date,'YYYY') = '2008';
      DBMS_OUTPUT.PUT_LINE('Population = ' || vPopulation);

      -- Next get the sum of X
      SELECT SUM(CLOSED)
      INTO vSumX
      FROM npe_enrollment
      WHERE TIC = 'MRK' AND TO_CHAR(close_date,'YYYY') = '2008';
      DBMS_OUTPUT.PUT_LINE('Sum SamplesX= ' || vSumX);

      -- Next get the sum of A
      SELECT SUM(CLOSED)
      INTO vSumA
      FROM npe_enrollment
      WHERE TIC = 'PFE' AND TO_CHAR(close_date,'YYYY') = '2008';
      DBMS_OUTPUT.PUT_LINE('Sum SamplesA= ' || vSumA);

      -- Next get the sum of B
      SELECT SUM(CLOSED)
      INTO vSumB
      FROM npe_enrollment
      WHERE TIC = 'PG' AND TO_CHAR(close_date,'YYYY') = '2008';
      DBMS_OUTPUT.PUT_LINE('Sum SamplesB= ' || vSumB);

      -- Next get the sum of C
      SELECT SUM(CLOSED)
      INTO vSumC
      FROM npe_enrollment
      WHERE TIC = 'JNJ' AND TO_CHAR(close_date,'YYYY') = '2008';
      DBMS_OUTPUT.PUT_LINE('Sum SamplesC= ' || vSumC);
      -- Next get the sum of D
      SELECT SUM(CLOSED)
      INTO vSumD
      FROM npe_enrollment
      WHERE TIC = 'MO' AND TO_CHAR(close_date,'YYYY') = '2008';
      DBMS_OUTPUT.PUT_LINE('Sum SamplesD= ' || vSumD);

      -- Next get the sum of E
      SELECT SUM(CLOSED)
      INTO vSumE
      FROM npe_enrollment
      WHERE TIC = 'CAT' AND TO_CHAR(close_date,'YYYY') = '2008';
      DBMS_OUTPUT.PUT_LINE('Sum SamplesE= ' || vSumE);

      -- Next get the sum of F
      SELECT SUM(CLOSED)
      INTO vSumF
      FROM npe_enrollment
      WHERE TIC = 'INTC' AND TO_CHAR(close_date,'YYYY') = '2008';
      DBMS_OUTPUT.PUT_LINE('Sum SamplesF= ' || vSumF);

      -- The mean is the sum of the samples divided by the population
      vMeanX := vSumX/vPopulation;
      vMeanA := vSumA/vPopulation;
      vMeanB := vSumB/vPopulation;
      vMeanC := vSumC/vPopulation;
      vMeanD := vSumD/vPopulation;
      vMeanE := vSumE/vPopulation;
      vMeanF := vSumF/vPopulation;
      DBMS_OUTPUT.PUT_LINE('Mean X= ' || vMeanX);
      DBMS_OUTPUT.PUT_LINE('Mean A= ' || vMeanA);
      DBMS_OUTPUT.PUT_LINE('Mean B= ' || vMeanB);
      DBMS_OUTPUT.PUT_LINE('Mean C= ' || vMeanC);
      DBMS_OUTPUT.PUT_LINE('Mean D= ' || vMeanD);
      DBMS_OUTPUT.PUT_LINE('Mean E= ' || vMeanE);
      DBMS_OUTPUT.PUT_LINE('Mean F= ' || vMeanE);
      -- Obtain each sample for X and determine its distance from the mean. Square it, then sum it.
      -- Finally divide it by the population. This is our VARIANCE for X
      OPEN aCurX;
      LOOP
      FETCH aCurX INTO vCurValX;
      EXIT WHEN aCurX%NOTFOUND;
      aSqr := (vCurValX - vMeanX) * (vCurValX - vMeanX);
      aSumSqrX := aSqr + aSumSqrX;
      END LOOP;
      CLOSE aCurX;
      DBMS_OUTPUT.PUT_LINE('Variance X= ' || aSumSqrX);

      -- Obtain each sample for A and determine its distance from the mean. Square it, then sum it.
      -- Finally divide it by the population. This is our VARIANCE for A
      OPEN aCurA;
      LOOP
      FETCH aCurA INTO vCurValA;
      EXIT WHEN aCurA%NOTFOUND;
      aSqr := (vCurValA - vMeanA) * (vCurValA - vMeanA);
      aSumSqrA := aSqr + aSumSqrA;
      END LOOP;
      CLOSE aCurA;
      DBMS_OUTPUT.PUT_LINE('Variance A= ' || aSumSqrA);

      -- Obtain each sample for B and determine its distance from the mean. Square it, then sum it.
      -- Finally divide it by the population. This is our VARIANCE for B
      OPEN aCurB;
      LOOP
      FETCH aCurB INTO vCurValB;
      EXIT WHEN aCurB%NOTFOUND;
      aSqr := (vCurValB - vMeanB) * (vCurValB - vMeanB);
      aSumSqrB := aSqr + aSumSqrB;
      END LOOP;
      CLOSE aCurB;
      DBMS_OUTPUT.PUT_LINE('Variance B= ' || aSumSqrB);

      -- Obtain each sample for C and determine its distance from the mean. Square it, then sum it.
      -- Finally divide it by the population. This is our VARIANCE for C
      OPEN aCurC;
      LOOP
      FETCH aCurC INTO vCurValC;
      EXIT WHEN aCurC%NOTFOUND;
      aSqr := (vCurValC - vMeanC) * (vCurValC - vMeanC);
      aSumSqrC := aSqr + aSumSqrC;
      END LOOP;
      CLOSE aCurC;
      DBMS_OUTPUT.PUT_LINE('Variance C= ' || aSumSqrC);

      -- Obtain each sample for D and determine its distance from the mean. Square it, then sum it.
      -- Finally divide it by the population. This is our VARIANCE for D
      OPEN aCurD;
      LOOP
      FETCH aCurD INTO vCurValD;
      EXIT WHEN aCurD%NOTFOUND;
      aSqr := (vCurValD - vMeanD) * (vCurValD - vMeanD);
      aSumSqrD := aSqr + aSumSqrD;
      END LOOP;
      CLOSE aCurD;
      DBMS_OUTPUT.PUT_LINE('Variance D= ' || aSumSqrD);

      -- Obtain each sample for E and determine its distance from the mean. Square it, then sum it.
      -- Finally divide it by the population. This is our VARIANCE for E
      OPEN aCurE;
      LOOP
      FETCH aCurE INTO vCurValE;
      EXIT WHEN aCurE%NOTFOUND;
      aSqr := (vCurValE - vMeanE) * (vCurValE - vMeanE);
      aSumSqrE := aSqr + aSumSqrE;
      END LOOP;
      CLOSE aCurE;
      DBMS_OUTPUT.PUT_LINE('Variance E= ' || aSumSqrE);

      -- Obtain each sample for F and determine its distance from the mean. Square it, then sum it.
      -- Finally divide it by the population. This is our VARIANCE for F
      OPEN aCurF;
      LOOP
      FETCH aCurF INTO vCurValF;
      EXIT WHEN aCurF%NOTFOUND;
      aSqr := (vCurValF - vMeanF) * (vCurValF - vMeanF);
      aSumSqrF := aSqr + aSumSqrF;
      END LOOP;
      CLOSE aCurF;
      DBMS_OUTPUT.PUT_LINE('Variance F= ' || aSumSqrF);

      -- Determine the covarience of X and A. Subtract the respective mean from each value and
      -- get their product. Sum this product until out of values.
      OPEN aCurX;
      OPEN aCurA;
      OPEN aCurB;
      OPEN aCurC;
      OPEN aCurD;
      OPEN aCurE;
      OPEN aCurF;

      -- Loop through the population
      FOR i IN 1..vPopulation
      LOOP
      FETCH aCurX INTO vCurValX;
      FETCH aCurA INTO vCurValA;
      FETCH aCurB INTO vCurValB;
      FETCH aCurC INTO vCurValC;
      FETCH aCurD INTO vCurValD;
      FETCH aCurB INTO vCurValE;
      FETCH aCurB INTO vCurValF;
      vDistMeanX := vCurValX - vMeanX;
      vDistMeanA := vCurValA - vMeanA;
      vDistMeanB := vCurValB - vMeanB;
      vDistMeanC := vCurValC - vMeanC;
      vDistMeanD := vCurValD - vMeanD;
      vDistMeanE := vCurValE - vMeanE;
      vDistMeanF := vCurValF - vMeanF;
      vProdXA := vDistMeanX * vDistMeanA;
      vProdXB := vDistMeanX * vDistMeanB;
      vProdXC := vDistMeanX * vDistMeanC;
      vProdXD := vDistMeanX * vDistMeanD;
      vProdXE := vDistMeanX * vDistMeanE;
      vProdXF := vDistMeanX * vDistMeanF;
      vSumProdXA := vSumProdXA + vProdXA;
      vSumProdXB := vSumProdXB + vProdXB;
      vSumProdXC := vSumProdXC + vProdXC;
      vSumProdXD := vSumProdXD + vProdXD;
      vSumProdXE := vSumProdXE + vProdXE;
      vSumProdXF := vSumProdXF + vProdXF;
      END LOOP;

      -- Now get the correlation coeficient
      vXACorrel := vSumProdXA/SQRT(aSumSqrX * aSumSqrA);
      DBMS_OUTPUT.PUT_LINE('Correl Coef of X and A= ' || vXACorrel);

      -- Now get the correlation coeficient
      vXBCorrel := vSumProdXB/SQRT(aSumSqrX * aSumSqrB);
      DBMS_OUTPUT.PUT_LINE('Correl Coef of X and B= ' || vXBCorrel);

      -- Now get the correlation coeficient
      vXCCorrel := vSumProdXC/SQRT(aSumSqrX * aSumSqrC);
      DBMS_OUTPUT.PUT_LINE('Correl Coef of X and C= ' || vXCCorrel);

      -- Now get the correlation coeficient
      vXDCorrel := vSumProdXD/SQRT(aSumSqrX * aSumSqrD);
      DBMS_OUTPUT.PUT_LINE('Correl Coef of X and D= ' || vXDCorrel);

      -- Now get the correlation coeficient
      vXECorrel := vSumProdXE/SQRT(aSumSqrX * aSumSqrE);
      DBMS_OUTPUT.PUT_LINE('Correl Coef of X and E= ' || vXECorrel);

      -- Now get the correlation coeficient
      vXFCorrel := vSumProdXF/SQRT(aSumSqrX * aSumSqrF);
      DBMS_OUTPUT.PUT_LINE('Correl Coef of X and F= ' || vXFCorrel);



      END;
      /
        • 1. Re: arranging  DBMS_OUTPUT.PUT_LINE  in descending order
          Karthick_Arp
          The code is all wrong. You are reading the same table multiple times. This is a very bad approach. You should not be doing like this.

          Please give us your exact requirement and we would let you know how you can do it in an efficient way. Also give us the following details.

          1. DB Version.
          2. Table Structure and Sample Data.
          3. Index on table.
          • 2. Re: arranging  DBMS_OUTPUT.PUT_LINE  in descending order
            Stew Ashton
            Well, SQL has an ORDER BY clause in it that has a DESC keyword.

            Maybe you should just replace all that code by one SQL statement.

            If you provided some sample data to load into our test databases, we could probably provide that SQL statement.

            To give us sample data, provide CREATE TABLE and INSERT statements.

            Please tell us exactly what output you would expect from the sample data.

            Please tell us what Oracle version you have.

            Now I am going to criticize your code; please don't be angry.

            - You are not alone. Many many times we write code in some language (PL/SQL or Java or whatever) when one SQL statement will do the job.

            - You have hard-coded every value; are you sure those values will never change? Will you always be in 2008?

            - You are converting part of close_date to '2008' in the WHERE clause. This prevents the use of any index on close_date. This means your code is going to read the entire table 15 times. You should use TO_DATE on your input date. We can show you how to do this once you provide the test data.
            • 3. Re: arranging  DBMS_OUTPUT.PUT_LINE  in descending order
              Billy~Verreynne
              Yes, you can order the output from the DBMS_OUTPUT buffer. You can define a pipeline table for outputting the buffer (as described in {message:id=9336934}) and use the ORDER BY clause.

              But being able to do this, does NOT mean the approach is the correct one.

              DBMS_OUTPUT is a poor choice for getting the client to display server data. The DBMS_OUTPUT buffer resides in expensive server memory. Writing into this buffer increases its size in server memory. The client needs to read from the buffer after a server call is made, and render the contents on the client.

              This is far less efficient, a lot slower, and significantly less scalable that using the proper interface - passing a cursor handle of a SQL cursor to the client, for the client to use to fetch and process the output from the server cursor.

              As mentioned by others - the approach your code shows, is not suited for Oracle. It violates basic principles. Delete it. Start again - after reading and understanding what the SQL language is, and what the PL/SQL language is. SQL is by far superior to PL/SQL when it comes to crunching database data. Using PL/SQL instead as your code is doing, is not sensible.