Home » SQL & PL/SQL » SQL & PL/SQL » Help with Package concepts
Help with Package concepts [message #229452] Sun, 08 April 2007 03:48 Go to next message
ramaswamynanda
Messages: 6
Registered: April 2007
Junior Member
Hello,

I am studying to give the 1ZO-147 exam and I am not able to find proper information on the following topics. A lot of sites meander around the topics but do not provide any useful information.

Package concepts -
Use Forward Referencing
Describe errors with mutually referential subprograms
Initialize variables with a one-time-only procedure
Identify persistent states in package variables and cursors
Invoke packaged functions from SQL
Use PL/SQL tables and records in Packages

Any links or explanations will be very welcome.

Thanks and regards
Nanda Ramaswamy
Re: Help with Package concepts [message #229458 is a reply to message #229452] Sun, 08 April 2007 06:46 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14261/packages.htm#i6016
Re: Help with Package concepts [message #229467 is a reply to message #229458] Sun, 08 April 2007 11:30 Go to previous messageGo to next message
ramaswamynanda
Messages: 6
Registered: April 2007
Junior Member
Hello Mahesh,

thank you for your prompt reply. I had already gone thru this page on this site. The site is excellent, but it does not cover any of the above mentioned topics other than a brief mention of a one-time only initialization subprogram.

Still looking for help ):

Regards
Nanda
Re: Help with Package concepts [message #229600 is a reply to message #229467] Mon, 09 April 2007 06:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You must be using some sort of course or book or whatever to study for your exam. This should all be in there.
If it is not, get yourself another book/course/whatever.
And no, I do not know of a good book for that particular exam.

What good will your certification be when you not only don't know some of the basics of pl/sql programming, but you are also unable to find info about them?
Re: Help with Package concepts [message #229673 is a reply to message #229600] Mon, 09 April 2007 11:35 Go to previous messageGo to next message
ramaswamynanda
Messages: 6
Registered: April 2007
Junior Member
Hello Frank,

Thanks for taking the time to reply and also for your encouragement. I hope your leadership qualities have a similar positive effect on the team working under you.

Regards
Nanda
Re: Help with Package concepts [message #229677 is a reply to message #229673] Mon, 09 April 2007 12:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I don't have or want or need a team working for me.
I hope you don't confuse a a lack of resistance with good leadership.
Re: Help with Package concepts [message #229685 is a reply to message #229452] Mon, 09 April 2007 13:13 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
ramaswamynanda wrote on Sun, 08 April 2007 09:48
Package concepts -
Use Forward Referencing
Describe errors with mutually referential subprograms
Initialize variables with a one-time-only procedure
Identify persistent states in package variables and cursors
Invoke packaged functions from SQL
Use PL/SQL tables and records in Packages

Any links or explanations will be very welcome.


There are six items in that list. If you can be more specific about what you are having trouble with then you will have more chance of getting help.

For example, Forward Referencing is the technique of providing an empty procedure/function signature early on in the code, in advance of the full implementation which you provide later on, which allows more flexibility in how you arrange your code. (Otherwise you have to make sure that all procedures/functions are already defined in full before you refer to them in any code.) I try to avoid it personally, but it's there if you need it. An advantage of packages over standalone procedures is that they make this technique possible.

Similarly, any variables defined in standalone procedures have to be set each time the procedure is called, while the package approach allows variables to be defined at the package level and defined only once. This is what the points about "initializing variables" and "persistent state" seem to be about.

I'm not sure what "mutually referential subprograms" are, or how they "describe errors" - these seem to be phrases used only in OCA material. Presumably they refer to exception handling.

I would have thought the last two points were fairly self-explanatory, except for the deprecated term "PL/SQL Tables". I think they meant "PL/SQL Collections".
Re: Help with Package concepts [message #230031 is a reply to message #229685] Tue, 10 April 2007 12:44 Go to previous messageGo to next message
ramaswamynanda
Messages: 6
Registered: April 2007
Junior Member
Hello william,

Thanks for replying. The list I posted is copied from the list of items for the exam, and you are right, it is confusing. Whie searching for help, i get a variety of sites that give definitions, but rarely examples.

I found some help for the topics concerned in the PL/SQL User's Guide and Reference.

1. PL/sql tables does mean collections, and I found some material on that
2. I am also ok with the one-time only proc.
3. I have no examples on forward referencing. I guess the mutual errors means the errors that crop up when the subprograms are referenced before they are declared.
4. Also, no examples anywhere for calling udf's from sql. there is the thory and the restrictions list, but no examples
5. I guess persistent states means variables declared in the pkg spec, as they are available to all who all the pakages and they persist for a user session.

If you could direct me to any sites or links that illustrate any of the above concepts, I would be very grateful. This is my first post on this site and I will try to be more clear in the future.

Thanks and Regards
Nanda

Re: Help with Package concepts [message #230080 is a reply to message #230031] Tue, 10 April 2007 16:38 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Calling user-defined functions from SQL:

SQL> CREATE OR REPLACE PACKAGE pkgtest AS
  2      FUNCTION func
  3          RETURN VARCHAR2;
  4  END pkgtest;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY pkgtest AS
  2      FUNCTION func
  3          RETURN VARCHAR2
  4      IS
  5      BEGIN
  6          RETURN 'Hello, ' || USER;
  7      END func;
  8  END pkgtest;
  9  /

Package body created.

SQL> SELECT pkgtest.func FROM dual;

FUNC
----------------------------------------------------------------------------------------------------
Hello, WILLIAM

1 row selected.

SQL> 


Example of forward declaration adapted from the documentation (the documentation tends to use the term "forward declaration" rather than "forward referencing"):

DECLARE
   PROCEDURE proc1(number1 NUMBER);  -- forward declaration

   PROCEDURE proc2(number2 NUMBER) IS
   BEGIN
       proc1(number2);
   END;

   PROCEDURE proc1(number1 NUMBER) IS
   BEGIN
      proc2 (number1);  
   END;
BEGIN
   NULL;
END;
/

The code is not particularly meaningful in itself, but the idea is that proc2 uses proc1 before proc1 has been fully defined. This technique will almost certainly never be useful.

Quote:
I guess the mutual errors means the errors that crop up when the subprograms are referenced before they are declared.

No! When course material like this refers to anything around the subject of errors, it always means PL/SQL exception handling, and the writers always have in mind some over-elaborate and ultimately unworkable system of named exceptions.
Re: Help with Package concepts [message #230345 is a reply to message #230080] Wed, 11 April 2007 11:08 Go to previous message
ramaswamynanda
Messages: 6
Registered: April 2007
Junior Member
Hey William,

thanks a lot for taking the effort to help.

Best Regards
Nanda
Previous Topic: Count row
Next Topic: Utl_File error
Goto Forum:
  


Current Time: Sat Dec 03 14:00:09 CST 2016

Total time taken to generate the page: 0.06076 seconds