Apex copy page does not change items consistently

Being able to copy a whole page with all its items, region, processes and logic with just a few clicks is just awesome right? Today I was confronted with an inconsistency (could be either a bug or a feature) in Apex 4.2.

When you copy a page, Apex copies and renames all the items. An item :P1_NAME on page 1 will be copied to page 2 and named :P2_NAME  and so on. A cool feature is that processes are copied too and whenever a page item is referenced in a page process, the copied process will reference the corresponding copied page item.

Our development team often uses this copy functionality, either to back up a page or to test some implementations. What we found today is that in the case of conditions, Apex does not change the items, as you would expect it too. In our case specifically, we have a report on a page with one conditional column. The column was rendered only if a page item had a specific value.
For example:
On page 1, region Employees, column Salary should only be rendered when item :P1_SALARY_VISIBLE has a value 'Y'. After copying this page to page 2, you would expect that the condition on column Salary would reference :P2_SALARY_VISIBLE, but it does not. It still references the item on Page 1.

I should test if it still happens in Apex 5, but I'm already tired of finding this bug.

ORA-01427: single-row subquery returns more than one row... but not consistently?

Today we had an issue with saved reports. A clients application has a page with an interactive report based on a view. The source of this report is fairly simple:

select * from some_view; 

What could go wrong right? For one thing, some of the saved reports were throwing an ORA-01427 error and some were not. Even worse, the view did not have any errors and running the query in SQLDeveloper did not result in errors.

Eventually we found the problem. SQLDeveloper shows the first 50 records. It's very possible that an error takes place in a result set which is not shown. In our case the view was something like:

select p.last_name
     , p.first_name
     , p.date_of_birth
     , p.city
     , (select c.country
          from cities c
         where c.city_name = p.city
        ) country
  from persons p; 

So I guess the lesson learned is, that when you have select statements in the select part of your query, you should make sure to return all rows when debugging.

Oooorrrr... like my mentor Frank would say: "Don't put a select in the select part of your query!".

Recover a deleted Apex page

Sometimes, actually more than sometimes I delete stuff that should not have been deleted. I guess working with SVN and good old windows Ctrl + Z spoiled me.
So when I deleted an important page I initially thought Apex also had an "magically get my page back and save my ass" functionality.
Newsflash: Apex does not have that! ... Or does it? :)
After some googling around I found an article by Peter Raganitsch explaining how to "hack" the export page functionality to do exactly this.
Check the article for a detailed explanation and pictures. It references Apex 4, but the method is still applicable.
In simple steps:

1. Head over to the export Page functionality
2. Use Inspect Element on the Page select list, because your deleted page will not be in the list.
3. Edit the HTML to add your deleted page
4. Use the "As of xx minutes ago" field to go back in time
5. Press the export page button and witness the magic

This functionality uses Oracle's FLASHBACK functionality, so how far you can go back is dictated by related settings. Your mileage may vary.

Run sql commands from Notepad++

Notepad++ is the standard and favorite text editor for a lot of us, including me. At one time I thought that it would be great if i could run sql statements straight from Notepad++, because it's the default application that opens all my .sql files.
 Lo and behold, the nice guys at Toadworld already found out how and cared enough to post it years ago. Here are the steps copy-pasted from their article:

1. Launch Notepad++
2. Main menu → Plugins → Plugin Manager → Show Plugin Manager
3. Available Tab, Find and check NppExec plugin (see Figure 3 below)
4. Press Install button to download & install plugin – restarts Notepad++
5. Open a SQL script
6. Press F6 key (NppExec’s default execute keyboard mapping)
7. Enter the following macro script into the Execute pop-up (see Figure 4 below)
   
   set ORA_USER=bert
   set ORA_PASS=bert1234
   set ORA_SID= ORCLnpp_save
   cmd /c copy /y "$(CURRENT_DIRECTORY)\$(FILE_NAME)" "$(SYS.TEMP)\$(FILE_NAME)" >nul 2>&1cmd /c echo. >> "$(SYS.TEMP)\$(FILE_NAME)"cmd /c echo exit >> "$(SYS.TEMP)\$(FILE_NAME)"sqlplus -l $(ORA_USER)/$(ORA_PASS)@$(ORA_SID) @"$(SYS.TEMP)\$(FILE_NAME)"
    
8. Change the first three variables for your database, username and password
9. Press the OK button

 That's all there is to it!

Just replace "bert" with the schema username, "bert1234" is the password and "ORCL" is the SID eg:

set ORA_USER=hr
set ORA_PASS=hr
set ORA_SID=172.19.2.155/XE

Hit enter and see the magic. Don't forget to check the original article. It has a better explanation and also ... pictures!! Yay!

Textfield in upper or lower case

In most applications you would like to keep some text fields in upper case. For example: car license plates, ID numbers, passport numbers etc.

We can achieve this with a simple javascript in the custom attributes of the field.

onKeyUp="this.value=this.value.toUpperCase();"

Or similarly for lower case:

 onKeyUp="this.value=this.value.toLowerCase();"

See example here.

EDIT 24-apr-2018:
Alternatively we could also use CSS to display textfields in upper or lower case with:

 u-textUpper u-textLower

Be advised though, this only changes the display.

Checkboxes and values when paginating

So the basic requirement was simple: "There's this report on a page with checkboxes and these checkboxes should remain checked after pagination.". Hmmmm it's been done here, here, here and also ..... here? I think that last guy just copy pasted Martin D'Souza's blog without even a hint of crediting the original writer. Not cool dude. You could have taken the time to change the link though.

This is not the Martin you are looking for.

So, like this guy, we first used Martins method, which worked perfectly and then my colleague dropped this bomb: "Great! Now in this version of the report we also add values. Could you do the same for the text fields?". "Uuuhmmm okay, you do realize it's not that simple right?"
So long story short, with Serishma's help, I ended up modifying Joel R. Kallman's method to achieve it. I realized we could have used this method for the checkboxes too, but by then we implemented the first method on alot of pages so we didn't change it back.
I made a page on my demo app for future reference and might take the time to put all of the code on this blog.... someday. All of the code should be on the demo app.

Dead on Arrival?

♪ So no one told you life was gonna be this way. Your job's a joke, you're broke, your love life's D.O.A. ♪

Catchy tune right? I've been wanting to start blogging again since forever and honestly, this isn't my first time, not even my second. It's actually my third time starting a blog. Let's hope it's not Dead On Arrival but really "Databases, Oracle and Application Express". I'll try to keep every post short, simple and fun.