Home » SQL & PL/SQL » SQL & PL/SQL » package
package [message #197517] Wed, 11 October 2006 10:28 Go to next message
satyen1412
Messages: 4
Registered: October 2006
Junior Member
when you remove the package specification then is the package body(using constants declared in specification) invalidated or removed ?
Re: package [message #197518 is a reply to message #197517] Wed, 11 October 2006 10:33 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I don't think there is a separate command to remove a package specification. All you can do is "drop package xxx", which drops both spec and body.
Re: package [message #197523 is a reply to message #197518] Wed, 11 October 2006 11:00 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
And this required an expert to answer? Could have not just tried to do it yourself and see what happens?
Re: package [message #199211 is a reply to message #197523] Sat, 21 October 2006 05:09 Go to previous messageGo to next message
satyen1412
Messages: 4
Registered: October 2006
Junior Member
Sorry friend , I was preparing for IZO-147 exam and was under stress. The doubt arose because one of the dumps was giving wrong answer as correct. It was marking correct the answer ' The package specification and Package Body always need to be removed separately.' I knew it was incorrect but needed to be assured. The correct position is
- U can drop both the package specs & body by a single command and
- U can drop the package body only by a single command.
- but there is no command that drops the package specs and retains the body.
Thanks anyway.
Re: package [message #199218 is a reply to message #199211] Sat, 21 October 2006 12:28 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

SQL> CREATE PACKAGE emp_stuff AS
  2     CURSOR c1 RETURN employees%ROWTYPE;  -- declare cursor spec
  3  END emp_stuff;
  4  /

Package created.

SQL> CREATE PACKAGE BODY emp_stuff AS
  2     CURSOR c1 RETURN employees%ROWTYPE IS
  3        SELECT * FROM employees WHERE salary > 2500;  -- define cursor body
  4  END emp_stuff;
  5  /

Package body created.

SQL> drop package body emp_stuff;

Package body dropped.

SQL> drop package emp_stuff;

Package dropped.

SQL> drop package emp_stuff;
drop package emp_stuff
*
ERROR at line 1:
ORA-04043: object EMP_STUFF does not exist


SQL> ed
Wrote file afiedt.buf

  1  CREATE PACKAGE emp_stuff AS
  2     CURSOR c1 RETURN employees%ROWTYPE;  -- declare cursor spec
  3  END emp_stuff;
  4  /
  5  CREATE PACKAGE BODY emp_stuff AS
  6     CURSOR c1 RETURN employees%ROWTYPE IS
  7        SELECT * FROM employees WHERE salary > 2500;  -- define cursor body
  8* END emp_stuff;
SQL> /

Warning: Package created with compilation errors.

SQL> ed
Wrote file afiedt.buf

SP2-0223: No lines in SQL buffer.
SQL> CREATE PACKAGE emp_stuff AS
  2     CURSOR c1 RETURN employees%ROWTYPE;  -- declare cursor spec
  3  END emp_stuff;
  4  /


SQL> CREATE PACKAGE BODY emp_stuff AS
  2     CURSOR c1 RETURN employees%ROWTYPE IS
  3        SELECT * FROM employees WHERE salary > 2500;  -- define cursor body
  4  END emp_stuff;
  5  /

Warning: Package Body created with compilation errors.

SQL> drop package emp_stuff;

Package dropped.

SQL> CREATE PACKAGE emp_stuff AS
  2     CURSOR c1 RETURN employees%ROWTYPE;  -- declare cursor spec
  3  END emp_stuff;
  4  /

Package created.

SQL> CREATE PACKAGE BODY emp_stuff AS
  2     CURSOR c1 RETURN employees%ROWTYPE IS
  3        SELECT * FROM employees WHERE salary > 2500;  -- define cursor body
  4  END emp_stuff;
  5  /

Package body created.

SQL> drop package emp_stuff;

Package dropped.

SQL>

[Updated on: Sat, 21 October 2006 12:28]

Report message to a moderator

Re: package [message #199263 is a reply to message #199218] Sun, 22 October 2006 23:43 Go to previous messageGo to next message
romi
Messages: 67
Registered: October 2006
Member


We can drop package with command DROP PACKAGE PACKAGE_NAME.In this condition both package specification and package body will remove.

But with the help of this command DROP PACKAGE BODY PACKAGE_NAME,we can remove only package body.package specification will be remain in this condition.
Re: package [message #199266 is a reply to message #199263] Mon, 23 October 2006 00:29 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
I think its because oracle uses the package spec as the base structure of your package, so if you create a function/procedure in body but not in spec it prompts for an error, because of the discrepancy.
Previous Topic: Char to Numeric Functionality
Next Topic: how to get newly inserted row (or rowid) in oracle?
Goto Forum:
  


Current Time: Sat Dec 10 13:00:57 CST 2016

Total time taken to generate the page: 0.08457 seconds