Category Archives: Tech

Oracle, SQL, Operating Systems

Analytic Views: Powerful Oracle 12.2 Feature for Business Intelligence

Oracle 12.2: Analytic Views

Data Warehousing with Oracle

Analytic Views are one of the main features for Business Intelligence introduced with Oracle 12c Release 2. They provide a lot of new possibilities for analytical queries on star schemas. This blog post gives an overview of the new functionality.

View original post 2,453 more words

ANSI Join Syntax and Query Rewrite

which JOIN syntax?

Data Warehousing with Oracle

There are many good reasons to use ANSI join syntax instead of the old Oracle join syntax. With one exception: If you want to use your Materialized Views for Query Rewrite, avoid to write them with ANSI joins.

View original post 1,471 more words

APEX: Form on a Procedure


You have a procedure.

And you want to prepare an interface for enduser so they could execute it by themselves.

however, your procedure is expecting some input parameters which are not so-easy for the users to get..

Let ‘s go with an example:



Say this procedure is taking an account number and generating a risk report for that customer.

So you decided to publish a form on one of your APEX user dashboards:

Building form on a procedure

After a few easy steps with this wizard, you ‘ll have your form with a field (i.e. :p_acc)on it prompting you to enter an account number

Although it works just fine, it is not that easy for users to know which company has which number. It would be way easier if they could go with company name for instance..

If you have no chance to touch to that procedure, means you can ‘t modify it to make it work with another parameter instead of v_acc (account number), you still can do something similar.

i. Create a Page Item on your page region

ii. Display as: Text with autocomplete

iii. Give your new page item a unique name (i.e. :p_cust_name)

iv. Adjust the population logic of your text field to make auto-complete thing work.

v. Then take a look at execution statement which runs your SP. You can see it by double-clicking Run Stored Procedure


vi. You ‘ll see something like this

#OWNER#.basitmis_proc(V_ACC => :p_acc);

vii. Now let ‘s modify it so it could work with our other parameter :p_cust_name


Let ‘s see what we did here:


i.We declared another variable p_cust

ii.Use it to collect account number. To know which customer ‘s account should return, we used our Page Item p_cust_name and remember, this information was provided by user.

iii.Then we bind it to our SP


Now your users can type ABC to automatically get ABC Company in the company name field, then click the button that triggers your execution process. Your little declaration above will do the rest :)




APEX: dbms_scheduler

Bir sorgu hazirladiniz ve bu sorgudan donen sonucu periyodik olarak bir tabloya yazmak istiyorsunuz..

Ya da bu bir rapor ve bu rapora bakarak periyodik olarak bir kisi ya da ekibe mail gondermek istiyorsunuz..

APEX icinden nasil mail gonderilecegine dair kucuk bir ornegi su yazimda anlatmaya calismistim.

Simdi ise bunu “otomatik” hale getirmeyi deneyelim.

Farkli yontemler elbette var, ama ben -veri tabaninizin “eski” bir versiyonda olmadigini varsayarak- dogrudan dbms_scheduler uzerinden bir procedure ile ilerleyecegim.

i. Oncelikle sorgumuzu bir procedure haline getirerek objemizi olusturalim. Bunu APEX ‘in etkilesimli menulerine yaptirabilecegimiz gibi SQL konsolunda elle de yapabiliriz.

create or replace procedure “ORNEK_BASITMIS” is 

Sorgunun detaylari bu yazinin konusu degil. Fakat soz gelimi, belirli bir data setini dinamik olarak sececegimiz alicilara gondermek istiyorsak

  • Bir cursor acabilir,
  • Sonra bu cursor ‘i loop icinde %NOTFOUND oluncaya kadar fetch ederek aksiyonumuzu aldirabiliriz: mesela her defasinda bir email gondermek gibi.

Schedule etmeye karar verdiginiz islem sisteme periyodik olarak email gonderme isi vermekse ve ornek mail gonderme kismini burada isledigimiz gibi apex_mail.send uzerinden kotariyorsaniz, dikkat etmemiz gereken aslinda belki de tek onemli nokta su:

Hazirladiniz SP icinde BEGIN END arasinda APEX Workspace ID ‘nizi assign emelisiniz..

Aksi takdirde schedule ettiginiz Job ‘un calisma vakti geldiginde

ORA-20001: This procedure must be invoked from within an application session.

gibi bir hata ile karsilasirsiniz :)


APEX Workspace ID

Bu ID bilgisine ustteki gibi erisebilirsiniz. Set etmek icin asagidaki gibi bir ifade kullaniyoruz:


Stored Procedure ‘imizi hazirladik ve ORNEK_PROCEDURE ismi ile create ettik diyelim. Simdi sirasi ile DBMS_SCHEDULER ile once bir program create etmeliyiz:


Ardindan bir schedule create ediyoruz:


repeate_interval degiskenine dikkat edelim. Burada frekans, saat, dakika, saniye turunden bir cok deger verme sansimiz var..

Dolayisiyla, Ayin belirli bir gunu, Haftanin belirli gunleri, gunun belirli saatleri gibi spesifik periyod tanimlari yapabiliyoruz. Detayli bilgiye Oracle dokumanlarindan erisebilirsiniz.

Simdi de job create edelim:


Artik veri tabaninda bir SP ‘miz var ve bu SP ‘nin istedigimiz zaman araliklarinda otomatik calismasi icin bir Program tanimimiz, bir Schedule ‘imiz ve bir de Job ‘imiz var..

Simdi program ve job ‘imizi “acalim”.


Hazir.. Artik repeat_interval olarak verdigimiz vakit geldigi zaman job ‘in calismadigini kontrol edebiliriz.

Mesela az once enable ettigimiz Job ‘u suradan gorebiliriz:

select * from dba_scheduler_jobs;

Zamani geldiginde islemin calisip calismadigini ise su tablodan takip edebiliriz:

select * from user_scheduler_job_run_details;

Yaptiklarimizi drop etmek icinse yine DBMS_SCHEDULER icindeki

  • drop_job
  • drop_schedule
  • drop_program

yapilarini kullanabiliriz. Fakat tahmin edebileceginiz gibi bunlari “sirali” yapmalisiniz, aksi takdirde sistem dependency nedeniyle kizacaktir :)


DROP etmek..

Gorusmek uzere!

APEX: Send Mail Thru Report Page


You published a useful interactive report for your users and now want to let them get that as email also through that page.

Here you can find a basic example..

  • Go to your report page from Application Builder panel
  • Create a page item button 
    • Pick Defined by Dynamic Action as button action
  • Right click to your brand new Dynamic Action and select Create.

APEX Dynamic Action

As you can see above, we have to create this action with some conditions. Such as:

  • Event: Click
  • Selection Type: Button
  • Button: address your button which you ‘ve just created. This item will trigger your email sending process.

Once we completed these steps, we can add a True action. You ‘ll see a button named Add True Action at your Dynamic Action Edit page. You can also create one by choosing from right click menu.

Adding a TRUE action

You may want to uncheck Fire On Page Load. Because.. Well we want to trigger it by clicking our button, manually :)

So.. Now we need this PL/SQL code which will take care of sending email via Oracle APEX.

I ‘ll share 2 basic examples with you. One for plain-text emails, one for HTML based ones.

I personally prefer to send mails in HTML format… But it ‘s up to you and depends on your needs..


It is pretty easy, right? Just give the right parameters and a simple sentence as email body. You can always use variables to collect these informations like i did..

If you want to go with HTML format, then you ‘ll need some extra time to create a good-looking and small-sized body. That ‘s all :)

Once you store these codes you ‘ve wrote and apply the changes onto your report page, you can start using your button. It should be sending emails to given address. Click on your button and wait couple of minutes to receive it.

I am not going to remind you not to copy everything from my sample snapshots. You do need to change email addresses etc. :)

Should you want to add some extra features on this dynamic action, you can add another True action(s) as well.

You may need to.. Otherwise someone would be able to click on that button more than necessary and we don ‘t want to spam people :)

Let ‘s add a Confirmation action..



  • Right Click, Create
  • Action: CONFIRM

Now you ‘ll be prompted to click OK everytime you click on your email button..

O.K. but not enough? Well let ‘s hide the button after it ‘s got clicked.


  • Now we have a button on our report page
  • Our authorized report viewers can click on it to send email
  • We have a confirmation dialog before trigger
  • Our email send button will be hidden once it used.


APEX: List of Values


It is not the best way to place an input field on your report interface if there are only a certain amount of possible input values. Right?

Let ‘s say we want our users to enter a Currency Code to return the Currency Rate of it..

sample SQL

So you deployed that one to APEX as an interactive report..

Since we ‘ve a bind variable, there has to be page item on your report interface.. If you create your page item as TEXT field, it works just fine.

But the problem is, users can enter currency codes which are not exist :)

To avoid that, we can set the page item type as Pop-Up LoV.

Item Types

At some point, we will be prompted to define Values for our List.

List of Values Query/ Named LoV Selection

Now we have 2 + 1 options:

  1. We can create a static list. It can be useful if we have only a bunch of values and they really are static.
  2. We can write our query which will return 2 columns: Display Value, Return Value
  3. We would create a Shared Component so we can reuse it on other pages as well :)

Number one is quite easy.. Just type your values as it ‘s shown below, be careful with spelling.

Static Values

Number two is a one time solution. Meaning, if you want to do same thing for another interactive report, you ‘ll have to do it again.

Say we have a param table where we can get all possible ISO Currency Codes:

Sample Dynamic LoV Query

Once we use this simple query as LoV Query, indeed we ‘ll have a dynamic LoV object.

However, to make it more efficient, we can also create a shared component with it so we can could use that object on other pages too.

Go to Application Builder then select your Application and click to Shared Component link.

You ‘ll see some User Interface options there:

List of Values Menu

  • Pick the List of Values.
  • Start from scracth
  • Set a name
  • Click to Dynamic radio button.

Write or Paste your Dynamic LoV Query to the text box.

Complete these steps by following on-page instructions.


Now we have a generic LoV which will list all the possible Currency Codes in our environment.

Now go a few steps back.. As you can see from List of Values Query/ Named LoV Selection there is a yellow marked area called Named LoV.

Click on it and pick your freshly built LoV.

Finally our user can input the currency codes without typing them manually :)

Sample Pop-Up LoV


SQL Plus: Generating Delimited Files

Say you need to dump a special set of data into a file..

Sure you can do that with fancy SQL clients which have graphical user interfaces. However, sometimes you do need to export your data set into a plain text file in a *certain* format, via sqlplus.

In some cases, for instance, if you ‘re going to use that file to feed another system, you ‘ll have to follow that 3rd systems data delivery rules. There can be some restrictions such as

  • Maximum line size
  • End of Line characters
  • Default Character Set
  • Pages
  • Spaces


So, since we are using sqlplus for our action, better prepare our query as a script.. I won ‘t explain all of these definitions, but it ‘s quite easy to find out what they do :)

Let ‘s write our .sql script by starting these SETs:


If you think you don ‘t need some of them, just put a dash before the line to make it disable.

As you can imagine, first one is the one that you defined the maximum length of a line in your output file.. Needless to say, you should read the specs of that 3rd system before setting it :)

Then we need to enter a path to address the exported file ‘s location.


spool D:\EXPORTS\export.txt


you can place your sql query after this line. For example


FROM dual;


then we will have to tell that the spooling has to stop, and script ‘s got to exit.


spool OFF;


Now we can save this file as an .sql file.

It would be a wise idea to test it. Run sqlplus on your terminal screen.

Most of us are using GUI tools for our daily works. If you don ‘t know how to run it, believe me it ‘s quite normal :)

On Windows, START > RUN > CMD

It will open a terminal window. Then use that syntax with your own parameters.


c:\> sqlplus user/pass@database_IP:port/service


On GNU/Linux, start eterm, xterm or any terminal you like :)

Can you see SQL> prompt? If yes, you are almost done. Otherwise you ‘ll have to google it :)

Well, let ‘s try to execute our script now.


SQL> @c:\example.sql


This should work.. Now we can check our spool output folder..

If you are using a Windows and everything was fine so far, maybe it ‘d be better to create a .BAT to make this whole process easier.

Write these lines in notepad,

# sample BATCH file
c:\> sqlplus user/pass@database_IP:port/service @c:\example.sql
exit the file with the .BAT extension.

Now you can use your Task Scheduler to execute that batch file automatically.

Hope it helps.. Next time maybe we can talk about configuring your Task Scheduler.


Nostalji: Linux, Infrared ve GPRS

Cok uzun zaman once, bol bol bos vaktim varken yalnizca eglence olsun diye ugrastigim, sonra da notlarima bakarak nasil yaptigimi paylastigim bir Linux maceram :)

Evet, bugun acik kaynak kodlu yazilimlar son derece kullanici dostu, Linux dagitimlari kullanici memnuniyetine eskisinden daha cok deger veriyorlar. Ve evet, bugun ne bu telefonu, ne de kizil otesi baglantiyi kullanan biri kalmamis olmali. Fakat gozumun onunde dursun, ki baktikca eskiden ne cok bos vaktim diyelim :)

Referans gosterdigim linkler calismiyor ya da paketler artik dagitilmiyor olabilir.


isletim sistemi : Linux/ Slackware 8.1 
kernel : linux mos-laptop 2.4.18 #4 fri may 31 01:25:31 pdt 2002 i686 unknown 
telefon : ericsson r520m 

*baglanti icin kizil otesi (irda) kullandim. 


Oncelikle telefonunuzu bilgisayara gosterebilmelisiniz, bunun icin irda-utils cekin, adres: su andaki guncel surum 0.9.15 

kullandiginiz kernel’da irda destegi yoksa sanirim bastan derlemeniz gerekiyor ancak guncel ve onerilen ayarlarla kurulmus linuxlarda bu destek var. 

irda-utils’i kurduktan sonra /dev altinda gerekli aygitlari olusturmalisiniz, bunun icin: 

mknod /dev/ircomm0 c 161 0 >mknod /dev/ircomm1 c 161 1>mknod /dev/irnet c 10 187 

ve bunlar icin gerekli izinleri vermek uzere:

chmod 664 /dev/ir*


eger irda portunuzun hangi adresi ve kesmeyi kullandigini bilmiyorsaniz bios’unuzdan bunu ogrenip bir kenara not etmelisiniz.

irda-utils’i kurabilmissek, terminal penceresinde; 

root@mos-laptop:/home/mos/progz# irattach /dev/ttys1 -s 

root@mos-laptop:/home/mos/progz# irattach irda0 -s 

root@mos-laptop:/home/mos/progz# modprobe irport io=0x2f8 irq=3 

root@mos-laptop:/home/mos/progz# irattach irda0 -s 

buraya kadar sorun yoksa, 

cat /proc/net/irda/discovery


irlmp: discovery log: 
nickname: r520, hint: 0x9124, saddr: 0x57ce4e10, daddr: 0x10eb4046 

gibi bir cikti alabiliyor olmalisiniz.(telefonunuz kizilotesi alicinizin onunde ve kizilotesi alma modunda olmali :) ) 

daha sonra adresinden cektigimiz gprsec adli programi kullanarak baglanti icin gerekli pppd scriptlerini olusturuyoruz, programi kurmak istediginizde tk ‘niz yok, grafik arabirimi kuramam diyebilir, problem degil zaten grafik arabirim pek hos da degil, bize sadece olusturacagi script gerekli.ama illa da grafik arabirim olsun diyorsaniz adresinde tk ‘yi nereden cekip nasil kuracaginizi anlatan bir yazi var. 

terminal penceresinde sg_en yazarak script generator’i baslatiyoruz ve bize bir telefon listesi cikariyor, orada ericsson r520‘yi gorebilirsiniz (daha bircok model var). bir sonraki adimda bize telefonun listedeki numarasini soracak, oraya r520’ye karsilik olarak 60 yaziyoruz, sonra iletisim baglanti portunu soracak, buraya da /dev/ircomm0 yazarak geciyoruz, sonra apn name olarak hangi operatoru kullaniyorsaniz onu yazin, ben telsim yazdim. sonraki adimda da kullanici adi ve sifre girecek misiniz diyor, hayir diyoruz ve bize sony-ericsson-r520 adinda bi dosya olusturuyor bulundugu dizinde. 


root@mos-laptop:/home/mos/progz# pppd file sonyericsson-r520 

yazinca asagidakine benzer ciktilar almis olmalisiniz; 

at ok ate0 ok 
+cgatt: 1 
connect serial connection established. using channel 1 using interface ppp0 connect: ppp0 /dev/ircomm0 warning – secret file /etc/ppp/pap-secrets has world and/or group access sent [lcp confreq id=0x1 ] rcvd [lcp confreq id=0x1 ] sent [lcp confrej id=0x1 ] rcvd [lcp confrej id=0x1 ] sent [lcp confreq id=0x2 ] rcvd [lcp confreq id=0x2 ] sent [lcp confack id=0x2 ] rcvd [lcp confack id=0x2 ] warning – secret file /etc/ppp/pap-secrets has world and/or group access sent [pap authreq id=0x1 user=”xxx” password=] sent [pap authreq id=0x2 user=”xxx” password=] rcvd [pap authack id=0x2 “”] sent [ipcp confreq id=0x1 ] sent [ipcp confreq id=0x1 ] rcvd [ipcp confreq id=0x1 ] sent [ipcp confack id=0x1 ] rcvd [ipcp confnak id=0x1 ] sent [ipcp confreq id=0x2 ] sent [ipcp confreq id=0x2 ] rcvd [ipcp confack id=0x2 ] local ip address remote ip address primary dns address secondary dns address script /etc/ppp/ip-up started (pid 320) script /etc/ppp/ip-up finished (pid 320), status = 0x0 

not : bu islemleri ve baglantiyi root olarak yapmalisiniz.