i am trying to build a script to build another DDL script. problem is that the linefeed chr(10) does not appear to work when there is a SQL*plus keywords like password.
code:
set serveroutput on size 1000000;
set linesize 120;
set pagesize 50000;
set term on;
set feedback off;
set echo off;
set verify off;
spool buildDDLuser.sql;
declare
vDynamicSQL varchar2(2000);
begin
vDynamicSQL := 'create user user1 identified by "lax4g!VCdm"' || chr(10) ||
'default tablespace user '|| chr(10) ||
'temporary tablespace temp ' || chr(10) ||
'account unlock password expire;';
dbms_output.put_line(vDynamicSQL);
end;
/
spool off;
output:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> @sampleDDLuser.sql;
create user user1 identified by "lax4g!VCdm"
default tablespace user
temporary tablespace temp
account unlock password
expire;
SQL>
the correct output that i expect is
SQL> @sampleDDLuser.sql;
create user user1 identified by "lax4g!VCdm"
default tablespace user
temporary tablespace temp
account unlock password expire;
SQL>
where there is only 4 lines and the keyword expire stays on the 4th line.
thanks.