zcat in Bash

zcat is a command to display the contents of a file that is zipped using compress in unix.
Eg:
zcat log
The above commad looks for a file of the format log.Z in the directory and decompresses it and then displays to the standard output.

PL\SQL Program to get the counts of all the tables in a schema.

Here is a small PL\SQL program to get the number of rows in all the tables accessible from a scheam. It uses dyanmic SQL in the program.

*************************************************************************************

Rem This program get the number of rows in each table that is accessible from a schema.

SET FEEDBACK OFF;
SET TERM OFF
SET ECHO OFF;
SET SERVEROUTPUT ON size 1000000
SPOOL countall.csv
--CREATE OR REPLACE PROCEDURE countall AS
DECLARE
t_c1_tname all_tables.table_name%TYPE;
t_owner_name all_tables.owner%TYPE;
t_command varchar2(200);
t_cid integer;
t_total_records number(10);
stat integer;
row_count integer;

CURSOR c1 IS SELECT owner , table_name
FROM all_tables
WHERE owner = user
ORDER BY table_name;
BEGIN
DBMS_OUTPUT.PUT_LINE('OWNER,TABLE NAME,NUMBER OF ROWS');
FOR r_c1 IN c1
LOOP
t_command := 'SELECT COUNT(0) FROM '||r_c1.owner||'.'||r_c1.table_name;
t_cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(t_cid,t_command,dbms_sql.native);
DBMS_SQL.DEFINE_COLUMN(t_cid,1,t_total_records);
stat := DBMS_SQL.EXECUTE(t_cid);
row_count := DBMS_SQL.FETCH_ROWS(t_cid);
DBMS_SQL.COLUMN_VALUE(t_cid,1,t_total_records);
DBMS_OUTPUT.PUT_LINE(r_c1.owner||','||r_c1.table_name||','||t_total_records);
DBMS_SQL.CLOSE_CURSOR(t_cid);
END LOOP;
END;
/
SPOOL OFF;
SET TERM ON;
SET ECHO ON;

*************************************************************************************

Updating from Two Tables

We can update data from one table using data from another table. The following set of statements explains how it is done. One requirement fro updates like this is that both the tables should be key preserved.

SQL> create table tab1 ( col1 number , col2 varchar(20), constraint pk_col1 primary key (col1) );

Table created.

SQL> insert into tab1 values (1 , 'nirmala');

1 row created.

SQL> insert into tab1 values (2 , 'rahul');

1 row created.

SQL> insert into tab1 values (3 , 'rahul');

1 row created.

SQL> insert into tab1 values (4 , 'kumar');

1 row created.

SQL> insert into tab1 values (5 , 'pragati');

1 row created.

SQL> commit;

Commit complete.

SQL> create table tab2 as select * from tab1;

Table created.

SQL> select * from tab1;
cls COL1 COL2
---------- --------------------
1 nirmala
2 rahul
3 rahul
4 kumar
5 pragati

SQL> select * from tab1;
cls COL1 COL2
---------- --------------------
1 nirmala
2 rahul
3 rahul
4 kumar
5 pragati

SQL> update tab2 set col2 = 'chaitu' where col1 = 2;

1 row updated.

SQL> select * from tab2;
cls COL1 COL2
---------- --------------------
1 nirmala
2 chaitu
3 rahul
4 kumar
5 pragati

SQL> update (select a.col2 target, b.col2 source from tab1 a, tab2 b where a.col1 = b.col1) set target = source;
update (select a.col2 target, b.col2 source from tab1 a, tab2 b where a.col1 = b.col1) set target = source
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table


SQL> create unique index idx_tab2 on tab2 (col1);

Index created.

SQL> update (select a.col2 target, b.col2 source from tab1 a, tab2 b where a.col1 = b.col1) set target = source;

5 rows updated.

SQL> commit;

Commit complete.

SQL> select * from tab1;
cls COL1 COL2
---------- --------------------
1 nirmala
2 chaitu
3 rahul
4 kumar
5 pragati