Home » SQL & PL/SQL » 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 Go to next message
wtolentino
Messages: 284
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 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
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 #672015 is a reply to message #672014] Thu, 27 September 2018 14:22 Go to previous messageGo to next message
wtolentino
Messages: 284
Registered: March 2005
Senior Member
thanks. right it will execute but for clarity i would like it in that format.
Re: SQL*Plus output problem [message #672016 is a reply to message #672015] Thu, 27 September 2018 14:28 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You want to have it where?

Re: SQL*Plus output problem [message #672017 is a reply to message #672016] Thu, 27 September 2018 15:04 Go to previous messageGo to next message
wtolentino
Messages: 284
Registered: March 2005
Senior Member
for example in this format
create user user1 identified by "lax4g!VCdm"
default tablespace user
temporary tablespace temp
account unlock password expire;

Re: SQL*Plus output problem [message #672018 is a reply to message #672017] Thu, 27 September 2018 15:06 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

But where? In a file? On screen?

Re: SQL*Plus output problem [message #672019 is a reply to message #672018] Thu, 27 September 2018 20:13 Go to previous messageGo to next message
wtolentino
Messages: 284
Registered: March 2005
Senior Member
both if that is possible but in a file is the most important. i need to do it on SQL*Plus and by using a simple anonymous PL/SQL block.

thanks.
Re: SQL*Plus output problem [message #672020 is a reply to message #672019] Fri, 28 September 2018 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I found one of your problem:
set linesize 120;
Set it bigger:
SQL> set lines 1000
SQL> declare
  2    vDynamicSQL varchar2(2000);
  3  begin
  4
  5    vDynamicSQL := 'create user user1 identified by "lax4g!VCdm"' || chr(10) ||
  6                   'default tablespace user '|| chr(10) ||
  7                   'temporary tablespace temp ' || chr(10) ||
  8                   'account unlock password expire;';
  9
 10    dbms_output.put_line(vDynamicSQL);
 11
 12  end;
 13  /
create user user1 identified by "lax4g!VCdm"
default tablespace user
temporary tablespace temp
account unlock password expire;

PL/SQL procedure successfully completed.
But why use a PL/SQL an not plain SQL?
SQL> select 'create user user1 identified by "lax4g!VCdm"' || chr(10) ||
  2  'default tablespace user '|| chr(10) ||
  3  'temporary tablespace temp ' || chr(10) ||
  4  'account unlock password expire;'
  5  from dual;
'CREATEUSERUSER1IDENTIFIEDBY"LAX4G!VCDM"'||CHR(10)||'DEFAULTTABLESPACEUSER'||C
------------------------------------------------------------------------------
create user user1 identified by "lax4g!VCdm"
default tablespace user
temporary tablespace temp
account unlock password expire;
Re: SQL*Plus output problem [message #672075 is a reply to message #672020] Mon, 01 October 2018 10:27 Go to previous messageGo to next message
Bill B
Messages: 1917
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 #672090 is a reply to message #672075] Tue, 02 October 2018 10:04 Go to previous messageGo to next message
wtolentino
Messages: 284
Registered: March 2005
Senior Member
thank you all increasing the linesize greater than 120 works.
Re: SQL*Plus output problem [message #672091 is a reply to message #672020] Tue, 02 October 2018 10:12 Go to previous messageGo to next message
wtolentino
Messages: 284
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 #672093 is a reply to message #672091] Tue, 02 October 2018 12:18 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
i had to use some select into clause to get some system and other info
What can't be done with a simple SELECT?

Quote:
dbms_output.put_line(chr(13));
This is dbms_output.new_line.

Re: SQL*Plus output problem [message #672109 is a reply to message #672093] Wed, 03 October 2018 07:05 Go to previous messageGo to next message
Bill B
Messages: 1917
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 Go to previous messageGo to next message
wtolentino
Messages: 284
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 #672118 is a reply to message #672113] Wed, 03 October 2018 07:44 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No this is not true.
Put the complete specification and we will show you.

Re: SQL*Plus output problem [message #672122 is a reply to message #672118] Wed, 03 October 2018 07:54 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And you can't prompt from input inside a PL/SQL block.

Re: SQL*Plus output problem [message #672288 is a reply to message #672118] Wed, 10 October 2018 08:07 Go to previous message
wtolentino
Messages: 284
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

Previous Topic: DBMS_OUTPUT not showing lines when procedure executes OK
Next Topic: Disappearing Days
Goto Forum:
  


Current Time: Sun Dec 08 16:09:32 CST 2019