Oracle Replace and Translate

Replace:
Replace funtion replaces a given string with another set of characters

Syntax:
replace( string1, string_to_replace, [ replacement_string ] )

Example:

SQL> select replace ('Hello World','World','Beutiful World') from dual;
REPLACE('HELLOWORLD'
--------------------
Hello Beutiful World

What will be the output, if we didnt provide the string to be replaced?
SQL> select replace ('Hello World','World') from dual;
REPLAC
------
Hello

The word World is removed.


What if, we need to replace "Hello" with "Hi" and "World" with "Beautiful World"?
Then we have to use TRANSLATE
TRANSLATE:

If we need to replace multiple set of characters with another given set of characters use TRANSLATE. Translate takes a list of characters to be replaced and a list of adjacent character which will replace the given one. It replaces by position, the first character of the given list is replaced with the first character of the given replacement list, second with second and so on.

As the above given example for replace, if there is no string in the equivalent position, then the character is dropped from the source text.
Examples:

Use translate to:

Replace a single character
SQL> select translate  ('Hello World','W','i') from dual;

TRANSLATE('
-----------
Hello iorld

Replace a multiple characters
SQL> select translate  ('Hello World','Wo','wO') from dual;
TRANSLATE('
-----------
HellO wOrld

Note: You have to make sure that at least one character should be given in the replacement list, else TRANSLATE wont give us expected result.

Use TRANSLATE to replace double quotes
SELECT TRANSLATE('"Darn double quotes "', 'A"', 'A')
FROM DUAL;

here if you hadn't given the character A, the value returned is null, ie everything is dropped.
SQL> SELECT TRANSLATE('"Darn double quotes "', '"', '')
  2  FROM DUAL;
T
-
Use TRANSLATE to get the count of any items in the given string
SQL> WITH data AS (SELECT 'Whose line is it anyway' line FROM DUAL)
  2  SELECT LENGTH(line)-LENGTH(TRANSLATE(line,'xaeiou','x')) Vowels
  3  FROM data;
    VOWELS
----------
         8


SQL> WITH data AS (SELECT 'Whose line is it anyway' line FROM DUAL)
  2  SELECT LENGTH(line)-LENGTH(TRANSLATE(line,'aeiou','')) Vowels
  3  FROM data;
    VOWELS
----------

Encryption and Decryption:
You can also use TRANSLATE for encryption and decryption

SQL> SELECT TRANSLATE('Market crashes down',
  2  'abcdefghijklmnopqrstuvwxyz', '0123456789qwertyuiop[kjhbv')
  3  FROM DUAL;
TRANSLATE('MARKETCR
-------------------
M0iq4p 2i0o74o 3tjr

Now let us use this encrypted value and try to decrypt

SQL> SELECT TRANSLATE('M0iq4p 2i0o74o 3tjr',
  2  '0123456789qwertyuiop[kjhbv', 'abcdefghijklmnopqrstuvwxyz')
  3  FROM DUAL;
TRANSLATE('M0IQ4P2I
-------------------
Market crashes down
Fine, works well...


Note: Translate always replace a single character at a time. Also it wont replace a single character to a multiple character as like we do with Replace function.

****
Hope this helps..Phoenix

How do Oracle process Hierarchical queries

Not yet published. Sorry for the inconvenience caused.

Oracle Hierarchical queries

It was really confusing when I was going through the documentation of Oracle Hierarchical queries for the first time. I red it many times to understand it well,  practiced many times to save it in my memory and put it down in my own words so that it stays there for a long time in the way I understood it. And I hope you too can understand it well.

When we talk about hierarchical queries, then it is very well clear that we are dealing with hierarchical data. Hope you know what a hierarchical data refers to. It can be a parent child relation, boss to employee relation, manager to reportee etc. So whatever such data we have in a table we need to select the data suitable to our convenience.

Understanding Hierarchical query clause:
When we are trying to fetch data from our hierarchical table, it is very obvious that we need to know two things.

1. From which level of hierarchy we need to start? That is we need a starting point.
2. What kind of relationship are we looking in this hierarchy? That is we need a connection between the data.

Hope you understood above two parts, if then, you wont forget your hierarchical query clause hereafter. For our hierarchical query clause we need both the above "start with" and "connect" keywords.

START WITH condition CONNECT BY condition

Hence START WITH specifies the root row of the hierarchy and CONNECT BY specifies the relationship between the parent rows and child rows of the hierarchy.

Initially while I was going through the examples of hierarchical query the operation PRIOR was so confusing for me and I was not able to understand this code well enough. Then I learned in a different method and now it become easy for me. Let me try to explain it in the below example.

--Create one table to hold our hierarchical data

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 30 17:52:02 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table hierarchy_test
  2  (
  3  emp_id number(10),
  4  l_name varchar2(10),
  5  m_name varchar2(10),
  6  f_name varchar2(10),
  7  manager_id number(10),
  8  constraint hierarchy_test_pk primary key (emp_id)
  9  );
Table created.
SQL>

--Now insert the hierarchical data into this table

 SQL> INSERT INTO hierarchy_test VALUES (100,'King','','','');
1 row created.
SQL> INSERT INTO hierarchy_test VALUES (101,'Scott','','','100');
1 row created.
SQL> INSERT INTO hierarchy_test VALUES (102,'King','','',101);
1 row created.
SQL> INSERT INTO hierarchy_test VALUES (103,'King','','',101);
1 row created.
SQL> INSERT INTO hierarchy_test VALUES (104,'King','','',101);
1 row created.
SQL> INSERT INTO hierarchy_test VALUES (105,'King','','',103);
1 row created.
SQL> INSERT INTO hierarchy_test VALUES (106,'King','','',103);

--Example for hierarchical query
SQL> select emp_id, manager_id, level
  2  from hierarchy_test
  3  start with emp_id=100
  4  connect by  PRIOR emp_id =  manager_id
  5  order by level;


    EMP_ID MANAGER_ID      LEVEL
---------- ---------- ----------
       100                     1
       101        100          2
       104        101          3
       102        101          3
       103        101          3
       106        103          4
       105        103          4
7 rows selected.

You can use PRIOR keyword on either side of the operator '='. But I got confused why? What difference does it make. Also I was not able to understand why this keyword is used here.

Let me read this clause as below so that I make sure that I understood it well.

connect by  PRIOR emp_id =  manager_id
as
connect by previous emp_id = manager_id of the current row.

So, here in the example, first row emp_id=100, manager_id=null.
Now to get the current row get all the records for which the manager_id is equal to the employee id of the previous record. Now you go back and look into the result, you will be clear on why we use PRIOR in our code.

You can rewrite the above code as below with same result, as per my above sentence.

 SQL> select emp_id, manager_id, level
  2  from hierarchy_test
  3  start with emp_id=100
  4  connect by   manager_id =PRIOR emp_id
  5  order by level;
    EMP_ID MANAGER_ID      LEVEL
---------- ---------- ----------
       100                     1
       101        100          2
       104        101          3
       102        101          3
       103        101          3
       106        103          4
       105        103          4
7 rows selected.
SQL>

What if you give the PRIOR keyword just opposite as below for our data?

SQL> select emp_id, manager_id, level
  2  from hierarchy_test
  3  start with emp_id=100
  4  connect by   emp_id =  PRIOR manager_id
  5  order by level;
    EMP_ID MANAGER_ID      LEVEL
---------- ---------- ----------
       100                     1
SQL>

We get only one result set right?
Here what happens is we started with emp_id 100 and for the next row we said that, get me the row for which the employee id is the manager id of the previous record!!

Ooops!!
Kind don't have a manager and hence we have that column value as null. Hence oracle doesn't find any way to proceed further. Hence we got only one record.

Hope you got my point.

Now, what if I am not using the start by keyword. Your code will work but result is bad and not as expected.

SQL> select emp_id, manager_id, level
  2  from hierarchy_test
  3  connect by  PRIOR emp_id =  manager_id

    EMP_ID MANAGER_ID      LEVEL
---------- ---------- ----------
       104        101          1
       106        103          1
       101        100          1
       103        101          1
       105        103          1
       100                     1
       102        101          1
       106        103          2
       105        103          2
       104        101          2
       103        101          2
    EMP_ID MANAGER_ID      LEVEL
---------- ---------- ----------
       102        101          2
       101        100          2
       104        101          3
       102        101          3
       103        101          3
       105        103          3
       106        103          3
       106        103          4
       105        103          4
20 rows selected.
SQL>


Level 4 considers only 105 and 106 whereas level 3 consider 105,106, 102, 103 and 104 and it goes like that till level 1. Hence give the start with keyword with your hierarchical query.

Related Articles:
How do Oracle process Hierarchical queries.


****
Hope this is useful. Thanks Phoenix.

Tweet

UNIX interview questions

Q: What is the difference between vi and emacs editor?
Ans: vi is a modaleditor whereas emacs is not. That is in vi editor you have edit mode and command mode whereas in emacs only one mode. For more details visit vi editor.

Q: UNIX is case sensitive. To avoid this issue with case sensitive what option you can use while searching for a pattern?
Ans:
grep 'Phoenix' *.txt may give you output, whereas grep 'PHOENIX' *.txt may not.
To avoid this case sensitivity use the option 'i'
grep -i 'PHOENIX' *.txt

Q: What is meant by a filter in UNIX?
A filter is a program which can receive a flow of data from std input, process (or filter) it and send the result
to the std output.

Q: What is the significance of tee command?
Ans:
A powerful command that reads the standard input and sends it to standard output while redirecting a copy to a file specified. It is just re-route the pipline with tee.

Q: What is the significance of awk command?
Ans:
Helps you grab specific columns of information, modify text as it follow past, and swap the order of column information in a file.

Helps in analyzing and manipulating text files. Another alternative for this is the sed command which is less powerful compared to awk command.

Q: What does the command “ $who | sort –logfile > newfile” do?
Ans: This example explains the tricky use of hyphen (-). Above code gives the output from who command as input for sort command, meanwhile sort command will open the file called logfile and the content of this file is sorted togehter with the output of who and moving that to the file called newfile.
Q: What does the command “$ls | wc –l > file1” do?
Ans: Here ls is the input for the command wc and takes the count of line from ls and the count of lines is stored into file1 instead of displaying in the monitor.

Q: Which of the following commands is not a filter man , (b) cat , (c) pg , (d) head
Ans:

Ans: man

A filter is a program which can receive a flow of data from std input, process (or filter) it and send the result
to the std output.Q: What is the difference between the redirection operators > and >>?
Ans:
When redirection operator > overwrite the content of file >> operator appends to file.

Q: Explain the steps that a shell follows while processing a command.
Ans:
Below are the steps followed by shell

Parsing: Shell first breaks up the command line into words, using spaces and delimiters, unless quoted. All consecutive spaces or tabs are replaces with single space.

Variable Evaluation: All words preceded by a $ are evaluated as variables, unless quoted or escaped.

Command Substitution:Any commands surrounded by backquotes is executed by the shell which then replaces the standard output of the command into the command line.

Wild Card Interpretation: The shell finally scans the command line for the wildcard characters (*,.,?,[,]). Any word containing a wild card is replaced by a sorted list of fienames that match the pattern. The list of this filenames then become the argument to the command.

PATH Evaluation:
It finally looks for the PATH variable to determine the sequence of directories it has to search in order to hunt for the command.

Q: What difference between cmp and diff commands?
Ans:

cmp - Compares two files byte by byte and displays the first mismatch

diff - tells the changes to be made to make the files identical

Q: What is the difference between cat and more command?

Ans:

Cat displays file contents. If the file is large the contents scroll off the screen before we view it. So command more is like a pager which displays the contents page by page.

Q: Write a command to kill the last background job?

Ans:

Kill $!

Q: Which command is used to delete all files in current directory and all its sub-directories?
Ans:
rm -r *

Q: Write a command to display a file’s contents in various formats?

Ans:$od -cbd file_name

c - character, b - binary (octal), d-decimal, od=Octal Dump.
Q: What will the following command do/
$echo *
Ans;
It is similar to ls command, list down all the files in the current directory.

Q: Is it possible to create a new file system in UNIX?
Ans: Yes, 'mkfs' is used to create a new file system.

Q: Is it possible to restrict the incoming message?
Ans: Yes, using the 'mesg' command

Q:What is the use of the command "ls -x chapter[1-5]"?

ls stands for list; so it displays the list of the files that starts with 'chapter' with suffix '1' to '5', chapter1, chapter2, and so on.

Q: Is ‘du’ a command? If so, what is its use?

Ans:

Yes, it stands for ‘disk usage’. With the help of this command you can find the disk capacity and free space

of the disk.

Q: Is it possible to count number char, line in a file; if so, How?

Ans:

Yes, wc-stands for word count.

wc -c for counting number of characters in a file.
wc -l for counting lines in a file.

Q: Name the data structure used to maintain file identification?

Ans:

inode, each file has a separate inode and a unique inode number.

Q: How many prompts are available in a UNIX system?

Ans:

Two prompts, PS1 (Primary Prompt), PS2 (Secondary Prompt).

Q. How does the kernel differentiate device files and ordinary files?
Kernel checks 'type' field in the file's inode structure.

***
Hope this is helpful. Thanks Phoenix.

Oracle sql statement processing steps.

Whenever a SQL statement is submitted to execute, Oracle goes through the below mentioned steps

1. Check for identical statements in memory to avoid performance overhead due to parsing.
2. Allocate memory in shared memory area.
3. Evaluate the syntax of the statement to check whether all the oracle key words are spell correctly.
4. Do semantic check, where all the objects (tables or columns) are validated and check for the user's privilege to access these objects.
5. Form an execution plan to execute the statement.

****
Hope this is useful. Thanks Phoenix

Oracle Autonomous Transaction

Let me try to explain Oracle Autonomous Transaction in a little different way so that you can remember it very easily.

How many different types of plsql blocks can you write?

Ooops!!

Procedures and Functions are the pls/ql blocks written uniquely with or without parameters. What else could I do with this? What are all these different types available? Let us try it out.

1. We use to write a Procedure/Function without giving it a name for adhock purpose as we don't want to store that in the database to use it later. - ANONYMOUS PROC/FUNC

2. We use to use this anonymous items in declaration section of plsql as well - LOCAL PROC/FUNC

3. When we need this proc/func to be stored in database to use frequently, we store it with a name tagged to it- STAND ALONE PROC/FUNC or say stored Procedure or Function.

4. To logically group our procs/funcs we can place these procedures or functions inside a package - PACKAGED PROC/FUNC

So now we know there are 4 different ways we can use procedures and functions.
We can use all the above mentioned different ways of proc/fncs for autonomous transactions.

Autonomous Transactions?????

I had heard about Autonomous Institutes we're they function independently.
PL/SQL autonomous transaction also work exactly the same. They function independently. It helps you leave the calling transaction and performs an individual transaction and resume to the calling transaction without affecting the calling transaction.

Confused? No need to worry. You will get it by the time you complete this.

Here calling transaction refers to your main PL/SQL block and the autonomous transaction is called from this block. The purpose of your main PL/SQL block and autonomous PL/SQL block is entirely different. Both behave as transaction done is seperate sessions. There is no link between both these type of transactions, hence only committed data can be shared among them.

Autonomous transaction code block is nothing but plsql blocks. Syntax is as below,

Example for anonymous:

DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
Statement;
COMMIT;
END;
/

Example for stored PROC:
CREATE OR REPLACE PROCEDURE PROCEDURE_NAME AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
statement;
COMMIT;
END;
/

We have 5 different type of plsql blocks which can be used for this purpose. Four of them are those discussed above and the fifth one is "TYPE methods".

Let us try to understand autonomous transaction a little more deep by going through few scenarios.

1. You need to insert two records into an empty table.

INSERT INTO temp_table (id, name) VALUES (1, 'Scott');
INSERT INTO temp_table (id, name) VALUES (2, 'Peter');

Next without committing this transaction you want to execute an autonomous transaction, say an individual transaction.

Before executing the autonomous transaction, select count(*) give you result: 2

SELECT count(*) FROM temp_table;

Execute autonomous transaction which inserts 5 records to the same table and have a commit statement within this autonomous transaction code block.

DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
FOR i IN 3 .. 5 LOOP
INSERT INTO temp_table (id, name)
VALUES (i, 'Name ' || i);
END LOOP;
COMMIT;
END;
/

Now select count(*) give you result as expected:7

Now execute a rollback.

Rollback;

Select count(*) gives you only 5 as the result.

The records inserted in current transaction got rolled back. The commit affected only the autonomous transaction block.

Hope now, you are clear with how to user autonomous transaction.

Now the question is why should we use autonomous transaction?
It is most popularly used for error logging. In applications particularly like banking, you can't commit a transaction until it is successfully completed. Hence to capture any error that may happen in between any transaction we use autonomous transaction.

How can we use this?
Create a proc as autonomous which logs data into one table. Call this proc in the exceptional section of your plsql block before you rollback.The error data is committed successfully by the autonomous transaction and your calling block actions are rolled back

Restrict the use of autonomous transaction only for error logging, else it will be a disaster.

Hope you understood well. Regards Shiyas..

How to retrieve accidentally overwritten content in my blog

Necessity is the father of invention!!

Today I feel, I tried something out of the box for a solution I need and here I share it with my readers.

I worked hard on my third blog BUS#356 V, edited and edited, red many times and just before posting in social networks called up one of my close friend to review. Upon receiving nice feedback, I posted and was doing my final touch up. I was working on my friend's brand new Apple laptop the functionality of which is awesome but sophisticated as well. I ended up in deleting the content of my blog without any backup.

My friends started calling me up to tell me that my blog is blank. I go broke on realizing that all my work is gone within a second. I was getting more hits into my site and everyone could see only blank page. Only option I have is to write it again but still it cant be in its original form as I have spent too much time on that.

I started googling for any options to retrieve my overwritten data. I can find only for deleted one, but not for overwritten files in blog site. Then I started looking into the history. But that too goes in vain. The browsing history has all the urls, but as it says "All Roads Lead To Venice", all the history urls upon clicking on that will take me to my blogsite where I can see only blank content which was latest update saved in the web.

I sat for some time not knowing what to do. I start setting up my mind for writing the entire thing once again, then work and rework on it. I made up my mind.

Suddenly the thought came to mind, what if I hit the history URL which I havent touched and dont allow the system to get the data from web. Will I get the page which I visited?

Didnt waited for a second thought, I pulled out the network cable hastly. Searched for the list of history. Selected the one which I havent touched after this data loss and hit.

I got my data which need only slight changes which I have done after my friends review.


In future, if this can be useful to atleast one, I will be happy.

Please don't forget to leave me a comment, if you are reading this for a solution.