Learn Oracle PL/SQL from the Best Tutors
Search in
Lesson Posted on 05/01/2020 Learn Oracle PL/SQL
Interview questions based on "level", a pseudocolumn
Gavi Yatnalli
It's going to be a multidimensional experience when you attend the classes. Take a chance and enjoy the ride.
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 lessLesson Posted on 05/01/2020 Learn Oracle PL/SQL
How does a SQL Query Execute inside Oracle? What are the steps followed in the background?
Gavi Yatnalli
It's going to be a multidimensional experience when you attend the classes. Take a chance and enjoy the ride.
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 lessAnswered on 11/10/2018 Learn Oracle PL/SQL
V.R. Naga Pawan Yallapragada
Professional Faculty with 22 Years of teaching experience
Learn Oracle PL/SQL from the Best Tutors
Lesson Posted on 30/06/2017 Learn Oracle PL/SQL
How To Create The Java Souce In Oracle PLSQL?
Murali Krishna
Having 13+ years of experience in oracle plsql,unix,informatica ,Core java,JDBC,JSP,Servlets and JAVA script
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
Answered on 17/03/2017 Learn Oracle PL/SQL
Manoj Kumar Vishwakarma
MS SQL SERVER DBA Trainer
Lesson Posted on 22/02/2017 Learn Oracle PL/SQL
How To Return a Result Set from a Stored Procedure
Sagar Sahoo
I am currently working as Oracle PL/SQL Developer and designated as IT Analyst. I am having experience...
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 lessLearn Oracle PL/SQL from the Best Tutors
Lesson Posted on 13/02/2017 Learn Oracle PL/SQL
Amazon written test for experience
Suresh
I have more than four years of professional experience in development and maintenance in Hadoop and Hadoop...
For the below written test answer I defined with "*" at the end of the options
NULL is
The basic function of ____________ is to search for files(or other units of text) that contain a pattern.
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");
}
}
What does the literal “$?” mean in Shell script?
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?
What will be the output of the following query? SELECT DECODE(TRANSLATE('A','1234567890','1111111111'),'1','YES', 'NO' ) from DUAL;
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);
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
A train running at the speed of 60 km/hr crosses a pole in 9 seconds. What is the length of the train?
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;
}
What will be the output of the following query? SELECT REPLACE(TRANSLATE(LTRIM(RTRIM('!! ATHEN !!','!'), '!'), 'AN', '*'),'','TROUBLE') FROM DUAL;
How will you list all the files within a directory including hidden files aka (.) dot files?
How to make any script file executable?
How to find current running processes in Unix server?
Bottom of Form
The least significant bit of the binary number, which is equivalent to any odd decimal number is
Which OSI layer is responsible for Encryption and Decryption?
Which of the following memories has the shortest access time?
Expand the following abbreviation – USB?
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()); }
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;
}
What is the difference between linux file system and windows file system?
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.
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
How would you sort a file called shopping on column 3?
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);
}
What is 2NF in normalization?
A train running at the speed of 60 km/hr crosses a pole in 9 seconds. What is the length of the train?
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;
}
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;
}
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');
Size of IPv6 address is
What is the probability of getting a sum 9 from two throws of a dice?
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);
}
Which command puts a script to sleep untill a signal is received?
read less
Lesson Posted on 29/01/2017 Learn Oracle PL/SQL
Introduction to Performance tuning
Gavi Yatnalli
It's going to be a multidimensional experience when you attend the classes. Take a chance and enjoy the ride.
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
Lesson Posted on 09/01/2017 Learn Oracle PL/SQL
Some Interesting and important built in packages in Oracle
Gavi Yatnalli
It's going to be a multidimensional experience when you attend the classes. Take a chance and enjoy the ride.
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
Learn Oracle PL/SQL from the Best Tutors
Answered on 20/12/2016 Learn Oracle PL/SQL
Manoj Kumar Vishwakarma
MS SQL SERVER DBA Trainer
UrbanPro.com helps you to connect with the best Oracle PL/SQL in India. Post Your Requirement today and get connected.
Ask a Question
The best tutors for Oracle PL/SQL Classes are on UrbanPro
The best Tutors for Oracle PL/SQL Classes are on UrbanPro