You are here: Home ยป Blog

What is a schema in an Oracle database?

Posted on Monday, October 06 2014 at 12:46 | Category: SQL & Databases | 0 Comment(s)

There is sometimes a little confusion about the term Schema in Oracle databases. In General, a database schema is a collection of database objects (Tables, Views, Packages, ...) which conceptually belong together.

However, in Oracle RDBMS, a schema is also tightly coupled to a user. Essentially, once there is a user, there is a schema (which can be empty though, as for user "RUN" in the following diagram):

The Schema name is the same as the user name - generally, in Oracle databases, the terms "User" and "Schema" are the same.

A typical deployment scenario is to have a runtime user ("RUN" in the example above) who does not own any database objects, and an owning user who owns the database objects. The application then accesses the database through the runtime user, which can be granted or denied access to the various database objects which are owned by the owning user.

See also Difference between a User and a Schema in Oracle? on stackoverflow for more information.


Passing string parameters to a SQL*Plus script

Posted on Friday, April 04 2014 at 16:23 | Category: SQL & Databases | 0 Comment(s)

With SQL*Plus, it is possible to use parameters in the form &1, &2, ... inside a script to be executed through the sqlplus command and pass the parameter values from the command line. Consider this script, contained in a file called sample.sql:


SELECT '&1' FROM DUAL;

This script can be executed from a unix shell using sqlplus as follows:

$ sqlplus user/pass@instance @sample.sql Hello
...
old   1: SELECT '&1' FROM DUAL
new   1: SELECT 'Hello' FROM DUAL

'HELL
-----
Hello

Now, it is also possible that the parameter is not quoted inside the script:


SELECT &1 FROM DUAL;

If we execute this script in the same way as before, SQL*Plus simply inserts the parameter into the SELECT statement and it is finally treated as identifier:

$ sqlplus user/pass@instance @sample.sql Hello
...
old   1: SELECT &1 FROM DUAL
new   1: SELECT Hello FROM DUAL
SELECT Hello FROM DUAL
       *
ERROR at line 1:
ORA-00904: "HELLO": invalid identifier

To really pass the parameter as String, we need to properly escape it on the command line, using double quotes, single quotes and both of them need to be escaped:

$ sqlplus user/pass@instance @sample.sql \"\'Hello\'\"
...
old   1: SELECT &1 FROM DUAL
new   1: SELECT 'Hello' FROM DUAL

'HELL
-----
Hello

Note that using parameters this way should always be taken with care, so that no sql injection security issues are introduced!


Using database links in the Oracle database

Posted on Monday, February 27 2012 at 21:42 | Category: SQL & Databases | 0 Comment(s)

Read this tutorial if you want to find out how to create and use database links in the Oracle database: Using database links in the Oracle RDBMS


Performance of ORDER BY in SQL queries

Posted on Tuesday, February 07 2012 at 22:31 | Category: SQL & Databases | 0 Comment(s)

I did some basic investigation on the performance of ORDER BY in SQL Queries. Read the complete article here: Order By Performance


Displaying results 5 to 8 out of 9