Posts

csv Data to Rows - SQL

 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

Your session has expired - For Custom Apps after Oracle Apex Upgrade from 5 to 20

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.

Hierarchical Query with search parameters (Pruning)

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

Truth behind show user

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

Reports Builder wont open in Windows 10 64bit - [SOLVED]

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

Reuse and Show Apex's Wait Popup

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

Inserting DBMS_OUTPUT from wrapped procedures into a table

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(...); ...