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.

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

This mini tip is to help those who does not know CSS much and those who know CSS but do not know where to apply code in Oracle Apex. In Interactive reports by default the control panel items (filters/report titles) etc will be displayed one line below the other. If you have ten filters applied on an IR/Interactive Report then the panel when expanded occupies 10 lines in the report just like the one below: This mini tip will enable you to change this like the one below: If you have a Page 0/Global Page created within your application do not follow step 1. Step 1: Create a Global Page/Page 0 in your application. Step 2: Create a HTML region with template "No template" in this page. Step 3: Add the following CSS inside this region source and save. <style>   table#apexir_CONTROL_PANEL_COMPLETE tr{float:left;padding-right:15px;} </style> This is all you have to do to change Interactive report's control panels visibility option in your entire a...

Oracle's Web Toolkit (HTP/HTF) supports creating webpages with full control. I have been using them for my official project and we have built a framework associated with a style sheet we have created. Among other things it supports uploading files by user. Using the HTML INPUT type="file" we can position the file selection dialog. The form needs to be open with multipart/form-data. For the first time if you are using this functionality you may have to configure the table name where Oracle populates the file in a BLOB column. The configuration is part of DAD , placed in Database server within / dbfolder /Apache/modplsql/conf/dads.conf file. " dbfolder " is where you have installed the database. In the Location tag specify a table for the attribute      PlsqlDocumentTablename MYDOCUMENTS A sample document table can consist of the following: CREATE TABLE [table_name] (   NAME VARCHAR2(256) UNIQUE NOT NULL,   MIME_TYPE VARCHAR2(128), ...

This is a mini-tip on changing characters entered in a text/input field to all Capital letters. A little use of Javascript is required. Navigate to the page where the changes is required. Click on the field/Item for which all capital characters is required. Scroll till you reach Element section and in "HTML Form Element Attributes" mention the following (including quotes): onKeyUp="this.value=this.value.toUpperCase();" Now Click on Apply Changes button and run the page. For the field this attribute is applied, whenever a key is typed the case is changed. But if you dont want this type of animation and just want all characters to be in upper case no matter what, add the following to the text field: style="text-transform: uppercase;" onKeyUp="this.value=this.value.toUpperCase();" To learn more about text-transform click here . The style attribute is a CSS property and applies an all UPPER CASE property to the text field. Even though the...

How can I redirect to a page after submit button's process has completed in Oracle APEX ? Click on a page where you need to do the change. You must have atleast 1 submit button in your page already added. Click on Utilities Menu in the Page Toolbar. Select Page Events In the right hand side below Page Processing click on Create Branch in case a new redirect has to be created. If you need to modify a redirecting/branching click on the URL displayed along side Go To Page: and modify the page you want to be redirected. APEX Version:  4.1.1.00.23

Question:  How to get the last password changed time for a oracle user? Answer: The SYS view user$ consists of a column PTIME which tells you what was the last time the password was changed for the user. Try this query: SELECT name,        ctime,        ptime FROM   sys.user$ WHERE   name = ' USER-NAME '; Note: Replace USER-NAME with the user name which you want to know the information. CTIME Indicates - Creation Time PTIME Indicates - Password Change Time Here's the table DESC ription: Name         Type                Nullable Default Comments  ------------ ------------------- -------- ------- --------  USER#        NUMBER                                         NAME         VARCHAR2(30 BYTE)   ...

This question I have frequently faced while developing packages/procedures in PL/SQL in Oracle.  Question: How will I identify who has locked a procedure or a package. I need to identify this because it should not hang after the compilation is issued? Answer: You may see the details from v$sql  with the search text (Your package name). If the query fetches results then join it with v$session to get the session information. select *  from v$sql, v$session where sql_address=address and upper(sql_text) like '%PACKAGE_NAME%'; Hope this simple tip saves you some time.