How do you change the password of an Oracle Schema

ALTER USER <SCHEMA_NAME> IDENTIFIED BY <PASSWORD>


Example
ALTER USER scott IDENTIFIED BY lion;

Generate Insert Script for a table For Oracle

Generating Insert Script may be a simple task if your are using Toad or any Editor. But If you are working with Oracle PL SQL directly, it IS a tough task. You will have to write script. Below function will help you to generate the insert script of the table. First I will list the function then the implementation of the same

create or replace Function GenInsScript(v_table_name varchar2) return varchar2 As

    b_found boolean:=false;

    v_tempa varchar2(8000);

    v_tempb varchar2(8000);

    v_tempc varchar2(255);

begin

    for tab_rec in (select table_name from user_tables where table_name=upper(v_table_name))

    loop

        b_found:=true;

        v_tempa:='select ''insert into '||tab_rec.table_name||' (';

        for col_rec in (select * from user_tab_columns

                            where

                                table_name=tab_rec.table_name

                            order by

                                column_id)

        loop

            if  col_rec.column_id=1 then

                v_tempa:=v_tempa||'''||chr(10)||''';

            else

                v_tempa:=v_tempa||',''||chr(10)||''';

                v_tempb:=v_tempb||',''||chr(10)||''';

            end if;

            v_tempa:=v_tempa||col_rec.column_name;

            if  instr(col_rec.data_type,'CHAR') > 0 then

                v_tempc:='''''''''||'||col_rec.column_name||'||''''''''';

            elsif instr(col_rec.data_type,'DATE') > 0 then

                v_tempc:='''to_date(''''''||to_char('||col_rec.column_name||',''mm/dd/yyyy hh24:mi'')||'''''',''''mm/dd/yyyy hh24:mi'''')''';

            else

                v_tempc:=col_rec.column_name;

            end if;

            v_tempb:=v_tempb||'''||decode('||col_rec.column_name||',Null,''Null'','||v_tempc||')||''';

        end loop;

        v_tempa:=v_tempa||') values ('||v_tempb||');'' from '||tab_rec.table_name||';';

    end loop;

    if  Not b_found then

        v_tempa:='-- Table '||v_table_name||' not found';

    else

        v_tempa:=v_tempa||chr(10)||'select ''-- commit;'' from dual;';

    end if;

    return v_tempa;

end;

Now to the Implementation

--------------SAMPLE TABLE -------------------------

CREATE TABLE TEST_TABLE

(

  COL1  NUMBER,

  COL2  VARCHAR2(100 BYTE)

)

insert into TEST_TABLE (

COL1,

COL2) values (1,

'test');

insert into TEST_TABLE (

COL1,

COL2) values (2,

'test2');

insert into TEST_TABLE (

COL1,

COL2) values (3,

'test3');

commit;

-----------------------------------

Steps for generating the Script

select GenInsScript('test_table') from dual

This Query would give the below output

select 'insert into TEST_TABLE ('||chr(10)||'COL1,'||chr(10)||'COL2) values ('||decode(COL1,Null,'Null',COL1)||','||chr(10)||''||decode(COL2,Null,'Null',''''||COL2||'''')||');' from TEST_TABLE;

select '-- commit;' from dual;

Executing this Query would result in

insert into TEST_TABLE (

COL1,

COL2) values (1,

'test');

insert into TEST_TABLE (

COL1,

COL2) values (2,

'test2');

insert into TEST_TABLE (

COL1,

COL2) values (3,

'test3');

commit;

Yet Another way of Extracting XML Data

We shall insert the xml into a table and retrieve that


CREATE TABLE xml_test
(
   id          NUMBER DEFAULT 1 ,
   xml_text    VARCHAR2 (4000 BYTE),
   ts_update   DATE DEFAULT SYSDATE
)

SET DEFINE OFF;Insert into XML_TEST
   (ID, XML_TEXT, TS_UPDATE)
 Values
   (2, '<page>
  <id>72054439</id>
  <redactions>
    <redaction>
      <id>0</id>
      <privileges>
        <privilege>
              <id>164</id>
              <Code>ACP</Code>
         </privilege>
               <privilege>       
                  <id>165</id>
                  <Code>AWPD</Code>
               </privilege>
      </privileges>
     </redaction>
     <redaction>
       <id>0</id>
        <privileges>
          <privilege>
                   <id>164</id>
                  <Code>ACP</Code>
            </privilege>
            <privilege>
                  <id>165</id>
                  <Code>AWPD</Code>
            </privilege>
      </privileges>
    </redaction>
  </redactions>
</page>', SYSDATE);
COMMIT;


This Query

SELECT   x1.id page_id, x2.id privilege_id, x2.code privilege_code
  FROM   xml_test t,
         XMLTABLE ('/page' 
                   PASSING xmltype (t.xml_text) 
                   COLUMNS 
                        id NUMBER PATH './id', 
                        xml_privilege XMLTYPE PATH '/page/redactions/redaction/privileges'
                   ) x1,
         XMLTABLE ('/privileges/privilege' 
                    PASSING x1.xml_privilege 
                    COLUMNS
                          id NUMBER PATH './id', 
                          code VARCHAR (5) PATH './Code'
                  ) x2
 WHERE   t.id = 2

would return  PAGE_ID            PRIVILEGE_ID      PRIVILEGE_CODE

72054439      164                         ACP
72054439      165                         AWPD
72054439      164                         ACP
72054439      165                         AWPD

Extract XML Data - ORACLE

How do you extract a tag from a XML


Using XML DATA

Below is a Sample

declare
       xml_data xmltype;
      destination_var varchar2(2000);
      destination_num number;
begin
     xml_data:=xmltype('<tag><char>Some text</char><num>123</num></tag>');
    destination_var := xml_data.extract('/tag/char/text()').getStringVal;
    destination_num := xml_data.extract('/tag/num/text()').getNumberVal;
    dbms_output.put_line('/tag/char ' ||destination_var);
    dbms_output.put_line('/tag/num ' ||destination_num);
end;

Would give the below Output
/tag/char Some text
/tag/num 123

Sample for Having more than one node in XML 

declare
       xml_data xmltype;
      destination_var varchar2(2000);
      destination_num number;
begin
     xml_data:=xmltype('<tag><char>Some text</char><num>123</num><char>Next text</char><num>987</num></tag>');
    destination_var := xml_data.extract('/tag/char/text()').getStringVal;
    destination_num := xml_data.extract('/tag/num/text()').getNumberVal;
    dbms_output.put_line('/tag/char ' ||destination_var);
    dbms_output.put_line('/tag/num ' ||destination_num);
end;

Would give the below output
/tag/char Some textNext text
/tag/num 123987