Oracle External Table PREPROCESSOR :: Loading first n lines from datafile.

This is based on one of the thread in OTN where user wants to load first 100 lines from the data file. Of course this does not make much sense, because you can load the whole file as an external table without preporcessor complexity and then just use a WHERE clause with rownum <=100.
But, for some reason if you must have to do this, then there is a workaround.
 My source file emp_csv.txt has 14 lines, emp_ext.sh is my shell file used in the table definition.

[oracle@localhost saubhik]$ pwd
/home/oracle/saubhik
[oracle@localhost saubhik]$ cat emp_csv.txt
7369,"SMITH","CLERK",7902,17-DEC-80 00:00:00,800,,20
7499,"ALLEN","SALESMAN",7698,20-FEB-81 00:00:00,1600,300,30
7521,"WARD","SALESMAN",7698,22-FEB-81 00:00:00,1250,500,30
7566,"JONES","MANAGER",7839,02-APR-81 00:00:00,2975,,20
7654,"MARTIN","SALESMAN",7698,28-SEP-81 00:00:00,1250,1400,30
7698,"BLAKE","MANAGER",7839,01-MAY-81 00:00:00,2850,,30
7782,"CLARK","MANAGER",7839,09-JUN-81 00:00:00,2450,,10
7788,"SCOTT","ANALYST",7566,19-APR-87 00:00:00,3000,,20
7839,"KING","PRESIDENT",,17-NOV-81 00:00:00,5000,,10
7844,"TURNER","SALESMAN",7698,08-SEP-81 00:00:00,1500,0,30
7876,"ADAMS","CLERK",7788,23-MAY-87 00:00:00,1100,,20
7900,"JAMES","CLERK",7698,03-DEC-81 00:00:00,950,,30
7902,"FORD","ANALYST",7566,03-DEC-81 00:00:00,3000,,20
7934,"MILLER","CLERK",7782,23-JAN-82 00:00:00,1300,,10
[oracle@localhost saubhik]$ cat emp_csv.txt|wc -l
14
[oracle@localhost saubhik]$ cat emp_ext.sh
#!/bin/bash  
  
# Listing the first 10 lines  
/usr//bin/head -10 $1 
exit 
[oracle@localhost saubhik]$ 

Now the table creation and testing:

SCOTT@orclSB 27-FEB-17>  --My database version
SCOTT@orclSB 27-FEB-17> select * from v$version; 

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SCOTT@orclSB 27-FEB-17> -- External table definition which will read first 10 lines.
SCOTT@orclSB 27-FEB-17> ed
Wrote file afiedt.buf

  1  create table emp_ext
  2         (       empno varchar2(30),
  3            ename varchar2(30),
  4            job varchar2(30),
  5            mgr varchar2(30),
  6            hiredate varchar2(30),
  7            sal varchar2(30),
  8            comm varchar2(30),
  9            deptno varchar2(30)
 10        )
 11        ORGANIZATION EXTERNAL
 12         ( TYPE ORACLE_LOADER
 13           DEFAULT DIRECTORY SAUBHIK
 14           ACCESS PARAMETERS
 15           ( records delimited by newline
 16         preprocessor saubhik: 'emp_ext.sh'
 17         badfile saubhik:'emp_ext%a_%p.bad'
 18         logfile saubhik:'emp_ext%a_%p.log'
 19         fields terminated by ","
 20          optionally enclosed by '"'
 21          missing field values are null
 22          (empno,ename,job,mgr,hiredate ,sal,comm,deptno
 23          )
 24             )
 25           location
 26            ( SAUBHIK:'emp_csv.txt'
 27            )
 28         )
 29        reject limit unlimited
 30*     PARALLEL 2
SCOTT@orclSB 27-FEB-17> /

Table created.

SCOTT@orclSB 27-FEB-17> select * from emp_ext;

EMPNO                          ENAME                          JOB                            MGR                            HIREDATE                       SAL
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
COMM                           DEPTNO
------------------------------ ------------------------------
7369                           SMITH                          CLERK                          7902                           17-DEC-80 00:00:00             800
                               20

7499                           ALLEN                          SALESMAN                       7698                           20-FEB-81 00:00:00             1600
300                            30

7521                           WARD                           SALESMAN                       7698                           22-FEB-81 00:00:00             1250
500                            30


EMPNO                          ENAME                          JOB                            MGR                            HIREDATE                       SAL
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
COMM                           DEPTNO
------------------------------ ------------------------------
7566                           JONES                          MANAGER                        7839                           02-APR-81 00:00:00             2975
                               20

7654                           MARTIN                         SALESMAN                       7698                           28-SEP-81 00:00:00             1250
1400                           30

7698                           BLAKE                          MANAGER                        7839                           01-MAY-81 00:00:00             2850
                               30


EMPNO                          ENAME                          JOB                            MGR                            HIREDATE                       SAL
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
COMM                           DEPTNO
------------------------------ ------------------------------
7782                           CLARK                          MANAGER                        7839                           09-JUN-81 00:00:00             2450
                               10

7788                           SCOTT                          ANALYST                        7566                           19-APR-87 00:00:00             3000
                               20

7839                           KING                           PRESIDENT                                                     17-NOV-81 00:00:00             5000
                               10


EMPNO                          ENAME                          JOB                            MGR                            HIREDATE                       SAL
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
COMM                           DEPTNO
------------------------------ ------------------------------
7844                           TURNER                         SALESMAN                       7698                           08-SEP-81 00:00:00             1500
0                              30


10 rows selected.

Calling Web Service From PL/SQL : Temperature Converter Using 1. UTL_HTTP 2. UTL_DBWS

This is part II of my earlier post of Calling Webservice through pl/sql. This post is also based on one of the threads in OTN.
I will demonstrate calling free Web-services available in http://www.webservicex.net/new/Home/Index  using two methods: 1. UTL_HTTP 2. UTL_DBWS and both are working perfectly.

SCOTT@orclSB 27-FEB-17> -- My database version.
SCOTT@orclSB 27-FEB-17> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SCOTT@orclSB 27-FEB-17> ed
Wrote file afiedt.buf

  1  create or replace function ConvertTemperature( Temperature integer, FromUnit varchar2, ToUnit varchar2 ) return integer is
  2              --// URL to call
  3              SOAP_URL        constant varchar2(1000) := 'http://www.webservicex.net/ConvertTemperature.asmx';
  4              --// SOAP envelope template, containing $ substitution variables
  5              SOAP_ENVELOPE   constant varchar2(32767) :=
  6      '
  7  
  8    
  9      
 10        $TEMPERATURE
 11        $FROMUNIT
 12        $TOUNIT
 13      
 14    
 15  ';
 16             --// we ll identify ourselves using an IE9/Windows7 generic browser signature
 17             C_USER_AGENT    constant varchar2(4000) := 'Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0)';
 18             --// these variables need to be set if web access
 19             --// is via a proxy server
 20             proxyServer varchar2(20) default null;
 21             proxyUser varchar2(20) default null;
 22             proxyPass varchar2(20) default null;
 23             --// our local variables
 24             soapEnvelope    varchar2(32767);
 25             proxyURL        varchar2(4000);
 26             request         utl_http.req;
 27             response        utl_http.resp;
 28             buffer          varchar2(32767);
 29             soapResponse    clob;
 30             xmlResponse     XmlType;
 31             eof             boolean;
 32             lResponse integer;
 33     begin
 34             --// create the SOAP envelope
 35             soapEnvelope := replace( SOAP_ENVELOPE, '$TEMPERATURE', Temperature );
 36             soapEnvelope := replace( soapEnvelope, '$FROMUNIT', FromUnit );
 37             soapEnvelope := replace( soapEnvelope, '$TOUNIT', ToUnit );
 38             --// our "browser" settings
 39             utl_http.set_response_error_check( true );
 40             utl_http.set_detailed_excp_support( true );
 41             utl_http.set_cookie_support( true );
 42             utl_http.set_transfer_timeout( 10 );
 43             utl_http.set_follow_redirect( 3 );
 44             utl_http.set_persistent_conn_support( true );
 45             --// configure for web proxy access if applicable
 46             if proxyServer is not null then
 47                     proxyURL := 'http://'||proxyServer;
 48                     if (proxyUser is not null) and (proxyPass is not null) then
 49                             proxyURL := Replace( proxyURL, 'http://',  'http://'||proxyUser||':'||proxyPass||'@' );
 50                     end if;
 51                      utl_http.set_proxy( proxyURL, null );
 52             end if;
 53             --// make the POST call to the web service
 54             request := utl_http.begin_request( SOAP_URL, 'POST', utl_http.HTTP_VERSION_1_1 );
 55             utl_http.set_header( request, 'User-Agent', C_USER_AGENT );
 56             utl_http.set_header( request, 'Content-Type', 'text/xml; charset=utf-8' );
 57             utl_http.set_header( request, 'Content-Length', length(soapEnvelope) );
 58             utl_http.set_header( request, 'SoapAction', 'http://www.webserviceX.NET/ConvertTemp' );
 59             utl_http.write_text( request, soapEnvelope );
 60             --// read the web service HTTP response
 61             response := utl_http.get_response( request );
 62             dbms_lob.CreateTemporary( soapResponse, true );
 63             eof := false;
 64             loop
 65                     exit when eof;
 66                     begin
 67                             utl_http.read_line( response, buffer, true );
 68                             if length(buffer) > 0 then
 69                                     dbms_lob.WriteAppend(
 70                                             soapResponse,
 71                                             length(buffer),
 72                                             buffer
 73                                     );
 74                             end if;
 75                     exception when utl_http.END_OF_BODY then
 76                             eof := true;
 77                     end;
 78             end loop;
 79             utl_http.end_response( response );
 80             --// as the SOAP responds with XML, we convert
 81             --// the response to XML
 82             xmlResponse := XmlType( soapResponse );
 83             dbms_lob.freetemporary( soapresponse );
 84             select extractvalue(xmlResponse,'/soap:Envelope/soap:Body/ns0:ConvertTempResponse/ns0:ConvertTempResult'
 85  ,'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/", xmlns:ns0="http://www.webserviceX.NET/"')
 86   into lResponse
 87   from dual;
 88             return( lResponse );
 89     exception when OTHERS then
 90             if soapResponse is not null then
 91                     dbms_lob.FreeTemporary( soapResponse );
 92             end if;
 93             raise;
 94*   end;
 95  /

Function created.

SCOTT@orclSB 27-FEB-17> select converttemperature(68,'degreeFahrenheit','degreeCelsius') from dual; 

CONVERTTEMPERATURE(68,'DEGREEFAHRENHEIT','DEGREECELSIUS')
---------------------------------------------------------
                                                       20

SCOTT@orclSB 27-FEB-17> ed
Wrote file afiedt.buf

  1  create or replace function ConvertTemperature_dbws( Temperature integer, FromUnit varchar2, ToUnit varchar2 ) return integer is
  2              --// URL to call
  3              SOAP_URL        constant varchar2(1000) := 'http://www.webservicex.net/ConvertTemperature.asmx';
  4              --// SOAP envelope template, containing $ substitution variables
  5  SOAP_ENVELOPE   constant varchar2(32767) :=
  6      '
  7        $TEMPERATURE
  8        $FROMUNIT
  9        $TOUNIT
 10      ';
 11    lservice           sys.utl_dbws.service;
 12    DBWScall             sys.utl_dbws.CALL;
 13    serviceQname      sys.utl_dbws.QNAME;
 14    port_qname         sys.utl_dbws.QNAME;
 15    xmlResponse           sys.xmltype;
 16    request            sys.xmltype;
 17    lResponse integer;
 18    soapEnvelope    varchar2(32767);
 19     begin
 20             --// create the SOAP envelope
 21             soapEnvelope := replace( SOAP_ENVELOPE, '$TEMPERATURE', Temperature );
 22             soapEnvelope := replace( soapEnvelope, '$FROMUNIT', FromUnit );
 23             soapEnvelope := replace( soapEnvelope, '$TOUNIT', ToUnit );
 24             serviceQname := sys.utl_dbws.to_qname(null, 'ConvertTemp');
 25    lservice := sys.utl_dbws.create_service(serviceQname);
 26    DBWScall := sys.utl_dbws.create_call(lservice);
 27    sys.utl_dbws.set_target_endpoint_address(DBWScall, SOAP_URL);
 28    sys.utl_dbws.set_property( DBWScall, 'SOAPACTION_USE', 'TRUE');
 29    sys.utl_dbws.set_property( DBWScall, 'SOAPACTION_URI', 'http://www.webserviceX.NET/ConvertTemp');
 30    xmlResponse :=sys. utl_dbws.invoke(DBWScall, xmltype(soapenvelope));
 31   select extractvalue(xmlresponse,'ConvertTempResponse/ConvertTempResult','xmlns:="http://www.webserviceX.NET/"')
 32   into lresponse
 33  from dual;
 34    return (lresponse);
 35*   end ConvertTemperature_dbws;
 36  /

Function created.

SCOTT@orclSB 27-FEB-17> select converttemperature_dbws(68,'degreeFahrenheit','degreeCelsius') from dual; 

CONVERTTEMPERATURE_DBWS(68,'DEGREEFAHRENHEIT','DEGREECELSIUS')
--------------------------------------------------------------
                                                            20

SCOTT@orclSB 27-FEB-17> l   

SQL101: Using Oracle Analytical function - range between unbounded preceding and 1 preceding

In SQL101 series, I will post some simple SQL related techniques. Most of them are posted in OTN time to time by me.
This SQL101 is related to Using Oracle Analytical function with range between unbounded preceding and 1 preceding, the problem was posted in OTN.
Test table and data setup:
First, I have used the following to create the test table and insert the sample data.

--Test table
create table daily_tr_tab
  (
    invid    number unique, --- though this is also not used in my query. if you need any unique column to give me a better solution.
    dates    date,
    purchase number,
    sales    number
  );
  

--Test data setup 
REM INSERTING into daily_tr_tab
SET DEFINE OFF;
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (1,to_date('22-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),50,20);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (2,to_date('23-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),20,15);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (3,to_date('24-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),10,10);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (4,to_date('25-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),60,45);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (5,to_date('26-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),40,25);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (6,to_date('27-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),15,5);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (7,to_date('28-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),25,20);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (8,to_date('29-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),15,15);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (9,to_date('30-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),30,20);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (10,to_date('31-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),20,10);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (11,to_date('01-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),20,10);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (12,to_date('02-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),30,25);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (13,to_date('03-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),25,0);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (14,to_date('04-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),35,25);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (15,to_date('05-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),15,10);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (16,to_date('06-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),20,15);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (17,to_date('07-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),25,10);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (18,to_date('08-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),30,20);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (19,to_date('09-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),35,30);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (20,to_date('10-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),20,20);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (21,to_date('11-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),30,5);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (22,to_date('12-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),40,0);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (23,to_date('13-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),50,20);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (24,to_date('14-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),12,10);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (25,to_date('15-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),13,10);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (26,to_date('16-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),14,10);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (27,to_date('17-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),24,5);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (28,to_date('18-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),34,0);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (29,to_date('19-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),35,25);
insert into daily_tr_tab (invid,dates,purchase,sales) values (30,to_date('20-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),40,10);

The output needed:

1. DATES     OPENING_QTY   PURCHASE      SALES CLOSING_QTY  
2. --------- ----------- ---------- ---------- -----------  
3. 01-JAN-16         100         20         10         110  
4. 02-JAN-16         110         30         25         115  
5. 03-JAN-16         115         25          0         140  
6. 04-JAN-16         140         35         25         150  
7. 05-JAN-16         150         15         10         155  
8. 06-JAN-16         155         20         15         160  
9. 07-JAN-16         160         25         10         175  
10. 08-JAN-16         175         30         20         185  
11. 09-JAN-16         185         35         30         190  
12. 10-JAN-16         190         20         20         190  
13. 11-JAN-16         190         30          5         215  

 Opening quantity in a date is total of previous purchase minus total sales. The closing balance is opening quantity of that date minus the sale.

The solution is:
 

SCOTT@orclSB 28-JAN-17> ed
Wrote file afiedt.buf

  1  select *
  2  from
  3    (select dates,
  4        sum(purchase) over(order by dates range between unbounded preceding and 1 preceding )
  5      - sum(sales) over(order by dates range between unbounded preceding and 1 preceding) opening_qty ,
  6      purchase,
  7      sales ,
  8        sum(purchase) over(order by dates range between unbounded preceding and 1 preceding)
  9      - sum(sales) over(order by dates range between unbounded preceding and 1 preceding) + purchase - sales closing_qty
 10    from daily_tr_tab
 11    )
 12  where dates >= to_date('01/01/16', 'dd/mm/rr')
 13* order by dates
SCOTT@orclSB 28-JAN-17> /

DATES     OPENING_QTY   PURCHASE      SALES CLOSING_QTY
--------- ----------- ---------- ---------- -----------
01-JAN-16         100         20         10         110
02-JAN-16         110         30         25         115
03-JAN-16         115         25          0         140
04-JAN-16         140         35         25         150
05-JAN-16         150         15         10         155
06-JAN-16         155         20         15         160
07-JAN-16         160         25         10         175
08-JAN-16         175         30         20         185
09-JAN-16         185         35         30         190
10-JAN-16         190         20         20         190
11-JAN-16         190         30          5         215

DATES     OPENING_QTY   PURCHASE      SALES CLOSING_QTY
--------- ----------- ---------- ---------- -----------
12-JAN-16         215         40          0         255
13-JAN-16         255         50         20         285
14-JAN-16         285         12         10         287
15-JAN-16         287         13         10         290
16-JAN-16         290         14         10         294
17-JAN-16         294         24          5         313
18-JAN-16         313         34          0         347
19-JAN-16         347         35         25         357
20-JAN-16         357         40         10         387

20 rows selected.

SCOTT@orclSB 28-JAN-17> 

SQL101: Using sequence.nextval with group and analytical function.

In SQL101 series, I will post some simple SQL related techniques. Most of them are posted in OTN time to time by me.
This SQL101 is related to Using sequence.nextval with group and analytical function, the problem was posted in OTN.
The problem with sequence.nextval is it increments within SQL statement even within the group. Carefully watch this test case:

SCOTT@orclSB 28-JAN-17> --My database version.
SCOTT@orclSB 28-JAN-17> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SCOTT@orclSB 28-JAN-17> ed
Wrote file afiedt.buf

  1  --Creating the sequence.
  2* create sequence file_seq minvalue 1 maxvalue 9999999999999999999999999999 increment by 1 start with 100 cache 20 noorder nocycle
SCOTT@orclSB 28-JAN-17> /

Sequence created.

SCOTT@orclSB 28-JAN-17> ed
Wrote file afiedt.buf

  1  --Creating the test table.
  2  create table test4
  3    ( file_number number, seq_no number
  4*   )
SCOTT@orclSB 28-JAN-17> /

Table created.

SCOTT@orclSB 28-JAN-17> ed
Wrote file afiedt.buf

  1  --Inserting my test data.
  2  insert into test4
  3  select 10,null from dual
  4  union all
  5  select 10,null from dual
  6  union all
  7  select 10,null from dual
  8  union all
  9  select 20,null from dual
 10  union all
 11  select 20,null from dual
 12  union all
 13* select 30,null from dual
SCOTT@orclSB 28-JAN-17> /

6 rows created.

SCOTT@orclSB 28-JAN-17> commit;

Commit complete.

SCOTT@orclSB 28-JAN-17> select file_number,dense_rank() over (order by file_number) dnk from test4;

FILE_NUMBER        DNK
----------- ----------
         10          1
         10          1
         10          1
         20          2
         20          2
         30          3

6 rows selected.

SCOTT@orclSB 28-JAN-17> 

Required Output
FILE_NUMBER, DNK
10                          100
10                          100
10                          100
20                          101
20                          101
30                          103

Rules:
If you noticed that I created sequence name as file_SEQ, system should  user this sequence to generate dnk column  and if file_number change then get next sequence. So far I created the below query but it is not working.

SCOTT@orclSB 28-JAN-17> ed
Wrote file afiedt.buf

  1  select file_number,
  2    dense_rank() over (order by file_number) + file_seq.nextval dnk
  3* from test4
SCOTT@orclSB 28-JAN-17> /

FILE_NUMBER        DNK
----------- ----------
         10        101
         10        102
         10        103
         20        105
         20        106
         30        108

6 rows selected.

SCOTT@orclSB 28-JAN-17> 

Now, you can see the problem, it is creating counting without grouping of file_number.
This is the actual solution of this problem:

SCOTT@orclSB 28-JAN-17> ed
Wrote file afiedt.buf

  1  create or replace function retfileseq(
  2      pi_indicator integer)
  3    return number
  4  as
  5  begin
  6    if pi_indicator = 1 then
  7      return (file_seq.nextval);
  8    else
  9      return (file_seq.currval);
 10    end if;
 11* end retfileseq;
SCOTT@orclSB 28-JAN-17> /

Function created.

SCOTT@orclSB 28-JAN-17> ed
Wrote file afiedt.buf

  1  select file_number ,
  2    case
  3      when lag(file_number) over(order by file_number) is null
  4      or file_number <> lag(file_number) over(order by file_number)
  5      then retfileseq(1)
  6      else retfileseq(0)
  7    end as file_seq
  8* from test4
SCOTT@orclSB 28-JAN-17> /

FILE_NUMBER   FILE_SEQ
----------- ----------
         10        106
         10        106
         10        106
         20        107
         20        107
         30        108

6 rows selected.

SCOTT@orclSB 28-JAN-17> 

SQL101 : Oracle Check constraint to prevent alphanumeric value in column

In SQL101 series, I will post some simple SQL related techniques. Most of them are posted in OTN time to time by me.
This SQL101 is related to check constraint. We can implement check constraint very efficiently for many of our problems, sometimes people tends to use triggers for those, but remember trigger is not a good option in most of the time. You may want to read this article from Tom Kyte regarding trigger : Ask Tom: The Trouble with Triggers..
I will describe two scenarios, first we have a table with whole_name column, So we want to restrict bad entries in that column and we want to allow only alphabet and space as name can not be alphanumeric or purely numeric!
Second, we have a table with first_name and last_name column, So we want to allow only alphabet here, no numeric and no space.
 

SCOTT@orclSB 28-JAN-17> --My database version.
SCOTT@orclSB 28-JAN-17> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SCOTT@orclSB 28-JAN-17> ed
Wrote file afiedt.buf

  1  -- In the whole_name column, we will only allow alphabet and space.
  2  CREATE TABLE contacts
  3      (
  4      whole_name varchar2(30) constraint chk_whole_name check (regexp_like (whole_name,'^[[:alpha:][:space:]]+$'))
  5*     )
SCOTT@orclSB 28-JAN-17> /

Table created.

SCOTT@orclSB 28-JAN-17> --This should work.
SCOTT@orclSB 28-JAN-17> insert into contacts values ('Saubhik');

1 row created.

SCOTT@orclSB 28-JAN-17> --This should not work as 123 is numeric.
SCOTT@orclSB 28-JAN-17> insert into contacts values ('Saubhik123');
insert into contacts values ('Saubhik123')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHK_WHOLE_NAME) violated


SCOTT@orclSB 28-JAN-17> --This should not work as 1234 is numeric.
SCOTT@orclSB 28-JAN-17> insert into contacts values ('1234');
insert into contacts values ('1234')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHK_WHOLE_NAME) violated


SCOTT@orclSB 28-JAN-17> --This should work as only alphabet and space.
SCOTT@orclSB 28-JAN-17> insert into contacts values ('Saubhik Banerjee');

1 row created.

SCOTT@orclSB 28-JAN-17> drop table contacts purge;

Table dropped.

SCOTT@orclSB 28-JAN-17> ed
Wrote file afiedt.buf

  1  -- Now, there is no whole_name column, instead we have broken down as first_name and last_name.
  2  -- So, Only alphabet should be allowed, no numeric character and no space.
  3  CREATE TABLE contacts
  4      (
  5      first_name varchar2(30) constraint chk_first_name check (regexp_like (first_name,'^[[:alpha:]]+$'))
  6*     )
SCOTT@orclSB 28-JAN-17> /

Table created.

SCOTT@orclSB 28-JAN-17> --This should work.
SCOTT@orclSB 28-JAN-17> insert into contacts values ('Saubhik');

1 row created.

SCOTT@orclSB 28-JAN-17> --This should not work as 123 is numeric.
SCOTT@orclSB 28-JAN-17> insert into contacts values ('Saubhik123');
insert into contacts values ('Saubhik123')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHK_FIRST_NAME) violated


SCOTT@orclSB 28-JAN-17> --This should not work as 1234 is numeric.
SCOTT@orclSB 28-JAN-17> insert into contacts values ('1234');
insert into contacts values ('1234')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHK_FIRST_NAME) violated


SCOTT@orclSB 28-JAN-17> --This should not work as space is not allowed within first_name.
SCOTT@orclSB 28-JAN-17> insert into contacts values ('Saubhik Banerjee');
insert into contacts values ('Saubhik Banerjee')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHK_FIRST_NAME) violated


SCOTT@orclSB 28-JAN-17> drop table contacts purge;

Table dropped.

SCOTT@orclSB 28-JAN-17>