Home » SQL & PL/SQL » SQL & PL/SQL » Packages
Packages [message #256932] Tue, 07 August 2007 03:26 Go to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
Am having a package with one procedure in specification and in body with 2 parameters.Am using this procedure in some other package.In future if i wants to add one more parameter, is there any possibility to add parameter with altering the dependent pacakges.

Re: Packages [message #256935 is a reply to message #256932] Tue, 07 August 2007 03:30 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Yes, you can add parameters without having to change dependend packages. You could use optional parameters or overloading.

MHE
Re: Packages [message #256936 is a reply to message #256935] Tue, 07 August 2007 03:35 Go to previous messageGo to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
But here the problem is,I dont know how may parameters am going to add.So,obviously we cant have overloading function.
Consider that the parameters are going to be added dynamically.
Re: Packages [message #256939 is a reply to message #256936] Tue, 07 August 2007 03:55 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I fail to see that as a reason why you can't use overloading. Have you considered passing a table type as parameter?

Edit: There's no such thing as a 'dynamic parameter'. Oracle doesn't support VARARGS or anything like that in PL/SQL.

MHE

[Updated on: Tue, 07 August 2007 03:56]

Report message to a moderator

Re: Packages [message #256948 is a reply to message #256939] Tue, 07 August 2007 04:37 Go to previous messageGo to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
I dont want to change the procedure in specification.
Re: Packages [message #256950 is a reply to message #256948] Tue, 07 August 2007 04:43 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
So you want to change the procedure yet you don't want to change it. Good luck on that. What did you have in mind?

If you don't want to change the signature, you could opt for the table type. I'll post an example later on.


MHE
Re: Packages [message #256967 is a reply to message #256950] Tue, 07 August 2007 05:13 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Here you go. My script:
CREATE OR REPLACE TYPE parrec IS OBJECT ( parname VARCHAR2(30)
	                                      , parval VARCHAR2(30)
	                                      );
/
CREATE OR REPLACE TYPE partab IS TABLE OF parrec;
/
	
CREATE OR REPLACE PACKAGE mhe_pkg
IS
	PROCEDURE my_procedure( v_partab IN partab);
END mhe_pkg;
/

CREATE OR REPLACE PACKAGE BODY mhe_pkg
IS
	PROCEDURE my_procedure( v_partab IN partab)
	IS
	BEGIN
		dbms_output.put_line('Number of parameters: '||v_partab.count);
		FOR rec in (SELECT parname, parval FROM TABLE(v_partab))
		LOOP
			dbms_output.put_line(rec.parname||' has value '||rec.parval);
		END LOOP;
  END my_procedure;
END mhe_pkg;
/
sho err

SET SERVEROUT ON
DECLARE
  v_tab partab := NEW partab(parrec('Firstname','Frank'));
BEGIN
	mhe_pkg.my_procedure(v_tab);
END;
/

DECLARE
  v_tab partab := NEW partab( parrec('Firstname','Maarten')
                            , parrec('Lastname','Hereijgers')
                            );
BEGIN
	mhe_pkg.my_procedure(v_tab);
END;
/
DROP PACKAGE mhe_pkg
/
	
DROP TYPE partab
/
DROP TYPE parrec
/

I call the procedure with one parameter and with two parameters. But the different parameters are hidden in the table type. So, in fact the procedure only takes one real parameter (of the partab table type) and inside I add logic to investigate the different parameter names and values.

SQL> @orafaq

Type created.


Type created.


Package created.


Package body created.

No errors.
Number of parameters: 1
Firstname has value Frank

PL/SQL procedure successfully completed.

Number of parameters: 2
Firstname has value Maarten
Lastname has value Hereijgers

PL/SQL procedure successfully completed.


Package dropped.


Type dropped.


Type dropped.


MHE

[Updated on: Tue, 07 August 2007 05:13]

Report message to a moderator

Previous Topic: datatype equialent
Next Topic: error creating xml file
Goto Forum:
  


Current Time: Sun Dec 11 04:27:56 CST 2016

Total time taken to generate the page: 0.20784 seconds