APEX-AT-WORK by Tobias Arnhold

Browsing "Older Posts"

APEX CSV exports with strange line breaks inside

Von Tobias Arnhold → 1.13.2010

I came across a problem with line breaks inside my automatically generated csv export.
It had to do with some field values which had some line breaks inside. I updated the effected columns like that:


select replace(replace(my_column,CHR(10),' '),CHR(13),' ') AS column_without_line_breaks,
from tbl_test

An alternative way could be this:
RTRIM(my_column, CHR(10) || CHR(13))

SQL Developer

Set up the SQL Developer layout/environment in English

Von Tobias Arnhold →

Just a short hint for those who wants to use the SQL Developer in an English environment.

You have to edit the following file:
<SQL_DEV_ROOT_PATH>\sqldeveloper\bin\sqldeveloper.conf

It's important to add these two lines:
AddVMOption -Duser.language=en
AddVMOption -Duser.country=US


IncludeConfFile ../../ide/bin/ide.conf

SetJavaHome ../../jdk

AddVMOption  -Doracle.ide.util.AddinPolicyUtils.OVERRIDE_FLAG=true

AddVMOption -Dsun.java2d.ddoffscreen=false

AddVMOption -Dwindows.shell.font.languages=

AddVMOption -Duser.language=en
AddVMOption -Duser.country=US

AddVMOption  -XX:MaxPermSize=128M


IncludeConfFile  sqldeveloper-nondebug.conf

APEX examples

Some Christmas goodies for APEX

Von Tobias Arnhold → 12.03.2009

The German Oracle APEX team created a really nice Christmas countdown calendar. Unfortunately it's in German but just use Google translate and you will understand probably most of it. :)

It's a really nice idea.
Thanks Carsten.

Tools

Create your own holiday dvd movie with freeware tools

Von Tobias Arnhold → 11.29.2009

This time my post has nothing to do with APEX nor Oracle.
You all know the situation. You are the one who knows everything about computers, at least thats is the opinion from family and friends. And of course creating a DVD movie from your last holiday should be as easy as a short *snap* with your fingers.
In case you are the one "WHO KNOWS" may this little collection of free picture/sound/video editing tools make your life easier. At least you don't have to google them now. :D

1. Download the pictures/videos from your camera to a local directory
2. Create MPG files from your video files (like AVI, FLV,...). Use the tool: Any Video Converter
3. Create a slideshow movie with DVD slideshow GUI
- Downloads and installs all necessary other tools like Avisynth, Imgburn, Xvid and ffdshow automatically.
- For movies DVD slideshow GUI requires MPG files
- Save the downloaded version of DVD slideshow GUI because it can be so that you wont be able to use your current project in newer versions of the tool.
4. Create a M2V file with ProjectX from your newly created MPG file
5. Create a audio file for your movie with Audacity
5.1 Download some music/sounds from Youtube which fits to your movie with DVDVideoSoft Free Studio Manager - Free Youtube to MP3 converter
5.2 Use the AC3 file which you created with ProjectX (4.)
5.3 At the end create a new AC3 file with Audacity.
5. Merge your newly created video and audio file with ImagoMPEG-Muxer
6. Create a new DVD (with layout and movie files) use GUI for dvdauthor or DVDStyler
- I would recommend to first create ISO images > less DVD trash
- As image loader use Virtual CloneDrive
- As DVD burner use ImgBurn
- As DVD player use VLC media player

Of course the handling will be tricky especially at the beginning. But the result will be a new level of presenting pictures and videos.

APEX issues

Strange behavior of "No Inline Validation Errors Displayed" check

Von Tobias Arnhold → 11.19.2009

I had two conditional processing checks for one pl/sql process to do. One "No Inline Validation Errors Displayed" check and one "Exist (SQL query returns at least one row)" check.

I thought it would be quite easy to merge it together into a "Exist (SQL query returns at least one row)" condition:


select *
   from user_db_links 
    where UPPER(db_link) = UPPER(:P1_I_INSTANCE_NAME)
    and (UPPER(username) not like UPPER(:P1_USERNAME)
         or UPPER(host) not like UPPER(:P1_DB_NAME)
         or :P1_PASSWORD is not null)
    and wwv_flow.g_inline_validation_error_cnt = 0

   -- and I tried this AND clause
   -- and (select wwv_flow.g_inline_validation_error_cnt from DUAL) = 0

The result was less promising. It just didn't work. The pl/sql process never run with wwv_flow.g_inline_validation_error_cnt in it.

My workaround was to add a new item called :P1_ERROR. I wrote the amount of validation errors into the :P1_ERROR variable inside a pl/sql process which was running before the one I tried the conditional processing with.


-- pl/sql process 1.
...
SELECT wwv_flow.g_inline_validation_error_cnt INTO :P1_ERROR FROM DUAL;
-- or this: :P1_ERROR := wwv_flow.g_inline_validation_error_cnt;
...

-- pl/sql process 2.
select *
   from user_db_links 
    where UPPER(db_link) = UPPER(:P1_I_INSTANCE_NAME)
    and (UPPER(username) not like UPPER(:P1_USERNAME)
         or UPPER(host) not like UPPER(:P1_DB_NAME)
         or :P1_PASSWORD is not null)
    and :P1_ERROR = 0

This works without any issues. Maybe one of you know why this happens?

APEX examples

Page refresh button

Von Tobias Arnhold → 11.17.2009

If you want to create a page refresh button inside your APEX page you need to add the following things:

1. Upload a button picture (Shared Components>Images>Create>Upload)
2. Create a new navigation bar (Shared Components>Navigation Bar Entries>Create):
- Image: "LET IT EMPTY!"
- Icon Subtext: <img src="#APP_IMAGES#page_refresh.png" title="Refresh">
- Icon Image Alt: "Refresh"
- Image Height: 40
- Width: 30
- Target type: URL
- URL Target: javascript:window.location.reload(false);

3. Watch the result:

ORA-

Ignore sql error messages in pl/sql process

Von Tobias Arnhold → 11.16.2009

Have you experienced the case that you want to execute a process and when a specified error occurs then it should go on like nothing happened.

In my case I had several pl/sql processes and one was to delete a database link. In my special case there shouldn't be an error if no database link exists.

Here is the code snippet for it:


declare
-- error variable
v_no_link EXCEPTION;
-- Map error number returned by raise_application_error to user-defined exception.
PRAGMA EXCEPTION_INIT(v_no_link, -2024);
-- About the error: http://download.oracle.com/docs/cd/B28359_01/server.111/b28278/e1500.htm#sthref1158 

begin
-- Drop existing database link 
EXECUTE IMMEDIATE 'drop database link ' || UPPER(:P1_I_INSTANCE_NAME);

EXCEPTION
  WHEN v_no_link THEN
    null;
  --WHEN OTHERS THEN
  --  raise_application_error(SQLCODE, 'SQLERRM');
end;

More information: PL/SQL User's Guide and Reference - Error Handling