Home » Other » Client Tools » SQL*Plus output problem (merged 3) (SQL*Plus: Release 12.2.0.1.0 Production)
SQL*Plus output problem (merged 3) [message #672011] |
Thu, 27 September 2018 13:53 |
wtolentino
Messages: 413 Registered: March 2005
|
Senior Member |
|
|
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.
|
|
|
Re: SQL*Plus output problem [message #672014 is a reply to message #672011] |
Thu, 27 September 2018 14:12 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Just dbms_output line length limit; chr(10) is a character as another one for dbms_output.
SQL> exec dbms_output.put_line('create user user1 identified by "lax4g!VCdm" default tablespace user temporary tablespace temp account unlock password expire;');
create user user1 identified by "lax4g!VCdm" default tablespace user temporary tablespace temp account unlock password
expire;
PL/SQL procedure successfully completed.
What is the problem?
If you want to execute the statement just execute it.
If you want to display it then break the statement and call dbms_output as much as there are lines.
[Updated on: Thu, 27 September 2018 14:13] Report message to a moderator
|
|
|
|
|
|
|
|
|
Re: SQL*Plus output problem [message #672075 is a reply to message #672020] |
Mon, 01 October 2018 10:27 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
You should use the following settings
set linesize 5000 <---- allow up to 5000 characters in a single line
set trimspool on <---- remove any trailing spaces in the spool file
set pagesize 0 <---- Turn off paging
set termout off <---- don't output to the screen
set feedback off <---- No feedback from sqlplus
set echo off <---- Do not echo the commands
set verify off <---- Don't show variable replacement
set heading off <---- Do not output any column headings
spool c:\temp\buildDDLuser.sql; <---- Spool to a specific location instead of the oracle home bin directory
select 'create user user1 identified by "lax4g!VCdm"' || chr(10) ||
'default tablespace user '|| chr(10) ||
'temporary tablespace temp ' || chr(10) ||
'account unlock password expire;'
from dual;
spool off <---- Stop and save the spooled output
[Updated on: Mon, 01 October 2018 10:28] Report message to a moderator
|
|
|
|
Re: SQL*Plus output problem [message #672091 is a reply to message #672020] |
Tue, 02 October 2018 10:12 |
wtolentino
Messages: 413 Registered: March 2005
|
Senior Member |
|
|
that was only a snippet of the bigger part of the code. i had to use some select into clause to get some system and other info and format the text and the lines. for example
select banner,
substr(sys_context('USERENV','SESSION_SCHEMA'),1,20) schema_name,
substr(sys_context('userenv','db_name'),1,20) database_name,
sys_context('userenv','host') host_name,
to_char(sysdate,'dd-Mon-rrrr hh:mi:ss am') run_start_date
into vDBversion,
vLoginName,
vDBname,
vHostName,
vStartDate
from v$version
where banner like 'Oracle%';
-- header line
dbms_output.put_line(chr(13));
dbms_output.put_line('+------------------------------------------------------------------------------+');
dbms_output.put_line(vDBversion);
dbms_output.put_line('Login Name: '||vLoginName);
dbms_output.put_line('Database: '||vDBname);
dbms_output.put_line('Host Name: '||vhostName);
dbms_output.put_line('Start Date: '||vStartDate);
dbms_output.put_line('+------------------------------------------------------------------------------+');
dbms_output.put_line(chr(13));
this is why i have to use an anonymous PL/SQL code.
thanks again.
[Updated on: Tue, 02 October 2018 10:26] Report message to a moderator
|
|
|
|
Re: SQL*Plus output problem [message #672109 is a reply to message #672093] |
Wed, 03 October 2018 07:05 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Why use a procedure when a simple sql select will do it
SELECT
'+------------------------------------------------------------------------------+'
|| CHR(10) || Banner || CHR(10)
|| 'Login Name: ' || SUBSTR(SYS_CONTEXT('USERENV','SESSION_SCHEMA'),1,20) || CHR(10)
|| 'Database: ' || SUBSTR(SYS_CONTEXT('userenv', 'db_name'),1,20) || CHR(10)
|| 'Host Name: ' || SYS_CONTEXT('userenv', 'host') ||CHR(10)
|| 'Start Date: ' || TO_CHAR(SYSDATE, 'dd-Mon-rrrr hh:mi:ss am')|| CHR(10)
||'+------------------------------------------------------------------------------+'
|| CHR(10)
FROM V$version
WHERE Banner LIKE 'Oracle%';
[Updated on: Wed, 03 October 2018 07:08] Report message to a moderator
|
|
|
Re: SQL*Plus output problem [message #672113 is a reply to message #672109] |
Wed, 03 October 2018 07:20 |
wtolentino
Messages: 413 Registered: March 2005
|
Senior Member |
|
|
that was the original build a simple query. however, some added functionality that needs to prompt for input and use that input as Yes or No. somewhere in the code that needs if..then..else. and some variable query string to be used for execute immediate.
|
|
|
|
|
Re: SQL*Plus output problem [message #672288 is a reply to message #672118] |
Wed, 10 October 2018 08:07 |
wtolentino
Messages: 413 Registered: March 2005
|
Senior Member |
|
|
this is the code
set serveroutput on;
set linesize 1000;
set pagesize 50000;
set term on;
set feedback off;
set echo off;
set verify off;
spool sample_output.txt;
declare
vLoginName varchar2(40);
vDBname varchar2(40);
vHostName varchar2(40);
vServerName varchar2(40);
vDBVersion v$version.banner%type;
vStartDate varchar2(40);
begin
select banner,
substr(sys_context('USERENV','SESSION_SCHEMA'),1,20) schema_name,
substr(sys_context('userenv','db_name'),1,20) database_name,
sys_context('userenv','host') host_name,
to_char(sysdate,'dd-Mon-rrrr hh:mi:ss am') run_start_date
into vDBversion,
vLoginName,
vDBname,
vHostName,
vStartDate
from v$version
where banner like 'Oracle%';
-- header line
dbms_output.put_line(chr(13));
dbms_output.put_line('+------------------------------------------------------------------------------+');
dbms_output.put_line(vDBversion);
dbms_output.put_line('Login Name: '||vLoginName);
dbms_output.put_line('Database: '||vDBname);
dbms_output.put_line('Host Name: '||vhostName);
dbms_output.put_line('Start Date: '||vStartDate);
dbms_output.put_line('+------------------------------------------------------------------------------+');
dbms_output.put_line(chr(13));
end;
/
accept iRoleName prompt "enter role name: ";
accept iSchemaName prompt "enter schema name: ";
prompt enter object type when multiple separate by comma
prompt example TABLE, VIEWS, PROCEDURE, FUNCTION, PACKAGE, SEQUENCE, TYPE, MATERIALIZE VIEW
accept iObjectType prompt "";
prompt enter username when multiple separate by comma
prompt example user1, user2, user3
accept iUserName prompt "";
accept iDynamicYN prompt "execute immediate [Y/N]: ";
declare
vRoleName varchar2(40) := '&&iRoleName';
vSchemaName varchar2(40) := '&&iSchemaName';
vObjectType varchar2(200) := '&&iObjectType';
vUserName varchar2(200) := '&&iUserName';
vDynamicYN varchar2(1) := '&&iDynamicYN';
vCurrDBRole dba_roles.role%type;
vCtr number := 0;
vEndDate varchar2(40);
vObjTypCnt number := 0;
vRowCnt number := 0;
vEntObjTypeCnt number := 0; -- entered object type count
vFndObjTypeCnt number := 0; -- found object type count
vNfnObjTypeCnt number := 0; -- not found object type count
vFndUserNameCnt number := 0; -- found username count
vFndUserNameCnt2 number := 0; -- found username count
vNfUserNameCnt number := 0; -- not found username count
vNfUserNameCnt2 number := 0; -- not found username count
vFndUserNameCnt3 number := 0; -- found username count
vNfUserNameCnt3 number := 0; -- not found username count
vRandomPwd varchar2(10); -- random password
vDynamicSQL varchar2(2000); -- dynamic SQL string
cursor rObjectType (pRole varchar2, pOwner varchar2, pObjectType varchar2) is
select distinct vd.ddl_grants
from (select 'grant '||
decode(do.object_type,'PROCEDURE','execute',
'FUNCTION','execute',
'PACKAGE','execute',
'TYPE','execute',
'TABLE','select, update, delete, insert',
'VIEW','select',
'MATERIALIZED VIEW','select',
'SEQUENCE','select',null) || ' ' ||
'on ' || do.owner ||'.'||
decode(do.object_type,'TABLE',(select nvl(dt.iot_name,dt.table_name) from dba_tables dt where dt.owner = do.owner and dt.table_name = do.object_name),do.object_name) ||
' to '||pRole||';' ddl_grants
from dba_objects do
where do.owner = pOwner
and do.object_type = pObjectType
order by do.object_name ) vd
order by substr(substr(vd.ddl_grants,instr(vd.ddl_grants,pOwner)),1,instr(substr(vd.ddl_grants,instr(vd.ddl_grants,pOwner)),' ')-1);
-- this code is to get the parent table if the table is index organized table
--decode(do.object_type,'TABLE',(select nvl(dt.iot_name,dt.table_name) from dba_tables dt where dt.owner = do.owner and dt.table_name = do.object_name),do.object_name)
begin
dbms_output.put_line('...'||chr(13));
select count(dr.role)
into vCtr
from dba_roles dr
where dr.role = upper(vRoleName);
if vCtr > 0 then
dbms_output.put_line(chr(13));
dbms_output.put_line('role '||vRoleName||' exist');
else
dbms_output.put_line(chr(13));
dbms_output.put_line('create role '||vRoleName||';');
if vDynamicYN = 'Y' then
--execute immediate 'create role '||vRoleName;
NULL;
end if;
end if;
select count(du.username)
into vCtr
from dba_users du
where du.username = upper(vSchemaName);
if vCtr = 0 then
dbms_output.put_line(chr(13));
dbms_output.put_line('schema '||vSchemaName||' not found abort code!');
dbms_output.put_line('...');
dbms_output.put_line(chr(13));
else
-- get the object count
select regexp_count(vt1.nObjType,',') + 1 cnt
into vEntObjTypeCnt
from (select vObjectType nObjType from dual) vt1;
dbms_output.put_line(chr(13));
dbms_output.put_line(vEntObjTypeCnt ||' object type entered');
-- check for individual existing object types
for cRec1 in (select level,
--regexp_substr(nObjType,'[[:alnum:]]+', 1, level) ObjType
trim(regexp_substr(nObjType,'[^,]+', 1, level)) ObjType
from (select regexp_count(vt1.nObjType,',') + 1 cnt,
nObjType
from (select vObjectType nObjType from dual) vt1) vt2
connect by level <= vt2.cnt) loop
select count(*)
into vRowCnt
from (select distinct do.object_type
from dba_objects do
where do.owner = upper(vSchemaName)) vdo
where vdo.object_type = cRec1.ObjType;
if vRowCnt > 0 then
vFndObjTypeCnt := vFndObjTypeCnt + 1;
if vFndObjTypeCnt = 1 then
dbms_output.put_line(chr(13));
dbms_output.put_line('object type found: ');
dbms_output.put_line('------------------------------');
end if;
dbms_output.put_line(cRec1.ObjType);
end if;
end loop;
-- check for individual non-existing object types
for cRec2 in (select level,
--regexp_substr(nObjType,'[[:alnum:]]+', 1, level) ObjType
trim(regexp_substr(nObjType,'[^,]+', 1, level)) ObjType
from (select regexp_count(vt1.nObjType,',') + 1 cnt,
nObjType
from (select vObjectType nObjType from dual) vt1) vt2
connect by level <= vt2.cnt) loop
select count(*)
into vRowCnt
from (select distinct do.object_type
from dba_objects do
where do.owner = upper(vSchemaName)) vdo
where vdo.object_type = cRec2.ObjType;
if vRowCnt = 0 then
vNfnObjTypeCnt := vNfnObjTypeCnt + 1;
if vNfnObjTypeCnt = 1 then
dbms_output.put_line(chr(13));
dbms_output.put_line('object type not found: ');
dbms_output.put_line('------------------------------');
end if;
dbms_output.put_line(cRec2.ObjType);
end if;
end loop;
if vNfnObjTypeCnt > 0 then
dbms_output.put_line(chr(13));
dbms_output.put_line('object types not found abort code!');
else
dbms_output.put_line(chr(13));
dbms_output.put_line('DDL grants...');
for cRec3 in (select level,
--regexp_substr(nObjType,'[[:alnum:]]+', 1, level) ObjType
trim(regexp_substr(nObjType,'[^,]+', 1, level)) ObjType
from (select regexp_count(vt1.nObjType,',') + 1 cnt,
nObjType
from (select vObjectType nObjType from dual) vt1) vt2
connect by level <= vt2.cnt) loop
for cRec4 in rObjectType (vRoleName, vSchemaName, cRec3.Objtype) loop
dbms_output.put_line(cRec4.ddl_grants);
if vDynamicYN = 'Y' then
-- execute immediate cRec4.ddl_grants
NULL;
end if;
end loop;
end loop;
end if;
end if;
-- check for individual existing username account
for cRec5 in (select level,
--regexp_substr(nUserName,'[[:alnum:]]+', 1, level) UserName
trim(regexp_substr(nUserName,'[^,]+', 1, level)) UserName
from (select regexp_count(vt1.nUserName,',') + 1 cnt,
nUserName
from (select vUserName nUserName from dual) vt1) vt2
connect by level <= vt2.cnt) loop
vFndUserNameCnt := vFndUserNameCnt + 1;
if vFndUserNameCnt = 1 then
dbms_output.put_line(chr(13));
end if;
select count(*)
into vRowCnt
from dba_users du
where du.username = cRec5.UserName;
if vRowCnt > 0 then
vFndUserNameCnt := vFndUserNameCnt + 1;
if vFndUserNameCnt = 1 then
dbms_output.put_line(chr(13));
dbms_output.put_line('username account found: ');
dbms_output.put_line('------------------------------');
end if;
dbms_output.put_line(cRec5.UserName);
end if;
end loop;
-- check for individual non-existing username account
for cRec6 in (select level,
--regexp_substr(nUserName,'[[:alnum:]]+', 1, level) UserName
trim(regexp_substr(nUserName,'[^,]+', 1, level)) UserName
from (select regexp_count(vt1.nUserName,',') + 1 cnt,
nUserName
from (select vUserName nUserName from dual) vt1) vt2
connect by level <= vt2.cnt) loop
vFndUserNameCnt := vFndUserNameCnt + 1;
if vFndUserNameCnt = 1 then
dbms_output.put_line(chr(13));
end if;
select count(*)
into vRowCnt
from dba_users du
where du.username = cRec6.UserName;
if vRowCnt = 0 then
vNfUserNameCnt := vNfUserNameCnt + 1;
if vNfUserNameCnt = 1 then
dbms_output.put_line(chr(13));
dbms_output.put_line('username account not found: ');
dbms_output.put_line('------------------------------');
end if;
dbms_output.put_line(cRec6.UserName);
-- generate random 10 alphanumeric character
select substr(rStr2,1,rNum2 -1) || '!' || substr(rStr2,rNum2 + 1) rStr3
into vRandomPwd
from (select rNum1, rNum2,
substr(rStr1,1,rNum1 -1) || trunc(dbms_random.value(1,10)) || substr(rStr1,rNum1 + 1) rStr2
from (select trunc(dbms_random.value(1,10)) rNum1,
trunc(dbms_random.value(1,10)) rNum2,
dbms_random.string('a',10) rStr1
from dual));
end if;
end loop;
-- generate new username account for non-existing username account
for cRec7 in (select level,
--regexp_substr(nUserName,'[[:alnum:]]+', 1, level) UserName
trim(regexp_substr(nUserName,'[^,]+', 1, level)) UserName
from (select regexp_count(vt1.nUserName,',') + 1 cnt,
nUserName
from (select vUserName nUserName from dual) vt1) vt2
connect by level <= vt2.cnt) loop
vFndUserNameCnt2 := vFndUserNameCnt2 + 1;
if vFndUserNameCnt2 = 1 then
dbms_output.put_line(chr(13));
end if;
select count(*)
into vRowCnt
from dba_users du
where du.username = cRec7.UserName;
if vRowCnt = 0 then
vNfUserNameCnt2 := vNfUserNameCnt2 + 1;
if vNfUserNameCnt2 = 1 then
dbms_output.put_line(chr(13));
dbms_output.put_line('username account DDL: ');
dbms_output.put_line('------------------------------');
end if;
-- generate random 10 alphanumeric character
select substr(rStr2,1,rNum2 -1) || '!' || substr(rStr2,rNum2 + 1) rStr3
into vRandomPwd
from (select rNum1, rNum2,
substr(rStr1,1,rNum1 -1) || trunc(dbms_random.value(1,10)) || substr(rStr1,rNum1 + 1) rStr2
from (select trunc(dbms_random.value(1,10)) rNum1,
trunc(dbms_random.value(1,10)) rNum2,
dbms_random.string('a',10) rStr1
from dual));
vDynamicSQL := 'create user '|| cRec7.UserName ||' identified by "'|| vRandomPwd ||'"'|| chr(10) ||
'default tablespace user '|| chr(10) ||
'temporary tablespace temp '|| chr(10) ||
'account unlock password expire;';
if vNfUserNameCnt2 > 1 then
dbms_output.put_line(chr(13));
end if;
dbms_output.put_line(vDynamicSQL);
dbms_output.put_line('-- note: '|| cRec7.UserName ||' password '||vRandomPwd);
if vDynamicYN = 'Y' then
-- execute immediate vDynamicSQL;
NULL;
end if;
end if;
end loop;
-- assign role to username account
for cRec8 in (select level,
--regexp_substr(nUserName,'[[:alnum:]]+', 1, level) UserName
trim(regexp_substr(nUserName,'[^,]+', 1, level)) UserName
from (select regexp_count(vt1.nUserName,',') + 1 cnt,
nUserName
from (select vUserName nUserName from dual) vt1) vt2
connect by level <= vt2.cnt) loop
vFndUserNameCnt3 := vFndUserNameCnt3 + 1;
if vFndUserNameCnt3 = 1 then
dbms_output.put_line(chr(13));
end if;
select count(*)
into vRowCnt
from dba_users du
where du.username = cRec8.UserName;
if vRowCnt > 0 then
vNfUserNameCnt3 := vNfUserNameCnt3 + 1;
if vNfUserNameCnt3 = 1 then
dbms_output.put_line(chr(13));
dbms_output.put_line('role grant to username DDL: ');
dbms_output.put_line('------------------------------');
end if;
vDynamicSQL := 'grant '|| vRoleName ||' to '|| cRec8.UserName ||';';
dbms_output.put_line(vDynamicSQL);
if vDynamicYN = 'Y' then
--execute immediate vDynamicSQL;
NULL;
end if;
end if;
end loop;
-- footer line
select to_char(sysdate,'dd-Mon-rrrr hh:mi:ss am') run_end_date
into vEndDate
from dual;
dbms_output.put_line(chr(13));
dbms_output.put_line('+------------------------------------------------------------------------------+');
dbms_output.put_line('End Date: '||vEndDate);
dbms_output.put_line('+------------------------------------------------------------------------------+');
dbms_output.put_line(chr(10));
end;
/
spool off;
notes: I commented out the line that has the execute immediate for testing purpose
example of run:
SQL> @buildUserDDL;
+------------------------------------------------------------------------------+
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Login Name: WTOLENTINO
Database: DBWORLD
Host Name: MYHOST
Start Date: 10-Oct-2018 08:57:13 am
+------------------------------------------------------------------------------+
enter role name: NEW_ROLE
enter schema name: APPS
enter object type when multiple separate by comma
example TABLE, VIEWS, PROCEDURE, FUNCTION, PACKAGE, SEQUENCE, TYPE, MATERIALIZE VIEW
TABLE, VIEW
enter username when multiple separate by comma
example user1, user2, user3
user1, user2
execute immediate [Y/N]: N
...
create role NEW_ROLE;
2 object type entered
object type found:
------------------------------
TABLE
VIEW
DDL grants...
grant select, update, delete, insert on APPS.FND_APP_USER to NEW_ROLE;
grant select, update, delete, insert on APPS.FND_APP_RESPONSIBILITY to NEW_ROLE;
grant select on APPS.FND_APP_USR_RESP_V to NEW_ROLE;
username account not found:
------------------------------
user1
user2
username account DDL:
------------------------------
create user user1 identified by "sOVxFG!yrx"
default tablespace user
temporary tablespace temp
account unlock password expire;
-- note: user1 password sOVxFG!yrx
create user user2 identified by "8Mc!jaGEgi"
default tablespace user
temporary tablespace temp
account unlock password expire;
-- note: user2 password 8Mc!jaGEgi
role grant to username DDL:
------------------------------
grant NEW_ROLE to user1;
grant NEW_ROLE to user2;
+------------------------------------------------------------------------------+
End Date: 10-Oct-2018 08:57:44 am
+------------------------------------------------------------------------------+
SQL>
thanks.
[Updated on: Wed, 10 October 2018 08:32] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Sat Sep 28 07:11:20 CDT 2024
|