My style (CSS) for blogs

Here is sample HTML code with CSS inside, which I am planning to use as template for future blog posts. This is initial one, and may be enhanced as needed.

<html>

<head>
<style type="text/css">

pre {
 white-space: pre-wrap;
 font-family: Ubuntu Mono, Courier New, monospace;
 color: #000000;
 background-color: #dddddd;
 border-style:dashed;
 border-width:thin;
 margin-top:1px;margin-bottom:25px;margin-right:50px;margin-left:25px;
 }

body {
 font-family: Droid Sans, Verdana, Sans-Serif;
 color: #000000;
 background-color: #ffffff;
 line-height:125%
 }

</style>
</head>

<body>

<p> This is a sample text paragraph 1. </p>

<p> This is a sample text paragraph 2. </p>

<pre>
This is a sample code line 1
  This is a sample code line 2 with indentation
</pre>

<p> This is a sample text paragraph 3. </p>

</body>

</html>

Loading LOB data using SQL Loader

When there is LOB data at client side, SQL loader can be used to load into Oracle tables. Here are the steps to do it.

Create table with LOB column(s), if it is not already there.

create table tab1
( 
  id   number(5),
  text varchar2(10),
  dt   date,
  doc  clob
);

Here is the CSV file with data. Instead of LOB data, there will be respective file names that contain LOB data.

$ cat data.csv 
1,AAA,2014-05-01,doc1.txt
2,BBB,2014-05-02,doc2.txt

$ cat doc1.txt 
Text of doc1

$ cat doc2.txt 
Text of doc2

The control file of SQL Loader looks like below. There will be FILLER column for file names.

$ cat load.ctl 
LOAD DATA 
INFILE 'data.csv'
   APPEND INTO TABLE tab1
   FIELDS TERMINATED BY ','
   (
    id        CHAR(5),
    text      CHAR(10),
    dt        DATE "YYYY-MM-DD" ":dt",
    file_name FILLER CHAR(100),
    doc       LOBFILE(file_name) TERMINATED BY EOF
    )

The output on screen looks like below when sqlldr command is executed.

$ sqlldr userid=murty/xxxx@mm1 control=load.ctl 

SQL*Loader: Release 12.1.0.1.0 - Production on Tue May 13 10:13:26 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 2

Table TAB1:
  2 Rows successfully loaded.

Check the log file:
  load.log
for more information about the load.

We can verify if the data is loaded successfully

SQL> select * from tab1;

 ID TEXT       DT
---------- ---------- ---------
DOC
--------------------------------------------------------------------------------
  1 AAA       01-MAY-14
Text of doc1

  2 BBB       02-MAY-14
Text of doc2

The same procedure can be used for BLOB data also.

Reference partitioning example

create table orders (
  order_id    number not null,
  order_date  date not null,
  customer_id number not null
 ) tablespace tbs1
partition by range (order_date) (
  partition orders_y2011 values less than (to_date('01-JAN-2012', 'dd-mon-yyyy')) tablespace tbs1,
  partition orders_y2012 values less than (to_date('01-JAN-2013', 'dd-mon-yyyy')) tablespace tbs1,
  partition orders_y2013 values less than (to_date('01-JAN-2014', 'dd-mon-yyyy')) tablespace tbs1
 );
  
alter table orders add constraint orders_pk primary key(order_id) using index tablespace tbs1;

create index orders_ix1 on orders(customer_id) 
tablespace tbs1
local
(
  partition orders_ix1_y2011 tablespace tbs1,
  partition orders_ix1_y2012 tablespace tbs1,
  partition orders_ix1_y2013 tablespace tbs1
);

-- 1. Referential constraint must be defined while creating table.
-- 2. PARTITION BY REFERENCE clause should be used for reference partitioning
--    and it should mention referential constraint name.
create table order_items (
  order_id    number not null,
  product_id  number not null,
  price       number,
  quantity    number,
  constraint order_items_fk foreign key (order_id) references orders
) tablespace tbs1
partition by reference (order_items_fk) (
  partition order_items_y2011 tablespace tbs1,
  partition order_items_y2012 tablespace tbs1,
  partition order_items_y2013 tablespace tbs1
);

alter table order_items add constraint order_items_pk primary key(order_id, product_id) using index tablespace tbs1;

create index order_items_ix1 on order_items(order_id)  
tablespace tbs1 
local
(
  partition order_items_ix1_y2011 tablespace tbs1,
  partition order_items_ix1_y2012 tablespace tbs1,
  partition order_items_ix1_y2013 tablespace tbs1
);
 
-- While adding partition to parent table, we can use 
-- 1. DEPENDENT TABLES clause to specify names of child partitions
-- 2. In UPDATE INDEXES clause, we can give indexes of parent and child tables. 
alter table orders 
  add partition orders_y2014 
     values less than (to_date('01-JAN-2015', 'dd-mon-yyyy')) 
     tablespace tbs1
  dependent tables (
     order_items (partition order_items_y2014 tablespace tbs1) 
     )
  update indexes (
     orders_ix1 (partition orders_ix1_y2014 tablespace tbs1),
     order_items_ix1 (partition order_items_ix1_y2014 tablespace tbs1)
     )
;

-- Dropping parent partition will drop child table partition(s), and corresponding index partitions
alter table orders drop partition orders_y2011 update global indexes;

Decrypting Oracle database DB link password (Versions <= 11.2.0.2)

-- Run as SYS user
set serveroutput on

declare
 db_link_password varchar2(100);
begin

 db_link_password := '0560A31A6EFEC902B9286FFC981F4C9A92F8470D406ADEA670';

 dbms_output.put_line ('Plain password: ' ||
                          -- Convert RAW to varchar2
                          utl_raw.cast_to_varchar2 (

                              dbms_crypto.decrypt (

                               -- from 19th char to end, it is encrypted source
                               substr (db_link_password, 19) ,

                               -- Type of encryption
                               dbms_crypto.DES_CBC_PKCS5 ,

                               -- From 3rd to 16th char, it is key
                               substr (db_link_password, 3, 16)

                              )
                          )
                      );

end;
/

Plain password: Forget12

Dropping Database link in another schema

Oracle database does not allow to drop database link owned by another user. As a DBA user, to drop database link in another schema, here are steps to follow.

1. Create a procedure in another schema in which database link exists
2. The procedure should have statement to drop database link
3. Execute the procedure
4. Drop the procedure

These steps can be written as PL/SQL anonymous block like below. 

declare

          schema_name varchar2(10) := 'MURTY';

     -- db_link_name should be same as value of DB_LINK column in DBA_DB_LINKS view

     db_link_name varchar2(100) := 'remote.xyz.com';

     random_proc_name varchar2(100);

     cnt number;

begin

     schema_name := upper(schema_name);

     db_link_name := upper(db_link_name);

        -- A random procedure to make sure the name of procedure should not conflict with existing procedures

     random_proc_name := schema_name || '.drdl_' || dbms_random.string('U', 20);

     select count(*) into cnt from dba_db_links where owner=schema_name and db_link=db_link_name;

 

           if ( cnt != 1 ) then

          raise_application_error(-20001, 'DB Link does not exist');

     end if;

    

          -- 1. Create a procedure in another schema in which database link exists

          execute immediate

               ' create procedure ' || random_proc_name || ' as ' ||

               ' begin ' ||

               -- 2. The procedure should have statement to drop database link

               ' execute immediate ''drop database link ' || db_link_name ||' ''; ' ||

               ' end;';

 

           begin

          -- 3. Execute the procedure

               execute immediate 'begin ' || random_proc_name || '; end;';

          exception when others then

                    execute immediate 'drop procedure ' || random_proc_name;

                     raise;

          end;

           -- 4. Drop the procedure

           execute immediate 'drop procedure ' || random_proc_name;

end;

/