Snippets and Thoughts of One Oracle Developer

I I was experimenting lately with page items and how to setup APEX page (preprocessing section) in more cleaner, reusable and generic way. I also wanted to move the logic from APEX to PL/SQL packages to fulfil my MVC vision (to keep as much as possible code in PL/SQL, not in APEX). So I made this up. For every page in application I have created a package, "P0" for page zero, "P100" for page 100... For 100 pages I would create 100 packages, but that is still better then have the logic scattered on individual pages. And most of the times I have to create packages anyway because I need a place where to store form/grid handlers and page related logic. Then I have created INIT_DEFAULTS procedures in each of these packages. And in this procedure I setup page items and run the pre-rendering processes. Since we are talking about packages, I can reuse other procedures and function and limit the amount of copy pasted code. I use my wrapper around these session state call...

E Everyone should know the power of WITH clause these days. For me this is a great way how to have more readable views and with some magic it also allows me to have dynamic views (well, the views returning different rows based on page items). You are asked to show a monthly based calendar on a page (and let's skip the Calendar region for demo purposes) based on a page item. Here is how you can utilize WITH clause on a regular query (returning a calendar data for a whole year): WITH days AS ( SELECT TRUNC(SYSDATE, 'Y') + LEVEL - 1 AS day, TO_CHAR(TRUNC(SYSDATE, 'Y') + LEVEL - 1, 'IYIW') AS week, TO_CHAR(TRUNC(SYSDATE, 'Y') + LEVEL - 1, 'MM/YYYY') AS month FROM DUAL CONNECT BY LEVEL <= ADD_MONTHS(TRUNC(SYSDATE, 'Y'), 12) - TRUNC(SYSDATE, 'Y') ) SELECT d.month, d.week, MAX(DECODE(TO_CHAR(d.day, 'DY'), 'MON', TO_NUMBER(T...

T This is a followup for part 1 which was mainly about setting up the SQL Developer interface for more efficient work. In this part I would like to cover tips for developers. Shortcuts mentioned in these articles might not be the default ones. My bonus tip on shortcut is: if you can't remember shortcut, then change it to what you find intuitive. Editor First weird thing I see all the time is missing line numbers. It is so much easier to navigate through the code especially and cooperate with others when you actually can use line numbers as references. Turn that on in Code Editor - Line Gutter. Also you can quickly jump to specific line by pressing [ctrl+g] (like goto). I use text transformations a lot. Keywords to upper case, identifiers to lowercase. I try to setup auto format as close as possible to my desired look, but mainly I use it just to format legacy code to understand it better. You can download my beautifier setup at the end of this article. To quickly fix ...

H Here are some tips for my fellow developers using SQL Developer . The web version is getting a lot of attention and getting better every day, however I use the desktop version and this article is about that. For anything about SQL Developer you should visit Jeff's blog , it is a bottomless well of wisdom. Cleaning up I like simple tools. I don't like clutter and too much options (like Toad), it is distracting. So lets close the panels you don't need. Are you really using Reports panel? If not, close it, you will have more space for Objects Browser and panels you really use. On the left panel I use Connections, Find Database Objects and SQL History. You can turn them on or off in View menu. I also use Bookmarks and Code Outline at the bottom, but just when I need that. At the bottom I use Compiler Log, Bookmarks, Code Outline, but I am also closing these when they are not needed. There are more panels, you should explore options under the main menu View, you mi...