APEX - Grab value from Shuttle and put it into Textarea with carriage return

In some design, you may require to grab the value from Shuttle and put those value into a new Textarea or even in email HTML template. How am I going to do that?

What you need is a new Textarea so that the values you about to grab will be put inside. Lets put the Shuttle as P1001_X and assume the new Textarea as P1001_TXTAREA.

One solution can be used is a Computations (After Submit). You need to right click the Computations > Create > Item on This Page. Fill in those required value in that form and select [Item Value] in the Computation Type. In Computations field, put below code:

:P1001_TXTAREA := REPLACE( :P1001_X , ':', (chr(13)||chr(10)) );

What above code will do is it will grab each value from Shuttle, together with new line feed and carriage return.

Information:
chr(13) : Carriage Return
chr(10) : New Line Fees

APEX - How to grab value from Shuttle and save it into database

So you are using Oracle APEX shuttle in your form and will give the ability to user to select multiple value from the list. The next challenge is to store the selected values into the Oracle database.

Oracle APEX Shuttle : How to do it?

In this post, I will talk about storing the multiple values into Oracle database in a way of storing the value one by one. Meaning that, if there are 4 active selection, the operation will be storing or insert the 4 value into Oracle table each row. So there will be 4 insert operation will be executed.

Please check below code:

declare
    tab apex_application_global.vc_arr2;
begin
    tab := apex_util.string_to_table (:PX_YOUR_ITEM);
    for i in 1..tab.count loop
        insert into YOUR_TABLE (YOUR_COLUMN_NAME, YOUR_SHUTTLE_COLUMN_NAME)
        values
('My List', tab(i) );
    end loop;
end;

Please note that :PX_YOUR_ITEM is referring to your Shuttle Item name.

Where should I put those code?

1.Page Processing > Processing > Process, right click > Create

2.Select PL/SQL and you may procees the rest

3.When you see 'Enter PL/SQL Page Process', paste the code here. Please change accordingly follow your item page name and table/column in your database.

Stylish Blogger Label using CSS

There are so much interesting you can do if you know much about CSS. For example for my current website design, I am using customize CSS to manage how the listing will behave. You can see a sample of it at my site now or at below image.

I am going to share you all the CSS here. Check out this CSS line:

.Label {  padding:4px 0;  width: 100%;  }
.Label ul{  padding:0;  margin: 0;  }
.Label ul li{ font-size:12px;clear: both;  display: inline;  float: right;  margin: 0 0 8px;  padding: 0; text-align:right; color: #00000;}
.Label ul li a{  background: #ff9900;  color: #FFFFFF;  display: block;  font-family:Verdana,serif;  padding: 4px 12px; font-size:11px;  }
.Label ul li a:hover{  background: #FFEF66;  color: #222222;  text-decoration: none;  }

 Just go Blogger Template Designer > Advance > Add CSS . This the location where you should paste. But remember to add the Label widget before or after you complete above step.

Need help? Post a comment below. :)

Part II : Create a page with multiple forms linked to multiple tables

Previously I has written about creating a page with multiple forms linked to multiple database. You can read it here. But, what I am going to post now is about trigger upon insert action, and how to pass the value into another page item.

Simple example is like this:

You are created 2 separate form in one page in APEX environment. One Submit button operation, all the data will be insert accordingly into their respective table, let say Table1 and Table2. Table1 has an auto generate ID by trigger on insert operation. And this Table1.ID was require to be pass to Table2. This scenario can be said as one to many relational table inside database.

I have tried to use the DML operation to pass the primary key into another page item unfortunately has failed. The workaround of it was to use 'RETURNING' clause in PL/SQL.

Check-out SQL below:

insert into Table1 ( column1, column2, column3) values
( :P1_Data1, :P1_Data2, :P1_Data3)
returning YourPrimaryKeyColumnName into :P1_YourTargetPageItem;

I took a lot of time searching how to do it. If you need more details on this, just let me know here.

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.

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’

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’.

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.

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.

Free hosting for blogging–Blogspot (Blogger)

Like many other free blogging platform available like Blogger, Wordpress.com, TypePad, and Weebly, they certainly will set limitation of their services.

Since I am a Blogger user, I will talk about blog limitation on Blogger.

Actually, first thing came across my mind is, How Many Blog can I have in Blogger?. Some of internet user created their blog account for some various reason. It can be for personal blog, just for fun, even to make some money from affiliate program and advertising program like Adsense. For me, I have multiple various reason creating an account for Blogger. For example like my site here purposely to put a notes on my journey of exploring internet and IT knowledge.

Well, the answer of my first thought is in Blogger you may have up to 100 blogs per account. Pretty much right? And there is no limit of storage size per account in Blogger. Actually, Blogger "limits" here aren't really very limiting. Below are the important “limits” as in Blogger support page. You can read it below or simply go to their original page here.

Number of Blogs: You can have up to 100 blogs per account.

Number of Posts: There is no limit on the number of posts you can have on one blog. They will all be saved on your account (unless you manually delete them) regardless of whether you are publishing archives or not.

Size of Posts: Individual posts do not have a specific size limit, but very large posts may run you up against the page size limit. (See the next item.)

Size of Pages: Individual pages (the main page of your blog, or your archive pages) are limited to 1 MB in size. This will allow for a few hundred pages of text, but it may be a problem if you are listing hundreds of posts on the front page of your blog. If you hit this limit, you will see an error message saying "006 Please contact Blogger Support." You can get around this error by lowering the number of posts on your main page, which will have the added benefit of making your page load faster as well.

Number of Comments: A post can have any number of comments. As with archived posts, if you choose to hide comments on your blog, all pre-existing comments will remain saved on your account.

Number of Pictures: Up to 1 GB of total storage, shared with Picasa Web

Size of Pictures: If you are posting pictures through Blogger Mobile there is a limit of 250K per picture.

Team Members: There is a limit of 100 members per blog.

Number of Labels: Up to 2000 unique labels per blog and 20 per post.

Blog Description: Limited to 500 characters, with no HTML. Adding additional characters or HTML may cause it to revert to a previous setting.

"About Me" Profile Information: Maximum of 1,200 characters.

Profile Interests and Favorites: Maximum of 2,000 characters in each field.

How to align to left for Blogger Simple Template

All Simple Template inside Blogger was designed with centered align for the the whole page. However we can align the page according to your customization needs. It can be either left, center or right.

First thing first

It is important to identify the DIV element in your blog template. As per provided in Simple Template, the body layout can be customize to 1 column, 2 columns and even 3 columns. Identify the DIV element of it, so that later we are modifying the correct element to make it align as per our needs.

How to identify it easily?

In this post I will talk particularly on Chrome browser. Chrome provided a very useful, wonderful tools to check the element inside the webpage.

1. Open your blog page on new tab if you not yet opened it. Right Click at anywhere at the page. Look for 'Inspect Element'
2. You now will see a new page divider below it. Here you will see all the html code, DIV element, CSS and also a script associate to the page.

3. From above image, I have identify the correct element for me to alter. This will be use to align my page to left (I wanted to make it left)

4. Go Blogger Template Designer > Advance > Add CSS . Enter the code below: (If you do know where Blogger Template Designer, click on this post here )

.content {float:left}

5. Click 'Apply to Blog'Now your blog successfully aligned to left. Happy editing.

Remove Blogger Navigation Bar on top of the blog

On some some reason that you wanted to remove the navigation bar for blogger blog, that was a piece of cake. But this is only applicable if you are using Blogger Simple Template for your blog.

1. Go to Layout. You will see a page as in Image below. Look for 'Navbar' and click 'Edit'.
2. On Navbar Configuration > Select 'Off"
3. Click 'Save'

You will not see the navbar after you have perform above step.

How to control Image display and properties in Simple Blogger Template

If you are using a Simple Blogger Template as your blog theme, you may notice that the image inside the post body will be display as maximum resolution if you select the image to full size when you attaching the image.

To take full control over it, you can customize it using CSS. This setting can be done in Blogger Template Designer. Just follow these below steps:

1. Go To Template > Live on Blog : Customize

2. Advance > Add CSS

3. Add code below:

.post-body img{box-shadow:none;max-width:100%;}

4. Click 'Apply to Blog'

*max-width:100% tells the browser to shrink or shrank the image to follow 100% of post-body width.

*box-shadow:none; I do not like shadow all around the image. So I set it to none.

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.

What Does Windows x64 and x86 Mean?

Sometimes people will easily get confuse. Common sense perspective will easily says x86 is higher than x64. And some will miss interpret saying that x64 refer to 32bit and x86 is referring to 64bit operating system.

x64 is 64bit platform while x86 is 32bit platform for Operating System.

As the x86 term became common after the introduction of the 80386, it usually implies a binary compatibility with the 32-bit instruction set of the 80386. This may sometimes be emphasized as x86-32 to distinguish it either from the original 16-bit x86-16 or from the newer 64-bit x86-64 (also called x64). Although most x86-processors used in new personal computers and servers have 64-bit capabilities, to avoid compatibility problems with older computers or systems, the term x86-64 is often used to denote 64-bit software, with the term x86 implying only 32-bit.

How to check your PC Operating System running on what bit using Windows command prompt?

systeminfo <--type this and press Enter in command prompt

System Manufacturer:       LENOVO
System Model:              4236NUA
System Type:               x64-based PC
Processor(s):              1 Processor(s) Installed.
                           [01]: Intel64 Family 6 Model 42 Stepping 7 GenuineIntel ~775 Mhz
BIOS Version:              LENOVO 83ET61WW (1.31 ), 7/7/2011
Windows Directory:         C:\Windows
System Directory:          C:\Windows\system32
Boot Device:               \Device\HarddiskVolume2
System Locale:             en-us;English (United States)
Input Locale:              en-us;English (United States)

Oracle Application Express (APEX) and ASP.NET

I am digging around of the benefit using Oracle Application Express (APEX) compare to ASP.NET in building my website for current job needs. Because I am getting the direction from superior to study and start using APEX instead of ASP.NET for my web development.

At first, when I look into Oracle website about APEX, I can imagine how easy my work will be. I mean the website will be easy to create, offer easy connectivity to Oracle database but still, I will need to connect my website to MSSQL server to store data input by user.

Anyway,let look at what other people has say about Oracle Application Express (APEX) versus ASP.NET (Visual Studio).

APEX is a framework that uses the database and PL/SQL to produce web pages. If you can figure out what the output to the browser will need to be you can create it in APEX. If you find any part of the framework inhibiting you can write PL/SQL procedures and expose them to the web server directly but still take advantage of the security, logging, session state, etc that the APEX system manages for you.

You should know PL/SQL, SQL, HTML, JavaScript and CSS. Sure the interface looks like a big data entry application but the data you enter will mostly be code snippets in each of these languages.

It scales as well as the database does. It typically uses Apache as a web server but is only used to serve static files and pass requests back to the database, where the web pages are created by the PL/SQL code in the APEX schema. You can use AJAX to minimize the size of the traffic traveling up and down the pipe. You can set caching for specific items, lists, page regions, pages, etc. 

Since most things are pretty simple to do with the framework, naturally there will be some things that are a little more complicated to do within the framework. The color coding example given above might be something you do with CSS or maybe you would need to turn to print statements to produce the output you need. The thing is to learn the how the framework makes life easier and then when you hit a limit you can easily resort to more direct methods.

Coming from VB.Net you will miss the step by step debugging and the drag and drop. You will never miss the fact that some part of the page lifecycle will do a bind and reset the values you bound to an object in another part of the page.

This fella have a huge migration project. What has he say about APEX?

I am involved in a huge project to migrate a 5000 module Oracle Forms application to Apex. This is an extreme use of Apex, but it's working just fine. It is a complete myth that Apex is suitable only for small internal apps built by DBAs, interns or end users: it is certainly suitable for those too (and more suitable than most other tools), but it can also be used to build extremely sophisticated applications. 

To build a sophisticated application (rather than a default out-of-the box Apex one) you will need someone on the team with Javascript skills, and someone with CSS skills. But most developers will just need PL/SQL initially. 

Is it scalable? Yes: probably more scalable than most other solutions! Apex adds very little overhead to the database server, and only the most minimal of application servers is required. "Facebook size"? I don't know for sure but I don't see why not, assuming you have an Oracle database on a machine large and powerful enough to handle "Facebook size" data and transaction volumes. Like any Oracle project, scalability is impeded mostly by bad database designs and poorly written SQL, not by the tool.

The good side of APEX has been discussed too.

The Good

• Incredibly easy to generate a respectable web app with basic CRUD data entry, simple reporting and populate it with data. If you're the IT guy who's been tasked with consolidating a company's mess of Excel/Access dbs into a central DB/web environment then you should take a look at APEX, it very well suited for this task. If you expect the scope to grow to something of even moderate complexity then I would move straight to a more flexible framework.
• If you are a DBA/PLSQL guru but have no experience with traditional web development you'll be well prepped to expose existing business logic in a web app without stuffing around with HTML/CSS/JavaScript if you dont want to.
• APEX support forum has a ton of info and is well staffed by APEX devs.

Take a look into the bad site of APEX compare to ASP.NET.

The Bad

• My experience with Apex began to go downhill when apps moved beyond CRUD data entry and required more dynamic and event driven behaviours.
• The web based GUI is not cool. Debugging is painful.
• When you (inevitably) need to do anything outside the limited scope of the framework, you'll have to get your hands dirty with PL/SQL. Writing business logic against the database is fine, but generating HTML from PL/SQL procedures felt uncomfortably archaic in 2007.
• Given the large number of sneaky places you can hide page and redirection logic, the program flow is both difficult to visualise and not naturally conducive to modular, separable and reusable code. OOP developers will be not be impressed. It's possible to have well structured maintainable applications with APEX but its harder than it should be. This is worlds away from MVC.
• Unacceptable number of framework bugs in the versions I used. I'd hope this has improved with recent versions, but the paradigm of integrating the IDE into the APEX platform itself caused me some of the darkest, soul destroying debugging sessions of my life. As an example, I was trying to reproduce an intermittent bug that would cause a user to lose their session data. Using the session information popup I saw that occasionally the session data would change when it shouldnt have. I spent 2 days trying to find the error in my code with no luck. Near delirious, I noticed by pure chance that I could reproduce errorous session data in the debug window but the application itself wouldnt go into an error state. My heart sunk when I realised what might be happening. Oracle later confirmed that I'd found a bug in APEX that caused the session information window to intermittently show me data from a prior session. I'd wasted 2 days debugging a session related bug with a buggy session debug window. That was the last Apex app I built.
• PL/SQL is not and will never be the Next Big Thing in web development. After working with APEX for a while I realised it wasnt going to make me a better web developer. Mastering APEX is really about PL/SQL. Thats fine if you plan to focus your career on Oracle technology, just be aware that APEX is so tangential to the direction of mainstream web technologies that the portable set of skills you can take from APEX to other web frameworks is minimal.

Optimize Your Blogger Blog Titles For SEO Friendly

There are so many blogger template available over the internet for you to download. However, if you would like to use template which has provided default by Google, some of blogger say it may be useful to alter the blog title instead using default by Google.

What will this alteration do? Basically if you look into your blog post title on the top of the browser, it will normally display as YourBlogName: BlogTitle. Well, after we alter some coding in the file, the blog post title will change to just BlogTitle, which the YourBlogName will disappear. In Search Engine Optimization perspective, this changes will helps to improve SEO merit.

How to Change Blog Titles for SEO

1. Log in to your Blogger Dashboard and go to Template > Edit HTML
2. Find (CTRL + F) this code:

<title><data:blog.pageTitle/></title>

3. Replace the code above with this one:

<b:if cond='data:blog.pageType == "item"'>
<title><data:blog.pageName/> |<data:blog.title/></title>
<b:else/>
<title><data:blog.pageTitle/></title> </b:if>

4.Click Save Template and you are done.

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.

How to solve TOAD Error "00.00.0000 00:00:00" is not a valid date and time

There is something weird about my TOAD when I tried to export query result from my TOAD application into Excel file. The error was something like this : "00.00.0000 00:00:00" is not a valid date and time.
I spent a lot of time searching for solution for this error. Luckily I was not so urgently need the data. I have time to google it and find the solution. It is so true when finally I found that it was actually related to Real Player installation in my machine. I do not know why this Real Player download and installation affected to my TOAD program, but this is really a bug which Real staff needed to be notify.
After searching over the net, you may wanted to try listed step I have provided as below.

1. Simply open TOAD, go to Utilities > Task Scheduler (this is somehow will trigger the error pop-up again)
2. From the task listed, locate for RealCreateProcessScheduledTask***** (it has a longer character than this)
3. Delete this task 

If above step was not so succesfull, you may need the other way around. Actaully above step was not working for me. Below step truely works. You may need a command promt running to delete this task, because normal Task Scheduler will not give you the complete list of task when running on your machine.
My machine running Windows 7 Pro, and to accomplish this, I need a full administrator access to delete this task. How to do that?
Find command.exe from your Windows\System32 folder. Right Click and click "Run as Administrator". When the command prompt has appear, do the following:

1. Type 'schtasks /query | more' and enter to list all current task
2. Locate the RealCreateProcessScheduledTask***** and copy it over (make sure you copied all full task name of this. If you screen limit it, you can copy the full task name from the TOAD program)
3. Excute this command 'schtasks /delete /tn [ProblemTask]', which [ProblemTask] is referring to RealCreateProcessScheduledTask***** (full task name).
4. You will see a prompt notification to confirm deletion, press Y and Enter.
5. Now you can do export in TOAD with no more error.

Please leave a comment below if you want to add anything.