Home » SQL & PL/SQL » SQL & PL/SQL » oracle pl/sql procedure (oracle 10g)
oracle pl/sql procedure [message #612088] Fri, 11 April 2014 15:59 Go to next message
karthik.m1988
Messages: 4
Registered: April 2014
Location: india
Junior Member
Hi,

am new to oracle,still learning.in the down mentioned procedure cant able find what is the mistake

table1 'employees' having column (employee_id,salary,department_id)
table2 'departments' having column (department_id,manager_id)

my procedure want to that take department ID and changes the manager ID for the department to the employee in the department with highest salary.

Am tried with this code:

create or replace procedure upd_man(dep_id employees.department_id%type)
as
emp employees_employee_id%type;
begin
select employee_id into emp from employees
where department_id = dep_id
and
salary = (select max(salary) from employees where department_id = dep_id);
update departments set manager_id = emp where department_id = dep_id;
end;

Am getting error of this:

ERROR at line 8: PL/SQL: SQL Statement ignored

6. where department_id = dep_id and
7. salary = (select max(salary) from employees where department_id = dep_id);
8. update departments set manager_id = emp where department_id = dep_id;
9. end;


this may be the issue not at your level,but please help me know the solution.

thank in advance Smile
Re: oracle pl/sql procedure [message #612089 is a reply to message #612088] Fri, 11 April 2014 17:20 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/

>update departments set manager_id = emp where department_id = dep_id;
in the SQL statement above, Oracle does not know what "DEP_ID" is; since it does not exist within DEPARTMENTS table.
Re: oracle pl/sql procedure [message #612092 is a reply to message #612089] Fri, 11 April 2014 17:50 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
dep_id is a parameter.

The declaration of emp is wrong.
Re: oracle pl/sql procedure [message #612108 is a reply to message #612088] Sat, 12 April 2014 02:42 Go to previous messageGo to next message
John Watson
Messages: 4611
Registered: January 2010
Location: Global Village
Senior Member
Please get into the habit posting exactly what you see, using copy/paste and code tags. Like this:
orclz> conn hr/hr
Connected.
orclz> create or replace procedure upd_man(dep_id employees.department_id%type)
  2  as
  3  emp employees_employee_id%type;
  4  begin
  5  select employee_id into emp from employees
  6  where department_id = dep_id
  7  and
  8  salary = (select max(salary) from employees where department_id = dep_id);
  9  update departments set manager_id = emp where department_id = dep_id;
 10  end;
 11  /

Warning: Procedure created with compilation errors.

orclz> sho err
Errors for PROCEDURE UPD_MAN:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/5      PL/SQL: Item ignored
3/5      PLS-00201: identifier 'EMPLOYEES_EMPLOYEE_ID' must be declared
5/1      PL/SQL: SQL Statement ignored
5/25     PLS-00320: the declaration of the type of this expression is
         incomplete or malformed

5/29     PL/SQL: ORA-00904: : invalid identifier
9/1      PL/SQL: SQL Statement ignored
9/37     PL/SQL: ORA-00904: "EMP": invalid identifier
9/37     PLS-00320: the declaration of the type of this expression is
         incomplete or malformed

orclz>
Fix your typo on line 3, and it compiles. Is this a college homework assignment? If so, I would write it a function, not procedure, and return the new manager employee_id. THat might get higher marks.
Re: oracle pl/sql procedure [message #612640 is a reply to message #612108] Wed, 23 April 2014 00:44 Go to previous messageGo to next message
VamshiRaj
Messages: 1
Registered: April 2014
Location: chennai
Junior Member
can You verify the blow code ...where has it smiley icon

create or replace procedure upd_man(dep_id employees.department_id%type)
2 as
3 emp employees.employee_id%type;-- Mad
4 begin
5 select employee_id into emp from employees
6 where department_id = dep_id
7 and
8 salary = (select max(salary) from employees where department_id = dep_id);
9 update departments set manager_id = emp where department_id = dep_id;
10 end;
11 /
Re: oracle pl/sql procedure [message #612641 is a reply to message #612640] Wed, 23 April 2014 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 59293
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Does it compile or not?
Please read How to use [code] tags and make your code easier to read.

Re: oracle pl/sql procedure [message #612643 is a reply to message #612641] Wed, 23 April 2014 02:01 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
hi Vamshi,

Please use COMMIT after update statement otherwise you can't see the updated result for departments table.

Re: oracle pl/sql procedure [message #612644 is a reply to message #612643] Wed, 23 April 2014 02:07 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
hi,

Please do the proper indentation,so it will easy for you to understand the code.Please find the following code :


CREATE OR REPLACE PROCEDURE UPD_MAN(DEP_ID EMPLOYEES.DEPARTMENT_ID%TYPE)
AS

    emp employees.employee_id%TYPE;

BEGIN

  BEGIN
  
     SELECT employee_id 
     INTO   emp 
     FROM   employees
     WHERE  department_id = dep_id
     AND    salary = (  SELECT    MAX(salary) 
                        FROM      employees 
                        WHERE     department_id = dep_id
                     );
                     
  EXCEPTION
  WHEN no_data_found then 
  
     NULL;
  END;
  
     UPDATE departments 
     SET    manager_id = emp 
     WHERE  department_id = dep_id;
 
 COMMIT;
 
END;

Re: oracle pl/sql procedure [message #612655 is a reply to message #612643] Wed, 23 April 2014 02:44 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
msol25 wrote on Wed, 23 April 2014 08:01
hi Vamshi,

Please use COMMIT after update statement otherwise you can't see the updated result for departments table.



That's not good advice. Stored procedures should not contain commit. The top level program that calls the procedure should determine when a commit is issued. If a commit is present then the procedure can't form part of a larger transaction.
Re: oracle pl/sql procedure [message #612656 is a reply to message #612655] Wed, 23 April 2014 02:46 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
The procedure can be written as a single update statement, no need for a separate select.
But you need to consider what happens if two employees have the max salary.
Re: oracle pl/sql procedure [message #612660 is a reply to message #612644] Wed, 23 April 2014 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 59293
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

EXCEPTION
  WHEN no_data_found then 
  
     NULL;
  END;


What's the purpose of this?
What do you think you will update after ignoring this exception?

And do NOT commit inside a procedure, only the caller knows if the wok of the procedure have to be or not to be commit or rolled back, NOT the procedure itself.

Re: oracle pl/sql procedure [message #612665 is a reply to message #612660] Wed, 23 April 2014 02:53 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
hi Michel,

Yes i also know,but he is new to oracle,so i have given advice for compiling pl/sql successfully.I know we should
not use "null" in exception and we should always insert result of SQLCODE AND SQLERRM in Error table only.
Re: oracle pl/sql procedure [message #612667 is a reply to message #612665] Wed, 23 April 2014 02:57 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
Null can be acceptable in no_data_found handlers if the select is looking for optional data, but that isn't the case here.
Re: oracle pl/sql procedure [message #612668 is a reply to message #612667] Wed, 23 April 2014 03:00 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Yes Cookie,

You are correct,but my aim was that op's will not get stuck in first attempt.
Re: oracle pl/sql procedure [message #612669 is a reply to message #612668] Wed, 23 April 2014 03:11 Go to previous messageGo to next message
Michel Cadot
Messages: 59293
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Getting an error (above all an execution error) is not being stuck, it is being involved.
First let raise the exceptions, all of them, then trap those you know what to do with.
And in this case, I doubt NO_DATA_FOUND exception should be trapped, above all at this place.

Re: oracle pl/sql procedure [message #612670 is a reply to message #612669] Wed, 23 April 2014 03:15 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
In this case i hope, if emp column is having null value then control will go into NO_DATA_FOUND exception and will get
ignored that case
Re: oracle pl/sql procedure [message #612678 is a reply to message #612670] Wed, 23 April 2014 04:54 Go to previous messageGo to next message
Michel Cadot
Messages: 59293
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As I said, it is misplaced, exception handler should then be at the end of the procedure, you would surely not want to update anything if nothing is found in the SELECT, won't you?

Re: oracle pl/sql procedure [message #612681 is a reply to message #612678] Wed, 23 April 2014 05:08 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Hi Michel,

In this specific case if exception handler will be at end or after select statement, there will be no impact.
Re: oracle pl/sql procedure [message #612682 is a reply to message #612681] Wed, 23 April 2014 05:16 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
There's two cases in which the select will throw that error:
1) The dept doesn't exist - in which case the update isn't going to update anything.
2) The dept doesn't have any employees - in which case the dept manager_id is going to get updated to null, which is arguably the correct action.

Moving the exception handler to end will have no effect in case 1 (ok it won't even try to run the update, but the resulting effect on data (none) will be the same). It'll make a difference to case 2.

Personally in case 1 I'd want it to throw an error back to say the dept doesn't exist. I'd ditch the exception handler as it doesn't allow you to distinguish between the two and do this instead:
BEGIN

  UPDATE departments 
  SET    manager_id = (SELECT employee_id 
                       FROM   employees
                       WHERE  department_id = dep_id
                       AND    salary = (SELECT    MAX(salary) 
                                        FROM      employees 
                                        WHERE     department_id = dep_id
                                       )
                      )
  WHERE  department_id = dep_id;
  
  IF sql%rowcount = 0 THEN
  
    raise_application_error(-20001, 'Dept '||dep_id||' does not exist.');
    
  END IF;
  
END;
Re: oracle pl/sql procedure [message #612689 is a reply to message #612682] Wed, 23 April 2014 05:42 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Hi Cookie,

Case 2 is the valid case,if no employee exist for that department(In case of new Department opened).If we'll handle exception at end then we are not able to update the department table correctly.
Re: oracle pl/sql procedure [message #612691 is a reply to message #612689] Wed, 23 April 2014 05:45 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
That was my point, though if it's a brand new department it shouldn't need updating anyway.
Re: oracle pl/sql procedure [message #612694 is a reply to message #612691] Wed, 23 April 2014 05:58 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
hi Cookie,

So,We can say to handle exception at end is not always good.We should handle exceptions on individual query level as well as at the end.
Re: oracle pl/sql procedure [message #612698 is a reply to message #612694] Wed, 23 April 2014 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 59293
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is your specification, model and data that define where, when and how you need to handle exceptions.

I remember Steven Feuerstein's article containing something closed to this:

Llewellyn suggests that we group all exceptions into three categories: deliberate, unfortunate, and unexpected.

In deliberate exceptions, the code deliberately raises an exception as part of its normal behavior. UTL_FILE.GET_LINE, which raises NO_DATA_FOUND when you read past the end of file, is a perfect example of a program that raises a deliberate exception.

Unfortunate exceptions are those where an exception has been raised that may not constitute an error in your application logic. It might, for example, simply be a different data condition. NO_DATA_FOUND, raised by a SELECT INTO, is an unfortunate exception.

A "hard error" that was raised, that you did not expect, and that may indicate a serious problem in your application is an unexpected error. TOO_MANY_ROWS is a classic unexpected error; it indicates that you have duplicate values for a primary key or unique index.

Before you start building your next application, decide on a strategy for handling exceptions of these three types. Then, when you run into a particular exception, figure out which category it falls into and take the appropriate action. Here are the guidelines I follow for these three exception types:

Deliberate. Redesign your program so that you can avoid placing application logic in the exception section. The code you'll need to write to work around a deliberate exception will, of course, vary for each exception (and the circumstances that raise it).

Unexpected. The guideline for handling unexpected exceptions is straightforward. In general, you should log the error, along with any application-specific information that will help you understand what caused it. And then you should reraise the error to stop the outer block from continuing to execute. Avoid hard-coding INSERT statements into your log table, and instead rely on a single, reusable, and generic error management package that will take care of all the administrative details for you.

Unfortunate. Now let's discuss what to do about unfortunate exceptions such as NO_DATA_FOUND. As with the deliberate exception, the general rule is to avoid having to put application logic into the exception section. The way to do that with an unfortunate exception is to make it possible for the programmer to choose whether an exception should be raised.


I don't completely agree with all the points but this is a good guideline.

Re: oracle pl/sql procedure [message #612699 is a reply to message #612698] Wed, 23 April 2014 07:05 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Dear Michel,

This is the part of "Unfortunate exceptions",Because you can't rely what changes will be done at organization level and what will be the impact on data.We hope our code should handle all the valid cases.So,i hope if we are adding new Department and there is not having any employee details then there is no mean we have to ignore data as NULL for employee details for Department table updation.

So,I hope in this case we should add exception for SELECT statement and at the end of Procedure.
Re: oracle pl/sql procedure [message #612703 is a reply to message #612699] Wed, 23 April 2014 07:44 Go to previous message
Michel Cadot
Messages: 59293
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The type of exception does definitively not depend on us (the developer) but on the specification. The project manager/designer defines the type of the exception by defining/designing (or not) if and what should be done with that exception.
If the designer (who may be wrong and we can (have to) point/ask him about it) does not mention this exception then it is an unexpected error otherwise it is an unfortunate one. And maybe the designer chooses to not handle this exception here (and treat it as an unexpected one for this procedure) but to handle it in the caller procedure (and this is one of the reason why "commit" or "rollback" should not be part of the procedure without speaking about bigger transaction cookiemonster already mentioned).

Previous Topic: Job not being executed on time
Next Topic: Unable to display contents of CHARARR;
Goto Forum:
  


Current Time: Thu Oct 02 01:36:26 CDT 2014

Total time taken to generate the page: 0.05748 seconds