Home » SQL & PL/SQL » SQL & PL/SQL » Procedure - Accept Multiple/Random Parameters (10g)
Procedure - Accept Multiple/Random Parameters [message #296699] Mon, 28 January 2008 13:50 Go to next message
sjordan
Messages: 19
Registered: October 2007
Junior Member
Hello,

I have been designing several web forms for data entry. Just to keep things basic, with each web forms, there are two procedures. The first procedure creates the form and displays it to the web page. The second procedure accepts the information when the form is submitted and added it to the database.

What I would like to do is, create a procedure that reads from a table all of the particular inputs for a form (this is similar to the first procedure listed above). While reading through the records, it would print out the necessary fields to the web page as an HTML form. I have no issues with doing this part.

The part that I am not sure how to handle is the procedure that accepts the information and would then process it. How would I create a procedure that can have any number of parameters in it? I know I can create one with a lot of parameters and then set the defaults to null, which I would be willing to do. But, if there would be a cleaner solution, I'd prefer it.

I am wanting to make an easy way to create simple forms for end users. Then, I could always expand it to get more complex once I had the groundwork created. I imagine someone has done something similar to this. I've done this before, but not in Oracle.

Thanks,
Shane
Re: Procedure - Accept Multiple/Random Parameters [message #296702 is a reply to message #296699] Mon, 28 January 2008 14:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I know I can create one with a lot of parameters and then set the defaults to null, which I would be willing to do. But, if there would be a cleaner solution, I'd prefer it.

This is the way to do it.
What do you mean cleaner solution?
Why is it not clean?

Regards
Michel
Re: Procedure - Accept Multiple/Random Parameters [message #296703 is a reply to message #296702] Mon, 28 January 2008 14:52 Go to previous messageGo to next message
sjordan
Messages: 19
Registered: October 2007
Junior Member
The reason I am saying it is not clean is because that puts a limitation on the number of fields. Just say, I created the procedure with 100 parameters. Then, I am limiting the form to 100 fields. If someone needed to create a form with more, I'd have to go back and modify the procedure.

Doing some research, I came across something like:
TYPE <typeName> IS TABLE OF VARCHAR2
   INDEX BY BINARY_INTEGER;


Would it be feasible to create the procedure with a single parameter with that type? Then, there would not really be a limitation on the number of fields.
Re: Procedure - Accept Multiple/Random Parameters [message #296709 is a reply to message #296703] Mon, 28 January 2008 16:42 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
This was answered in this forum before. I believe someone recommended using varrays in some capacity. Unfortunately, I can't remember enough of the details to get a good hit when searching the forum. Maybe someone else will remember this?

Aha! My search through my email archives pulled up this link...

http://www.orafaq.com/forum/?t=rview&goto=237819#msg_237819

Try reviewing this and see if it is something worth exploring.

HTH,
Ron
Re: Procedure - Accept Multiple/Random Parameters [message #296877 is a reply to message #296709] Tue, 29 January 2008 06:55 Go to previous messageGo to next message
sjordan
Messages: 19
Registered: October 2007
Junior Member
Thank you for the feedback/link. I think I am going to pursue going forward with the varray to see how things work out. I have a few projects with higher priority, but as soon as I get something (good or bad) I'll post it.
Re: Procedure - Accept Multiple/Random Parameters [message #296891 is a reply to message #296699] Tue, 29 January 2008 07:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.orafaq.com/forum/m/293644/102589/?srch=create+type+table+procedure#msg_293644

Regards
Michel
Re: Procedure - Accept Multiple/Random Parameters [message #296898 is a reply to message #296891] Tue, 29 January 2008 07:25 Go to previous message
sjordan
Messages: 19
Registered: October 2007
Junior Member
Thank you for the link. I am thinking that something like that will work for what I am trying to accomplish.
Previous Topic: Select statement for export with timestamp
Next Topic: aliasname
Goto Forum:
  


Current Time: Wed Dec 07 04:58:09 CST 2016

Total time taken to generate the page: 0.09723 seconds