Learn Oracle PL/SQL with Free Lessons & Tips

1. Write a query to get the below output,

1	11	21	31	..	..	91
2	12	22	32	..	..	92
3	13	23	33	..	..	93
						..
..	..	..	..	..	..	..
..	..	..	..	..	..	..
10	20	30	40			100

Ans:

select level,level+10,level+20,
         level+30,level+40,level+50,
         level+60,level+70,level+80,
         level+90
     from dual connect by level <=10;

 

2. Write a query to generate tables from 7 to 70.

Like,  7,14,21....8,16,24...9,18,27......70,140,210.

Ans:

Select a.a*b.b from
               (select level+6 a from dual connect by level <=64) a,
               (select level b from dual connect by level <=10)     b;

And there are many more.............

read less

 

The above snapshot says it all. Hence it is said, 'Picture says it all'.

Basically, there are 4-5 steps. There are two possible ways of query being executed.
      1. Soft Parse-  Improves performance. This is also called as 'Library Cache Hit'.
      2. Hard Parse- Degrades performance. This is also called as 'Library Cache Miss'.

Let us understand the steps one by one briefly.
There is a parser inside Oracle, which parses the SQL Query for further processing.
The first 3 steps of SQL Query Execution are taken care of by the parser.

Step 1: Syntax check

       This checks only for syntax errors. A pure syntax check and nothing else.
Examples: 1. Are the keywords correct?
                      Like, 'seelect', 'form', 'groupby', 'orderby' are few common spelling mistakes.
                 2. Does SQL Query end with a semicolon (;)?
                 3. Are column names in the 'Select' Clause are separated by commas (,)?
                 4. Does 'Select' clause include only the colmuns which are present in the 'Group by' clause?
                 5. Does 'Group by' clause appear after 'Where clause'?
                 6. Is 'Order by' clause the last clause in the query?
                 etc. etc....

            In each of these cases, if the answer is 'No', oracles throws an error stating the same.

Step 2: Sematic chck

      Once the query passes the Syntax check, next step is Semantic check.
Here, the references of all objects present in the query are checked along with user privileges. The check happens against metadata maintained in the Oracle.
Examples: 1. Is table name valid or such a table exists?
                 2. Are columns names correct?
                 3. Does user have select/update/delete/insert/create privilege?
                 etc. etc. etc.

So during Syntax check, it doesn't check for the validity of table names, column names, privileges etc.

Let's say, I am running this... "select * from epm;" 

This passes Syntax check, though I am writing a wrong table name. Instead of "emp", I have written "epm". But, this query is fine syntax-wise and hence passes the Syntax check. But it fails in the next step of Semantic check where the object names are verified.

But we will not be able to notice whether a query failed at Syntax check or at Semantic check when we run a query from SQL*Plus or any other GUI. Because, everything is handled in one go from user point of view and the errors are sent if the Query fails at any step. Otherwise, we get the final output.

 Step 3: Shared pool check

This is an important check. I am planning to write a separte lesson on this. Let us understand briefly here.

Basically, once the query passed both Syntax check and Semantic check, it goes for third check called 'Shared pool check'. 

Just to put it briefly, 'Shared pool' is a memory segment present inside every Oracle instance, which retains the recently executed SQL and PLSQL code inside its 'Library Cache' based on LRU algorithm.

So, if parser finds that the query is present inside 'Library Cache' or 'Shared pool', then it is called 'Soft Parse'. It goes to step 5, executes the query and sends the output to whoever requested it.

As simple as that. But, if parser finds that such a query is not present inside 'Shared pool' or 'Library Cache', then it is called 'Hard Parse'. So, Oracle must carry out step 4 first to prepare the query and then go to step 5 finally to execute the query and then send the output to the requestor.

So, the first three steps are always carried out for every SQL Query and they are less expensive.

Step 4:

      The step 4 is very expensive or costly. Meaning, it consumes lot of resources to complete this, like CPU, I/O reads, physical reads, extra algorithms to generate mutliple queries and select the best one etc.

Hence, 'Hard Parse' degrades performance. We should keep this in mind always before writing any query. To put it simply for now, avoid hard coding and use bind variables to take the advantage of 'Soft Parse'.

Step 4 happens only in case of 'Hard parse', which is decided in step 3 as we discussed above.

We can split this into two sub-steps.
      a. Optimization: The Optimizer generates mutliple plans using various combinations of joins                      (Nested loop/Sort Merge/Hash), access paths (full table scan/index range scan/index skip scan                etc) and join orders.
          Finally selects the best plan based on cost. The lower the cost, the better the performance.

      b. Row source generation: Query execution plan is generated for the best query
          selected in the above step. This is what we see in Explain plan.
          We can further make modifications to the query, force the Optimizer to select an even lower cost            query by changing access paths, joins, join orders or using Hints. And we can verify if the                  execution plan got changed by again looking at its Explain plan.
         This is called 'Performance tuning' or 'Query Tuning'. Let's not go deep into it now.

None of this happens in case of 'Soft Parse' and hence improves performance.

Step 5: Query Execution

      Step 5 is again common for each query irresepctive of whether it is 'Soft Parse' or 'Hard Parse'. 

As we already discussed, it executes the SQL Query and sends the output
to the requested program or user.

 

So this is about it. To wrap up, there are two ways of execution or parsing namely- 'Hard parse' and 'Soft Parse'. And there are 5 steps totally. Steps 1 to 3 are common for every query or for each type of execution/parsing. Step 4 happens only for 'Hard Parse' way of execution and not for 'Soft Parse' way. Step 5 is the final and common step, which finally executes the SQL Query. Use Explain plan to check the execution plan selected by Optimizer and tune the query. Use Bind variables to enable 'Soft Parse'.

read less

I have around 8+ yrs of experience in Support on PLSQL. I wish to change my job. But feeling hard to crack interviews. Could any body provide me some material on PLSQL and performance tuning so that I can get through interviews?

read less

Requirement: How to find the list of files in specified directory?

Step1 : First find the JDK Install or not in the oracle, to find jdk version please run the below sql.

SELECT  dbms_java.get_ojvm_property(PROPSTRING=>'java.version') FROM dual

Step2: Once get the output above then only we can create the java source. If the output is diaplyed then perform the below task

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "list_of_files" AS

import java.io.File;

public class list_of_files {

public static String getFileList(String idir)

// public static void main(String[] args)
{
//System.out.println("testing");

File aDirectory = new File(idir);
File[] filesInDir = aDirectory.listFiles();
String result = "";
for ( int i=0; i<filesInDir.length; i++ )
{
if ( filesInDir[i].isFile()
&& !filesInDir[i].isHidden() )
{
result = result + "," +filesInDir[i].getName();
System.out.println("result="+result);

}
}
return result;
}

}

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

Compile the above code.

3. create or replace function test_fun(p_dir varchar2) return varchar2
AS LANGUAGE JAVA NAME
'list_of_files.getFileList(java.lang.String) return String';

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

Compile the above code

4.select test_fun('c:\jars') from dual;

 run the above sql command.

 

 

 

 

 

 

read less

In computer science, ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties of database transactions. In the context of databases, a sequence of database operations that satisfies the ACID properties and, thus, can be perceived as single logical operation on the data is called a transaction. read less

One of the easiest ways to tackle such Real Time scenarios is through use of Oracle Defined "SYS_REFCURSOR" or "REF CURSOR".

We can create a Procedure with an out parameter of Type SYS_REFCURSOR and in the execution block we can open a cursor variable of same type as SYS_REFCURSOR for the required Select Query.

I will update this with a sample Code shortly.

Till then Cheers and Enjoy!!

read less

For the below written test answer I defined with "*" at the end of the options 

NULL is

• the same as 0 for integer
• the same as blank for character       *
• the same as 0 for integer and blank for character
• the same as 0 for integer and blank for character

The basic function of ____________ is to search for files(or other units of text) that contain a pattern.

• awk  *
• search
• cat
• cmp

  What will be the output of the program?

class SSBool

{

    public static void main(String [] args)

    {

        boolean b1 = true;

        boolean b2 = false;

        boolean b3 = true;

        if ( b1 & b2 | b2 & b3 | b2 ) /* Line 8 */

            System.out.print("ok ");

        if ( b1 & b2 | b2 & b3 | b2 | b1 ) /*Line 10*/

            System.out.println("dokey");

    }

}

 

• ok
• dokey  *
• No output is produced
• Compilation error

  What does the literal “$?” mean in Shell script?

• Exit status of the previous command executed
• Exit status of the last command executed   *
• Exit status of the first command executed
• Exit status of the last command failed

  If 10 bulls can plough 20 identical fields in 3 days working 10 hours a day, then in how many days can 30 bulls plough 32 same identical fields working 8 hours a day?

• 2  *
• 4
• 8
• 10

 What will be the output of the following query? SELECT DECODE(TRANSLATE('A','1234567890','1111111111'),'1','YES', 'NO' ) from DUAL;

• NO  *
• YES
• 1234567890
• 1111111111

Table Employee has 10 records. It has a non-NULL SALARY column which is also UNIQUE. The SQL statement prints? SELECT COUNT(*) FROM Employee WHERE SALARY > ANY (SELECT SALARY FROM Employee);

• 10
• 9  *
• 1
• 0

  What is the output of the following program?

x = 3; y = 5; z = 10;

if [( $x -eq 3 ) -a ( $y -eq 5 -o  $z -eq 10 )]

then

    echo $x

else

    echo $y

fi

 

• 1
• 3
• 5
• Error  *

A train running at the speed of 60 km/hr crosses a pole in 9 seconds. What is the length of the train?

• 120 metres
• 180 metres
• 324 metres
• 150 metres  *

Predict output of the following program

 

#include

typedef struct film{

    int size;

    int pixel;

    float price;

}xyz,pqr;

 

struct film *jadu(){

    static xyz one={231,12,900.0},*p=&one;

    return p;

}

 

int main(){

    pqr *ptr;

    ptr=jadu();

    printf("%d",ptr->pixel);

    return 0;

}

 

• 231
• 12  *
• 900.0
• Compilation error

What will be the output of the following query? SELECT REPLACE(TRANSLATE(LTRIM(RTRIM('!! ATHEN !!','!'), '!'), 'AN', '*'),'','TROUBLE') FROM DUAL;

• ATHEN
• **ATHEN
• ATHEN***
• None of the above  *

 

How will you list all the files within a directory including hidden files aka (.) dot files?

• ls –a   *
• ls –h
• ls –q
• ls –t

How to make any script file executable?

• $chmod 655 *.sh
• $chmod 765 *.sh
• $chmod 755 *.sh  *
• $chmod 754 *.sh

How to find current running processes in Unix server?

• $ ps –ef *
• $ ds –ef
• $ du –ef
• $ ls –ef

Bottom of Form

 

The least significant bit of the binary number, which is equivalent to any odd decimal number is

• 0
• 1   *
• 1 or 0
• None

Which OSI layer is responsible for Encryption and Decryption?

• A) Network Layer   *
• A) Network Layer
• C) Data Link Layer
• D) Presentation Layer

Which of the following memories has the shortest access time?

• Cache memory *
• . Magnetic Bubble memory
• Magnetic core memory
• RAM

Expand the following abbreviation – USB?

• a) Universal sequence bit
• b) Universal serial bus *
• c) Universal sequence byte
• d) Unique serial bit

How many times printf() will be executed in the below mentioned program? main() { int i; for (i = 0; i < 4; i++) fork(); printf(“my pid = %d\n”, getpid()); }

• 4
• 8
• 16  *
• 32

 

 What will be the output of the below program?

 

#include

int main(){

int far *p=(int *)0X70230000;

int far *q=(int *)0XB0210000;

int near *x,near*y;

x=(int near *)p;

y=(int near *)q;

if(x==y)

printf("Both pointer are equal");

else

printf("Both pointer are not equal");

 

    return 0;

}

 

• Both pointers are equal
• Both pointer are not equal
• Segmentation fault
• Compilation error  *

What is the difference between linux file system and windows file system?

• A) Under Linux, the various partitions are detected at boot and assigned a drive letter whereas Under windows, unless you mount a partition or a device, the system does not know of the existence of that partition or device. *
• B) Under Windows, the various partitions are detected at boot and assigned a drive letter whereas Under Linux, unless you mount a partition or a device, the system does not know of the existence of that partition or device
• C) Under Windows, the various partitions are detected at reboot and assigned a drive letter whereas Under Linux, unless you mount a partition or a device, the system does not know of the existence of that partition or device
• D) Under Windows, the various partitions are detected at reboot and assigned a drive letter whereas Under Linux, you mount a partition or a deviceBottom of Form

A bitwise operation 'f' has an interesting characteristic, such that, if f(a,b) = c, it always turns out to be the case that f(b,a) = c; f(a,c) = b; f(c,a) = b; f(b,c) = a; f(c,b) = a.

1. a) Which of these functions could 'f' possibly be?

• f(a,b) = a XOR b  *
• f(a,b) = a + b
• f(a,b) = a - b
• f(a,b) = a * b

the following crontab file entry

0 17 * * 1,2,3,4,5 /usr/sbin/ckbupscd >/dev/console 2>1

The above entry says to run /usr.. at what time

• 17 mins pass midnight
• 5 pm on weekday of each month *
• Midnight on the 17 day of each month
• On the 17 day of each month at 1,2,3,4 and 5 pm

 

 How would you sort a file called shopping on column 3?

• A) Sort –n 3 shopping
• B) Sort –K 3 shopping *
• C) Sort –c 3 shopping 
• D) Sort –c3 shopping

What is the output of following program?

 

#include

void print(int n, int j)

{

   if (j >= n)

      return;

   if (n-j > 0 && n-j >= j)

       printf("%d %d\n", j, n-j);

   print(n, j+1);

}

 int main()

{

    int n = 8;

    print(n, 1);

}

 

• 1 7 2 6 3 5 4 4 4 4   *
• 1 7 2 6 3 5 4 4
• 1 7 2 6 3 5
• 1 2 3 4 5 6 7 8

 What is 2NF in normalization?

• A) if the domain of each attribute contains only atomic (indivisible) values, and the value of each attribute contains only a single value from that domain.[
• if all the attributes in a table are determined only by the candidate keys of that table and not by any non-prime attributes
• If no non-prime attribute is dependent on any proper subset of any candidate key of the table  *
• D) None of above

A train running at the speed of 60 km/hr crosses a pole in 9 seconds. What is the length of the train?

• 120 metres
• 180 metres
• 324 metres
• 150 metres  *

 Predict the output of the following program

 

#include

usingnamespacestd;

intmain()

{

    constchar* p = "12345";

    constchar**q = &p;

    *q = "abcde";

    constchar*s = ++p;

    p = "XYZWVU";

    cout << *++s;

    return0;

}

 

• a
• c
• b
• D) Garbage value  *

 Predict output of the following program

 

#include

using namespace std;

class Test

{

protected:

    int x;

public:

    Test (int i):x(i) { }

    void fun() const  { cout << "fun() const " << endl; }

    void fun()        { cout << "fun() " << endl;     }

};

 

int main()

{

    Test t1 (10);

    const Test t2 (20);

    t1.fun();

    t2.fun();

    return 0;

}

 

• Compilation error
• fun() fun()
• fun() const fun()
• fun() fun() const  *

 

   What does the following query find?

 

(SELECT DISTINCT r.sid

FROM boats b, reserves r

WHERE b.bid = r.bid

AND b.color = 'red')

MINUS

 (SELECT DISTINCT r.sid

FROM boats b, reserves r

WHERE b.bid = r.bid

AND b.color = 'green');

 

• Find the sailor IDs of all sailors who have reserved red boats but not green boats
• Find the sailor IDs of atleast one sailor who have reserved red boats but not green boats  *
• Find the sailor IDs of at most one sailor who have reserved red boats but not green boats
• None of the above

 

 

 Size of IPv6 address is

• 4 bytes
• 8 bytes
• 6 bytes
• 16 bytes  *

What is the probability of getting a sum 9 from two throws of a dice?

• 1/6
• 1/8
• 1/9  *
• 1/12

What will be output of following program?

 

#include

int main(){

static int a=25;

void cdecl conv1() ;

void pascal conv2();

conv1(a);

conv2(a);

return 0;

}

void cdecl conv1(int a,int b){

printf("%d %d",a,b);

}

void pascal conv2(int a,int b){

printf("\n%d %d",a,b);

}

 

• 25 0 0 25
• 25 25
• Error will occur   *
• 25 -99999999 -99999999 25

Which command puts a script to sleep untill a signal is received?

• sleep
• suspend   *
• disown
• break

 

read less

What is Performance in general terms?

Throughput per Input. For Eg: we keep saying, this bike has so  and so much of mileage and all that. That is one of the parameters in measuring the performance of a vehile. Similarly, in the computer programming world, performance is a key factor like in any other industry.

To keep it simple, let us take an example that you have written a program for your client based on his requiremetns and you are giving the desired results. But, as the volume of transactions increases, your program starts performaing poorly. It starts taking 2 mins to give the results, which was 1 sec earlier. Now, there is another competitor and has a solution for it and his program gives the same result in less than a sec inspite of increased volumes for the same set of hardware configuration as you were using. This hardware part is very important. This is our input/investment, which involves things like RAM, no. of cores of cpu etc.

You also have a solution to the above problem and you suggest your client to increas the RAM by 2 times. Hardware and especially what we call as Memory (RAM), is very costly. Why would customer agree to your solution? Here comes the need of performance tuning.

So, what it actually means is, to tune you program to use the hardware efficiently. So, there is a base to refer and it a set of hardware. If there are two versions of a program written. To compare them, we whould run them on the same set of hardware components.

For eg: Lets say, you are doing this statetement in your plsql code.

Select emp_name from emp where emp_id=1120;

which is fine. But what if you want to access the same information for all or 1000 employees. Definitely, you would use a cursor and run it under a loop and process each employee's data at a time.

So, what's happening here. Let me concentrate on context switch only in this case. There are 1000 context swicthes between plsql engine and Sql statement executor. So, if you use Bulk collect, these 1000 would be replaced by a single context switch, which is an anormous amount of perforamance gain. This is just an example. You would have 100s of columns to select and crores of rows in reality.

Let me ask you one question. Why Oracle is the leading RDBMS? Because of the same fact that it has given us lots of perforamnce tuning features like bulk collect, indexing, Materialized views, Partitioning, Clustering, bind variables, HINTS, AWR reports, ASH reports and so on. But, it's our responsibilty to understand them and apply it in our applications.

It was an attempt to make you understand what is performance tuning exactly and why is it needed.To conclude, let me put two important things in performance tuning-

1. It's mainly about reducing the I/O and hence make use of the memory effectively.

2. Writing effective Sql statements solves 80% of the problems and ofcourse you are required to tune other parts of plsql code too.

 

read less

DBMS_JOB : To schedule jobs.

DBMS_SCHEDULER: Extended the functionalities of DBMS_JOB.

UTL_SMTP: To send mails.

UTL_HTTP: To open Web APIs (URLs).

DBMS_LOB: Very interesting and useful. Programmatic interface to deal with Large objects like Clob, Nclob, Blob and Bfile

DBMS_UTILITY: Wide range of functionalities. Check format_error_backtrace, format_call_stack, format_error_stack etc.

UTL_FILE: File handling.

DBMS_REDIFINITION: Online redefinition of tables. To partition an existing table etc

DBMS_WORKLOAD_REPOSITORY: Used in performance tuning to generate/drop snapshopts, to create/remove baselines, to change snapshot settings etc which can be further used in generating the AWR reports.

DBMS_SQL: Dynamic SQL support

DBMS_XPLAN: Format the output of explain plan.

 

 

 

 

read less