oracle - How to alter sequence next number

DROP SEQUENCE MENU_SEQ;

create sequence MENU_SEQ
minvalue 1
maxvalue 999999999999999999999
start with 18456
increment by 1
cache 20;

ORACLE - How to find table name by table name substring

You can use CAT table (catalog). It shows to you all object in user profile.

select * from cat
where
table_name like 'MEN%'

Output:

TABLE_NAME                     TABLE_TYPE 
------------------------------ -----------
MENU                           TABLE      
MENU_SEQ                       SEQUENCE   
MENU_ZAL                       TABLE 

ORACLE - How declare variable accross existing table field (%type)

Both variables are declared accross existing PEOPLE.NAME table field:

function Month_Execute( pPeriod varchar2, sErrorMessage out varchar2 ) return number
is
  sMinName people.name%type;
  sMaxName people.name%type;
begin
...

ORACLE - How to unlock user account

connect sys/password as sysdba;

alter user username account unlock;

ORACLE - How to set user`s password and how to unlock account

connect sys/password as sysdba;

alter user UserName identified by NewTestPassword account unlock;

ORACLE - How generate text file in PL/SQL

function CreateExportFile( pNumber varchar2 ) return number
is
  cursor c_lower is
    select * from table
    where
    lief_sch_nr = pNumber and
    c_pom = 2   /* spec.type */
    order by
    bsg_nr, pos_lfd_nr, tl_nr, tl_fam, tl_atn
    ;
  pc_lower   c_lower%rowtype;

  iCount number;
  sFileName varchar2( 50 );
  sDir varchar2( 256 );
  fileHandler utl_file.file_type;
begin
  /* -- check if some data exists */

  select count( * ) into iCount
  from
  factory.cieb_karosa_rozpad
  where
  lief_sch_nr = pNumber;

  if iCount = 0 then
    return( -1 );
  end if;

  /* -- export */

  sDir := 'c:\ifc42\utlfile';
  sFileName := pNumber || '_name_' || to_char( sysdate, 'dd_mm_yyyy_hh24_mi' ) || '.csv';
  fileHandler := utl_file.fopen( sDir, sFileName, 'w' );

  open c_lower;
    loop
      fetch c_lower into pc_lower;
      exit when c_lower%notfound;

      utl_file.putf( fileHandler, 
                     pc_lower.lief_sch_nr || ';' ||
              pc_lower.bsg_nr_kd || ';' ||
       pc_lower.pos_lfd_nr || ';' ||
       to_char( pc_lower.abschl_dat, 'dd.mm.yyyy' ) || ';' ||
       pc_lower.tl_nr_lief || ';' ||
       pc_lower.lsch_mng_me || ';' ||
       to_char( to_number( pc_lower.lsch_mng_mz ) ) || ';' ||
                     '\n' 
                   );

    end loop;
  close c_lower;

  utl_file.fclose( fileHandler );

  return( 0 );
end;

Output (could be):

DL160386;30003313;10;11.03.2016;5006006343;ks;5;
DL160386;30003313;240;11.03.2016;E5801470258;ks;4;
DL160386;30003313;240;11.03.2016;E5801470258;ks;12;
...

ORACLE - How make PL/SQL cursor with parameter(s)

function CreateInvoice( pNumber varchar2, pErrorMessage out varchar2 ) return number
is
  cursor c_sets( pLief_sch_nr varchar2 ) is
    select * from table
    where
    lief_sch_nr = pLief_sch_nr and
    c_px = 1   
    ;
  pc_sets   c_sets%rowtype;
begin
  ...
  open c_sets( pNumber );
    loop
      fetch c_sets into pc_sets;
      exit when c_sets%notfound;
      ...
    end loop;
  close c_sets;

ORACLE - How round number to three decimal places

select ( 3.652 * 5.673 ) as n1, round( 3.652 * 5.673, 3 ) as n2 from dual

Output:

        N1         N2
---------- ----------
20,717796  20,718  

ORACLE - "for" statement in PL/SQL

Easy for loop for labels generation:

declare
  i number;
  ...
begin
...
  open c_city( pc1.arb_ord_nr, pc1.arb_ord_zae );
  loop

    fetch c_city into pCity;
    exit when c_city%notfound;

    for i in 1..pCity.quantity loop

      /* -- add one label per city cursor row */

      insert into labels
      ( sessionid, arb_ord_nr, c_number, tl_fam, tl_atn, c_type, dat_neu )
      values
      ( sid, pCity.arb_ord_nr, i, null, null, sType, sysdate );
      
      commit;

    end loop;

  end loop;
  close c_city;

ORACLE - How to make hierarchical query

In this example you can see hierarchical query with start with and connect by section.

• start with - is initial row (data) for question (..here key to top item).
• connect by - tells, how the new data are connected to previous row.

Here is example from BOM table:

select substr(lpad(' ', level) || level, 1, 10) as level_,
a.tl_nr, a.tl_unt_nr, a.tl_unt_fam, a.tl_unt_atn, 
a.mng_pos_re_mz as quantity, a.mng_pos_me as unit
from ertesr a
start with 
a.tl_nr = '442-97345-622-1' and
a.tl_fam = '0' and 
a.tl_atn = '02'
connect by prior a.tl_unt_nr = a.tl_nr
and prior a.tl_unt_fam = a.tl_fam
and prior a.tl_unt_atn = a.tl_atn
order by rownum

Output:

LEVEL_     TL_NR                TL_UNT_NR            TL_UNT TL_UNT   QUANTITY UN
---------- -------------------- -------------------- ------ ------ ---------- --
 1         442-97345-622-1      S-312-221-1001       0      0      ,025       KG
 1         442-97345-622-1      442-90780-566-1      0      0               1 KS
  2        442-90780-566-1      S-137-114-1060       0      0      ,6         KG
 1         442-97345-622-1      S-246-931-1005       0      0      ,019       KG
 1         442-97345-622-1      S-821-303-0144       0      0               1 KS
 1         442-97345-622-1      442-05011-145-1      0      0               2 KS
  2        442-05011-145-1      S-331-123-2058       0      0               1 KS

7 rows selected. 

ORACLE, SQL SERVER - typical update with subselect

update factory.an_plan a
set
a.sbj_id = ( select sbj_id from j_vad
             where
        vad_id = ( select vad_id from kuauko
                 where
            bsg_nr = a.bsg_nr
        )
    )
where
a.c_period = pPeriod and
a.bsg_nr is not null;

ORACLE - How get last day in the month

select sysdate, last_day( sysdate ) from dual;

select last_day( to_date( '201801', 'yyyymm' ) ) from dual;

Output:

SYSDATE  LAST_DAY
-------- --------
22.01.18 31.01.18

LAST_DAY
--------
31.01.18

ORACLE - select with limiting rows count (..how to show first 5 rows)

select tl_nr, dat_anf from ertest
where
rownum <= 5

Output:

TL_NR                DAT_ANF 
-------------------- --------
S-319-421-1000       12.02.97
-94547-508           12.02.97
-90391-127           12.02.97
-94547-517           12.02.97
-94215-137           12.02.97

ORACLE - having clause with between expression

You can limit group by condition with between expression:

select sbj_id, sum ( bew_mng_mz )
from factory.checks
where
to_char( c_datum, 'yyyy' ) = '2018' 
having sum( bew_mng_mz ) between 100 and 500
group by sbj_id;

Output:

    SBJ_ID SUM(BEW_MNG_MZ)
---------- ---------------
     37024             300
     36336             140
     37305             210
     30890             500
     38433             403

ORACLE - How get error text from PL/SQL function

Sometimes you need return from PL/SQL functions error code and error text too.

function Sql_Salary( pPeriod number, pErrorMessage out varchar2 ) return number
is
begin
  ...
  if i <> 0 then
    pErrorMessage := '..error message..';
    return( -1 );
  end if;  
  ...
  return( 0 );
end;  

Call it:

declare
  i number;
  sErrorMessage varchar2(500);
begin
  i := factory.an_p.Sql_Salary( '201801', sErrorMessage );
end;

ORACLE - How calculate months to date type

select sysdate, add_months( sysdate, -1 ) minus1, add_months( sysdate, 12 ) minus12 from dual

Output:

SYSDATE  MINUS1   MINUS12 
-------- -------- --------
22.01.18 22.12.17 22.01.19

ORACLE - How replace NULL value

nvl() function replace NULL value with second parameter (..in this example 0):

function sql_Color_AddNew_Rule( pQuestion varchar2, pUser varchar2, pGroup number ) return number
is
  iID number;
begin

  /* -- find new ID */

  select nvl( max( c_id ), 0 ) + 1 into iID
  from factory.an_color
  where
  lower( c_question ) = lower( pQuestion ) and
  lower( c_user ) = lower( pUser ) and
  c_group = pGroup;
...

ORACLE - How get sessionid

Every login in Oracle has our sessionid - you can use it for example for data inserted to table:

insert into factory.an_question_fort
( sessionid, tl_nr, tl_fam, tl_atn, bst_akt_mz, dat_neu )
values
( userenv( 'SESSIONID' ), pc_m.tl_nr, pc_m.tl_fam, pc_m.tl_atn, iInv, trunc( sysdate ) );

ORACLE - How get already first/last row value from dataset (dense_rank)

Suppose this data:

select tl_nr, bel_nr, bew_dat, bew_art from factory.himtbw
where
tl_nr like 'S-442-700-0000' and
to_char( bew_dat, 'yyyymm' ) = '201503'

TL_NR                BEL_NR BEW_DAT  BE
-------------------- ------ -------- --
S-442-700-0000       108923 03.03.15 PP
S-442-700-0000       108924 03.03.15 PP
S-442-700-0000       391498 11.03.15 VY
S-442-700-0000       391498 11.03.15 PR
S-442-700-0000       384932 12.03.15 VV
S-442-700-0000       384936 12.03.15 VV
S-442-700-0000       394694 20.03.15 VV
S-442-700-0000       401817 26.03.15 VV

8 rows selected. 

When you need to get BEW_ART type for last date, you can use oracle analytical function dense_rank for it:

select min( bew_art ) keep ( dense_rank last order by ( bew_dat ) ),
min( bel_nr ) keep ( dense_rank last order by ( bew_dat ) ) from factory.himtbw
where
tl_nr like 'S-442-700-0000' and
to_char( bew_dat, 'yyyymm' ) = '201503'

Output:

MI MIN(BE
-- ------
VV 401817

How it works: You get BEW_ART/BEL_NR from data set, which is ordered by BEW_DAT -> and you use for data LAST row (when is there in this sorting one row only).

ORACLE, SQL SERVER - Typical subselect

Typical SQL subselect for finding value:

update factory.an_turnover a
set
a.c_last_date = ( select max( bew_dat ) from factory.himtbw
                  where
                  tl_nr = a.tl_nr and
                  tl_fam = a.tl_fam and
                  tl_atn = a.tl_atn and
                  bew_art in ( 'PP', 'PV', 'PN' ) and 
                  lag_ort not in ( '700', '701' )
                 )
;

commit;