Monday, December 22, 2014

Problems in: FETCH FIRST n PERCENT ROWS ONLY

Wanting to count how many rows in emp table

SQL> SELECT COUNT (*) FROM emp;

COUNT(*)
----------
100

Cool... now let me see how much is 5 percent of emp

SQL> SELECT COUNT (*) FROM emp
2 FETCH FIRST 5 PERCENT ROWS ONLY;

COUNT(*)
----------
100

Mmmmm.... it can't be also 100 ... somthing is wierd ..
Let's try other way

SQL> SELECT COUNT (*)
2 FROM ( SELECT empno
3 FROM emp
4 FETCH FIRST 5 PERCENT ROWS ONLY);

COUNT(*)
----------
5

Cool, now it is working :)
Now let's try in PL/SQL

SQL> DECLARE
2 l_Percent_to_fetch PLS_INTEGER;
3 l_cnt PLS_INTEGER;
4 BEGIN
5 SELECT COUNT (*)
6 INTO l_cnt
7 FROM ( SELECT empno
8 FROM emp
9 FETCH FIRST l_Percent_to_fetch PERCENT ROWS ONLY);
10 END;
11 /
DECLARE
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4480
Session ID: 196 Serial number: 37163

What!!!! can't I use variables ???
let's try hadcoded:

SQL> DECLARE
2 l_cnt PLS_INTEGER;
3 BEGIN
4 SELECT COUNT (*)
5 INTO l_cnt
6 FROM ( SELECT empno
7 FROM emp
8 FETCH FIRST 5 PERCENT ROWS ONLY);
9 END;
10 /

PL/SQL procedure successfully completed
.

Cool.. not so cool - I was forced to bypass a BUG :( :(

Friday, July 26, 2013

Export in Pl/Sql via DBMS_DATAPUMP

CREATE OR REPLACE PACKAGE maintenance
AS
   PROCEDURE export_myschema;
   PROCEDURE stop_job (job_name VARCHAR2, schema_name VARCHAR2);
   PROCEDURE stop_all_jobs;
   PROCEDURE import_schema (file_name VARCHAR2);
END maintenance;
CREATE OR REPLACE PACKAGE BODY maintenance
AS
   PROCEDURE export_myschema
   AS
      handle        NUMBER;
      file_name     VARCHAR2 (200);
      log_name      VARCHAR2 (200);
      JOBNAME       VARCHAR2 (200);
      file_prefix   VARCHAR2 (30);
      dir_name      VARCHAR2 (4000);
      l_fexists BOOLEAN;
      l_file_length PLS_INTEGER;
      l_block_size BINARY_INTEGER;
   BEGIN
      dir_name := 'EXPORT_DIR';
      file_prefix := 'myschema_prod_data_';
      file_name :=
         file_prefix || MOD (TO_NUMBER (TO_CHAR (SYSDATE, 'D')), 2) -- toggle 0 or 1                                                                   || '.dmp';
      log_name :=
            file_prefix
         || MOD (TO_NUMBER (TO_CHAR (SYSDATE, 'D')), 2)
         || '_'
         || TO_CHAR (SYSDATE, 'YYMMDDHH24MISS')
         || '.log';
      JOBNAME := 'EXPORT_JOB_' || TO_CHAR (SYSDATE, 'YYMMDDHH24MISS');
      UTL_FILE.fgetattr (dir_name,
                     file_name,
                     l_fexists,
                     l_file_lenght,
                     l_block_size);
      IF l_fexists
      THEN
       DBMS_OUTPUT.put_line ( 'file to backup:'|| file_name || ' already exists - deleting it...');
       UTL_FILE.fremove (dir_name, file_name);
      ELSE
       DBMS_OUTPUT.put_line ( 'file to backup:'|| file_name || ' does not exists - continuing...');
      END IF;
      handle :=
         DBMS_DATAPUMP.open (operation   => 'EXPORT',
                             job_mode    => 'SCHEMA',
                             job_name    => JOBNAME,
                             version     => 'LATEST');
      DBMS_DATAPUMP.ADD_FILE (
         handle      => handle,
         filename    => log_name,
         directory   => dir_name,
         filetype    => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
      );
      DBMS_DATAPUMP.ADD_FILE (
         handle      => handle,
         filename    => file_name,
         directory   => dir_name,
         filetype    => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
      );
      DBMS_DATAPUMP.METADATA_FILTER (handle   => handle,
                                     name     => 'SCHEMA_EXPR',
                                     VALUE    => 'IN (''MYSCHEMA'')');
      DBMS_DATAPUMP.METADATA_FILTER (
         handle        => handle,
         name          => 'NAME_EXPR',
         VALUE         => 'NOT IN (''EMP'',''DEPT'')',
         object_type   => 'TABLE'
      );
      DBMS_DATAPUMP.START_JOB (handle);
      DBMS_DATAPUMP.DETACH (handle);
   END export_myschema; 
   PROCEDURE stop_job (job_name VARCHAR2, schema_name VARCHAR2)
   AS
      handle     NUMBER;
      day_name   VARCHAR2 (3);
   BEGIN
      handle := DBMS_DATAPUMP.ATTACH (job_name, schema_name);
      DBMS_DATAPUMP.STop_JOB (handle, 1, 0);
   END stop_job;
   PROCEDURE stop_all_jobs
   IS
      handle   NUMBER;
   BEGIN
      FOR x IN (SELECT   * FROM DBA_datapump_jobs)
      LOOP
         BEGIN
            BEGIN
               EXECUTE IMMEDIATE   'drop table '
                                || x.owner_name
                                || '.'
                                || x.job_name;
               DBMS_OUTPUT.put_line (
                  'Table Job: ' || x.job_name || ' - Dropped'
               );
            EXCEPTION
               WHEN OTHERS
               THEN
                  DBMS_OUTPUT.put_line (
                     'Table Job: ' || x.job_name || ' - does not exists'
                  );
            END;
            handle := DBMS_DATAPUMP.ATTACH (x.job_name, x.owner_name);
            DBMS_DATAPUMP.STop_JOB (handle, 1, 0);
            DBMS_OUTPUT.put_line ('Job: ' || x.job_name || ' - DONE');
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line (
                  'Job: ' || x.job_name || ' couldn''t removed'
               );
         END;
      END LOOP;
   END stop_all_jobs; 
   PROCEDURE import_schema (file_name VARCHAR2)
   AS
      handle      NUMBER;
      log_name    VARCHAR2 (200);
      JOBNAME     VARCHAR2 (200);
      dir_name    VARCHAR2 (4000);
   BEGIN
      dir_name := 'IMPORT_DIR';
  
      log_name := 'import_' || TO_CHAR (SYSDATE, 'YYMMDDHH24MISS') || '.log';
      JOBNAME := 'IMPORT_JOB_' || TO_CHAR (SYSDATE, 'YYMMDDHH24MISS');
      handle :=
         DBMS_DATAPUMP.open (operation   => 'IMPORT',
                             job_mode    => 'SCHEMA',
                             job_name    => JOBNAME,
                             version     => 'LATEST');
      DBMS_DATAPUMP.ADD_FILE (
         handle      => handle,
         filename    => log_name,
         directory   => dir_name,
         filetype    => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
      );
      DBMS_DATAPUMP.ADD_FILE (
         handle      => handle,
         filename    => file_name,
         directory   => dir_name,
         filetype    => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
      );
      DBMS_DATAPUMP.METADATA_FILTER (handle   => handle,
                                     name     => 'SCHEMA_EXPR',
                                     VALUE    => 'IN (''MYSCHEMA'')');
      DBMS_DATAPUMP.SET_PARAMETER (handle   => handle,
                                   name     => 'TABLE_EXISTS_ACTION',
                                   VALUE    => 'TRUNCATE');
      DBMS_DATAPUMP.SET_PARALLEL (handle => handle, degree => 4);
      DBMS_DATAPUMP.START_JOB (handle);
      DBMS_DATAPUMP.DETACH (handle);
   END import_schema; 
END maintenance;
/

Thursday, January 24, 2013

Undelete Files in Linux

Usually I take care for backups, but the first time I tempted to neglect this habit - I just have met Morphy's law :(
Fortunately there is a solution:

yum install testdisk
OR
apt-get install testdisk
To recover files simply type:
photorec
and follow the instructions

So simple :)

Monday, December 03, 2012

Format scripts in awk, csh, ksh, perl, sh

I am used to relay on tools for formatting and indenting SQL or PL/SQL code.
Looking for the same for linux scripts (especially bash) I found the following code
fmt.script

Monday, August 27, 2012

Sending Mail from the database


You should have an outgoing SMTP server IP to configure sending mail from the database.
I already put it in the database server /etc/hosts as mailhost

sqlplus / as sysdba
@?/rdbms/admin/utlmail.sql
@?/rdbms/admin/prvtmail.plb
grant execute on UTL_MAIL to public;
ALTER SYSTEM SET smtp_out_server = 'mailhost' scope=both;

a simple example

BEGIN
UTL_MAIL.send(sender => 'Yossi@NixonIT.com',
recipients => 'you@address.com',
subject => 'Test Mail',
message => 'Hello World',
mime_type => 'text; charset=us-ascii');
END;
/

For further advanced options such as attachments see in this wiki

Wednesday, May 16, 2012

Data Pump, the unix command line

Data Pump import as sysdba and several indexes, the unix way ....

impdp "'sys/dba as sysdba'" schemas=SCOTT INCLUDE=INDEX:\"in \(\'PK_DEPT\',\'EMPIDX\'\)\" directory=TMP_DIR dumpfile=scott.dmp job_name=importing_scott.log

Tuesday, February 28, 2012

All about ORA-600 lookup tool

ORA-600/ORA-7445 are generic internal error numbers for Oracle program exceptions.
Sometimes these errors are unique for your specific problem and cannot be found via search engines.
Using "ORA-600 lookup tool" may point your specific problem, faster and accurate.
The tool can be found in ORA-600 lookup tool - Metalink Document ID 153788.1

The flowing video will guide you the usage of the LookUp Tool (11:12) - Metalink Document ID 1082674.1