Find the best tutors and institutes for PL/SQL

Find Best PL/SQL Classes

Please select a Category.

Please select a Locality.

No matching category found.

No matching Locality found.

Outside India?

Search for topics

PL/SQL Updates

Ask a Question

Post a Lesson

All

All

Lessons

Discussion

Lesson Posted on 27/09/2017 IT Courses/PL/SQL

Pl SQL Cursors

Madhulika Nunna

I am 7 Plus years Experienced Oracle Apps Techno Functional Developer with four years of Training Experience....

To process an SQL statement, ORACLE needs to create an area of memory known as the context area; this will have the information needed to process the statement. This information includes the number of rows processed by the statement, a pointer to the parsed representation of the statement. In a... read more
  • To process an SQL statement, ORACLE needs to create an area of memory known as the context area; this will have the information needed to process the statement.
  • This information includes the number of rows processed by the statement, a pointer to the parsed representation of the statement.
  • In a query, the active set refers to the rows that will be returned.
  • A cursor is a handle, or pointer, to the context area.
  • Through the cursor, a PL/SQL program can control the context area and what happens to it as the statement is processed.
  • Two important features about the cursor are
  • 1.Cursors allow you to fetch and process rows returned by a SELECT statement, one row at a time.

  • There are two types of cursors:

  • 1.An IMPLICIT cursor is automatically declared by Oracle every time an SQL statement is executed. The user will not be aware of this happening and will not be able to control or process the information in an implicit cursor.

    2.An EXPLICIT cursor is defined by the program for any query that returns more than one row of data. That means the programmer has declared the cursor within the PL/SQL code block.

    2.A cursor is named so that it can be referenced.

  • Declaring a cursor defines the name of the cursor and associates it with a SELECT statement.

  • The first step is to Declare the Cursor with the following syntax:

  •   CURSOR c_cursor_name IS select statement

    • Cursor names follow the same rules of scope and visibility that apply to the PL/SQL identifiers.
    • Because the name of the cursor is a PL/SQL identifier, it must be declared before it is referenced.
    • Any valid select statement can be used to define a cursor, including joins and statements with the UNION or MINUS clause.
read less
Comments
Dislike Bookmark

Lesson Posted on 29/01/2017 IT Courses/Database Training IT Courses/Oracle Training/Oracle Database IT Courses/PL/SQL +1 IT Courses/Oracle Training/Oracle PL/SQL less

Introduction to Performance tuning

Gavisiddappa Yatnalli

Have 5 years of experience as plsql developer on various domains including but not restricted to Banking,...

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... read more

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
Comments
Dislike Bookmark

Lesson Posted on 09/01/2017 IT Courses/PL/SQL IT Courses/Database Training IT Courses/Oracle Training/Oracle Database +1 IT Courses/Oracle Training/Oracle PL/SQL less

Some Interesting and important built in packages in Oracle

Gavisiddappa Yatnalli

Have 5 years of experience as plsql developer on various domains including but not restricted to Banking,...

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... read more

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
Comments
Dislike Bookmark

Looking for PL/SQL Classes

Find best PL/SQL Classes in your locality on UrbanPro.

FIND NOW

Lesson Posted on 29/12/2016 IT Courses/Database Training IT Courses/Oracle Training/Oracle Database IT Courses/PL/SQL +1 IT Courses/Oracle Training/Oracle PL/SQL less

Starter on exception handling

Gavisiddappa Yatnalli

Have 5 years of experience as plsql developer on various domains including but not restricted to Banking,...

Just run these two blocks individually, observe the output and comment on what do you understand from it. SET SERVEROUTPUT ONDeclareCursor C is select * from emp;i c%rowtype;BeginFetch C into i;End; SET SERVEROUTPUT ONDeclareCursor C is select * from emp;i c%rowtype;BeginFetch C into i;ExceptionWHen... read more

Just run these two blocks individually, observe the output and comment on what do you understand from it.

SET SERVEROUTPUT ON
Declare
Cursor C is select * from emp;
i c%rowtype;
Begin
Fetch C into i;
End;

SET SERVEROUTPUT ON
Declare
Cursor C is select * from emp;
i c%rowtype;
Begin
Fetch C into i;
Exception
WHen others Then
DBMS_OUTPUT.put_line('Error MSG: '||Substr(SQLERRM,1,30)||' And Error Code: '||SQLCODE);
End;

And lastly, focus on this statement in the output- 'PL/SQL procedure successfully completed.'

 

read less
Comments
Dislike Bookmark

Lesson Posted on 28/12/2016 IT Courses/Database Training IT Courses/Oracle Training/Oracle Database IT Courses/PL/SQL +1 IT Courses/Oracle Training/Oracle PL/SQL less

SQL Subqueries - Used in various ways

Gavisiddappa Yatnalli

Have 5 years of experience as plsql developer on various domains including but not restricted to Banking,...

If you master the concepts of subqueries, you would gain a lot of flexibility in writing complex SQL queries. Let's not get into Correlated subqueries, let us see the different versions of nested subquery with some simple examples.I am attaching the EMP table data as a document in my profile, pls download... read more

If you master the concepts of subqueries, you would gain a lot of flexibility in writing complex SQL queries. Let's not get into Correlated subqueries, let us see the different versions of nested subquery with some simple examples.
I am attaching the EMP table data as a document in my profile, pls download it and have it inserted in your DB.

What is a Subquery?
A query within a query is Subquery.

For Eg:
Select * from EMP;

Empid Ename Sal Deptid
1 Mohit 50000 10
2 Vikas 40000 20
3 Naveen 30000 30
4 Pravin 20000 40
5 Rohit 10000 50
6 Gavi 60000 10
7 Ramesh 42000 20
8 Manav 33000 30
9 Manoj 27000 40
10 Nilesh 11000 50
11 Vinay 52000 10

To find out all the employees who work in the department of 'Nilesh'
Select * from emp where deptid in (Select deptid from emp where ename='Nilesh');

Further, maximum of 255 nested subquery levels are allowed in Oracle in WHERE clause.

Adding to that you can use subquery in HAVING clause, FROM Clause and SELECT clause also, just like in WHERE Clause.

Eg. for HAVING CLAUSE subquery:
To find out the departments whose department-wise average salary is more than the avg salary of the whole organization.
Select Deptid,count(*),avg(sal) from EMP group by Deptid having avg(sal)>(Select avg(sal) from emp);

Eg. for FROM CLAUSE subquery:
To find out what are the employees salary and minimum salary in their department.
Select Empid,Ename,e.Deptid, Sal, MinSal.min_sal from emp e, (Select deptid,min(sal) min_sal from emp group by deptid) MinSal
where e.deptid = minsal.deptid Order by e.deptid,e.sal

Another common example for FROM CLAUSE Subquery is to find out the 5 most well paid employees.
Select * FROM (Select * from EMP ORDER BY SAL DESC) WHERE Rownum<6

Eg. for SELECT CLAUSE subquery:
To find out the difference between an employee salary and his department avg salary for all the employees.

Select Empid,Ename,Deptid, Sal, Sal-(Select avg(sal) from emp where deptid=e.deptid) DIFF from emp e order by deptid;

The example queries used here can be written in a better and efficient way, but my purpose here is to make it easier to understand for the reader.
Also, we have used only one table. In real life scenarios, you would face lots of tables and more complex situations.
Another importan thing is that joins always work faster than subqueries. But it again depends on optimizer, if it is smart enough and generates the same execution plan for both the queries, you will get the same response time.

read less
Comments
Dislike Bookmark

Lesson Posted on 26/12/2016 IT Courses/Database Training IT Courses/Oracle Training/Oracle Database IT Courses/PL/SQL +1 IT Courses/Oracle Training/Oracle PL/SQL less

Exception Handling topics

Gavisiddappa Yatnalli

Have 5 years of experience as plsql developer on various domains including but not restricted to Banking,...

Here, I am only going to list the topics which one should consider to master Exception Handling. 1. Types of errors in Pl/sql- Compile time and Run time 2. How to read and understand the Compile time errors? 3. What are runtime erros? 4. What is basic block structure in plsql and what is Exception... read more

Here, I am only going to list the topics which one should consider to master Exception Handling.

1. Types of errors in Pl/sql- Compile time and Run time

2. How to read and understand the Compile time errors?

3. What are runtime erros?

4. What is basic block structure in plsql and what is Exception section in that?

5. Why is exception handling necessary? What are it's advantages?

6. What are the types of exceptions in Oracle plsql? What are Built in exceptions and their sub types?

7. What are User defined Exceptions and how they are different from Oracle named or unnamed exceptions?

8. What is the propogation path of exceptions? Cases of multiple nested blocks.

9. What happens if an exception is raised in the declaration section? What happens if that declaration section belongs to some inner block?

10. Similarly, what happens if an exception is raised in the exception block itself? What happens if that exception block is actually inside some inner block?

11. Can we reraise the exceptions in the exception block and pass it on to the outer block and how? or can we raise another exception inside the exception block?

12. What is When 'OTHERS' exception handler, why is it used, where is it used?

13. What is SQLCODE and SQLERRM and what are their uses?

14. What is EXCEPTION_INIT Pragma and why is it used?

15. What is RAISE_APPLICATION_ERROR? Why is it used?

Some tricky topics:-

16. How to EXCEPTION_INIT and RAISE_APPLICATION_ERROR to generate some meaningful error messages?

17. Can we start a new block inside the WHEN clause of exception block or an exception handler? What are the advantages of such block?

18. What is the beauty of a FOR loop when it comes to Exceptions? How to continue with rest of the iterations when exception occurs in of the iterations?

19. How do you find out the actual line no. of the occurence of an error programatically on rutime?

20. What are Bulk exceptions?

21. How do you handle the exceptions raised in Triggers?

And the list just goes on and on and on.

 

 

 

read less
Comments
Dislike Bookmark

Looking for PL/SQL Classes

Find best PL/SQL Classes in your locality on UrbanPro.

FIND NOW

Lesson Posted on 26/12/2016 IT Courses/Oracle Training/Oracle PL/SQL IT Courses/Oracle Training IT Courses/Database Training +1 IT Courses/PL/SQL less

How to read and understand the Compilation errors?

Gavisiddappa Yatnalli

Have 5 years of experience as plsql developer on various domains including but not restricted to Banking,...

Today, we will understand how to interpret the compile time erros and fix them. Don't confuse with runtime errors which is altogether a different subject called Exception Handling. Compilation erros are normally typing errors or lexical error/syntax errors. For Ex: 1. Misspelling a keyword/reserved... read more

Today, we will understand how to interpret the compile time erros and fix them. Don't confuse with runtime errors which is altogether a different subject called Exception Handling.

Compilation erros are normally typing errors or lexical error/syntax errors. 

For Ex: 1. Misspelling a keyword/reserved word or forgetting a keyword itself

           2. Forgetting to close a brace or a delimiter etc.

           3. Forgetting a semicolon.

Compiler would throw an error in such cases and this lesson is to teach you to understand those patterns of errors. Basically, there are 3 patterns as

1. Prior line

2. Current line

3. Declaration Error.

Let us start with examples.
Example 1:


SQL> BEGIN
2 dbms_output.put_line('Hello World!')
3 END;
4 /

Error report -
ORA-06550: line 3, column 1:
PLS-00103: Encountered the symbol "END" when expecting one of the following:

:= . ( % ;
The symbol ";" was substituted for "END" to continue.

Explanation: Prior line error points to an error in the prior statement line, which is mostly a missing semicolon error as in above example. observe the column no. which always says 1 for prior line error, which means beginning of the line or end of the prveious line.

Example 2:

   SQL> DECLARE
       2     a Number :=7;
       3     b Number :=5;
       4     c Number;
       5   BEGIN
       6     c := a b;
       7     dbms_output.put_line('The value of c : '||c);
       8   END;
       9      /

Error report -
ORA-06550: line 6, column 11:
PLS-00103: Encountered the symbol "B" when expecting one of the following:

. ( * @ % & = - + ; < / > at in is mod remainder not rem
<> or != or ~= >= <= <> and or like like2
like4 likec between || multiset member submultiset
The symbol "." was substituted for "B" to continue.

Explanation: Current line error points to the column of the error or one column after the error. This occurs normally because of missing lexical unit as in above example. Error is at line no. 6 and column 10.

If you are using SQL* Plus, you would also get an asterisk placed right below the column or below the one column right after the error. In case of prior line error, asterisk would be placed immediately below where the error occurs in a statement line.

Example 3:

 SQL> DECLARE
      2    a CHAR := 'AB';
      3  BEGIN
      4    dbms_output.put_line('a contains : '||a);
      5  END;
      6     /

DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 2

Explanation: When an error occurs in Declaration section, the above discussed logic doesn't apply. We get some less obvious error message as in the above example. Error line no. is 1, but that doesn't mean that error occurred before the declaration block.It tells you that error occurs in the declaration section and the last line in the error message points to the specific error line no. This output is from SQL* Plus, unlike other two examples and hence asterisk is placed.

read less
Comments
Dislike Bookmark

Lesson Posted on 25/12/2016 IT Courses/Database Training IT Courses/Oracle Training/Oracle Database IT Courses/PL/SQL +1 IT Courses/Oracle Training/Oracle PL/SQL less

Interview questions on Packages in Oracle

Gavisiddappa Yatnalli

Have 5 years of experience as plsql developer on various domains including but not restricted to Banking,...

Let me list few standard questions asked in the interviews on plsql package. 1. Why packages are used? What are it's advantages? 2. Name few bulin-in Oracle packages. 3. Can we have a function or procedure declared in Package specification and not defined in package body and why? 4. Can we have a... read more

Let me list few standard questions asked in the interviews on plsql package.

1. Why packages are used? What are it's advantages?

2. Name few bulin-in Oracle packages.

3. Can we have a function or procedure declared in Package specification and not defined in package body and why?

4. Can we have a function or procedure not declared in Package Specs and defined in Package body alone?

5. What are private components in Plsql packages?

6. Can we have only a Package specification and no body at all? If yes, under what circumstances? Such a Package specification would be compiled without errors?

7. Can we have Package body alone and no Specs? Will it be compiled without errors?

8. What is overloading and how is it accomplished in Oracle?

9. What are restrictions on overloading?

10. What are differences between and advantages/disadvantages of stand alone and packaged procedures/Functions?

11. What is forward declaration?

12. What are the disadvantages of Pacakges?

13. What is session varibale and how is it defined in package?

14. How Packages improve or degrade performance?

15. What is pragma serially_reusable and why is it used?

These questions actually cover the entire lesson on Plsql Packages.

 

 

 

read less
Comments
Dislike Bookmark

Lesson Posted on 21/12/2016 IT Courses/Oracle Training IT Courses/Database Training IT Courses/Oracle Training/Oracle Database +2 IT Courses/PL/SQL IT Courses/Oracle Training/Oracle PL/SQL less

Oracle Architecture - Introduction

Gavisiddappa Yatnalli

Have 5 years of experience as plsql developer on various domains including but not restricted to Banking,...

As a Oracle developer or a DBA, you should know the in & out of it’s Architecture. Let’s keep it simple first and explore later. Oracle is a software or a program, so like any other program, it should be installed on your OS using setup file and then run to get your things done by it.... read more

As a Oracle developer or a DBA, you should know the in & out of it’s Architecture.

Let’s keep it simple first and explore later. Oracle is a software or a program, so like any other program, it should be installed on your OS using setup file and then run to get  your things done by it. When I say run, it means to load into memory (RAM).  So obviously, it should be made of these components, as most of the programs do,

  1. Memory – Every program/software you run, needs some part of RAM to be allocated. So does Oracle. So Memory in this context is nothing but a part of RAM allocated to Oracle.
  2. Processes – Oracle is a program by itself. (Taken this from google) “In computing, a process is an instance of a computer program that is being executed. It contains the program code and its current activity. Depending on the operating system (OS), a process may be made up of multiple threads of execution that execute instructions concurrently”. Every program has related processes and so does Oracle.
  3. Storage/Data  – Associated data. Like if you have a game installed, the data should be saved on hard drive as you go on playing the game, so that when you close the game  and start again later some time, you should be able to resume from where you left. Makes sense.

read less
Comments
Dislike Bookmark

Looking for PL/SQL Classes

Find best PL/SQL Classes in your locality on UrbanPro.

FIND NOW

Lesson Posted on 21/12/2016 IT Courses/Oracle Training/Oracle PL/SQL IT Courses/PL/SQL IT Courses/Database Training +1 IT Courses/Oracle Training/Oracle Database less

Common mistake even experienced programmers make in Plsql.

Gavisiddappa Yatnalli

Have 5 years of experience as plsql developer on various domains including but not restricted to Banking,...

Hope, you are aware of Procedures in Plsql. Let us discuss one simple thing which we should always remember while writing the datatype of a parameter for a procedure or a function. Eg: Create or replace procedure proc1 (Name IN Varchar2(10)) AS. What's wrong in this? Any guesses? You should never... read more

Hope, you are aware of Procedures in Plsql. Let us discuss one simple thing which we should always remember while writing the datatype of a parameter for a procedure or a function.

Eg: Create or replace procedure proc1 (Name IN Varchar2(10)) AS.

What's wrong in this? Any guesses?

You should never write Varchar2(10) or Number(2) etc for parameters while creating procedure or function. It's an error.

Just write it like,

Create or replace procedure proc1 (Name IN Varchar2, Sal Number)  AS.

Even for the return type of a function.

Inside the declaration section of procedure or function or anonymous block, you should mention the size as Varchar2(20) or Number(3) or so. Otherwise, default size would be assumed depending on the type.

read less
Comments
Dislike Bookmark

About UrbanPro

UrbanPro.com helps you to connect with the best PL/SQL Classes in India. Post Your Requirement today and get connected.

Overview

Lessons 11

Total Shares  

+ Follow 3,654 Followers

Top Contributors

Connect with Expert Tutors & Institutes for PL/SQL

x

Ask a Question

Please enter your Question

Please select a Tag

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 25 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 6.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more