PL/SQL program to get metadata by transforming

set serveroutput on

set linesize 32767

declare

  handle        number;

  ddl_handle    number;

  modify_handle number;

  fetched_ddl   clob;

  owner         varchar2(100);

  table_name    varchar2(100);

  ts_arg        varchar2(10);

begin

  owner := 'MURTY';

  table_name := 'TAB1';

  -- Do we need tablespace names in table and index DDLs?

  ts_arg := upper('YES');

 

  handle := dbms_metadata.open('TABLE');

  dbms_metadata.set_filter(handle, 'NAME', table_name);

  dbms_metadata.set_filter(handle, 'SCHEMA', owner);

 

  modify_handle := dbms_metadata.add_transform(handle, 'MODIFY');

  -- Instead of original schema, we can remap to other schema, 

  -- blank '' to remove schema name

  dbms_metadata.set_remap_param(modify_handle, 'REMAP_SCHEMA', owner, '');

 

  ddl_handle := dbms_metadata.add_transform(handle, 'DDL');

  -- PK and FKs will be generated as ALTER, not in table definitions

  dbms_metadata.set_transform_param(ddl_handle, 'CONSTRAINTS_AS_ALTER', true);

  dbms_metadata.set_transform_param(ddl_handle, 'SQLTERMINATOR', true);

 

  if(ts_arg = 'YES')

  then

   dbms_metadata.set_transform_param(ddl_handle, 'SEGMENT_ATTRIBUTES', true);

  else

   dbms_metadata.set_transform_param(ddl_handle, 'SEGMENT_ATTRIBUTES', false);

  end if;

 

  -- We do not need INITIAL, NEXT extent in DDLs

  dbms_metadata.set_transform_param (ddl_handle, 'STORAGE', false);

 

  fetched_ddl := dbms_metadata.fetch_clob(handle);

  dbms_output.put_line(replace(

                               replace(fetched_ddl, '"', ''), -- Remove quotes in DDLs

                               ';',

  -- Extra blank line between table and constraint definitions

                               ';' || chr(10)  

                              )

                      );

  -- Now dependent INDEX objects

  handle := dbms_metadata.open('INDEX');

  dbms_metadata.set_filter(handle, 'BASE_OBJECT_NAME', table_name);

  dbms_metadata.set_filter(handle, 'BASE_OBJECT_SCHEMA', owner);

 

  modify_handle := dbms_metadata.add_transform(handle, 'MODIFY');

  dbms_metadata.set_remap_param(modify_handle, 'REMAP_SCHEMA', owner, '');

 

  ddl_handle := dbms_metadata.add_transform(handle, 'DDL');

  dbms_metadata.set_transform_param(ddl_handle, 'SQLTERMINATOR', true);

 

  if(ts_arg = 'YES')

  then

   dbms_metadata.set_transform_param(ddl_handle, 'SEGMENT_ATTRIBUTES', true);

  else

   dbms_metadata.set_transform_param(ddl_handle, 'SEGMENT_ATTRIBUTES', false);

  end if;

 

  dbms_metadata.set_transform_param (ddl_handle, 'STORAGE', false);

  -- Get one index at a time.

  fetched_ddl := replace(dbms_metadata.fetch_clob(handle), '"', '');

  while (fetched_ddl is not null) loop

    dbms_output.put_line(fetched_ddl);

    fetched_ddl := replace(dbms_metadata.fetch_clob(handle), '"', '');

  end loop;

end;

/

Converting list of values to result of a query

When there is list of values that have to be converted to a result of a query, here is the way.

SQL> select column_value from table(dbmsoutput_linesarray('AAA','BBB'));

COLUMN_VALUE

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

AAA

BBB

Now the above query can be used as a subquery as if the values 'AAA', 'BBB' are result of a query (though we have given them like a list).

Note: 

 dbmsoutput_linesarray is a varray(2147483647) of varchar2(32767) that comes with database by default in SYS schema. And it can be used by any database user.

Finding average balance from HDFC bank statement

Here is a perl script which parses a text delimeted account statement and generates an SQL script that gives average balance maintained for the statement period. If the statement is for a quarter, it gives average quarterly balance (AQB).

#!/usr/bin/perl

# Usage: perl gen_aqb_calc.pl < stmt_file.txt > aqb_calc.sql

$seq = 1;

print "set feedback off;\n\n";

print "create table xcts(sn number, dt date, bal number(20,2));\n\n";

while(<stdin>)

{

 next unless /^\s*\d\d\/\d\d\/\d\d/; # Skip heading and blank lines

 @fields = split(',');

 # Example format of downloaded statement is

 # 20/10/11  ,POS 4214 BHARTI AIRTEL LI  ,20/10/11 , 444.00  , 0.00 ,1011413 ,  23730.59 

 $date = $fields[0];

 $closing_bal = $fields[6];

 $date =~ s/\s//g;

 $closing_bal =~ s/\s//g;

 print "insert into xcts values ($seq, to_date('$date', 'dd/mm/yy'), $closing_bal); \n";

 $seq++;

}

print "\ncommit;\n";

print "

with closing_balances as(select x1.dt, x1.bal

                  from xcts x1,

                       (select max(sn) maxsn, dt from xcts group by dt) x2

                 where x1.sn = x2.maxsn

                 order by x1.dt)

 select round( sum(t1.bal * nvl(t2.dt - t1.dt, 1)) / (max(t1.dt) - min(t1.dt) + 1) , 2 ) average_balance

  from (select rownum rn, dt, bal

          from closing_balances) t1,

       (select rownum - 1 rn, dt

          from closing_balances) t2

 where t1.rn = t2.rn(+);

";

print "\ndrop table xcts purge;\n";

The perl script generates below SQL script. When we execute it against Oracle database, it gives average balance.

 

set feedback off;

create table xcts(sn number, dt date, bal number(20,2));

insert into xcts values (1, to_date('01/10/11', 'dd/mm/yy'), 45651.08);

-- Some more insert statements here, removed from clarity

commit;

with closing_balances as(select x1.dt, x1.bal

                  from xcts x1,

                       (select max(sn) maxsn, dt from xcts group by dt) x2

                 where x1.sn = x2.maxsn

                 order by x1.dt)

 select round( sum(t1.bal * nvl(t2.dt - t1.dt, 1)) / (max(t1.dt) - min(t1.dt) + 1) , 2 ) average_balance

  from (select rownum rn, dt, bal

          from closing_balances) t1,

       (select rownum - 1 rn, dt

          from closing_balances) t2

 where t1.rn = t2.rn(+);

drop table xcts purge;

udev rules to change ownership of LVM partition (RHEL/OEL 5)

When a logical volume is used for ASM disk, the logical volume should be given ownership grid infrastructure user and group. This can be set simply by adding chown commands /etc/rc.local file (a dirty work around). The same thing can be accomplished even by using udev rules like below.

In this example, we are trying to use logical volume vol05 in volume group vg1 for grid infrastructure.

Add below line to /etc/udev/rules.d/91-lvm- asmdisk.rules (entire content in single line)

SUBSYSTEM=="block", KERNEL=="dm-*", ACTION=="add|change", PROGRAM="/sbin/dmsetup info -c --noheadings -o name -j %M -m %m", RESULT=="vg1-vol05", RUN+="/bin/chown grid:asmadmin /dev/mapper/vg1-vol05"

Deactivate and activate logical volume to verify if udev rule is working fine.

# lvchange -an vg1/vol05
# lvchange -ay vg1/vol05

 

Now ownership is changed for logical volume.

# ls -l /dev/mapper/vg1-vol05
brw-rw---- 1 grid asmadmin 253, 4 Dec 14 22:33 /dev/mapper/vg1-vol05