Reset Password By Values in 12c

select u.username,
'alter user '||u.username||' identified by values '''||s.spare4||''';' cmd
from dba_users u
join sys.user$ s
on u.user_id = s.user#
where u.username = upper('user1');

French characters and CLOB

I stored the import file in BLOB and then convert the BLOB to CLOB and process it.

However, today file include a french letter and throw off everything.

I am using something like this to convert the BLOB to CLOB.

dbms_lob.converttoclob(dest_lob     => l_clob
,src_blob     => p_blob
,amount       => dbms_lob.lobmaxsize
,dest_offset  => l_dest_offsset
,src_offset   => l_src_offsset
,blob_csid    => dbms_lob.default_csid
,lang_context => l_lang_context
,warning      => l_warning);

The problem here is the blob_csid is wrong for the file.To determine the correct blob_csid, you can first save the file to the file system and run

C:\>lcsscan file=dent_47268_cexp.txt

Language and Character Set File Scanner v2.0
(c) Copyright 2003, 2004 Oracle Corporation.  All rights reserved.

dent_47268_cexp.txt:    FRENCH  WE8ISO8859P1;

Once I determined the correct language, I change my function to this and everything is fine again.

dbms_lob.converttoclob(dest_lob     => l_clob
,src_blob     => p_blob
,amount       => dbms_lob.lobmaxsize
,dest_offset  => l_dest_offsset
,src_offset   => l_src_offsset
,blob_csid    => NLS_CHARSET_ID ('WE8ISO8859P1'),
,lang_context => l_lang_context
,warning      => l_warning);

Handling XML

This is how you import the XML from file system into oracle table as a xmltype field.

INSERT INTO pc_xmltable (my_id, my_xml)
VALUES (2, xmltype (BFILENAME ('ECHELON_DIR', 'xml.txt'), NLS_CHARSET_ID ('AL32UTF8')));

These are some examples as to how you can extract records from the XML

SELECT xt.*
FROM pc_xmltable pc,
XMLTABLE ('/PaymentOutput/DocumentInfo' PASSING pc.my_xml COLUMNS filecreationdate VARCHAR2 (20) PATH 'FileCreationDate') xt
WHERE pc.my_id = 2;

SELECT xt.*
FROM pc_xmltable pc,
XMLTABLE (
'/PaymentOutput/EOBDetails/SBClaimPredLines'
PASSING pc.my_xml
COLUMNS claimlinesequencenumber NUMBER (10) PATH 'ClaimLineSequenceNumber',
submittedclaimline VARCHAR2 (20) PATH 'SubmittedClaimLine',
certificatenumber VARCHAR2 (20) PATH 'CertificateNumber',
dateofservice DATE PATH 'DateOfService',
totalsubmittedamount NUMBER (14, 4) PATH 'TotalSubmittedAmount',
totalamountpaid NUMBER (14, 4) PATH 'TotalAmountPaid') xt
WHERE pc.my_id = 2;

Script to create schema and grant proper security from schema owner

Assume the schema is ACCDB and the new schema is ACCDBP1.

This script will create the new schema, setup synonym and grant security to it.

CREATE USER accdbp1 IDENTIFIED BY passwordxyz
 DEFAULT TABLESPACE ACC_DATA
 TEMPORARY TABLESPACE TEMP
 QUOTA UNLIMITED ON ACC_DATA;

 GRANT "CONNECT" TO accdbp1;

ALTER USER accdbp1 DEFAULT ROLE "CONNECT";

 SELECT 'DROP SYNONYM accdbp1.'
 || object_name
 || ';'
 || CHR (13)
 || CHR (10)
 || 'Create synonym accdbp1.'
 || object_name
 || ' for accdb.'
 || object_name
 || ';'
 || CHR (13)
 || CHR (10)
 || CASE
 WHEN object_type IN ('TABLE', 'VIEW')
 THEN
 'grant select,update,insert,delete on accdb.' || object_name || ' to accdbp1;'
 WHEN object_type IN ('SEQUENCE')
 THEN
 'grant select on accdb.' || object_name || ' to accdbp1;'
 WHEN object_type IN ('PROCEDURE', 'PACKAGE', 'FUNCTION')
 THEN
 'grant execute on accdb.' || object_name || ' to accdbp1;'
 END
 syn_sql
 FROM all_objects
 WHERE object_type IN ('TABLE',
 'VIEW',
 'SEQUENCE',
 'PROCEDURE',
 'PACKAGE',
 'FUNCTION')
 AND object_name NOT LIKE 'SEB%'
 AND object_name NOT LIKE '%\_H' ESCAPE '\'
 AND object_name NOT LIKE '%\_AUDIT' ESCAPE '\'
 AND ( (object_type = 'TABLE' AND data_object_id IS NOT NULL) OR object_type <> 'TABLE')
 AND owner = 'ACCDB'
ORDER BY object_type, object_name;