Can an OCP or an experienced Oracle Professional answer these very simple questions?
1. From Unix shell scripts when I code in SQLPLUS to create an output file, say a comma delimiter file using DBMS_OUTPUT.putline() function why does the stack size increases? Is there anyway that we can flush the buffer to reduce the stack size? We are getting production support calls when job fails in the middle of night.
2. Why do we have to use error handling routines such as awk, or sed in Unix to trap or catch errors from Unix scripts when “select ..from <table> statement or from any execute stored procedure or even dbms_output.putline() function is used? Why does Oracle not provide us clear error checking routines within SQLPLUS as third generation languages, which we refer to as application programs such as “C” or COBOL have?
3. Would you advice to program in “C” or in “COBOL” to create a comma delimiter file for the above case instead of using SQLPLUS utility from unix? If answer is yes, can you discuss what are the advantages and disadvantages of using “C” with using PL/SQL or select clause within SQLPLUS in Unix.
1. The stack size is increasing because of the buffering taking place by dbms_output. You can control the buffer size set serveroutput on size <NUMBYTES> (100000 max), but if you set it too small you'll get buffer overrun erros. This also implies that you are spooling to generate the file which has some more buffering issues.
I would suggest that you look at the utl_file package which will allow you to write directly out to files. It's a better fit for what you are doing.
l_output := utl_file.fopen( p_dir, p_filename || '.ctl', 'w' );
utl_file.put_line( l_output, 'my data string' || ' and more stuff' );
utl_file.fclose( l_output );
2. This sounds like a misunderstanding of the interaction between a shell script and an oracle utility such as sqlplus. Note that using pl/sql blocks and/or stored procedures with exception handing (a feature of pl/sql), you have tons of error handing flexibility inside your oracle pl/sql code. Only a final exit code should be returned to the shell environement indicating success or failure.
3. It basically is a matter of choice and balancing your need for performance against your desire for ease of maintence, time to code, etc. I always use the cheapest (dev. time) tool that meets the requirements. Tool and PL/SQL solutions tend be be highly portable. However, sometimes a rock crushing C program is just what the doctor ordered.
I have uploaded a code fragement that I believe come from Thomas Kyte's 'Expert One-on-One' book examples. It gives an idea for a generic export procedure that also creates sql*loader files, etc. It utilizes the utl_file package, etc. It may need some testing/debugging however.