Ask Anantha

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

I received some queries lately from various users regarding unable to open page http://localhost:8081/apex or http://127.0.0.1:8081/apex. The linux environment reported was Ubuntu and here is how to solve this error:

The steps involved are simple as below: 1. Save the jar file where forms jar files are saved    Usually {FormsInstalledPath}\forms\java folder 2. Open Registry editor (REGEDIT)    Browse to the following Key    HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/KEY_DevSuiteHome1 3. Add Java archive name in the Name FORMS_BUILDER_CLASSPATH   4. Now open Form Builder and create new form. 5. Open Menu Program > Import Java Classes and find your java class added and Import. Enjoy

I have a data in CSV format (comma separated values) like follows: first,second,third,fourth,fifth  I want the output as follows: column_value first second third fourth fifth The easiest way to achieve is to use a CONNECT BY clause in DUAL table and to populate the results. Here is the query: SELECT substr(str, instr(str, ',', 1, LEVEL) + 1, instr(str, ',', 1, LEVEL + 1) - instr(str, ',', 1, LEVEL) - 1) column_value FROM   ( SELECT ',' || '&mystring' || ',' str  FROM  dual) CONNECT BY LEVEL <= length(str) - length(REPLACE(str, ',')) - 1; Pass value   first,second,third,fourth,fifth to mystring.

Prior to Oracle 11g, we have used PLS_INTEGER data type in PL/SQL programs. In 11g, a new data type SIMPLE_INTEGER has been introduced. It is a sub-type of PLS_INTEGER data type and has the same range as PLS_INTEGER. The basic difference between the two is that SIMPLE_INTEGER is always NOT NULL . When the value of the declared variable is never going to be null then we can declare it with SIMPLE_INTEGER data type. Another major difference is that it never gives numeric overflow error like its parent data type instead it wraps around without giving any error. When we don’t have to worry about null checking and overflow errors, SIMPLE_INTEGER data type is the best to use. Posted by decipherinfosys , More information check Oracle Documentation

This time it is a review of a product which is freely available for download. The CSV Loader. It is a Java Application that can load data to databases such as Oracle, MySQL and PostgreSQL databases. The software is available for download here http://sourceforge.net/projects/csvloader/ The installation instructions are also available through this website http://csvloader.sourceforge.net/ What makes this tool unique is its integration with SunGard Banner software. It is a big replacement for SQL Loader provided by Oracle. You can either use its interface to specify the tables in which the data needs to be imported or pretty advanced mode where you can edit the configuration file and upload the details. I loved the tool, and in initial attempt loaded about 1000s of records to a table. I as a developer became free to use the data to load it to appropriate tables. I give a cool 4.5 star rating out of 5 for this tool. I reserve 0.5 points for little user-interface difficulties which...

Virtual Columns has been introduced in Oracle Database 11g Release 1. Here is a good tutorial I could find from Oracle-Base website. The link for the tutorial is at the bottom of this article. - Anantha When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc. The syntax for defining a virtual column is listed below. column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL] If the datatype is omitted, it is determined based on the result of the expression. The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only. The script below creates and populates an employees table with two levels of commission. It includes two virtual columns to display the commission-based salary. The first uses the most abbreviated syntax while the second uses the most verbose form. CREATE TABLE employees (  id          NUMBER,  first_name  VARCHAR...