APEX – Create a page with multiple forms linked to multiple tables and 1 Submit Page Button for INSERT operation

I am having a problem to update into Oracle table. To be exact, I have created 2 separate form into 1 page. And those forms are actually binded with different table as well. My operation or design will be, when I click a ‘Submit’ button, all the data entered by user will be inserted into their respective table. Well, I got an error. What has been wrong?

Oracle APEX limitation?

After ‘googling’ (I wonder if this word has in dictionary..) around, I found that this is a known limitation for APEX. Well, obviously Oracle need to check further for this. You can only have one automated row fetch (DML) process per page. However, it does not means that we cannot add other form on the page. There are few a solution for that which works well for me.

Create Separate Region for separate Form

1. On Page Rendering, locate Region > Body and right click. Click ‘Create’ to create new region. This one will be your 1st Form.
2. Create Region, select ‘Form’ and press ‘Next’
3. Create Form, select ‘Table’ and press ‘Next’. Please just proceed until the wizard complete. The 1st Form will be straight forward.

The Important on Next Sequence Form

You may now see the new region successfully created from the wizard. The Form will populated text field or other field as per your selection.

APEX-Page Definition

Now, the next step we need to do will bit different.

4. Repeat same step 1 and 2, but for 3 select ‘SQL Query (fetch row) instead of ‘Table’

image

5. Press ‘Next’ until you found ‘SQL SELECT Statement’ page tab. Here will be SQL to populate the page item into the page. You can type it manually, or click [Query Builder] link.

6. The new page items will be populated according to your SQL. You can see the Form already created for you.

Setting the INSERT Operation

You may notice that there are few button automatically created inside the page. For me, I do not want it so many, so I created a new region for only 1 button. This button will do action INSERT for those 2 form above for 2 different table. Just make sure that the button action is ‘Submit Page’.

image

7. Since the 1st Form using Automatic Row Processing(DML), I just need to set the INSERT operation for Table #2 in Form #2. Go to Page Processing > Processes and right click on it.

image

8. Select PL/SQL and Next. Give your process a name and Next.

9. When reach at [*Enter PL/SQL Page Prosess] , enter you INSERT SQL statement. Remember to use page items in this SQL. For Example:

insert into Table2 (column1, column2, column3) values ( : P1_Item1, : P1_Item2, : P1_Item3);

Hopefully above steps works for you. If you got comment, leave your comment below.

Obtain machine IP Address using Oracle SQL command

I never know this function has exist until I was required to explore Oracle APEX to design a website. Here is the code, which can get an IP address of machine and the value can be store into a database or pass it into other variable for processing, or whatever your programming do.

select sys_context(‘userenv’,’ip_address’)  from dual

Have fun.

Oracle Database Express Edition 11g Release 2 not work for 64bit OS?

Oracle Database 11g Express Edition (Oracle Database XE) is an entry-level, small-footprint database based on the Oracle Database 11g Release 2 code base.  

It’s free to develop, deploy, and distribute; fast to download; and simple to administer.

 

Oracle Database XE is a great starter database for:

 

  • Developers working on PHP, Java, .NET, XML, and Open Source applications
  • DBAs who need a free, starter database for training and deployment
  • Independent Software Vendors (ISVs) and hardware vendors who want a starter database to distribute free of charge
  • Educational institutions and students who need a free database for their curriculum

 

With Oracle Database XE, you can now develop and deploy applications with a powerful, proven, industry-leading infrastructure, and then upgrade when necessary without costly and complex migrations.

 

Oracle Database XE can be installed on any size host machine with any number of CPUs (one database per machine), but XE will store up to 11GB of user data, use up to 1GB of memory, and use one CPU on the host machine.

 

But, it also has a limitation. This XE does not offer to 64bit Operating System. I was wondering why? Perhaps it is due to Oracle strategy in terms of sales, which at the end will bring user to choose a upper version of Oracle database, Enterprise edition.

 

Well, it make sense. If Oracel to offer everything for free. How are they going to make money from their Oracle licensing revenue.

 

However, some information from the internet has say that Oracle Database XE can be install in 64bit operating machine, somehow. Well, I am about to test it and see how true the statement was.

 

See you in my next post about this.

 

If you wanted to know more about Oracle Database XE, you can go to Oracle website here.

ASP.net how to execute query to Oracle using Button click

I would like to share a simple code which currently I am doing. This is a very basic application you may design for your website and require a database connection to Oracle databases.
First, lets go to the design of it. This is truely very useful for beginner like me especially you do not know where to start with. I am using Microsoft Visual Studio 2012 for Web to design my application website.

Second, talk about the Oracle connection string. I have quite sometime searching for correct oracle connection string that works (on my machine at least). You may copied it over, hopefully it will works on you too. Please change where necessary in the string follow your server configuration and setting.

“Data Source=(DESCRIPTION=” _
+ “(ADDRESS_LIST=” _
+ “(LOAD_BALANCE=YES)” _
+ “(FAILOVER=YES)” _
+ “(ADDRESS=(PROTOCOL=tcp)(HOST=YOUR_HOST_NAME)(PORT=YOUR_PORT))” _
+ “(ADDRESS=(PROTOCOL=tcp)(HOST=YOUR_HOST_NAME)(PORT=YOUR_PORT))” _
+ “)” _
+ “(CONNECT_DATA=” _
+ “(SERVICE_NAME=YOUR_SERVICE_NAME)” _
+ “)” _
+ “);” _

+ “User ID=YOUR_USER_ID;Password=YOUR_PASSWORD;” 

Now, when you double click on Button from the project, it will bring you to the new coding page. This file is normally ended with *.vb filename. Here will be the location to paste the Oracle connection string as pasted above. Below is how the code will be looks alike:

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        ‘Your Oracle connection Setting
        Dim strConnString As String = “Data Source=(DESCRIPTION=” _
                                    + “(ADDRESS_LIST=” _
                                    + “(LOAD_BALANCE=YES)” _
                                    + “(FAILOVER=YES)” _
                                    + “(ADDRESS=(PROTOCOL=tcp)(HOST=YOUR_HOST_NAME)(PORT=YOUR_PORT))” _
                                    + “(ADDRESS=(PROTOCOL=tcp)(HOST=YOUR_HOST_NAME)(PORT=YOUR_PORT))” _
                                    + “)” _
                                    + “(CONNECT_DATA=” _
                                    + “(SERVICE_NAME=YOUR_SERVICE_NAME)” _
                                    + “)” _
                                    + “);” _
                                    + “User ID=YOUR_USER_ID;Password=YOUR_PASSWORD;” 
        ‘Your sql statement goes here
        Dim strQuery As String = “SELECT cus_name from  tbl_cus where cus_ID = ‘” + TextBox1.Text + “‘”
        Dim con As New OracleConnection(strConnString)
        Dim cmd As New OracleCommand()
        cmd.CommandType = CommandType.Text
        cmd.CommandText = strQuery
        cmd.Connection = con
        Try
            con.Open()
            Dim sdr As OracleDataReader = cmd.ExecuteReader()
            While sdr.Read()
                ‘Here will be your output result
                Label1.Text = “Item”
            End While
        Catch ex As Exception
            Throw ex
        Finally
            con.Close()
            con.Dispose()
        End Try

    End Sub

Above code working just fine when I debug the project and click the button. This may help you to explore more on query to Oracle database for your project.

Problem open TOAD after install : You have no Oracle Client installed.

I encounter a problem with my TOAD for Oracle just now. It mentioned You have no Oracle Client installed .Wondering what is the problem and below was the screenshot of it.


You have no Oracle Client installed



The solution is very simple. And I also found the root cause of it.


Root cause: Toad do not support Oracle Client for 64 bit. As simple as that


Solution : Install Oracle Client 32 bit. You may install another copy of Oracle client, or you may remove the 64 bit which you previously installed.


To be particular, my machine was a Windows 64 bit platform, and I directly apply Resolution 2 as below.


Below are the statement from the Quest Software support site.
For more detail from the site: https://support.quest.com/Search/SolutionDetail.aspx?id=SOL39689
Cause
– There is no Oracle client installed on the machine or the client installed is 64-bit. Toad does not support a 64-bit Oracle client.
– Environment Variable Path is missing.

Solution for ‘You have no Oracle Client installed’ :

RESOLUTION 1:
Check to make sure there is an Oracle client installed on your machine. If there is not, you will need to install one. Please review solution SOL76452 for video demo and instructions on installing a 32-bit Oracle Instant Client. See additional information below for link to full 32-bit Oracle Client


RESOLUTION 2:
If there is an Oracle client installed on the machine, check to see if it is 64-bit. If it is, you will need to install a 32-bit Oracle client.


RESOLUTION 3:
1) Look for your OCI.DLL file in your computer and Copy the location path i.e. C:oracleproduct10.2.0client_1BIN
2) Right-Click your My Computer | Properties | Advanced Tab | Environment Variables button
3) Under ‘System Variables’ at lower half of screen, highlight ‘Path’ | click Edit
4) Under ‘Variable Value:’ | go to the very beginning of the existing entry | Paste the path you copied earlier
5) Add a semi-colon (;) | and then click OK, OK, OK.
6) Start Toad and re-test


RESOLUTION 4:
You installed an Oracle Client 10g version in a Windows 64-bit machine. There is an issue with the Oracle Client 10g version on a Windows 64-bit machines where 3rd party Oracle Tools like Toad for Oracle will not be able to see the client. Possible resolutions for this are:
– Install Toad in “C:Quest Software” folder instead of the default “C:Program Files (x86)” folder.
– Install and use Oracle Client 11g version instead. See the download link below for the client we recommend.


To download Oracle 11g Client software:
11g 64bit client
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html
11g 32bit Client
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html
You may need a Oracle Metalink account to download it.