Ask Anantha

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

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

Author: Michael J. Hillenbrand Step aside … I am certified! Just two days after I proudly framed and hung my first Oracle OCP certification on my cubicle wall, one of my colleagues hung a Dilbert cartoon right next to it. The Dilbert went something like this. In the first frame Dilbert is having trouble with his computer. A superhero in tights with a Capital C on his chest tells Dilbert "Step away from the server, I am certified!" In the next frame, our superhero sits down in front of the computer, Dilbert now watching over his shoulder, and says "I SUMMON THE VAST POWER OF CERTIFICATION!" In the last frame, the superhero realizes he doesn't know what to do and admits, "This is embarrassing -- that's all I remember from the classes." The certification debate Whether or not to get certified has been a widely debated topic for as long as I can remember. On one side of the fence are those that see great value in certification, that claim that the...

Author: David (DBA) This article makes direct comparison between the Oracle architecture (instance, databases, physical files, network, configuration files, etc) and the DB2 architecture thus allowing Oracle DBAs to understand the similarities and differences between Oracle and DB2. Because of its comparative nature (showing you how the same things work in DB2 compared to Oracle) I think its a great first read if you are an Oracle DBA new to DB2. I do have some comments I would like to add: 1) IBM says: DB2 does contain a binary file known as the system database directory that contains entries of all the databases you can connect from your DB2 machine. This compares best to Oracle tnsnames.ora file. I don't understand why IBM didn't draw this comparison. 2) IBM says: Every Oracle database contains a table space named SYSTEM, which Oracle creates automatically when the database is created. Other table spaces for user, temporary and index data need to be created after the datab...

Author: Nishith Data warehouse vs. database is a long and old debate. In this article I am going to discuss the difference between data warehouse and database. I will also discuss that which one is better to use. Similarities in Database and Data warehouse: Both database and data warehouse are databases. Both database and data warehouse have some tables containing data. Both database and data warehouse have indexes, keys, views etc. Difference between Database and Data warehouse: So is that ‘Data warehouse' really different from the tables in you application? And if the two aren't really different, maybe you can just run your queries and reports directly from your application databases! Well, to be fair, that may be just what you are doing right now, running some end-of-day reports as complex SQL queries and shipping them off to those who need them. And this scheme might just be serving you fine right now. Nothing wrong with that if it works for you...

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

A constraint is defined by Oracle as a declarative way to define a business rule for a column of a table. An integrity constraint is a statement about a table's data that is always true. The difference between a unique index and a unique key/primary key constraint starts with the fact that the constraint is a rule while the index is a database object that is used to provide improved performance in the retrieval of rows from a table. It is a physical object that takes space and is created with the DDL command: create index or as part of a create table with primary key or unique key constraints or an alter table command that adds these constraints (see SQL Manual). Briefly the constraints are: Not Null - Column value must be present. Unique Key - Column(s) value(s) must be unique in table or null (see note below). Primary Key - Unique key + Not Null which equates to every column in the key must have a value and this value is unique so the primary key uniquely identifies each and ev...

Let’s say we have an instance where in there are 100 tables in a schema and that Schema Owner is not supposed to use delete on any of those tables. Then instead of getting into the mess of Grants / revokes, we can use one of the facilities that are provided by Oracle. There is a table called as product_profile and it can be created (if not present) by executing the PUBBLD.SQL The PRODUCT_PROFILE table is owned by SYSTEM and has the following structure: Name           Null?         Type ------------------------------------ PRODUCT       NOT NULL     VARCHAR2(30) USERID                    VARCHAR2(30) ATTRIBUTE                 VARCHAR2(240) SCOPE         ...

The answer for this question took me and Google hours. And finally I got the answer: What is OHS? Oracle HTTP Server is often called Oracle HTTP Server. What version of Apache is Oracle HTTP Server based on? Two versions. Apache version 1.3.34 for OHS based on Apache 1.3 and Apache version 2.0.55 for OHS based on Apache 2.0. Are Apache modules, not provided by Oracle, supported when integrated with OHS? Oracle only provides support for modules included in the Oracle distribution. Oracle does not support modules obtained from any other source, including the Apache Software Foundation. However, OHS will still be supported when non-Oracle provided modules are included. If Oracle Support suspects that a non-Oracle provided module is contributing to a reported problem, customers may be requested to reproduce the problem without that module being included. What is Oracle’s policy on fixing security bugs found in OHS? Oracle’s policy and process for fixing security vulnerabilities can be foun...