TRIM function [message #672291] |
Wed, 10 October 2018 08:20 |
wtolentino
Messages: 404 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 |
Solomon Yakobson
Messages: 3275 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 |
Solomon Yakobson
Messages: 3275 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 #674062 is a reply to message #672291] |
Tue, 01 January 2019 00:12 |
|
Barbara Boehmer
Messages: 9097 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.
|
|
|