I'm trying to use sqlplus to dump out some DDL for a stored procedure. I have found an issue with my script where it doesn't properly dump out the lines into a file, and this appears to be caused by problems with the "set linesize" command in sqlplus. Lines are getting truncated at 80 characters no matter what I set the linesize variable to (currently I changed it to 500 as a test -- but it still doesn't work)!
For example, suppose I have a line in the procedure like this:
CREATE OR REPLACE PROCEDURE
...
INSERT INTO SOME_TABLE_NAME_HERE (OWNER, ACTIVITY, ACTIVITY_COST, NUM_WORKERS, NULL)
SELECT ...
I can see in SQL Developer that the 80th character is the N in "NULL" and that "ULL" are beyond the limit. When I dump out the DDL in sqlplus, it's showing up like this, no matter what I set linesize to:
CREATE OR REPLACE PROCEDURE
...
INSERT INTO SOME_TABLE_NAME_HERE (OWNER, ACTIVITY, ACTIVITY_COST, NUM_WORKERS, N
ULL)
SELECT ...
Here are the options that I am using in their entirety. This is an excerpt from a shell script that I am working on, but you can see the options that get echoed into the file that is ultimately read/executed by sqlplus:
echo "set heading off;" >> proc.sql
echo "set echo off;" >> proc.sql
echo "set pages 999;" >> proc.sql
echo "set termout off;" >> proc.sql
echo "set long 90000;" >> proc.sql
echo "set linesize 200;" >> proc.sql
echo "spool ${schema}.txt" >> proc.sql
echo "select dbms_metadata.get_ddl('PROCEDURE', '${proc}') from dual;" >> proc.sql
No matter what I set linesize to it always truncates at the 80th character and starts printing on the next line. Changing the linesize option only seems to pad the right side of the output with spaces. So if the line is longer than 80 characters and I set linesize to 90, it will truncate at the 80th character, print 10 spaces, and then print "ULL)" and 86 spaces on the next line. If I change it to 100 then I get 20 spaces on the first line, followed by "ULL)" and 96 spaces on the next line. And so on...
I tried using trimspool ON but that doesn't help the issue. Setting linesize appears to be pointless -- everything is truncated at 80 characters. What am I doing wrong here? Thanks!