Re: Need Help with some PLSQL code please

From: Chris \( Val \) <chrisval_at_bigpond.com.au>
Date: Sun, 14 Sep 2003 16:52:33 +1000
Message-ID: <bk137h$o03ak$1@ID-110726.news.uni-berlin.de>

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1063488009.535753_at_yasure...
| Chris ( Val ) wrote:

Hi Daniel.

[snip]

| It is almost always the wrong choice to use DBMS_OUTPUT with production
| code. You just stated one of the reasons.

Just out of curiosity, what else is there to make it almost always the wrong choice ?

I have reworked the code as follows:

Can you please tell me if there are still any problems with it ?

CREATE OR REPLACE PROCEDURE FIND_DXF_ERRORS ( MyNumber VARCHAR2, MyDate VARCHAR2 ) IS

  BadDateEntry EXCEPTION;
  BadIdNumber EXCEPTION;

  CURSOR MyCursor IS SELECT REQUEST_ID, USER_ID, DISTRICT, DATE_REQUESTED,

                            MINX, MINY, MAXX, MAXY, DESTINATION, FILENAME,
                            PROCESSED, REQUEST_TYPE
         FROM DXFREQUEST
         WHERE TRUNC(DATE_REQUESTED) = MyDate
         AND PROCESSED = MyNumber;

  Counter NUMBER( 5 ) := 0;

  TempID VARCHAR2( 50 ) := MyNumber;
  nTempID NUMBER( 5 );

  TempDate VARCHAR2( 50 ) := MyDate;
  dTempDate DATE;

BEGIN    BEGIN

      nTempID := TO_NUMBER( TempID );
      IF nTempID < 0 OR nTempID > 15 THEN
         RAISE BadIdNumber;
      END IF;
   EXCEPTION
      WHEN OTHERS THEN
         RAISE BadIdNumber;

   END;    BEGIN
      dTempDate := TO_DATE( TempDate, 'DD-MON-YYYY' );    EXCEPTION

      WHEN OTHERS THEN
         RAISE BadDateEntry;

   END;    DBMS_OUTPUT.ENABLE( 1000000 );    --DBMS_OUTPUT.NEW_LINE;
   DBMS_OUTPUT.PUT_LINE(

'******************************************************************' );
   DBMS_OUTPUT.PUT_LINE( RPAD( 'RECORD', 8, ' ' )    || RPAD( 'USER_ID', 12, ' ' ) ||
                         RPAD( 'DISTRICT', 12, ' ' ) || RPAD( 'MINX', 12, ' ' )    ||
                         RPAD( 'MINY', 12, ' ' )     || RPAD( 'MAXX', 12, ' ' )    ||
                         RPAD( 'MAXY', 12, ' ' ) );

   DBMS_OUTPUT.PUT_LINE(

'******************************************************************' );

   FOR MyRow IN MyCursor LOOP

• Is it important to have this IF statement here ?
• because it still works without it ? IF MyNumber = MyRow.PROCESSED THEN DBMS_OUTPUT.PUT_LINE( RPAD( Counter + 1, 8, ' ' ) || RPAD( MyRow.USER_ID, 12, ' ' ) || RPAD( SUBSTR(MyRow.DISTRICT, 0, 8 ), 12, ' ' ) || RPAD( SUBSTR(MyRow.MINX, 0, 8 ), 12, ' ' ) || RPAD( SUBSTR(MyRow.MINY, 0, 8 ), 12, ' ' ) || RPAD( SUBSTR(MyRow.MAXX, 0, 8 ), 12, ' ' ) || RPAD( SUBSTR(MyRow.MAXY, 0, 8 ), 12, ' ' ) ); END IF;

       Counter := Counter + 1;

   END LOOP; EXCEPTION
   WHEN BadDateEntry THEN

        DBMS_OUTPUT.PUT_LINE(

'************************************************************' );
        DBMS_OUTPUT.PUT_LINE( 'ERROR: Please check that the date you entered is correct.
  );
        DBMS_OUTPUT.PUT_LINE(
'************************************************************' );

   WHEN BadIdNumber THEN
        DBMS_OUTPUT.PUT_LINE(
'***********************************************************' );
        DBMS_OUTPUT.PUT_LINE( 'ERROR: Please check that the number you entered is
correct.' );
        DBMS_OUTPUT.PUT_LINE(
'***********************************************************' );

   WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(
'***********************************************************' );
        DBMS_OUTPUT.PUT_LINE( 'ERROR: Unknown Error Occured.');
        DBMS_OUTPUT.PUT_LINE(
'***********************************************************' );

END; Thanks again.

Cheers.
Chris Val Received on Sun Sep 14 2003 - 01:52:33 CDT