Ask Anantha

 If you have a data with Comma, or any other character delimited use the following query to convert it to individual rows. -- Created on 03/07/2024 by ANANTHAN  DECLARE   -- Local variables here   CURSOR c1 IS WITH rws AS(     SELECT 'one,two,three' str     FROM   dual)     SELECT regexp_substr(str, '[^,]+', 1, LEVEL) split_val     FROM   rws     CONNECT BY LEVEL <= length(str) - length(REPLACE(str, ',')) + 1; BEGIN   FOR m1 IN c1 LOOP     dbms_output.put_line(m1.split_val);   END LOOP; END; Output: one two three

I was getting the above error " Your session has expired " immediately after upgrade of Oracle Apex from Version 5 to 20 in my premises. Searched a lot and did not find any resolution. After trying to login in same browser, tried a private browsing mode in Chrome and Firefox. It logged in. That is when i realized that it might be due to session cache/cookie. As a resolution I cleared all session cookies by clicking "Inspect Element/Storage" in Firefox. It worked after the storage for the site was cleared.

The following is a query from a friend, Reproducing it here: I have a menu structure using a hierarchical query. i have search field to enter some string and search. I should get the result in the same menu format with with the node and its parents. Can you please help me with the query for search ? eg. A1 A11 A12 A123 A13 B1 B123 C1 C11 C12 If I search for  %123%  the result - A1 A12 A123 B1 B123 My idea initially was, yeah possible BUT HOW. I was sure it needed two result sets and joining to form a query to get the output. But wait, what is internet saying about it.  I stumbled upon this brilliant article in orafaq and I was sure my initial thought was wrong. I was in a different mindset. Thanks to the article and Oracle SQL the query was much simpler. But with a catch, the hierarchy was in reverse. SELECT lpad(' ', LEVEL, '-') || h.title descr FROM   test_hier h CONNECT BY id = PRIOR parent_id START  WITH parent_id...

SQLPLUS command line provides a show user help which can print which user you are logged in as. Just learned new today that it is not hitting the DB to get the results. Tom, who else can bring it to light. I am just 12 years down the line to have read his message. The content points to this article The SQLPLUS caches the username as soon as a "connection" is established and saves it for reuse. Whenever somebody issues a show user, it just returns from the cache. Internally for the first time it will be doing a select user from dual; to set the value. Its like a global variable Next time onwards it will simply return from its variable instead of running a query. Even if the Connection is killed, SQLPLUS returns the variable value. (This was new to me) ----Copy pasting shamelessly the example from Tom Kyte's article for my reference: [tkyte@desktop tkyte]$ sqlplus / SQL*Plus: Release 10.1.0.4.0 - Production on Wed Aug 3 10:01:01 2005 Copyright (c) 1982, 20...

Though Oracle doesn't support Forms 10g in Windows 10: 64 bit, I had installed it and was working till last month. Then suddenly one fine day, the report builder window won't show up. Though it was running in the task bar, I couldnt do anything with it. Forms builder was working properly. Then taking cue from this Oracle form community post , I copied the cauprefs.ora from my colleague. Boom, the Report builder started to show up. Then spend some time analyzing changes of the backup file with my file obtained from my colleague. It showed differences in the section where x, y coordinates were saved for Reports preferences. ====cauprefs.ora Backup file (Report Builder not opening) Reports.root_ht = 725 Reports.root_max = No Reports.root_wd = 1333 Reports.root_x = "-32000" Reports.root_y = "-8" Reports.root_y = "-32000" ====cauprefs.ora from my colleague (Report Builder opening properly) Reports.root_ht = 725 Reports.root_max =...

Oracle Apex supports or offers a GIF image and an overlay while loading content using Dynamic Action/AJAX. While creating a dynamic action you have an option to select "Show Processing" for supported actions. This shows a nice overlay over the screen and a GIF image out of the box. You may have scenarios to show a loading while doing some javascript/jquery actions on your own. Will it not be nice to show/reuse the same overlay and image? This article explains how to do it. Say you have a function to load data from server and show on page using a javascript function: function previewFiles(){    ajax.widget.waitPopup(); } The above javascript call will show the overlay and GIF image. This is very simple isn't it. But to hide it, there is no function. We have to call following jquery method calls. $("#apex_wait_popup").remove();   $("#apex_wait_overlay").remove(); Which will remove the popup and overlay from DOM. To simplify things we ...

This tip is courtesy  +V. Kapoor  from  http://www.foxinfotech.in . Thank you, you saved my day. I had a situation where the database procedure SRW is wrapped and I needed to log the dbms_output.put_line output produced by the package into a table. Thanks to the tip provided, I was able to do so. I had to call the following procedure dbms_output.get_lines(vcol, n); This procedure is intended to retrieve an array of lines from buffer. This means if the buffer is not cleared it will be available in the array (out parameter 1). The second parameter is the number of lines in array returned by the procedure. To declare the OUT variables required by this procedure do the following: n number; vcol dbms_output.chararr; Simply put together the code will be as follows: DECLARE    n      NUMBER := 100;    vcol   DBMS_OUTPUT.chararr; BEGIN    srw.start_debugging;    srw.run_report(...); ...

Starting Oracle Apex 4.1, application pages by default will not open inside an iframe (inline frame). There is a new security setting introduced, which is restricting this feature. As commented by  +Patrick Wolf   in  community.oracle.com  the following setting has to be changed to enable this feature: This is what he had to say: "There is a new security feature to prevent clickJacking attacks. For new applications it's enabled, for existing ones it's disabled. Please see Shared Components > Security Attributes > Browser Security -> Embed in Frames" Keeping this for my reference purposes only.

Its been some time since I have written in this blog. Its mainly because of my work schedule. Cutting short personal note, let me introduce the problem. I have a plain HTML page which has two drop-down which is my interest in the article. The first one is Country, second one is Region/State. My requirement is to change the Regions when Country is selected/changed. I was trying to implementing this on my own, rather than trying to use features provided by AngularJS. Then with this stackoverflow thread, I was blown out. It was as simple as a filter clause in ng-options. I had never ever dreamed of anything simpler. Of course in my JSON, region data has a country id which made this approach simpler. Let me share my JSON. {     "states": [     {             "id": 1,             "code": "KAR",             "name": "Karnataka",           ...

This was a strange error which was daunting me for couple of hours today. The field name reported by Forms Builder had no formula attached to it. The property of all fields in the block was also same. But since this was a warning, I tend to ignore. But after some more time, bogged over with the unnecessary popup while creating FMX. Some googling gave me an answer that this was a Form Builder Bug. Then in no time, I got the solution from Oracle Forums.  Specify some dummy formula in the field which is reporting error, and later clearing the property to None and no Formula removed the warning thrown.  Recording for my reference.

I was struggling to find a fix for the problem. Only now I recognized it was a bug in Oracle APEX Version 4.2.3.00.08. The scenario: 1. I have two regions with Interactive & Classical Report 2. When a value is modified in select item in page, I need to refresh these reports. (Used dynamic action to refresh regions on page item change) 3. I used jQuery tabs for the reports ( instructions on how to created it is here ) 4. I modified the Template of the reports to "No Template" 5. The region refresh stopped working. I was assuming that the refresh did not work because of jQuery tabs. But later I realized that the "No Template" caused the bug. To fix this you may either create a new Template or use a region which has a template. I have used the "Borderless Region (no heading)" and the dynamic refresh works. Not sure about this being fixed in later versions of Apex.

This post is due to lack of clear instructions available where I searched for. These are notes which I used to perform a hard disk extension in Ubuntu host for a Windows Vista is guest/vm. Summary of steps: Step 1: Convert vmdk file to vdi (if you are using vmdk as hard drive) Step 2: Resize vdi file to required hard drive size Step 3: Convert the resized vdi file to a new vmdk file (so that the original file serves as backup) Step 4: Remove the attached vmdk file in vm and add the newly converted vmdk file. Step 5: If you are running a Windows OS which supports Disk Management > Extend Volume then the guest OS will recognize the size of extended hard disk. Step 6: Remove the original vmdk file if everything is fine. Steps and commands: The commands which I used are as follows: Step 1: Convert vmdk to vdi VBoxManage clonehd "WinVistaDev-disk2.vmdk" "cloned.vdi" --format vdi Step 2: Resize vdi file: VBoxManage modifyhd "cloned.vdi" --...

The new age Oracle Apex is here to download. Till 16th April it was only available as a preview in apex.oracle.com. Starting yesterday it is available for download. For the first time, first day first show I could download Apex from otn and install in my local Express Edition database. The installation was smooth only 3 steps. The upgradation steps are clearly mentioned in documentation here . Step 1: Download zip file from otn Step 2: Unzip in folder of your choice Step 3: Run the following command in sqlplus which is connected to sys @apexins.sql tablespace_apex tablespace_files tablespace_temp images As I had not created any tablespaces, I simply ran the following command: @apexins.sql SYSAUX SYSAUX TEMP /i/ Step 4: Login back after the previous step and execute following: @apxldimg.sql APEX_HOME APEX_HOME is the folder containing the unzipped downloaded files of apex. For example @apxldimg.sql c:\ Step 5: Complete upgrade by updating password: @apxchpwd.s...

Q: How can I obtain image attributes such as height, width, format from BLOB column? A: Using Oracle Multimedia ORDImage object type it is possible to get such attributes. Consider the following example: DECLARE   lv_blob                  BLOB;   unused_attributes        CLOB;   img_mimetype             VARCHAR2(32);   img_width                INTEGER;   img_height               INTEGER;   img_contentlength        INTEGER;   unused_fileformat        VARCHAR2(32);   unused_contentformat     VARCHAR2(32);   unused_compressionformat VARCHAR2(32); BEGIN   SELECT blob_content   INTO lv_blob   FROM mytable;   ordsys.ordimage.getproperties (lv_blob,             ...

Now-a-days if you search for any source code, this word is always there. "GIT". What is so special about it? Well check out the Wikipedia page to know about its history. I would like to borrow one sentence from Wikipedia though: Git was initially designed and developed by Linus Torvalds for Linux kernel development in 2005 The light bulbs glowed for the first time. I always fancied Linux and its kernel development, though I knew nothing about it. But this was the first time something near to the Linux kernel was available to me, and which could help me manage my source code. Although the learning was mandated for official reasons, I now fancy Git in "all and everything" which I code. Having said that what is the starting point? I implemented Git in my local development first. My aim was to have Git take backup of everything in my development folder. The starting point was this Git book . I summarize the tasks I was upto: Create a fresh project folder ...

Packt Publishing is celebrating 10 years of its presence by a unique promotional way.  To celebrate this huge milestone, from June 26th Packt is offering all of its eBooks and Videos at just $10 each for 10 days – this promotion covers every title and customers can stock up on as many copies as they like until July 5th . #Packt10Dollar Hurry up, and buy your stocks before the offer ends, click here:  http://bit.ly/1rhKWwS

I was getting this curious error lately in a development package. I could not resolve what could be the reason for this for sometime. ORA-00900: invalid SQL statement I got some head on from this website , but it did not help my case as both of the cases mentioned by him was not valid for me. I was not using any dblink or neither because of Y2K error. I started to investigate upon it and with the help of my colleague, I spotted the error. I was using EXECUTE IMMEDIATE, which was not tested had a code oversight. I was executing a procedure within EXECUTE IMMEDIATE but without an anonymous block. EXECUTE IMMEDIATE 'package.procedure'; The above was my statement, and with the oversight it took about 30 minutes of frustration to fix this error: EXECUTE IMMEDIATE 'begin package.procdure; end;'; The above code executed properly. The oversight I had was to miss the semi-colon which is not required when executing a SQL statement. Thanks to  +Ajish Kumar  who ...

Its a fact, users are lazy. They want to avoid multiple clicks of button. This time it is to avoid clicking on Actions Menu > Download > Click to download CSV. The requirement was to click one button, download the CSV. Oracle Apex allows this using a simple technique. I was searching many blogs, OTN etc where people were asking you to create a new region, then change the settings of the region. Why should I manage two copies of same region? The solution is simple. While creating a button, specify the option " Page in this application ". The page number should be same as the one where the Interactive report is created. (Probably this is one of the reasons why there is a limit of 1 Interactive report in a page). In the Request text field, mention the "CSV" option. That is all you need. Create a button with the Action when button clicked property as above

Version : Oracle Application Express 4 and above. Recently I had been asked by my colleague to how to configure header and footer for PDF printing of Interactive reports in Oracle Apex. We have been using Apex 4.2.5, but this tip will work for versions above 4. If PDF download is not working in interactive reports in your setup, refer this article to configure PDF printing. By default if you are downloading an interactive report in Oracle Apex, it will only download the data inside the report. It will not print anything which is available in Region header/footer. It is available as a separate option which will help configure attributes while printing. Navigate to your page in developer view, and click on Interactive Report link for the region. Click the Interactive Report link Click on the tab Print Attributes. Click the tab, Print Attributes In this section you will be able to add/modify the following settings: Print Server Override: If you have created a custo...

Digital Rights Management (DRM) is a technique used by manufacturers or publishers to control use of their products after sale. If you buy a book, you are not allowed to copy the contents (even take a copy of a page you feel is important). Today on 6th May lot of organizations are raising a voice against the DRM. As individuals it is our aim to support this voice, as we will be benefited. Raise your voice, support the supporters. https://www.defectivebydesign.org/node/2312 Packt Publishing is also celebrating this day by its offers. They are celebrating International Day against DRM on 6th May 2014 by offering all its DRM-free content at $10 for 24 hours only on May 6th – that’s all 2000+ eBooks and Videos at www.packtpub.com.