Oracle Apps: Reset FND User password from backend

Sample code to reset FND_USER password from backend

 declare   
      v_user_name varchar2(30)  := upper('AJ_TEST');   
      v_password  varchar2(30)  := 'johnytips'; -- new password  
      v_status    boolean;   
 begin   
      v_status := fnd_user_pkg.changepassword (   
                                    username    => v_user_name   
                                   ,newpassword => v_password );   
      if v_status = true then   
           dbms_output.put_line ('Password for '||v_user_name || ' got changed successfully !!!'); 
           commit;  
      else   
           dbms_output.put_line ('Unable to reset password  for '|| v_user_name ||' : '||SUBSTR(SQLERRM, 1, 1000));   
      end if;  
 end;  

Related blog: Oracle Apps: Create User and Add Responsibility from backend


Feel free to point out if anything is missing/wrong in this blog.

PL/SQL: Extract XML Data using SQL

Few examples to extract xml data in a SQL query.

#1. Extract Master Child xml using xmltable
XMLTABLE

 select header_row.department_id
       ,header_row.department_name  
       ,child_row.employee_id  
       ,child_row.first_name
       ,child_row.last_name  
 from XMLTABLE(
               XMLNAMESPACES(default 'http://johnytips.blogspot.com.au/ns/department')  
              ,'/department'  
          PASSING xmltype(  
              '<department xmlns="http://johnytips.blogspot.com.au/ns/department" id="1">  
                    <department_name>Research</department_name>  
                    <employees>  
                         <employee id="1">  
                              <first_name>ANOOP</first_name>  
                              <last_name>JOHNY</last_name>  
                         </employee>  
                         <employee id="2">  
                              <first_name>ANISH</first_name>  
                              <last_name>JOHNY</last_name>  
                         </employee>  
                    </employees>  
               </department>')  
          COLUMNS   
               department_id   VARCHAR2(30) PATH '@id',  
               department_name VARCHAR2(10) PATH 'department_name',  
               child_rows XMLTYPE PATH 'employees'  
              ) header_row  
     ,XMLTABLE(
               XMLNAMESPACES(default 'http://johnytips.blogspot.com.au/ns/department')  
              ,'/employees/employee'  
          PASSING header_row.child_rows  
          COLUMNS  
               employee_id NUMBER PATH '@id',   
               first_name  VARCHAR2(30) PATH 'first_name',  
               last_name   VARCHAR2(30) PATH 'last_name'
              ) child_row;  

#2. EXTRACTVALUE

 with t as  
  (select   
          xmltype('<employee>  
                        <employee_id>1</employee_id>  
                        <first_name>ANOOP</first_name>  
                        <last_name>JOHNY</last_name>  
                   </employee>') str   
   from dual)  
 select extractvalue(str,'/employee/employee_id') EMPLOYEE_ID  
       ,extractvalue(str,'/employee/first_name')  FIRST_NAME  
       ,extractvalue(str,'/employee/last_name')   LAST_NAME  
 from t;  

#3.EXTRACT

 with t as  
  (select   
          xmltype('<employee>  
                        <employee_id>1</employee_id>  
                        <first_name>ANOOP</first_name>  
                        <last_name>JOHNY</last_name>  
                   </employee>') str   
   from dual)  
 select extract(str,'/employee/employee_id/text()') EMPLOYEE_ID  
       ,extract(str,'/employee/first_name/text()')  FIRST_NAME  
       ,extract(str,'/employee/last_name/text()')   LAST_NAME  
 from t;  

#4. To extract with the xml tag

 with t as  
  (select   
          xmltype('<employee>  
                        <employee_id>1</employee_id>  
                        <first_name>ANOOP</first_name>  
                        <last_name>JOHNY</last_name>  
                   </employee>') str   
   from dual)  
 select extract(str,'/employee/employee_id') EMPLOYEE_ID  
       ,extract(str,'/employee/first_name')  FIRST_NAME  
       ,extract(str,'/employee/last_name')   LAST_NAME  
 from t;  

#5. Prior to Oracle Database 10g Release 2 using xmlsequence
XMLSEQUENCE

 select extractvalue(column_value, '/employee/first_name') "FIRST_NAME"  
       ,extractvalue(column_value, '/employee/last_name')  "LAST_NAME"  
 from table(xmlsequence(xmltype('<employees>  
                                      <employee>  
                                           <employee_id>1</employee_id>  
                                           <first_name>ANOOP</first_name>  
                                           <last_name>JOHNY</last_name>  
                                      </employee>  
                                      <employee>   
                                           <employee_id>2</employee_id>  
                                           <first_name>ANISH</first_name>  
                                           <last_name>JOHNY</last_name>  
                                      </employee>  
                                 </employees>').extract('/employees/employee')));  

Feel free to point out if anything is missing/wrong in this blog