Ask Anantha

Now-a-days if you search for any source code, this word is always there. "GIT". What is so special about it? Well check out the Wikipedia page to know about its history. I would like to borrow one sentence from Wikipedia though: Git was initially designed and developed by Linus Torvalds for Linux kernel development in 2005 The light bulbs glowed for the first time. I always fancied Linux and its kernel development, though I knew nothing about it. But this was the first time something near to the Linux kernel was available to me, and which could help me manage my source code. Although the learning was mandated for official reasons, I now fancy Git in "all and everything" which I code. Having said that what is the starting point? I implemented Git in my local development first. My aim was to have Git take backup of everything in my development folder. The starting point was this Git book . I summarize the tasks I was upto: Create a fresh project folder ...

Oracle's APEX is a RAD (rapid application development) tool which requires only a web browser like Google Chrome/Mozilla Firefox to develop and deliver "New Age" web based applications. Oracle allows a cloud based website apex.oracle.com to help ourselves familiarise this tool. As of this tutorial the version of APEX in the cloud is 4.2.2. In this cloud website one can apply for a free account. I am going to publish a series of videos helping you to get started with Oracle Apex. In this first of series I will be creating a free account with the cloud based website. Let's see how you can create your account: A simple introduction to Oracle APEX can be seen here .

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 .

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

A package once executed will have its variables in UGA (User Global Area). This is by default. If you are creating a package which will be executed only once, then the memory used by the package could be freed up after its execution. It is using a pragma (hint to compiler) that we do so. This directive is called as serially_reusable . It tells the compiler to run the package and free the space once its executed. The compiler on getting this directive does not save the program variables in UGA but it does in SGA (Shared Global Area). Each time the package with serially_reusable directive is called, its public variables are initialized. But in a normal package its not initialized every time the package is called. Here is an example: create or replace package pkg_with_pragma is pragma serially_reusable; n number := 5; -- default initialization end pkg_with_pragma; set serveroutput on BEGIN pkg_with_pragma.N := 10; dbms_output.put_line(pkg_with_pragma....

Renaming a schema is not an easy thing in Oracle. For reasons unknown Oracle does not allow you to rename a schema by a keyword such as rename old_schema to new_schema; or alter user old_schema rename to new_schema; THIS FACILITY DOES NOT EXIST in Oracle. There might be some utilities or some undocumented features which might leverage the renaming a schema. But if you really want one way to rename the schema go for the traditional way of exporting the existing schema and import into a new schema. Use clause fromuser touser while importing. But this too is not fully renaming schema as the privileges will not be imported.

Targeted Audience: Beginners Till today I was of the illusion that USER and SCHEMA are both equal. But Oracle Guru Andrew Clarke describes the difference between both USER and SCHEMA in his blog . Thanks Clarke for your blog. So understanding this difference we will define what an Oracle Schema is: A Schema represents set of objects owned by the user of the same name. For example in an organization there are 100 employees, each of these employees should have a separate space where they manage their objects. It is synonymous with the concept of an employee been allocated a new cabin where he can keep or organize his belongings. The same way in Oracle a user must be created for each database user. An organization can keep its own rules in naming the users but it is better to use a naming notation always in such cases. If a database user logs in to his space (using connect) he can create objects which becomes the schema. What a schema can contain? Just like a cabin where the employee sits...

The web gives me a definition of Synonym as "A word that means the same as another word." Well Oracle also defines its Synonyms in the same way. Then what is a Private Synonym? It is nothing more than a Synonym accessible to users of the particular schema where the synonym is created. Public synonyms on the other hand once created by any user, will be accessible for all schemas in a particular database. How to create a (Private) Synonym? The syntax for creating a synonym is CREATE [ OR REPLACE ] SYNONYM synonym_name FOR object; Example: CREATE SYNONYM clerk FOR employee; In our context employee is a table (or can be a view). As Clerk is also an employee, it makes understanding more easy. You can query from clerk as you would do from employee table/view. Now you must have got the idea of why a synonym is used.  Note: The usage of a (private) synonym from public synonym will be where you need to expose your not-so critical data to all database users. If your data is considered ...

Oracle SQL/PLSQL offers least function that will return least of values from a list provided. The syntax of the functions is: LEAST (val1, val2, …valn) Example: SELECT least(10,9,35) low_value FROM dual; The output is: LOW_VALUE --------------- 9 Note: If there is at least one NULL value in the least, the output will be always NULL. This is because least will not be able to identify the next least value. For avoiding this problem always make sure of using either NVL or DECODE for passing values.

The web gives me a definition of Synonym as "A word that means the same as another word." Well Oracle also defines its Synonyms in the same way. Then what is a Public Synonym? It is nothing more than a Synonym accessible to all users of a Oracle database. How to create a Public Synonym? The syntax for creating a public synonym is CREATE [ OR REPLACE ] PUBLIC SYNONYM synonym_name FOR object; Example: CREATE PUBLIC SYNONYM clerk FOR employee; In our context employee is a table (or can be a view). As Clerk is also an employee, it makes understanding more easy. You can query from clerk as you would do from employee table/view. Now you must have got the idea of why a synonym is used.  Note: The usage of a public synonym from private synonym will be where you need to expose your not-so critical data to all database users. If your data is considered private, you should not be creating public synonyms for this purpose. How to destroy a Public Synonym? To destroy or drop a public sy...

How to Pin objects to shared Pool? Pinning objects to the shared pool is a key to tuning your shared pool. Having objects pinned will reduce fragmentation and changes of encountering the ORA-04031 error. You must determine which objects to pin. These are particular to your own database, the application you are running, the size of your database, and the activity on your database. You need to pin objects when any of these happen: 1. If you have encountered the ORA-04031 already and need to resolve it or 2. You can also pin large packages frequently used by the users. (It is better to pin necessary objects while startup of database. Pinning a package to shared pool 1. Oracle automatically loads SYS.STANDARD, SYS.DBMS_STANDARD and SYS.DIUTIL.  Here is an example:    pk1 is a package with a variable called dummy.  Assigning dummy to a value and then executing the package will load it into the shared pool:  Example:-                                                                    begin  ...

Covering Developer version 9i for Windows NT/2000/XP By Richard Holowczak This tutorial introduces the Oracle9i Developer Suite Release 2 (August, 2002) that includes Oracle Forms 9.0 and Oracle Reports 9.0. The main objectives are to demonstrate and provide hands-on instructions on creating and modifying data entry and query forms in various configurations, reports and graphics. Caveats: Please note that Oracle tends to change things like menu items, prompts and other small things between each major (certainly) and often minor release. Depending on the exact release of Oracle9i Developer Suite you have, you may find some small discrepencies between what is shown in this tutorial and what you see on your screen. Prerequisites Before following this tutorial, a student must have a valid user account in an Oracle server or a local installation of an Oracle database. Contact your DBA or systems administrator to learn the details of how Oracle server is set up in your organization. If yo...

This is a step-by-step guide on the simplest of the simplest ways by which data can be uploaded from CSV file to Oracle table. I have done uploading using this way for as many as 10,000 records without any performance issues. It went very swiftly. Login to OracleXE first and follow the instructions below: Step 1 - Click Utilities Step 2 - Click Data Load/Unload Step 3 - Click Load Step 4 Step 5 - Click Load Spreadsheet Data Step 6 - Follow the screen Step 7 - Follow the screen Step 8 - Follow the screen Step 9 - Follow the screen Step 10 - Follow the screen

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.

To compile an entire schema in Oracle, there are two utilities provided by Oracle. DBMS_UTILITY.COMPILE_SCHEMA Two procedures in UTL_RECOMP DBMS_UTILITY.COMPILE_SCHEMA This package prior to 10g would recompile all objects (prior to Oracle 10g) and optionally recompile all Invalid Objects (starting from Oracle 10g). This procedure is available with all schemas (there is no need for giving additional privileges). I am using Oracle 10g (10.2.0.3.0) for the following examples. Syntax: DBMS_UTILITY.COMPILE_SCHEMA( schema VARCHAR2, compile_all BOOLEAN, reuse_settings BOOLEAN); Example (from SQL *Plus): EXEC DBMS_UTILITY.COMPILE_SCHEMA( schema => 'SCOTT', compile_all => FALSE); For recompiling all objects irrespective or VALID or INVALID use the following example (simply omit the compile_all parameter): EXEC DBMS_UTILITY.COMPILE_SCHEMA( schema => 'SCOTT'); UTL_RECOMP Package This package has been introduced from Oracle 10g Release 2 , which is ...

I chose to write on this topic in order to increase the readability of programs and to maintain a standard way of validating values from within PL/SQL blocks, whether be it Anonymous blocks or Stored Procedures or Stored Functions or Packages. There has been always need for writing efficient piece of code before delivering the final one. Validating NULL Values NVL Function This function is boon to programmers. I always chose to use this function because of its simplicity. In Oracle one must always remember that while validating with a NULL value the result you are going to get is also a NULL. So in order to avoid this it is better to use NVL() function. Syntax: NVL(variable1, variable2) Example: SELECT NVL(NULL, 'This is the output') null_test FROM dual; Result: null_test This is the output Both the parameters are required for NVL function to perform. You can use this function in WHERE clause so that you are not going to omit any NULL values from your query. NVL2 Function NVL2 ...

An internal exception is raised implicitly whenever your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit. Every Oracle error has a number, but exceptions must be handled by name. So, PL/SQL predefines some common Oracle errors as exceptions. For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows. To handle other Oracle errors, you can use the OTHERS handler. The functions SQLCODE and SQLERRM are especially useful in the OTHERS handler because they return the Oracle error code and message text. Alternatively, you can use the pragma EXCEPTION_INIT to associate exception names with Oracle error codes. PL/SQL declares predefined exceptions globally in package STANDARD , which defines the PL/SQL environment. So, you need not declare them yourself. You can write handlers for predefined exceptions using the names shown in the list below. Also shown are the corresponding Oracle error codes and S...

Who can read this:  A basic understanding of indexes, updates and their effects on indexes would help better understand the topic of this document. This article is intended to be an introduction to the topic of Function based index. Who should not read this: This document is meant to be a basic manual for those who would like to learn what are function based indexes and what magic can they do. So the persons who are already aware of this concepts, please do not waste your time. Also this concept is confined to Oracle database. There are no general topics discussed in this article. Now let's start with the topic straightaway: What and why a function based index is used?   Function based indexes were introduced with Oracle 8i. This was the most coveted features that Oracle came up with its 8i version of database. Traditionally there was no such feature that will use an index if your select statement has a function in it. But with this featur...

Rule #1 The design of a database must only be thought after the through analysis of the user requirements. Rule #2 Never implement any functionalities of Oracle if it is not required. Always adhere Rule #1. Rule #3 Never implement as per theory, but base your design on practical thought. Always adhere Rule #2. What does this mean? I will elaborate each and every point mentioned above. Also note that all the rules are related to one another. This ensures that the design is always as per user requirements. Rule #1 The first rule says that never start or think of database design before understanding what exactly the user requires. This is the major and very important rule which must be adhered, whatever comes. This rule thus verifies the importance of analysis of user requirements. Unless the requirements of user is fully understood, it will be nearly impossible to design the database. Rule #2 This rule again double verifies the rule #1. The design must be using only using such oracle fun...