Home » SQL & PL/SQL » SQL & PL/SQL » Package inquiry...
Package inquiry... [message #390034] Wed, 04 March 2009 12:10 Go to next message
WarrenMedernach
Messages: 4
Registered: March 2009
Location: Canada
Junior Member
Hello all,
I'm familiar with Stored Procedures and Functions, and I'm trying to get up-to-speed on Packages.

Can a Package reference an external Procedure/Function?
IOW, if I already have a Procedure/Function defined, can it be called from the Package?

Or, is the idea of Packages to have everything encapsulated within the Package, and all Procedures/Functions should be defined within the Package in the Package Body?

Thanks so much
Warren M
Re: Package inquiry... [message #390036 is a reply to message #390034] Wed, 04 March 2009 12:13 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm
Re: Package inquiry... [message #390037 is a reply to message #390034] Wed, 04 March 2009 12:17 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Procedures/functions in packages can do anything stand-alone procedures/functions can do.

You can do things with packaged procedures/functions you can't do with stand alone ones - look up overloading.

Also have a read of this ask tom thread:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7452431376537

Quote:

is the idea of Packages to have everything encapsulated within the Package, and all Procedures/Functions should be defined within the Package in the Package Body?



That's generally regarded as good practice, yes.
Re: Package inquiry... [message #390039 is a reply to message #390037] Wed, 04 March 2009 12:23 Go to previous messageGo to next message
WarrenMedernach
Messages: 4
Registered: March 2009
Location: Canada
Junior Member
Perfect!
Thanks so much for the speedy response cookiemonster.

Warren
Re: Package inquiry... [message #390046 is a reply to message #390039] Wed, 04 March 2009 13:29 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
be extra careful in your package to prefix calls to procs/functions within the package, by the package name, else if you have a standalone proc/function with the same name, I think it generally takes preference.
...
-- use this inside your pkg
X := my_pkg.my_func;
-- rather than this
X := my_func;


Only publish the procs, functions, global variables in the package spec that you want to expose (don't simply publish all unless needed).
Re: Package inquiry... [message #390048 is a reply to message #390034] Wed, 04 March 2009 14:22 Go to previous messageGo to next message
WarrenMedernach
Messages: 4
Registered: March 2009
Location: Canada
Junior Member
Well, I think I'm missing something and need some further clarification...

My idea of using a package was to pass it a couple parameters to handle all of the 'query processing' on the backend vs. building the queries from an asp.net app.

What I need is a package that accepts 2 inputs. These inputs are used to run a couple functions to retrieve some data, and then that data is used to build the final sql statement that I need to return a cursor of the records I want.

As an example, here is what I have:
TableA, TableB, Table? (name unknown at start)
InputParam1, InputParam2

I need to take InputParam1 and query TableA to retrieve the actual tablename (Table?) for the final query. (MYTABLE)

Then, I need to take InputParam1 and query TableB to get a list of fields I need in the final query. (F1, F2, ..., F20)

Finally, I need to build the following query and return the cursor:
SELECT <F1, F2, ..., F20> FROM <MYTABLE> WHERE somefield IN <InputParam2>

Is this what a package can be used for?

Any info/kicks in the right direction would be greatly appreciated.

FWIW, I already have created seperate functions/procedures (not in a Package) to accomplish the above scenario, so I guess I'm just struggling with how to put it all together...

Thanks!
Warren M
Re: Package inquiry... [message #390049 is a reply to message #390034] Wed, 04 March 2009 14:28 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>I already have created seperate functions/procedures (not in a Package) to accomplish the above scenario,
But you decided we would benefit from not seeing a solution.

Here I am handing a loaded gun to a child.
EXECUTE IMMEDIATE will allow you to build what you desire; a non-scalable application.
Re: Package inquiry... [message #390050 is a reply to message #390048] Wed, 04 March 2009 14:32 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://asktom.oracle.com/~tkyte/ResultSets/

The examples in the link above all use strong ref cursors. Your need is for weak ref cursors where the number and definition of columns in the resultset is dynamic. The problem is that your client (ASP) needs to know the structure to be able to accept the results. Search asktom for weak vs strong and also see dbms_sql.describe_columns.

Maybe new 10g/11g functionality has made this problem easier to solve.

If you already have a working solution, then you can just move the standalone procs into a package and it should work fine. As far as I recall ref cursors require a package, but the examples in the link are a little dated (search for SYS_REFCURSOR).

[Updated on: Wed, 04 March 2009 14:46]

Report message to a moderator

Re: Package inquiry... [message #390051 is a reply to message #390049] Wed, 04 March 2009 14:38 Go to previous message
WarrenMedernach
Messages: 4
Registered: March 2009
Location: Canada
Junior Member
Well, I thought I was being 'prudent' by not posting a bunch of code to the group, but I guess I was wrong...

andrew again, thanks for the link I will check that out.
Warren M
Previous Topic: EXECUTE IMMEDIATE
Next Topic: The wrap utility is not really hiding the source code
Goto Forum:
  


Current Time: Thu Dec 08 16:27:05 CST 2016

Total time taken to generate the page: 0.13592 seconds