Home » SQL & PL/SQL » SQL & PL/SQL » TRIM function (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0)
TRIM function [message #672291] Wed, 10 October 2018 08:20 Go to next message
wtolentino
Messages: 234
Registered: March 2005
Senior Member
i have this SQL that retrieves the metadata of the user account. there always results in a whitespace before the string. i want to get rid of the whitespace before the string. so i tried the function TRIM and LTRIM but it won't.

select trim(replace(replace(substr(dbms_metadata.get_ddl('USER',du.username),1,
            instr(dbms_metadata.get_ddl('USER',du.username),'''',1,2)),'CREATE','ALTER'),'"',null)) ||';'
       metadata_ddl
  from dba_users du
 where du.username like 'APPS%' 
order by du.username;

output
   ALTER USER APPSREAD IDENTIFIED BY VALUES '6572B2OOC13A2HK2C';
   ALTER USER APPSUSER IDENTIFIED BY VALUES '46B5LH325DED00HBY';

please help.

thanks,
warren
Re: TRIM function [message #672292 is a reply to message #672291] Wed, 10 October 2018 08:43 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
select  'ALTER' ||
        regexp_substr(
                      dbms_metadata.get_ddl('USER',du.username),
                      '^\s+CREATE(.+IDENTIFIED BY VALUES\s+''[^'']+'')',
                      1,
                      1,
                      null,
                      1
                     ) || ';' metadata_ddl
  from  dba_users du
  where du.username like 'APPS%'
/

SY.
Re: TRIM function [message #672293 is a reply to message #672292] Wed, 10 October 2018 08:45 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
Or using Q-literals:

select  'ALTER' ||
        regexp_substr(
                      dbms_metadata.get_ddl('USER',du.username),
                      Q'!^\s+CREATE(.+IDENTIFIED BY VALUES\s+'[^']+')!',
                      1,
                      1,
                      null,
                      1
                     ) || ';' metadata_ddl
  from  dba_users du
  where du.username like 'APPS%'
/

SY.
Re: TRIM function [message #672297 is a reply to message #672293] Wed, 10 October 2018 09:17 Go to previous message
wtolentino
Messages: 234
Registered: March 2005
Senior Member
that works thank so much.
Previous Topic: https://community.oracle.com/message/14958599#14958599
Next Topic: Return Rows when no data exists
Goto Forum:
  


Current Time: Thu Oct 18 05:28:43 CDT 2018