Against All Odds - Oracle

Parsing List Value in Oracle

Tue, 24 Apr 2012 09:32:00 -0700

Being Coldfusion programmer, parsing list value in coldfusion is very simple. If list value is "list1|list2|list3|list4", we can parse with ListToArray in Coldfusion as follow.

Above coding, we must need to assign true for includeEmptyFields if list value will contain empty. In Oracle, it's touch to parse and we need to write Oracle function. [More]

Wrap Oracle script

Sun, 25 Mar 2012 18:44:00 -0700

Today, I know how to wrap Oracle objects called stored procedure, functions, views and so on. It's because I want to make secure for our project source. How-to-do is very simple. 1) Open common prompt 2) and go to Bin folder under Oracle just like "C:\Oracle\Bin" 3) Then type following script


C:\Oracle\Bin\wrap iname=/SP/mysp.sql oname=/SP/wrap_mysp.sql

[More]

Share tables of schema in same SID

Mon, 27 Feb 2012 01:05:00 -0700

Today, I need to sync data of different schema in same SID (same database). Honestly, I'm not very good at Oracle but I gotta develop this kind of project. That's why I've asked in StackOverFlow and @Luck Woodwords and he replied good solution. http://stackoverflow.com/questions/9452488/how-to-create-oracle-dblink-in-same-sid#comment11957843_9452488 [More]

Backup Oracle database with bat file

Thu, 12 Jan 2012 05:57:00 -0700

Today, I've found how to backup/export Oracle database with bat file. I've just created it for only one schema first. Why I'm using to import Oracle with bat file because of there is no auto backup feature in Oracle (as far as I know). That's why it's needed to create bat file and integrate with window task scheduler (run daily). First of all, open Notepad and save as OracleBackup.bat first. Then, paste following code into this file.


@echo off
echo Importing Oracle Start
echo =======================
exp system/password file=C:/ppshein.dmp grants=y owner='ppshein';
echo ==========================
echo Importing Oracle End

[More]

Get free exam simulator from uCertify

Tue, 29 Nov 2011 20:15:00 -0700

uCertify, leading IT certification provider company is willing to spring for a license, that's a full license, for any of their test prep kits for one of my blog readers. The only requirement to get the full license is that you write a review of the software, within 30 days, that will be a guest post here on this blog.

So, I am offering this to all of my blog readers. Today is Nov 30, 2011. To get a chance to win the prep kit license, go to the ucertify site and take a look at the available tests. Decide which you would like to take.

Once you've made your choice, come back here and post a comment about which you would like and why you want to take it. Doesn't have to be anything big; just a couple of sentences is fine.

Make sure you either leave a real email address or you are signed in when you leave the comment. You don't need to leave your email address in the text of your comment if you are signed in. I can send you a message via the it toolbox messaging system in that case.

I will randomly choose the winner from the comments on Dec 10, 2011. So, you have about 10 days to post a comment.

Once I have notified the winner and they agree to review the software in a guest post here on this blog, I will give the info to Ucertify and they'll hand out the license.

The guest post doesn't need to be huge. At least a few hundred words which is just a page or two of double spaced text. As an example, this blog post is over 400 words. I'll be glad to proofread and edit it if you would like me to mail me (pyaephyoeshein[at]gmail[dot]com).

Oracle Database SQL Expert

Tue, 15 Nov 2011 23:38:00 -0700

Being developer, we must need to know how to write SQL query, functions, views and stored procedures for our development as long as we are not using ORM. I'm not sure using ORM is kinda useful or not in our development life cycle but we shouldn't forget our writing query skill. That's why I need to practise 1Z0-047 for enhancing my Oracle query skill with 1Z0-047 exam simulator of ucertify,

[More]

Oracle 11g: SQL Fundamentals (1Z0-051) exam guide

Wed, 19 Oct 2011 06:51:00 -0700

In this 2011, I feel like myself to take almost certification of what I know and what I'm using in my development space. Being programmer, I didn't say almost certificates are needed but just like non-computer science graduated like me, those certificate are very needed. Because almost people won't know why I'm surviving in IT without having computer science graduate. That's why I believe I'm not wrong to take certificate.

When I was in Myanmar, we use Microsoft SQL as our database because which is simple and easy to use with their UI. When I started working in Singapore (2009), our company use Oracle as database system instead of Microsoft SQL. Since that time, I'm addictive at Oracle because of the way of writing stored procedure, functions and views. That's why I think I need to have at least one Oracle certificate to let people know that I know Oracle.

That's why I decided to take 1Z0-051 exam of Oracle. It's called SQL Fundamentals exam which is suitable for me to take as beginner state of Oracle developer. Below is the exam topic of 1Z0-051.

[More]

Create new Listener and TNS in Oracle

Fri, 23 Sep 2011 06:16:00 -0700

Yesterday, I knew how to create new Listener and TNS in Oracle. It's kinda simple if you know how to do. If not, so complicated

Create new Listener in Oracle

First of all, we need to go Oracle first. Then, we need to go Configuration and migration tools > Net Manager. [More]

My first web based mobile project

Fri, 22 Jul 2011 18:28:00 -0700

Here is my first web-based project for my company. Our target is for customer who want to register Patient thru online. This project is based for iPad but it can work either on Android based tablet. For this project, I've used jQueryMobile framework, Coldfusion 9 and Oracle 11g. For jQueryMobile part, I've created our own customize theme for this project and added some JS coding into jQueryMobile existing scripts. Just example: my boss want me to highlight which field has been filled up by users and need to categorize for mandatory field. As you know, it's difficult to change or add some JS coding into their existing core JS file because we need to know the flow of existing JS coding. Likewise, what we add or change JS coding will impact the whole existing JS coding. I took 2 days to study the whole existing JS coding but it's not enough time honestly for it. Below is some of my change logs into jQueryMobile existing JS coding for this project. [More]

The powerful of DECODE in Oracle

Fri, 24 Jun 2011 00:55:00 -0700

In these days, I needed to manipulate Query statement in Oracle. Because, for database part, we don't want our CF to calculate the amount of our system. That's why we just let Oracle to do as much as database part can do. Because we don't want JRun can be more loaded because of such calculation and minor cases and we are always try to assign less process for our CF. Today, I need to develop some calculation in our projects for the following cases.

[More]

MAX function is kinda slow in Oracle

Tue, 07 Jun 2011 21:06:00 -0700

In these days, I'm on HL7 project for my company. In this project, most of processes are created within Oracle Stored Procedure. Most of Oracle stored procedure, we need to retrieve the last record from table. At that time, we sometimes use "MAX" built-in function of Oracle. Using "MAX" function in Oracle is very simple and cannot write complicated query to get last record from table like query after query method with RomNUM. But the one I haven't noticed is the performance of retrieving data. That's why I've wrote two query statements and test in PLSQL developer. Using MAX function


SELECT MAX(PATIENT_ID) FROM NH_PATIENT


0.047 seconds

Using query and query Using MAX function


SELECT PATIENT_ID FROM
    (SELECT PATIENT_ID FROM NH_PATIENT
    ORDER BY PATIENT_ID DESC) CLONE_USER
WHERE ROWNUM = 1


0.031 seconds

So, we should avoid using "MAX" function if we really don't need to use.

Usage of cursor and non-cursor in Oracle

Mon, 30 May 2011 23:21:00 -0700

Today I oddly found the different between using non-cursor and cursor in Oracle. Generally, using these two usages are same but kind of different memory usage and error handling. Advantage and disadvantage Of Cursor Using so many cursors in stored procedures, functions and views can be high memory usage in Server. Because every cursors take individual space in memory while creating and processing. If return value is NULL from this cursor, Oracle will not throw error message to application. Example coding of using cursor


CREATE OR REPLACE PROCEDURE PPS_TEST
       (P_PERSON_ID IN NUMBER)
IS
     L_MENU              VARCHAR2(50);
     L_ERR_MSG           VARCHAR2(200);

     CURSOR C_PP (P_PERSON_ID NUMBER) IS
     SELECT NAME FROM PERSON
     WHERE PERSON_ID = P_PERSON_ID;       
       
BEGIN
    OPEN C_PP(P_PERSON_ID);
         FETCH C_PP INTO L_MENU;
    CLOSE C_PP;
EXCEPTION
	WHEN OTHERS THEN
	   L_ERR_MSG := SQLERRM;
		RAISE_APPLICATION_ERROR(-20000, L_ERR_MSG);
END;

[More]

Create List String in Oracle

Tue, 03 May 2011 01:46:00 -0700

Create list string in Coldfusion is very simple. We just use ValueList function of Coldfusion. What we can do it in Oracle? As we all know, there are a lot of built-in functions in Oracle and new more functions are including whenever upgrading Oracle version. Being not DBA (database administrator), I cannot know how many built-in functions are in Oracle and how many new functions are added into each version. Today, I need to create list string in Oracle. My first though, I would search built-in function like ValueList in Oracle. As my though, I've search again and again but have no result at all. That's why, I decided myself to create new function for creating list string as follow: [More]

How to avoid using Trunc Function in Oracle

Tue, 19 Apr 2011 05:06:00 -0700

There are so many Date Function in Oracle. Some are very efficient to use when filtering date and others are not quite suitable to use for Oracle Tuning. Among them, Trunc function isn't good enough to use for Oracle Tuning. The description of the Trunc function returns a date truncated to a specific unit of measure. How does Trunc function work? It's a bit complicated to describe how Trunc works. The fastest way to describe is "it collect all of (selected) records from table and change Date Format. So, when we use Trunc function, Oracle must do those process." The following coding is I've wrote when I didn't consider for Oracle tuning.



	SELECT user_id, user_name, user_rank FROM tblLogs
	WHERE user_login_time <=  
	AND user_login_time >=

Above coding, user_login_time has been saved as SYSDATE of Oracle. That's why current date and time have been included in user_login_time. So, I must need to use Trunc function to change Date format from Date/time format. If I do so, Oracle collect all data from tblLogs table and change format to Date string then get records by from_date and to_date. So, it's not good if our table has over 1 million records. How to avoid using Trunc function for filter our date/time data field? It's very simple. Please see the following coding.



	SELECT user_id, user_name, user_rank FROM tblLogs
	WHERE user_login_time <=  
	AND user_login_time > to_date(, 'MM-DD-YY') + 1

Repeat String Function in Oracle

Tue, 22 Mar 2011 18:51:00 -0700

Now, I'm fixing the coding of HL7 interface project and clean any unnecessary coding and function from Coldfusion and Oracle Stored Procedure for performance. Among them, I've found that I've used looping in Oracle Stored Procedure for concatenating string as like follow.

         
V_FIRST_STRING := 'PPSHEIN';
V_LOOP := 10;
FOR LCOUNT IN 1.. V_LOOP
LOOP
	V_ZERO := V_ZERO || '0';
END LOOP;
V_FINAL_STRING := V_FIRST_STRING  || V_ZERO;

When I developed this coding, I didn't know there is RepeatString built-in function like Coldfusion in Oracle. My senior told me that there is RepeatString in Oracle but he forgot it. In this time, I was in rush and I decided to try with Looping. Now, it's time for me to consider the performance of my HL7 interface project. In Coldfusion, Every CF developers know repeat string in Coldfusion is RepeatString function. In Oracle, RepeatString build-in function is what? So, I've kept searching around Oracle forums and documentations. Finally, I found there is RPAD function in Oracle acts like RepeatString in Coldfusion. Usage is slightly like Coldfusion. [More]