PL/SQL ORACLE

Calling the SQL COUNT Function in PL/SQL
----------------------------------------

DECLARE

job_count NUMBER;

emp_count NUMBER;

BEGIN

SELECT Count(DISTINCT job_id)

INTO job_count

FROM employees;



SELECT Count(* )

INTO emp_count

FROM employees;

END;

/


Substituting PL/SQL Variables
------------------------------

CREATE TABLE employees_temp
AS
SELECT first_name,
last_name
FROM employees;



DECLARE

x VARCHAR2(20) := 'my_first_name';

y VARCHAR2(25) := 'my_last_name';

BEGIN

INSERT INTO employees_temp

VALUES (x,

y);



UPDATE employees_temp

SET last_name = x

WHERE first_name = y;



DELETE FROM employees_temp

WHERE first_name = x;



COMMIT;

END;

/


Checking SQL%ROWCOUNT After an UPDATE
--------------------------------------

CREATE TABLE employees_temp
AS
SELECT *
FROM employees;



BEGIN

UPDATE employees_temp

SET salary = salary * 1.05

WHERE salary < 5000;



dbms_output.Put_line('Updated '

||SQL%ROWCOUNT

||' salaries.');

END;

/



Output :-

Updated 49 salaries.

PL/SQL procedure successfully completed.


Data Manipulation With PL/SQL
---------------------------------

CREATE TABLE employees_temp
AS
SELECT employee_id,
first_name,
last_name
FROM employees;



DECLARE

emp_id employees_temp.employee_id%TYPE;

emp_first_name employees_temp.first_name%TYPE;

emp_last_name employees_temp.last_name%TYPE;

BEGIN

INSERT INTO employees_temp

VALUES (299,

'Bob',

'Henry');



UPDATE employees_temp

SET first_name = 'Robert'

WHERE employee_id = 299;



DELETE FROM employees_temp

WHERE employee_id = 299

RETURNING first_name,last_name INTO emp_first_name,emp_last_name;



COMMIT;



dbms_output.Put_line(emp_first_name

||' '

||emp_last_name);

END;

/



Output :-

Robert Henry

PL/SQL procedure successfully completed.



Using BULK COLLECT With a SELECT INTO Statement
-------------------------------------------------
You can use the BULK COLLECT clause with a SELECT INTO or FETCH statement to retrieve a set of rows into a collection of records.


DECLARE

TYPE employeeset IS TABLE OF employees%ROWTYPE;

underpaid EMPLOYEESET; -- Holds set of rows from EMPLOYEES table.

CURSOR c1 IS

SELECT first_name,

last_name

FROM employees;

TYPE nameset IS TABLE OF c1%ROWTYPE;

some_names NAMESET; -- Holds set of partial rows from EMPLOYEES table.



BEGIN

-- With one query, we bring all the relevant data into the collection of records.

SELECT *

BULK COLLECT INTO underpaid

FROM employees

WHERE salary < 5000

ORDER BY salary DESC;



-- Now we can process the data by examining the collection, or passing it to

-- a separate procedure, instead of writing a loop to FETCH each row.

dbms_output.Put_line(underpaid.COUNT

||' people make less than 5000.');



FOR i IN underpaid.FIRST.. underpaid.LAST LOOP

dbms_output.Put_line(Underpaid(i).last_name

||' makes '

||Underpaid(i).salary);

END LOOP;



-- We can also bring in just some of the table columns.

-- Here we get the first and last names of 10 arbitrary employees.

SELECT first_name,

last_name

BULK COLLECT INTO some_names

FROM employees

WHERE ROWNUM < 11;



FOR i IN some_names.FIRST.. some_names.LAST LOOP

dbms_output.Put_line('Employee = '

||Some_names(i).first_name

||' '

||Some_names(i).last_name);

END LOOP;

END;

/

How to Find duplicate copies of files Using fdupes

FDupes uses md5sums and then a byte by byte comparison to find
duplicate files within a set of directories. It has several useful options
including recursion.
Install fdupes in ubuntu
--------------------------
sudo aptitude install fdupes
----------------------------

This will install all the required packages for fdupes
Using fdupes

Fdupes syntax
--------------
-r --recurse - include files residing in subdirectories
-s --symlinks - follow symlinked directories
-H --hardlinks - normally, when two or more files point to the same disk area they are treated as nonn-duplicates; this
option will change this behavior
-n --noempty - exclude zero-length files from consideration
-f --omitfirst - omit the first file in each set of matches
-1 --sameline - list each set of matches on a single line

-S --size - show size of duplicate files
-q --quiet - hide progress indicator
-d --delete - prompt user for files to preserve, deleting all others
-v --version - display fdupes version
-h --help - displays help
Fdupes Examples
1) fdupes -r ./stuff > dupes.txt
Then, deleting the duplicates was as easy as checking dupes.txt and deleting the offending directories. fdupes also can prompt
you to delete the duplicates as you go along.
2) fdupes -r /home/user > /home/user/duplicate.txt
Output of the command goes in duplicate.txt
fdupes will compare the size and M D5 hash of the files to find duplicates

Reference
http://www.ubuntugeek.com/