DBMS_OUTPUT.PUT_LINE

[staceyz]

I have a PL/SQL script that is using DBMS_OUTPUT.PUT_LINE to create a file that has fixed lenth lines. Some of the fields need to be all spaces (example: every line should start with 12 spaces). The put_line strips the leading spaces - how do I stop this?

 

[sem]

As far as I know there are no means to do it. We use some "small" characters, namely dots or at the begining of the line. It works more or less fine in reports but for getting plain files for export/import try to use explicit selects.

 

[Kenrae]

If you want to create a file, why don't you use DBMS_FILE package instead?

 

[Kenrae]

Sorry, UTL_FILE I meant.

 

[stevecal]

UTL_FILE is fine when you've got the permissions and can access the permissioned drive/directory. But often as not you need some SQL just to write some output locally.

The trick here when you're spooling is //not// to use the dbms_output.put_line routine if you need spaces, but to build up your output creatively using SELECTs, either from the db or from dual. You could build up your output line using something like
SPOOL OFF
DEFINE Temp = 'c:\temp\output.txt'
DEFINE v_padding = ' '
DEFINE v_text = 'some text here'
SET HEADING OFF FEEDBACK OFF TERMOUT OFF ECHO OFF
SET VERIFY OFF FLUSH OFF LINESIZE 1000

spool &Temp

SELECT '&v_padding'||'&v_text' FROM dual;
SELECT '&v_padding'||my_text_field FROM my_table where rownum < 2;
spool off;

etcetera...

 

[sem]

The same result may be achieved by declaring bind variable by VAR and printing it by PRINT (sql*plus makes the same select :smth from dual).