Query to get previous data for several years

I’ve 3 categories to show data from loan master,
1. Date opening loan range 3 until 5 years
2. Date opening loan range 5 until 10 years
3. Date opening loan > 10 years

This is the script to show all data:

select 'PRODUK 604 range 3 s/d 5 tahun' KATEGORI, kd_kntr, kd_produk, kd_sub_produk, kd_val,no_rek, nm_nas, sld_akhir, sisa_bunga, tgl_akad_kredit 
from ln_master 
where kd_produk=6 and kd_sub_produk=4 and kd_val=0 and tgl_akad_kredit < trunc(add_months(sysdate,-(3*12))) and tgl_akad_kredit =trunc(add_months(sysdate,-(5*12))) 
union 
select 'PRODUK 604 range 5 s/d 10 tahun' KATEGORI, kd_kntr, kd_produk, kd_sub_produk, kd_val,no_rek, nm_nas, sld_akhir, sisa_bunga, tgl_akad_kredit 
from ln_master 
where kd_produk=6 and kd_sub_produk=4 and kd_val=0 and tgl_akad_kredit < trunc(add_months(sysdate,-(5*12))) and tgl_akad_kredit =trunc(add_months(sysdate,-(10*12)))
union
select 'PRODUK 604 range > 10 tahun' KATEGORI, kd_kntr, kd_produk, kd_sub_produk, kd_val, no_rek, nm_nas, sld_akhir, sisa_bunga, tgl_akad_kredit
from ln_master
where kd_produk=6 and kd_sub_produk=4 and kd_val=0
and tgl_akad_kredit < trunc(add_months(sysdate,-(10*12)));

Script to generate DDL from all schema except sys, system, etc.

set heading off;
set echo off;
Set pages 1000;
set long 50000;
SET linesize 150;
spool d:\exec_Myddl.sql;
select 'set heading off; set echo off;Set pages 1000;set long 50000; SET linesize 150; spool d:\My_ddl.sql;' from dual;
select 'select DBMS_METADATA.GET_DDL(''TABLE'','''||table_name||''','''||owner||''') from dual;' from all_tables
where owner not in ('SYS','SYSTEM','SYSMAN','DBSNMP','OUTLN','MDSYS','ORDDATA','APPQOSSYS','GSMADMIN_INTERNAL','XDB',
'DBSFWUSER','WMSYS','OJVMSYS','CTXSYS','LBACSYS','OLAPSYS','LBACSYS','DVSYS','HR','DEV_WLS','DEV_OPSS',
'DEV_IAU','DEV_STB','SCOTT','DEV_WLS_RUNTIME','ORDSYS');
select 'spool off;' from dual;
spool off;
@d:\exec_myddl.sql;
/

— NOTE: The exec_mydll.sql file is automate to generate script DDL, and the result is my_ddl.sql

How to disable second hard disk on VMWARE

Problem: The file (vmdk) from the second hard drive was accidentally deleted

I’ve tried to troubleshoot with open vmx files, There’s two scsi
First, scsi0:0
Second, scsi0:1

1. Change
scsi0:1.present = “TRUE“
with
scsi0:1.present = “FALSE“

How to kill Anydesk (full screen with keyboard locked) ~ Windows

Sometimes I’m using anydesk with fullscreen mode, and keyboard locking with another application happened, I tried to kill anydesk with task manager or kill manually on the task bar is useless. Then I try to another way to kill anydesk, with CMD run as administrator, and it works.

Follow this instruction to solve the problem.

Run as administrator on CMD

On CMD
type taskkill /IM anydesk.exe
if not success then taskkill /IM anydesk.exe /F

How to unlock weblogic

–Erase lok file ~ Hapus file lok

cd /home/oracle/Middleware/user_projects/domains/<domain_name>/servers/AdminServer/tmp
rm *.lok

–Erase inside ldap folder ~ hapus file didalam folder ldap

cd /home/oracle/Middleware/user_projects/domains/<domain_name>/servers/AdminServer/data
rm -rf ldap/

–find Middle id (like search in task manager on windows) ~ cari id Middle

ps -ef | grep Middle

kill -9

–start weblogic

sh /home/oracle/Middleware/user_projects/domains/<domain_name>/bin/startWebLogic.sh

Pengalaman Mengurus Roya di Kantor BPN Tangerang Selatan (September 2015)

Tahukah anda bahwa Kantor BPN Kota Tangerang Selatan buka di hari Sabtu dan Minggu? 🙂 🙂 🙂

Ya! Pelayanan akhir pekan ini memang khusus ditujukan bagi Masyarakat umum. Secara umum, Pelayanan terbagi menjadi 2 yaitu :
1. Pendaftaran dan Pemrosesan dokumen – dari jam 09.00 s.d. 11.00
2. Penyerahan Produk / Sertifikat yang telah di proses – dari jam 12.00 s.d. 13.00

Belum lama ini saya datang ke BPN Kota TangSel untuk mengurus Penghapusan Hak Tanggungan terhadap Sertifikat Tanah/Rumah yang saya miliki, atau lebih dikenal dengan istilah Roya. Proses administrasi ini diperlukan untuk membuktikan bahwa kita sebagai Pemilik Tanah/Rumah sudah tidak lagi memiliki hutang dengan Bank, tentunya karena kita telah menyelesaikan pelunasan kredit pinjaman tersebut.

Adapun syarat-syarat yang diperlukan sbb:

Nah – semua informasi persyaratan dapat dengan mudah diperoleh dari Brosur resmi BPN yang tersedia di kantor tersebut.

Secara garis besar ada 3 “ruangan” yang perlu kita kunjungi. Pertama adalah…

View original post 488 more words

How to connect Form/Report dev6i to Oracle Database 12c

We have database 10.2.0.5, want to convert to 12.2.0.1, the step are:
– exp from 10.2.0.5
– imp to 12.2.0.1
import Oracle 12.2.0.1 has been completed.

And then the first problem is oracle form crash when connecting to oracle 12.2.0.1

After searching, the solution come from this website:
https://www.youtube.com/watch?v=V0Achdev4gA

CONNECT AS SYSDBA
CONNECT SYSTEM USER (SYSTEM/(OWN YOUR DATABASE PASSWORD))
SHUTDOWN IMMEDIATE;
STARTUP RESTRICT;
ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252;
SHUTDOWN;
STARTUP MOUNT;
SHUTDOWN;
STARTUP RESTRICT;
SHUTDOWN;
STARTUP;

After solving the first problem, then the second problem is coming, ORA-28040: No matching authentication protocol

For second problem, this reference is the solution:
http://logic.edchen.org/how-to-resolve-ora-28040-no-matching-authentication-protocol/

Adding SQLNET.ALLOWED_LOGON_VERSION_SERVER=8 to sqlnet.ora in 12.1 database server (not the old client).

Connecting successfulle but still got malformed TTC package message, will solved later ..

SQL*Plus: Release 8.0.6.0.0 – Production on Wed May 8 15:24:46 2019

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter password:
ERROR:
ORA-03137: malformed TTC packet from client rejected: [kpoal8Check-5] [0]
[0x000000000] [0x000000000] [0] [0] [2080] []

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL>

XAMPP Solved : the procedure entry point ocistmtgetnextresult could not be located in the dynamic …

Notes . Noted

I’ve just got new laptop and when I re-installed the XAMPP I got this error :

the procedure entry point ocistmtgetnextresult could not be located in the dynamic …

So this was my setup when It’s error :

• I was using Windows 10 64 bit
• My PC using architecture 64 bit
• I installed XAMPP x86
• I used Oracle Install Client 12.2 64 bit, set into PATH environment

So after I configured php.ini (uncommented the extension)

extension=php_oci8_12c.dll 

that error occurred, after trial and error it was solved by doing these actions

1. Download 32 bit Oracle Install Client, and reset the PATH environment variable to that folder
2. Add oci.dll, oraociei12.dll, and oraons.dll to apache/bin folder. Replace the existing one
3. Restart the Apache

Basically, I change the oracle client according to the XAMPP/apache/php version (x86 v 64 bit)

View original post

How to access sharing folder using UTL_FILE (Oracle Database)

utl_fileDownload

When start service, run as user who can access the host system. See PDF file for details

PL/SQL: Sleep without using DBMS_LOCK

Oracle Deli

If you want to wait in your PL/SQL program (“sleep”) you may want to use the procedure “SLEEP” from the package “DBMS_LOCK“. While this function does exactly what you want, your DBA may not grant you access to this package, because it contains some other mighty and dangerous procedures.

But there is help! Here are some valid alternatives:

Option 1: APEX_UTIL.sleep

If APEX is installed you can use the procedure “PAUSE” from the publicly available package APEX_UTIL.

Example – “Wait 5 seconds”:

SET SERVEROUTPUT ON ;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Start ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
    APEX_UTIL.PAUSE(5);
    DBMS_OUTPUT.PUT_LINE('End   ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END;
/

Option 2: java.lang.Thread.sleep

An other option is the use of the method “sleep” from the Java class “Thread”, which you can easily use through providing a simple PL/SQL wrapper procedure:

CREATE OR REPLACE PROCEDURE SLEEP

View original post 48 more words