Re: Need Help with some PLSQL code please

Just my two cents worth...

I think the comments from others would be that in most instances, the end user would never see the serveroutput. If you are using Oracle applications, consider using the FND_FILE.PUT which will let you write to the OUTPUT file or LOG file of the concurrent manager job.

If you are not using Oracle Applications, try writing to a custom global Audit Log table, using process_id and sub_process_id's to identify jobs and their steps. Then you can wrap a form or web page around the log table or even a report.

On Sun, 14 Sep 2003 16:52:33 +1000, "Chris \( Val \)" <chrisval_at_bigpond.com.au> wrote:

>
>"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
>

MylxHQ - Oracle Resource Portal
http://oracle.mylxhq.com
bsc7080mqc_at_mylxhp.com
(replace mylxhp with mylxhq to reply/contact) Received on Sun Sep 14 2003 - 21:36:09 CDT