Copy OS files from Oracle

Sometimes you need to copy some OS file from one location to another. If you’re using only Sql*Plus and you can’t connect directly to OS, you can user Oracle’s UTL_FILE.FCOPY function to achieve this goal.

Firstly, we’ll create two directories and one file. Then we’ll create a user and copy this file from one folder to another using UTL_FILE package

[oracle@localhost /]$ cd /tmp
[oracle@localhost tmp]$ mkdir copy_dir
[oracle@localhost tmp]$ mkdir backup_dir
[oracle@localhost tmp]$ ls backup_dir/
[oracle@localhost tmp]$
[oracle@localhost tmp]$ cd copy_dir
[oracle@localhost copy_dir]$ vi test.dat (We are adding a line with word MY_TEST to file test.dat)
[oracle@localhost copy_dir]$ ls -lh
total 4.0K
-rw-r–r–  1 oracle oinstall 8 Dec  7 04:11 test.dat
[oracle@localhost copy_dir]$ cat test.dat
MY_TEST
[oracle@localhost copy_dir]$sqlplus “/ as sysdba”

SQL> CREATE USER kamran IDENTIFIED BY kamran;

User created.

SQL> grant connect, resource to kamran;

Grant succeeded.

SQL> grant create any directory to kamran;

Grant succeeded.

SQL> grant execute on utl_file to kamran;

Grant succeeded.

SQL> conn kamran/kamran
Connected.
SQL> create directory file_source as ‘/tmp/copy_dir’;

Directory created.
SQL> create directory backup_source as ‘/tmp/backup_dir’;

Directory created.

SQL> exec utl_file.fcopy(‘FILE_SOURCE’,’test.dat’,’BACKUP_SOURCE’,’copied_test.dat’);

PL/SQL procedure successfully completed.

SQL>exit
[oracle@localhost tmp]$ cd backup_dir/
[oracle@localhost backup_dir]$ ls -lh
total 4.0K
-rw-r–r–  1 oracle oinstall 8 Dec  7 04:32 copied_test.dat
[oracle@localhost backup_dir]$

Make Table read only

Today I’ll show how we can make a table read only using database triggers

In this example, we’ll create a table, insert data in it, and then create trigger to prevent any insert, update or delete statement on table

SQL> CREATE TABLE tbl_read_only (id NUMBER);

Table created.

SQL> INSERT INTO tbl_read_only VALUES(1);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM tbl_read_only;

        ID
———-
         1

SQL> CREATE TRIGGER trg_tbl_read_only BEFORE DELETE OR INSERT OR UPDATE
  2  ON tbl_read_only
  3  BEGIN
  4  RAISE_APPLICATION_ERROR(-20202,’Table Status: READ ONLY!!!’);
  5  END;
  6  /

Trigger created.

SQL> INSERT INTO tbl_read_only VALUES(2);
INSERT INTO tbl_read_only VALUES(2)
            *
ERROR at line 1:
ORA-20202: Table Status: READ ONLY!!!
ORA-06512: at “KAMRAN.TRG_TBL_READ_ONLY”, line 2
ORA-04088: error during execution of trigger ‘KAMRAN.TRG_TBL_READ_ONLY’
SQL>

How delete duplicate rows

Today I want to give you an example of how delete duplicate rows from a table
Let’s create new table with name students:
CREATE TABLE students (ID NUMBER, NAME VARCHAR2(10), surname VARCHAR2(10))
Now let’s add some duplicate rows to it:
INSERT INTO students VALUES (1, ‘Kamran’, ‘Agayev’);
INSERT INTO students VALUES (2, ‘Elcin’, ‘Mammedov’);
INSERT INTO students VALUES (1, ‘Kamran’, ‘Agayev’);
INSERT INTO students VALUES (2, ‘Elcin’, ‘Mammedov’);
INSERT INTO students VALUES (1, ‘Kamran’, ‘Agayev’);
INSERT INTO students VALUES (2, ‘Elcin’, ‘Mammedov’);
And now let’s look to the data of our table:
SELECT * FROM students
1  Kamran  Agayev
2  Elcin   Mammedov
1  Kamran  Agayev
2  Elcin   Mammedov
1  Kamran  Agayev
2  Elcin   Mammedov
Now let’s delete duplicate rows using this script:
 
DELETE FROM students   
WHERE ROWID NOT IN (SELECT MAX (ROWID) FROM students GROUP BY ID) 
And now let’s look to students table
SELECT * FROM students
1  Kamran  Agayev
2  Elcin   Mammedov

Run SQL statement from one Linux command

These two examples shows you how to run SQL command directly from Linux in a single command

[oracle@localhost~] echo “select count(*) from tab;” | sqlplus -s username/pass

With another method, you can run from one Linux command a SQL file

 [oracle@localhost~] sqlplus -s username/pass <filename.sql

 COUNT(*)

————-

                20

 [oracle@localhost~]

Welcome to my Oracle Blog

Hi All. Welcome to my Oracle Blog. In this blog, I’ll try to add new solutions for some problems and provide briefly examples. Moreover, I’m planning periodically to analyze some administration features of Oracle database