Re: PLSQL- parameter questions

> 1) is there a limit to the number of parameters you can pass (and
> received)?

Maybe, though I've written web procedures passing approx 50 or so data elements and never hit a brick wall.

> 2) obviously, passing 50 parameters from sp to sp is very messy. Is
> there a cleaner data structure I should use?

Yes, its messy. The best tactic is to use the first procedure that receives the data to clean up.

If the procedures using this data are not in the same package they probably should be. Whether they are or aren't, try creating a package that globally defines a programmer-defined PL/SQL record and table (I'll refer to it as MY_UTIL_PKG). Let's say your web form has 10 rows, each with a first name, last name, address, city and state. The web form elements have names like fname1, lname1... fname10, lname10, etc. Now define the following variable types in the package header of MY_UTIL_PKG:

     TYPE form_rec as RECORD (
        fname varchar2(20),
        lname varchar2(20),
        address varchar2(30),
        city varchar2(20),
        state varchar2(2));

     TYPE form_tbl as TABLE of form_rec
        index by binary_integer;

The procedure called by your form submit needs all 50 elements defined as IN parameters, each individually listed in the procedure declaration. Immediately declare the following variable:

     temp_form_tbl MY_UTIL_PKG.form_tbl default null;

Start dumping your incoming variables into the PL/SQL table you just defined. Unfortunately, you can't do it in a loop. You'll probably want to test one 'cell' in each row for null-ness to determine if you want to bother working with that row. I'll use 'lname', and assume that I'll at least have values in the first row:

     temp_form_tbl(1).fname := fname1;
     temp_form_tbl(1).lname := lname1;
     temp_form_tbl(1).address := address1;
     temp_form_tbl(1).city := city1;
     temp_form_tbl(1).state := state1;

     if lname2 is not null then
        temp_form_tbl(2).fname := fname2;
        temp_form_tbl(2).lname := lname2;
        temp_form_tbl(2).address := address2;
        temp_form_tbl(2).city := city2;
        temp_form_tbl(2).state := state2;
     end if;

     --etc...

Now you can call other procedures and simply pass 'temp_form_tbl'.

   proc2(temp_form_tbl);

Of course, the receiving procedures have to have:

   create or replace procedure proc2(in_form_tbl in MY_UTIL_PKG.form_tbl) is

Now that it's a PL/SQL table, you can work with the values in a loop, plus use all the standard 'methods' for PL/SQL tables (e.g. EXISTS, DELETE, NEXT, etc.). If you don't know what I'm talking about, read Steve Feurstein's 'Oracle PL/SQL Programming.

If your web form doesn't follow this model (the same X element types repeated Y times), then the process is the same except that you don't need the programmer defind table type, just the programmer defined record type.

Hope this helps.
-Brendan

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Mon Jul 20 1998 - 16:01:53 CDT