Copy two tables in Oracle

Dec 30

posted by Pyae Phyoe Shein in Oracle, Coldfusion, MsSql | 0 Comments Bookmark and Share

I found that so many query statements are different between in Microsoft SQL and Oracle. To copy two table in the same database with Microsoft SQL, I normally use SELECT INTO statement as follow.

view plain print about
1<!--- It's for all data fields --->
2<cfquery name="qryCopy" datasource="myDSN">
3    SELECT *
4    INTO tblPerson_Backup
5    FROM tblPerson
6</cfquery>
7
8<!--- For specific data field --->
9<cfquery name="qryCopy" datasource="myDSN">
10    SELECT NAME, DOB, SEX
11    INTO tblPerson_Backup
12    FROM tblPerson
13</cfquery>

[More]

This entry was posted on December 30, 2010 at 4:44 PM and has received 2006 views. There are currently 0 comments. Print this entry.

Change default submit action with Javascript

Dec 29

posted by Pyae Phyoe Shein in Coldfusion, Javascript, HTML | 0 Comments Bookmark and Share

We have one issue to solve the bugs come back by clients yesterday. The problem is when our client change quantity in input box of the item and press enter, this item is oddly disappeared from the main screen. So, I need to troubleshoot this issue and read through this code again. Obviously, I found that there are two submit buttons in this found.

If there are two submit buttons in a form, do you know which submit button will work when we press enter?

view plain print about
1<form name="frmDoubleSubmit" method="post" action="index.cfm">
2    <input type="Text" name="txtName"><br>
3    <input type="Submit" name="btnDelete" value="Delete">
4    <input type="Submit" name="btnSave" value="Save">    
5</form>

[More]

This entry was posted on December 29, 2010 at 5:15 PM and has received 2257 views. There are currently 0 comments. Print this entry.

Create check simple Numeric value in Oracle

Dec 27

posted by Pyae Phyoe Shein in Oracle, Coldfusion | 0 Comments Bookmark and Share

Creating check numeric value in Oracle is simple. Because TO_NUMBER function already exists in Oracle but I cannot control error handling.

with TO_CHAR,

view plain print about
1<cfquery name="chkQry" datasource="DSN">
2    select to_number('11') results from dual
3</cfquery>

Above coding, the result will be "11" because it's valid numeric value.

view plain print about
1<cfquery name="chkQry" datasource="DSN">
2    select to_number('11P') results from dual
3</cfquery>

Above coding, Oracle will output "ORA-00900" error code and the rest process will be terminate.

[More]

This entry was posted on December 27, 2010 at 11:03 PM and has received 1768 views. There are currently 0 comments. Print this entry.

NVL and NVL2 in Oracle

Dec 23

posted by Pyae Phyoe Shein in Oracle, Coldfusion | 0 Comments Bookmark and Share

I always use NVL when I replace NULL string to another value in Oracle. It's very useful and prevent database error by Oracle.

Here is usage of NVL.

view plain print about
1NVL(string, show_if_string_is_NULL)
2
3<cfquery name="getMS" datasource="myDSN">
4    SELECT NVL(DOB, SYSDATE) DateOfBirth FROM tblPERSON
5</cfquery>

[More]

This entry was posted on December 23, 2010 at 4:47 PM and has received 1842 views. There are currently 0 comments. Print this entry.

ROWNUM in Oracle and maxrows in Coldfusion

Dec 21

posted by Pyae Phyoe Shein in Oracle, Coldfusion, CF Tag, MsSql | 2 Comments Bookmark and Share

Normally, I always use maxrows attribute of CFQuery when I want to display limited records from table. Sometimes I want to display limited records from table with built-in Microsoft SQL command like TOP in Oracle. Yesterday I found that there is ROWNUM command in Oracle which can display limited records like maxrows in CF and Top in SQL. That's why I feel happy to use this command in my query. On the other hand, I want to test the performance between maxrows and rowcount.

So I've tested as follow

With maxrows

view plain print about
1<cfquery name="qryPerson" datasource="myDSN" maxrows="100">
2    select NAME, DOB from nh_person
3</cfquery>

Query execution time for above statement is 31ms and debugging output is as follow

[More]

This entry was posted on December 21, 2010 at 4:55 PM and has received 2116 views. There are currently 2 comments. Print this entry.

404 status code in Window 2008 R2 with Coldfusion

Dec 15

posted by Pyae Phyoe Shein in IIS7, Coldfusion | 0 Comments Bookmark and Share

Today I need to install brand-new server so-called window 2008 r2 for Coldfusion multiple-instances. Hardware developer installed only OS and IIS for me. As for me, I need to install Adobe Coldfusion 9 for multiple instances. Installing Adobe Coldfusion 9 is very simple. And I've done step by step installation. Once finished installation, normally Adobe Coldfusion 9 configuration will be come out instead of 404 Error screen as follow.

[More]

This entry was posted on December 15, 2010 at 5:27 PM and has received 3319 views. There are currently 0 comments. Print this entry.

ORA-00907: missing right parenthesis with Coldfusion

Dec 14

posted by Pyae Phyoe Shein in Oracle, Coldfusion | 2 Comments Bookmark and Share

Today I encountered the following error from Oracle ORA-00907: missing right parenthesis. That's why I've kept tracking for this error and reading the error logs message from Oracle. Unfortunately, I've found so many solutions and problems because of this errors.

Some DB developers said it's because of single quotes, Query structure and so on. As for me, it's because of missing query command.

Following is my query statement which led this error to come out.

view plain print about
1<cfquery name="qryTest" datasource="myDSN">
2    SELECT INV_TRANSACTION_ID FROM INV_TRX_TMP
3    WHERE
4        TRX_TYPE_CODE = <cfqueryparam
5                            cfsqltype="CF_SQL_INTEGER"
6                            value="01">
7    <cfif Type EQ "A">
8        PHM_TRANSACTION_ID = <cfqueryparam
9                                cfsqltype="CF_SQL_INTEGER"
10                                value="#TRANSACTION_ID#">
11    <cfelse>
12        OE_TRANSACTION_ID = <cfqueryparam
13                                cfsqltype="CF_SQL_INTEGER"
14                                value="#OE_TRANSACTION_ID#">
15    </cfif>    
16    AND TRANSACTION_DATE = <cfqueryparam
17                                cfsqltype="CF_SQL_VARCHAR"                                
18                                value="#lsdateformat(now())#">
19    ORDER BY INV_TRANSACTION_ID DESC
20</cfquery>

Regarding some developers suggestions, I thought it might be cause of lsdateformat. I encountered this error again even I've removed. So I need to carefully look through above query. Finally, I got it. It's because of "AND" is missing in front of PHM_TRANSACTION_ID and OE_TRANSACTION_ID. It's my careless mistake for sure.

Some solutions:

http://oraclequirks.blogspot.com/2008/01/ora-00907-missing-right-parenthesis.html

http://www.dba-oracle.com/sf_ora_00907_missing_right_parenthesis.htm

http://forums.oracle.com/forums/thread.jspa?threadID=355823

This entry was posted on December 14, 2010 at 6:38 PM and has received 2466 views. There are currently 2 comments. Print this entry.

Declare PARAM and assign default value in Oracle

Dec 12

posted by Pyae Phyoe Shein in Oracle, Coldfusion | 0 Comments Bookmark and Share

As I told in my previous posts, I'm very new to Oracle and have about 1 year experience in Oracle. That's why I'm still learning Oracle and trying to enhance myself day after day. Today, I know how to assign default value after declaration parameter in Oracle. I feel nice because it's very useful and don't need to waste time for assign value after it.

It's as follow:

view plain print about
1L_DEFAULT_ID    NUMBER := 99;
2L_DEFAULT_CODE    VARCHAR2(20) :='PPS-0005';
3L_DEFAULT_DATE    DATE := SYSDATE;

To compare with CF;

view plain print about
1<cfparam name="L_DEFAULT_ID" default="99">
2<cfparam name="L_DEFAULT_CODE" default="PPS-0005">
3<cfparam name="L_DEFAULT_DATE" default="#now()#">

This entry was posted on December 12, 2010 at 11:59 PM and has received 1449 views. There are currently 0 comments. Print this entry.

Test performance between Ternary and IF-THEN-ELSE

Dec 10

posted by Pyae Phyoe Shein in CF Function, Coldfusion, CF Tag | 4 Comments Bookmark and Share

Some CF developers mention that using IIF might leak your memory than IF-THEN-ELSE. I cannot convince why they told like that at that time. If so, why Adobe would add IIF in functions list. But I forgot to differentiate between IIF and IF-THEN-ELSE at that time. Now, it's time for me to differentiate between Ternary and IF-THEN-ELSE.

I've create following coding for these two operators.

view plain print about
1<cfset sTimer1 = GetTickCount()>
2<cfloop from="1" to="100000" index="i">
3    <cfset myparam = (i MOD 2 EQ 0) ? "A" : "B">
4</cfloop>
5<cfset eTimer1 = GetTickCount()>
6<cfoutput>Ternary : #eTimer1-sTimer1#</cfoutput> <br>
7
8<cfset sTimer2 = GetTickCount()>
9<cfloop from="1" to="100000" index="j">
10    <cfif j MOD 2 EQ 0>
11        <cfset myparam = "A">
12    <cfelse>
13        <cfset myparam = "B">
14    </cfif>    
15</cfloop>
16<cfset eTimer2 = GetTickCount()>
17<cfoutput>IF-THEN-ELSE : #eTimer2-sTimer2#</cfoutput>

[More]

This entry was posted on December 10, 2010 at 9:05 PM and has received 2255 views. There are currently 4 comments. Print this entry.

Simple float table header with CSS

Dec 8

posted by Pyae Phyoe Shein in Coldfusion, Stylesheet | 9 Comments Bookmark and Share

Yesterday I need to upgrade GUI and function of existing project. One of files in this project, our old programmer use very very awesome code as follow:

view plain print about
1<span style="position:absolute;z-index:9">
2    <table width="100%" border="1">
3        <tr>
4            <th>ID</th>
5            <th>Name</th>
6            <th>AGE</th>
7        </tr>
8    </table>
9</span>        
10<table width="100%" border="1">
11    <tr>
12        <td>1.</td>
13        <td>PPShein</td>
14        <td>28</td>
15    </tr>
16    <tr>
17        <td>2.</td>
18        <td>Rob</td>
19        <td>27</td>
20    </tr>
21</table>

I cannot convince why he/she didn't use simple CSS for floating table header. I think he/she might be weak in CSS for sure. Because look through above coding, some of web developer might say it's dirty code or not up to HTML structure. Ok, I need to edit something in this file for float table header as follow:

[More]

This entry was posted on December 8, 2010 at 4:41 PM and has received 4866 views. There are currently 9 comments. Print this entry.

More Entries

Top of Page