Ask Anantha

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

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

This parameter is used in conjunction with TABLE parameter of exp (export) utility of Oracle. This parameter will enable selection of rows from the list of tables mentioned in TABLE parameter. The value to this parameter is a WHERE clause for a SELECT statement which you would normally issue. For example if you want to query all records of employees for a particular department you will use: SELECT * FROM employees WHERE dept = 10; To export these rows into a file using exp utility you will follow the below syntax: exp scott/tiger TABLES=employees QUERY=\"WHERE dept=10\" Use \ for providing character or special characters like less than or greater than symbol inside the string. Also for operating system keywords you need to place \ as escape character. For example: exp scott/tiger TABLES=employees QUERY=\"WHERE name=\ANANTHA\' and sal \ You can also use ROWID for exporting, for example: exp scott/tiger@slspnc1 tables=emp query=\"where ROWID='AAAMgzAAEAAAAA...

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

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

There are two ways of renaming a table in Oracle: Method 1: Simple rename {old_table_name} to {new_table_name} Example: rename CUSTOMER to CUSTOMER_BACKUP Method 2: Not so Complex alter table {old_table_name} rename to {new_table_name}; Example: alter table CUSTOMER rename to CUSTOMER_BACKUP; The minimum version that supports table renaming is Oracle 8i . All the dependencies of the table will automatically updated. No need of updating them after wards.

Q: How to enter a single quotation mark in Oracle? Ans: Although this may be a undervalued question, I got many a search for my blog with this question. This is where I wanted to address this question elaborately or rather in multiple ways. Method 1 The most simple and most used way is to use a single quotation mark with two single quotation marks in both sides. SELECT 'test single quote''' from dual; The output of the above statement would be: test single quote' Simply stating you require an additional single quote character to print a single quote character. That is if you put two single quote characters Oracle will print one. The first one acts like an escape character. This is the simplest way to print single quotation marks in Oracle. But it will get complex when you have to print a set of quotation marks instead of just one. In this situation the following method works fine. But it requires some more typing labour. Method 2 I like this method personally becaus...

Oracle suports XML handling through the built in datatype XMLTYPE. Here is an example of handling XML data to convert xml to object types and viceversa. Consider the following XML <customer> <id>100</id> <name>XEROX</name> <country>FRANKFURT</country> <status>ACTIVE</status> </customer> Create a sample object type customer which contains the same elements as the XML data using the following command: CREATE OR REPLACE TYPE CUSTOMER AS OBJECT ( ID VARCHAR2(10) , NAME VARCHAR2(50) , COUNTRY VARCHAR2(50), STATUS VARCHAR2(15)) / The following pl/sql block will convert the xml data into one XMLTYPE variable and then populates the object variable v_in which is of type customer and displays the object contents. DECLARE v_in customer; v_xml xmltype; BEGIN v_xml :=XMLTYPE('<c...

Multitable inserts allow a single INSERT INTO .. SELECT statement to conditionally, or non-conditionally, insert into multiple tables. This statement reduces table scans and PL/SQL code necessary for performing multiple conditional inserts compared to previous versions. It's main use is for the ETL process in data warehouses where it can be parallelized and/or convert non-relational data into a relational format. --Unconditional Insert into all tables INSERT ALL INTO ap_cust VALUES (customer_id, program_id, delivered_date) INTO ap_orders VALUES (order_date, program_id) SELECT program_id, delivered_date, customer_id, order_dateFROM airplanes; -- Pivoting insert to split non-relational data INSERT ALL INTO Sales_info VALUES (employee_id,week_id,sales_MON) INTO Sales_info VALUES (employee_id,week_id,sales_TUE) INTO Sales_info VALUES (employee_id,week_id,sales_WED) INTO Sales_info VALUES (employee_id,week_id,sales_THUR) INTO Sales_info VALUES (employee_id,week_id, sales_FRI) SELEC...

I was hunting for an article when my friend came up and asked me this question. How to list/delete duplicate records without using ROWID? I never asked why he do not want to use ROWID, because I was caught in some thinking. I done some googling, and derived the method, but first the ROWID method First how to delete records with ROWID, and why? ROWID is the unique record identifier within Oracle, and it is easy to get the minimum of the ROWID for the duplicating criteria and delete the rest of them. We will take the example of EMP table for simplicity. Assume that there is no primary key in the Employee_Id column due to some data migration requirements. Once the data migration is over I need to check the existense of duplicate records. This is how traditionally we used to achieve this: Suppose the data after migration stands like this: Employee_Id Employee_Name 10          Scott 11          Tige...

Topic: Beginners Level From time immemorial there has been debate over the usage of like clause and its association (or rather non-association with index). What is the fuzz all about? Let's check here. Like clause allows you to use wildcard character searches over data stored in oracle database. By this means we can do pattern searching over the existing data. For example: You have a daily meeting calendar in which the attendees names are stored in comma seperated values in a VARCHAR2(2000) field. You want to search on what days a particular attendee say for instance Rose has attended the meeting. table: meeting_schedule fields: meeting_date date meeting_place varchar2(200) meeting_attendees varchar2(2000) In such a case of searching, without the usage of wildcard characters such as % will not yeild appropriate results. The query for such a situation would be: SELECT meeting_date, meeting_place FROM meeting_schedule WHERE meeting_attendees like '%Rose%'; Now the above query...

Starting with Release 7.1 of Oracle, users have had access to a feature called an inline view. An inline view is a view within a query. Using this feature, you can easily accomplish your task. Example: Show percentage of salaries for each department Every row in the report must have access to the total sum of sal. You can simply divide sum (sal) by that total, and you'll have a number that represents the percentage of the total. column percentage format 99.9 select deptno, sum(sal),sum(sal)/tot_sal*100 "PERCENTAGE" from emp, (select sum(sal) tot_sal from emp) group by deptno, tot_sal; With Oracle8i Release 2 (8.1.6 and higher), you can calculate percentages by using the new analytic functions as well. The query using an analytic function might look like this: column percentage format 99.9 select deptno, sum(sal), (ratio_to_report(sum(sal)) over())*100 "PERCENTAGE" from emp group by deptno; The query produces the same answer—but it does so more efficiently, becau...

Oracle offers several possibilities to process file from within PL/SQL. The most used package is UTL_FILE, but with the disadvantage that the read-buffer is limited to 1023 bytes. If you want to read huge chunks of files you can use the DBMS_LOB package, even for the processing of plain ASCII files. There are two solutions to read a file with DBMS_LOB The file is treaded as a large binary object LOB. The whole file is read and saved in a table column of the data type LOB and then processed. The file is read and processed directly from the filesystem location. This Tip shows exactly this case. Example: Suppose we want to read a big file word by word directly from PL/SQL without saving the whole file in a table column. The words in the file are separated with a blank. For simplicity we assume, that there is exactly one blank between the words and the file is a stream with a newline at the end of the file. First we have to create an ORACLE directory as the schema owner. Do not ad...

This tip will enable to create a view even if you do not have an underlying table already present in your database.   In this article you will learn to Create view without a table Creating a table for that view How to make the view to work   Consider the following example:-   CREATE OR REPLACE FORCE VIEW force_view AS SELECT * FROM force_table;   Now check whether the view is created or not:   SELECT object_name, object_type, status, temporary, generated, secondary FROM user_objects WHERE object_name='FORCE_VIEW';   OBJECT_NAME OBJECT_TYPE STATUS       TEMPORARY    GENERATED    SECONDARY ----------- ----------- --------    ---------    ---------   --------- FORCE_VIEW   VIEW         INVALID      N            N    ...

The pl/ sql package utl_file allows me to create, read and write flat files at the O/S level on the server. Also the dbms_lob package allows me to read files from the server and load them into the database. But how do I delete an O/S file from within pl/ sql after I have finished with it. One 'near- soultion ' is to use the utl_file package to re-open the file for writing (without the append option), and then close the file without writing to it. This recovers most of the disk space, but still leaves the file on the system as an empty O/S file. Another approach is to write a short piece of Java, which can then be called from PL/SQL. Java currently offers far more flexibility than PL/SQL when dealing with O/S files, for example you could use Java to invoke and load a directory listing from PL/SQL so that you know what files exist for deletion. (See further reading). A pure simple PL/SQL solution, however, appears to exist in the dbms_backup_restore package. This...

In 10g there is a new procedure hidden away in the undocumented DBMS_BACKUP_RESTORE package. It's called searchfiles , which is a bit of a giveaway and appears to have been introduced for the new backup features in 10g, as RMAN now needs to know about files in the recovery destination. Calling this procedure populates an in memory table called x$krbmsft , which is one of those magic x$ tables, the only column which is of relevance to us is fname_krbmsft which is the fully qualified path and file name. This x$ table acts in a similar fashion to a global temporary table in that its contents can only be seen from the calling session. So two sessions can call searchfiles and each can only see the results of their call (which is extremely useful). The code sample below will only really run as sys , due to the select from x$krbmsft , it's just intended as a demo. The first two parameters in the call to searchfiles are IN OUT so must be defined as variables, even though the secon...

The connection between constraints and the indexes which are used to check these constraints for the current user can be described by this query: select --+ rule o.owner index_owner, o.object_name index_name, n.name constraint_name from sys.cdef$ c, dba_objects o, sys.con$ n where c.enabled = o.object_id and c.con# = n.con# and n.owner# = uid; If you leave away the condition and n.owner# = uid you get all the constraints. You may further limit this query to your constraint name by adding the condition and n.name = 'your_constraint_name' . Why can indexes and constraints be so different? In particular, you may use for example an index on columns (c, a, b) to enable a unique constraint on columns (a, b, c). Remember a constraint is a logical structure whereas an index is a physical one. So a unique or a primary constraint just describe the uniqueness. If (c, a, b) is unique then all other permutations are...

Export Tips: * Use buffer parameter while using export and import by atleast 5000000( 5MB ) which will increase the performance of exp and imp by 3 folds. (conventional path) * Take care with NLS_LANG. It must match with V$NLS_PARAMETERS.NLS_CHARACTERSET. * For Speedy Exports set parameter db_file_multiblock_read_count =128. (OS Dependent) * The Export parameter BUFFER applies only to conventional path Exports. For direct path Export, use the RECORDLENGTH parameter to specify the size of the buffer that Export uses for writing to the export file. * Use direct mode export (direct=Y). * RECORDLENGTH recommended values: Multiples of the file system I/O block size, Multiples of DB_BLOCK_SIZE Import Tips: * Use indexes=n to ignore the index importing. * Using indexfile to create the index to file, create the indexes after the importing using script file. * Using rows=n indexes=y to import index in a separate import action. Example: exp eva6004/eva6004@uwms file=ev...

Disclaimer: This will work only in Windows REM REM Script: send.sql REM Rem Rem Name: Send.Sql Rem Function: Send a message to all connect users on NT. Rem Usage: Execute the file connected as SYS from SQL*Plus Rem Set Pages 0 Feed Off Term Off Echo Off Spool Temp.Bat Select Distinct 'Net Send ' || Terminal|| ' Please log off now.' From V$Session Where UserName Is Not Null and terminal is not null; Spool Off Host Temp exit

column dcol new_value mydate noprint select to_char(sysdate,'YYYYMMDD') dcol from dual; spool &mydate._report.txt -- my report goes here select * from mytable; spool off