Ask Anantha

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

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.

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

This tip was posted in Oracle Apex Knowledge Group by Richard Martens. Its my sincere pleasure in thanking the author for this simple tip. The original blog is available here . I am posting the steps first, and a slight deviation which makes the setup one time only through this article. The idea is to create a region first and all sub-regions as tabs. If this design suits your application then its simple as copy-paste.Create a template Go to Shared Components  Navigate to Templates Press the Create Button Choose Region Choose From Scratch Provide the name jQuery Tabs Choose Custom 1 for template class Press Create button Once the template is created, find the jQuery tabs from the list and click to edit. Paste both the following codes in Definition Section: <div id="#REGION_STATIC_ID#" #REGION_ATTRIBUTES#> #BODY##SUB_REGION_HEADERS##SUB_REGIONS# <div style="clear:both;"></div> </div> <link rel="stylesheet" href=...

This was one of my biggest concerns while using Oracle APEX. I will often enable PDF download from Interactive Report setup in a page, but the download was generating only a corrupt file. My google searches also did not yield any result. Then came the news that I have to install BI Publisher in order to use the feature. This message was floating in OTN forums related to this topic. I got disheartened learning this. Why would Oracle provide such a tool if it can't support PDF download by default. Then came the SOLUTION for the problem. In one of documentation available in Oracle's website I found the news I have been searching for a long time. The APEX download (4.2.2) had been shipped with a war file fop.war. This file is available in the folder  apex_4.2.2\apex\utilities\fop The installation instructions mentioned that I need to deploy this war file in OC4J but which requires  Oracle Containers for J2EE (10.1.3.2 or above) [I don't have a clue what this is]. ...

You must follow the exact steps to add link to attachments in BLOB column in Interactive Report/IR in Oracle APEX: 1. Table must have primary key (I could not make this work with ROWID in View), and it must be queried in the select statement for IR. 2. In query one column must fetch size of attachment. You may use dbms_lob.getlength(blobcolumn) function to fetch the size. 3. In the size column added provide format mast as BLOB and in Apex 4.2.2 "BLOB Column Attributes" section will come. 4. In "BLOB Column Attributes" you must specify at least two columns, BLOB column in "Column" and "Primary Key column 1". If you have composite primary key, you may specify "Primary Key column 2". 5. If you are saving the file name in table, you may also identify the column name in "Filename Column" field. If you are getting " no data found " error in the region of Interactive report, do make sure that the first point mentio...

Till Apex 4.1 while creating a page you had an option for creating "Page Zero". This page type is a special master page to your application. If you add any element to this page, automatically the element will be displayed in all pages within your application.

I was reading about features in Oracle APEX 4.1 in Patrick Wolf's Blog, and I wanted to try it out to see how easy or difficult it is to implement a Select list which dynamically changes based on a different value in the page. I created a sample application with three tables: Country_Master - Stores country id and name State_Master - Stores country id, state id, and name Company_Master - references country id and state id to store company record. My intention was to implement country select list and state select list in company master page. As soon as I select a country states belonging to the country should be refreshed in state select list. The APEX feature used: " Cascading LOV Parent Item(s) "

I have a form with some text fields and a BLOB (storing image) column. For storing the BLOB column I was using webutil_file_transfer.client_to_db_with_progress procedure to transfer and store to BLOB column. In essence the BLOB column in the data block was only for querying the image to be shown to users. But when I modify the text column and save the record I got an error message which I have never encountered till now. ORA-24813: cannot send or receive an unsupported LOB Thanks to my colleague Haris, I modified the data block property Update Changed Columns Only to "Yes" from default No. The error is now gone. The error is solved by this simple property setting. 

One point which is usually overseen with the usage of TO_CHAR() is the leading space that it returns when converting a number to character . Example: select length(to_char('109','000.00')) from dual; TO_CHAR('109','000.00') " 109.00" Do you notice the leading space that TO_CHAR() has returned? If you have not noticed and surprised, execute this and find it for yourself. This is not a bug. The Reason is Oracle reserves the first character for Sign. If you won't beleive me, execute the following statement: select to_char('-109','000.00') from dual; TO_CHAR('-109','000.00') "-109.00" Notice that the length of the string has not increased with the negative value being converted by TO_CHAR(). Hold on your tendency of calling TRIM() function to trim out the space. TO_CHAR() has a format model that suppresses this trailing space. To suppress this leading space simply use the FM (stands for...

Creating or Rebuilding Indexes Online: Online Index Rebuilds allows you to perform DML operations on the base table during index creation. You can use the CREATE INDEX ONLINE and DROP INDEX ONLINE statements to create and drop an index in an online environment. During index build you can use the CREATE INDEX ONLINE to create an index without placing an exclusive lock over the table. CREATE INDEX ONLINE statement can speed up the creation as it works even when reads or updates are happening on the table. The ALTER INDEX REBUILD ONLINE can be used to rebuild the index, resuming failed operations, performing batch DML, adding stop words to index or for optimizing the index. The CREATE INDEX ONLINE and ALTER INDEX REBUILD ONLINE options have been there for a long time to easy the task of online index rebuilding. However in highly active they still can introduce locking issues. Table Locks: A table lock is required on the index base table at the start of the CREATE or REBUILD process to...

UTL_FILE procedure has been enhanced in Oracle 9i and since then it provides a procedure fgetattr to return file size. PL/SQL Evangelist Steven Feuerstein has come up with this function that returns file length. CREATE OR REPLACE FUNCTION flength (    location_in   IN   VARCHAR2,    file_in       IN   VARCHAR2 )    RETURN PLS_INTEGER IS     TYPE fgetattr_t IS RECORD (       fexists       BOOLEAN,       file_length   PLS_INTEGER,       block_size    PLS_INTEGER    );    fgetattr_rec   fgetattr_t; BEGIN    UTL_FILE.fgetattr (       location         => location_in,       filename         => file_in,       fexists          => fgetattr_rec.fexi...

Question: How to find any value in any column in a schema? I have been searching for this answer for long, and stumbled upon a beautiful response in Oracle Forums and tried the block and stunning to find the output much easily. This saved my day as I was searching for occurence of a value in entire schema. Thanks for pollywog for the posting. I will reproduce the anonymous block here for my readers: declare aCount pls_integer; begin for c in (select table_name, column_name  from all_tab_columns  where owner = 'OWNER'  and data_type = 'DATATYPE') loop execute immediate 'select count(*) from '||c.table_name||' where '||c.column_name||' = ''value_to_find'' ' into aCount; if aCount > 0 then dbms_output.put_line('found value_to_find in table '||c.table_name||', column '||c.column_name); end if; end loop; end; Just modify the following in the block: 1. OWNER (The schema in which find is requ...

Article and Script Courtesy :  Niels Teusink The Oracle  wrap  utility can be used to obfuscate PL/SQL code, to ensure it can't be easily read. Pete Finnigan described ( pdf ) the wrapping process for Oracle 9g, but for 10g and 11g it still remains a bit of a mystery. I decided to release my Python unwrapping utility (supports 10g and 11g). The unwrapping steps for 10g are nicely described in the  Oracle Hacker's Handbook , but the actual substitution table needed to decode the package is omitted. Nobody (as far as I know) has published it. A lot of people seem to know how to do it though, there is even an  online unwrapper  available (and I'm sure everyone seriously involved in Oracle security knows how to do it). A Russian-made closed source tool is also available, but tends to upset virus scanners. So to save everyone a couple of hours of figuring it out, here it is:  unwrap.py It's easy to use (I've used the wrapped procedure from  th...

Sequence number generation in a query has been almost natural in reporting queries. You normally tend to use rownum pseudo column from either your table or if it is a generic query user_objects. For example: select rownum from user_objects where rownum The output will be: ROWNUM --------------- 1 2 3 . . 100 This is based on the assumption that there are more number of objects in user_objects view so as to return a high number. The more robust way to get an output without using such a large view was not literally possible in pre-9i era. Starting 9i Oracle has come up with a beautiful solution. You can use DUAL table in conjunction with CONNECT BY clause to come up with such a result. Example: select level from dual connect by level This also will generate the same output generated in our first case.

Oracle Universal Installer by default installs any oracle products in its GUI mode. In situations where the GUI could not be started, it is not possible to install Oracle, such as in Linux environments if X Server could not be started it is difficult to install any Oracle products. Moreover for a DBA, it is very cumbersome to sit and click buttons for installation. For automating purposes, Oracle Universal Installer (OUI) provides a silent mode of installation. The runInstaller script which is used for calling the OUI has some switches which can be used to achieve this functionality. ./runInstaller -record -destinationFile   ./runInstaller -silent -responseFile Here is how: First Step ./runInstaller -record -destinationFile ResponseFile.txt The record parameter tells the installer to write to the response file and the destinationFile parameter defines the name of the response file. Once the response file is created you can run the installer in silent mode using the following command:...

I got a following query from Suvankar Das. "I need to unload few Oracle tables with average data volume of 15M records to flat files. Is the SQL* Plus spool a good option for this volume of data from performance point of view OR is it better to do through PL/SQL UTL_FILE option." And this is how I responded: "SQL* Plus will have the fastest result to the flat file. Only use UTL_FILE if you need some more programmatic control in between the spooling. To make sure first take a sample set of data (some thousands of records) for spooling using SQL *Plus and do the same using UTL_FILE. Compare both the timings. Also based on the sample estimate the time for the entire 15M records." The Verdict: There is no hard rule that says either SQL *Plus or UTL_FILE will perform faster. It varies from environment to environment. Where SQL *Plus/UTL_FILE could perform faster SQL *Plus is a client program which is accessing Oracle database. Therefore it highly depends on the network. ...

Today I was answering one of the queries, this question seriously made my interest. The question was "I have 15 million records in my table. What do you suggest a best method for unloading them from Oracle table to a flat file". Seriously till that time I had never done this. The need had not come. I clearly know two ways of doing this: Using SQL Plus and SPOOL command Using UTL_FILE built-in package UTL_FILE vs SPOOL I know that SPOOL command will be faster in execution than UTL_FILE package , but also I reminded myself that this may not be the case always. SPOOL commands operates in the client machine and depending upon the network traffic it might take time for the SPOOL operation than UTL_FILE.  SO I ADVICE TO TEST BOTH OPTIONS IN YOUR ENVIRONMENT BEFORE FINALISING ON ONE OF THE ABOVE. I will soon publish routines for this purpose. Your patience is appreciated. More resources for your inquisitive mind A little surfing did a wonderful job in putting this article up. In ask...

This article is a continuation on Making Oracle Case Insensitive . Read that first to understand the context discussed here. Oracle is always Case Sensitive while querying data. Now we have learned how to make Oracle Case Insensitive for queries. Let us ask some questions to get going. What is the purpose of making Oracle Case Insensitive? By making Oracle Case Insensitive, you need not any more use Case mixed words. For example Test, TEST, tESt are all same once you have made Oracle Case Insensitive. Its use could be in Data warehousing applications. How to make Oracle Case Sensitive back again after this? You have to alter two session paramaters NLS_COMP and NLS_SORT. First set NLS_COMP to binary. ALTER SESSION SET nls_comp=binary; Secondly set NLS_SORT to generic. ALTER SESSION SET nls_sort=GENERIC_M_CI; Once these variables are altered Oracle is back in business as case sensitive.

I was very much frustrated with the simple but syntactical change between Oracle and MS-SQL Server. I was trying to just create a table with the normal Oracle syntax in MS-SQL Server: CREATE TABLE test AS SELECT * FROM existing_table; I got an error from MS-SQL and then I had to call up some experts on MS-SQL to understand how it is done in MS-SQL. The syntax for creating such a duplicate table of an existing one in MS-SQL is as follows: SELECT * INTO {new_table} FROM {existing_table} WHERE { if_clause if any}; Thus our example can be translated to as: SELECT * INTO test FROM existing_table; This short article is to just keep up the difference between MS-SQL and Oracle. Oracle professionals may sometimes need to work in MS-SQL, and this kind of tips on MS-SQL comes handy in times of trouble.