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: 303
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: 2968
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: 2968
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 messageGo to next message
wtolentino
Messages: 303
Registered: March 2005
Senior Member
that works thank so much.
Re: TRIM function [message #674062 is a reply to message #672291] Tue, 01 January 2019 00:12 Go to previous message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Apparently dbms_metadata.get_ddl adds chr(10) in front of the chr(32) spaces, so you could use ltrim to remove those as shown below.

SCOTT@orcl_12.1.0.2.0> select ltrim(replace(replace(substr(dbms_metadata.get_ddl('USER',du.username),1,
2 instr(dbms_metadata.get_ddl('USER',du.username),'''',1,2)),'CREATE','ALTER'),'"',null),chr(10)||chr(32)) ||';'
3 metadata_ddl
4 from dba_users du
5 where du.username like 'APP%'
6 order by du.username;

METADATA_DDL
----------------------------------------------------------------------------------------------------
ALTER USER APPQOSSYS IDENTIFIED BY VALUES 'S:6FF1A66922D5C9D588635B4F5E08632680ADF184429A3D61A34F045
19912;H:3F1C2D984693AB3A4D88B34DED6A65DF;T:C5C50CF5BE5560A9BC1FCC076D66E1821DCD398BE88E63C15C6AAA1D6
70AD7667DC69DE544738FFB55FB0F30C0677CC4628E9977F8D7E6B0C3645E3635A61A00C7B99B02F698FAACD0D0D8C507EF2
FC4;519D632B7EE7F63A';

1 row selected.
Previous Topic: Transactions than can be rejected, temporary tables or not?
Next Topic: Substring and Instring in combination
Goto Forum:
  


Current Time: Fri Jul 10 05:09:35 CDT 2020