Ask Anantha

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. 

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

I am very pleased to announce a project which is very close to my heart. " The biTracker. "  It uses the PL/SQL Web Toolkit for generating HTML pages which can be used by an organization for its internal needs. About Pl/SQL (Oracle) Web Toolkit A programming language now-a-days must be capable of producing web content using HTTP protocol. PL/SQL is no exception, and it does this job in style. Oracle supplies some packages called HTP, HTF etc which can generate HTML content to HTTP stream which can be opened by a browser. The flow of data can be in the following tiers: PL/SQL Procedure or Package (Database Layer) Web Server (Middle-Tier) Browser (Client Side) As Oracle handles the flow of data from Database Layer to Middle Tier to Client side it gives us time to develop feature rich applications using Database Layer.  Consider the following example: CREATE PROCEDURE helloworld IS BEGIN   HTP.htmlOpen;   HTP.print('Hello World');   HTP...

SQL Injection as the name suggests is injecting arbitrary SQL commands or clauses into an executing program to defeat its purpose. Why does one inject SQL commands to defeat the purpose of a procedure. The answer is 'hackers'. Hackers are always looking for easy preys to steal another ones information. In this age of Information Technology, unnoticed stealing is for information. The information thus gathered can be consolidated by an experienced hacker and cause 'hell lot of trouble'. Now as we are clear of the intentions of defeating a program, we will understand the simple types of SQL Injection which are very well-known. First Order Attack Second Order Attack Lateral Injection First Order Attack is caused when a hacker simply modifies the string passed to a procedure and adds a malicious string to make the program work even if without valid data.  For example consider the following code: create table users (username varchar2(20), password varchar2(20)); ...

Have you heard of JSP? Have you heard of ASP? Have you heard of PHP? Of course I have heard of them all. They all does one function, generate web pages dynamically to deliver a nice and rich front-end to the web. But Have you heard of PSP? What? PSP is acronym for Oracle's PL/SQL Server Pages. Oracle has this kind of capability? Well Oracle always has this sort of capability but it was called in a rather different name. It was and is called PL/SQL Web Toolkit. But rather unknown or less used fact is Oracle also has an extension to this. This is called as PSP. I have created a whitepaper which is here for you to read: For those who are unable to view the presentation in this web page or to view in Full screen, click here .

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

Question: How can I get difference between two timestamp variables in seconds in Oracle? Answer: Use interval datatype to store the difference. Example code is below. DECLARE   l_start_time TIMESTAMP;   l_end_time   TIMESTAMP;   l_sec        INTERVAL DAY(9) TO SECOND(6); BEGIN   l_start_time := systimestamp;   FOR i IN 1 .. 100000 LOOP     NULL;   END LOOP;   l_end_time := systimestamp;   l_sec := l_end_time - l_start_time;   dbms_output.put_line('Seconds past=' || abs(extract(SECOND FROM l_sec) + extract(minute FROM l_sec) * 60 + extract(hour FROM l_sec) * 60 * 60 + extract(DAY FROM l_sec) * 24 * 60 * 60)); END; Output is: Seconds past=.001144