How to get all dates from sysdate's month (ORACLE)

SELECT to_char(day, 'dd') day
FROM
(
    SELECT TRUNC(SYSDATE, 'MM') + LEVEL - 1 AS day
    FROM dual
    CONNECT BY LEVEL <= 32
)
WHERE EXTRACT(MONTH FROM day) = EXTRACT(MONTH FROM SYSDATE) and day<=sysdate

Function Kamus

declare
ret varchar(64);
begin

  ret := getRegional('Pertanggungan Node-B');
 
  dbms_output.put_line(ret);

end;

-----------------------------------------------------------------------------------------------------------------------------

CREATE OR REPLACE procedure DASHBOARD.reg_map as
cursor dt is
select * from fact_cost_bk where regional is null;
begin
    for data1 in dt loop
      if lower(data1.text) like '%jawa barat%' then
        update fact_cost_bk set regional ='Jawa Barat';
      end if;
    end loop;
end;

How to run a LIKE query with values from another table

select
a.nm_dokumen_singkat,
b.kata,
b.program
from
revenue_non_smile_2015 a,
kamus_revenue b
where
upper(a.nm_dokumen_singkat) like '%'||upper(b.kata)||'%'

Cumulative Sum using analytic functions

In this article we will how we will be able to achieve the cumulative sum or running total of a column using oracle supplied analytical functions:

Consider the following example:

CREATE TABLE dept (deptno NUMBER PRIMARY KEY, deptname VARCHAR2(20));

CREATE TABLE emp (eno NUMBER PRIMARY KEY, ename VARCHAR2(35), deptno NUMBER REFERENCES dept (deptno), sal NUMBER);

INSERT INTO dept VALUES (10, ‘Accounts’);

INSERT INTO dept VALUES (20, ‘HR’);

INSERT INTO emp VALUES (1, ‘A’, 10, 1000);

INSERT INTO emp VALUES (2, ‘B’, 10, 1500);

INSERT INTO emp VALUES (3, ‘C’, 20, 2000);

INSERT INTO emp VALUES (4, ‘D’, 20, 3500);

INSERT INTO emp VALUES (5, ‘E’, 20, 1500);

Now what we are going to achieve is the following result:

ENO   ENAME       DEPTNO   SAL       CUM_SAL

1     A           10       1000      1000

2     B           10       1500      2500

3     C           20       2000      2000

4     D           20       3500      5500

5     E           20       1500      7000

As per the above result what we require is clear. That is we are going to get department-wise running total of salary of all employees.

Now consider the following query:

select eno, ename, deptno, sal, sum(sal) over (partition by deptno order by deptno, eno, ename) cum_sal

from emp

And its output:

ENO   ENAME   DEPTNO   SAL   CUM_SAL

1      A      10       1000   1000

2      B      10       1500   2500

3      C      20       2000   2000

4      D      20       3500   5500

5      E      20       1500   7000

This is same as we desired. But how is this result achieved?

The crux for this is the analytic function which is used in the query.

The syntax for using an analytical function is as follows:

Function(arg1,..., argn) OVER ( [PARTITION BY <...>] [ORDER BY <....>] [<window_clause>] )

We will not delve deeper into each and every function available. In this example, we have used SUM () and OVER (PARTITION BY … ORDER BY …).

The PARTITION BY clause in OVER () determines where the data needs to be partitioned (not grouped). The ORDER BY clause orders the partitioned data. The SUM () in our case takes the sum of the values returned by the OVER ().


Sumber : http://askanantha.blogspot.co.id/2007/10/running-total-or-cumulative-sum-using.html

PERINTAH LINUX CENTOS

*DIRECTORY*
cd  : Untuk masuk ke directory
cd .. : untuk keluar directory
ls : untuk melihat list yang ada yang ada di dalam directory

*CRONTAB*
crontab -e : untuk masuk ke crontab
Ctrl+i : Untuk insert crontab / edit

*SCREEN*
Screen : Untuk masuk ke screen
Kill all screen : killall screen
Quit screen : Ctrl+A then press D
Create a file : cat>filename.*

Create Procedure, DBLink in Oracle

PROCEDURE
CREATE OR REPLACE procedure PROCEDURE_NAME as
begin
*Script yang akan di eksekusi.*   
    commit;
end;
/

 DBLINK
CREATE DATABASE LINK DBSMILE
 CONNECT TO user
 IDENTIFIED BY <pwd>
 USING '(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=scan-colodb.telkom.co.id) (PORT=1521) ) (CONNECT_DATA= (SERVICE_NAME=colodb) ) )';