What is (Private) Synonym?

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 private, you should be creating private synonyms for this purpose.

How to destroy a Synonym?
To destroy or drop a synonym follow the syntax:

DROP SYNONYM synonym_name;

Example:

DROP SYNONYM clerk;


Click here to learn What is Public Synonym

What is Oracle Least Function?

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.

Generating sequential numbers without using user_objects

Sequence number generation in a query has been almost natural in reporting queries. You normally tend to use rownum pseudo column from either your table or if it is a generic query user_objects.

For example:

select rownum from user_objects where rownum <= 100;

The output will be:

ROWNUM

---------------

1

2

3

.

.

100

This is based on the assumption that there are more number of objects in user_objects view so as to return a high number. The more robust way to get an output without using such a large view was not literally possible in pre-9i era. Starting 9i Oracle has come up with a beautiful solution. You can use DUAL table in conjunction with CONNECT BY clause to come up with such a result.

Example:

select level from dual connect by level <=100;

This also will generate the same output generated in our first case.

What is Public Synonym?

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 synonym follow the syntax:

DROP PUBLIC SYNONYM synonym_name;

Example:

DROP PUBLIC SYNONYM clerk;



Click here to learn What is (Private) Synonym?

How to kill a process within windows BATch file

This article is result of a friend's query related to "How to kill a windows/DOS process within a windows Batch file.

This is the solution in brief:

START "do something window" dir
FOR /F "tokens=2" %I in ('TASKLIST /NH /FI "WINDOWTITLE eq do something window"' ) DO SET PID=%I
ECHO %PID%
TASKKILL /PID %PID%

FOR Loop is only required if there are multiple processes.

A link is what I am going to provide as for more information:

Click here to access the solution