Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

Update Multi Record Forms: Passing variable of type table into a PLSQL procedure?

Hi,

I have the following form situated in a page called BaseDataEstDate.cfm

---------------------------------------------------------------------------------------------------

<form action="../components/BaseDataEstProcedure.cfm" method="POST" name="FormAllItems" onClick="changeto(event, 'lightgreen')" >
                                    
                                    
                  <table   border="0" cellspacing="1" cellpadding="0" >
                        <tr>
                              <td class="TableTitle" >OTIF Base Data</td>
                        </tr>
                  </table>

                  <table  class="TableBorder"  width="1500"  border="0" cellspacing="1" cellpadding="0">
                        <tr class="TableColumnHeader">
                                     <!---<td NOWRAP align="center" >Hide</td>--->
                                    <td NOWRAP align="center" >Region</td>
                                    <td NOWRAP align="center" >EM</td>
                                    <td NOWRAP align="center" >EM Desc</td>
                                    <td NOWRAP align="center" >TC</td>
                                    <td NOWRAP>Sold-To</td>
                                    <td NOWRAP>Plant</td>
                                    <td NOWRAP>POM</td>
                                    <td NOWRAP>Brand Family</td>
                                    <td NOWRAP>Brand Variant</td>
                                    <td NOWRAP>Material</td>
                                    <td NOWRAP>Primary Factory Code</td>
                                    <td NOWRAP>Order</td>
                                    <td NOWRAP>Line</td>
                                    <td NOWRAP>Orig. Ord Request</td>
                                    <td NOWRAP>Est. Date of Prod.</td>
                                    <td NOWRAP>Est. date of Sailing</td>
                                    <td NOWRAP>Act. Date of Sailing</td>
                                    <td>&nbsp;</td>
                        </tr>
                  <cfset lv_Num =0>
                  <cfloop query="WeeklyOrdersReturn">
                  <cfoutput>
                  
                                <cfset lv_Num = lv_Num +1>
                                 <cfif  (lv_Num MOD 2) eq 0>
                                      <cfset lv_row = "TableRow0">
                              <cfelse>
                                      <cfset lv_row = "TableRow1">
                                </cfif>
                  
                              <cfset lv_NumVal = lv_Num MOD 2>



                        <tr class="#lv_row#">
                              <!---<td>&nbsp;</td>--->
                                    <td NOWRAP>#WeeklyOrdersReturn.REGION#</td>
                                    <td NOWRAP>#WeeklyOrdersReturn.END_MARKET#</td>
                                    <td NOWRAP>#WeeklyOrdersReturn.END_MARKET_DESC#</td>
                                    <td NOWRAP>#WeeklyOrdersReturn.TRADE_CHANNEL#</td>
                                    <td NOWRAP>#WeeklyOrdersReturn.SOLD_TO_PARTY#</td>
                                    <td NOWRAP>#WeeklyOrdersReturn.PLANT#</td>
                                    <td NOWRAP>#WeeklyOrdersReturn.POINT_OF_MF#</td>
                                    <td NOWRAP>#WeeklyOrdersReturn.BRAND_FAMILY#</td>
                                    <td NOWRAP>#WeeklyOrdersReturn.BRAND_VARIANT#</td>
                                    <td valign="top"><INPUT TYPE="text" NAME="p_material"             SIZE="10"       MAXLENGTH="8" VALUE="#WeeklyOrdersReturn.MATERIAL#" class="TableText"  readonly></td>
                                    <td NOWRAP>#WeeklyOrdersReturn.PRIMARY_FACTORY_CODE#</td>
                                    <td valign="top"><INPUT TYPE="text" NAME="p_order_number"       SIZE="7"       MAXLENGTH="8" VALUE="#WeeklyOrdersReturn.ORDER_NUMBER#" class="TableText"  readonly></td>
                                    <td valign="top"><INPUT TYPE="text" NAME="p_line_number"       SIZE="3"       MAXLENGTH="3" VALUE="#WeeklyOrdersReturn.LINE_NUMBER#"  class="TableText" readonly></td>                                    
                                    <td NOWRAP>#dateformat(WeeklyOrdersReturn.ORIGINAL_ORD_REQUEST_DATE,'dd/mm/yyyy')#</td>
                                    <td valign="top"><INPUT TYPE="text" NAME="p_estimated_date" SIZE="10"  VALUE="#dateformat(WeeklyOrdersReturn.ESTIMATED_PRODUCTION_DATE,'dd/mm/yyyy')#" onchange="return isDate(p_estimated_date, 'Estimated Production Date')"  class="TableText" ></td>                                                                                                                                                            
                                    <td NOWRAP>#dateformat(WeeklyOrdersReturn.ESTIMATED_DATE_OF_SAILING,'dd/mm/yyyy')#</td>
                                    <td NOWRAP>#dateformat(WeeklyOrdersReturn.ACTUAL_DATE_OF_SAILING,'dd/mm/yyyy')#</td>
                                                                                                                        
                                    <td><input type="submit" name="butSubmit" value="Save" id="ButtonEmphasized" nowrap class="buttonTextEmphasized" tabindex="0"></td>
                                          
                                                            </tr>
                              
                </cfoutput>
                  </cfloop>
</form>

---------------------------------------------------------------------------------------------------

this then calls the following coldfusion page called BaseDataEstProcedure.cfm



---------------------------------------------------------------------------------------------------
<cfstoredproc datasource="MANIF"
                              procedure="Pkg_DP_Process.Pr_ProcessEstDates"
                              debug>
                                                 
                  <cfprocparam type="In"
                        cfsqltype=cf_sql_varchar
                        value="#p_order_number#"
                        dbvarname="p_order_number"
                        >
                        
                  <cfprocparam type="In"
                        cfsqltype=cf_sql_varchar
                        value="#p_line_number#"
                        dbvarname="p_line_number"
                        >

                  <cfprocparam type="In"
                        cfsqltype=cf_sql_date
                        value="#p_estimated_date#"
                        dbvarname="p_estimated_date"
                        >
                  
                  <cfprocparam type="In"
                        cfsqltype=cf_sql_varchar
                        value="#p_material#"
                        dbvarname="p_material"
                        >

                  <cfprocparam type="In"
                        cfsqltype=cf_sql_varchar
                        value="NULL"
                        dbvarname="butSubmit"
                        >
                                          
            </cfstoredproc>
            
      <cflocation url="../pages/BaseDataEstDate.cfm">
            

---------------------------------------------------------------------------------------------------

the plsql package is as follows:
spec:

   TYPE order_number_ids        IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
   TYPE line_number_ids     IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
   TYPE reason_code_ids     IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
   TYPE reason_text_ids    IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
   TYPE material_ids        IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
   TYPE estimated_date_ids      IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;



PROCEDURE Pr_ProcessEstDates                     (p_order_number order_number_ids,
                                                   p_line_number  line_number_ids,
                                                     p_reason_code      reason_code_ids,
                                                     p_reason_text reason_text_ids,
                                                         p_material material_ids,
                                                       p_estimated_date estimated_date_ids,
                                                       p_FormNum  NUMBER DEFAULT NULL,
                                                       butSubmit  VARCHAR2 DEFAULT NULL
                                                       )
IS


BEGIN
      
      IF butSubmit IS NOT NULL THEN
      
         FOR i IN p_order_number.FIRST .. p_order_number.LAST LOOP
             
               UPDATE OTIF_DP
               SET  ESTIMATED_PRODUCTION_DATE  =   p_estimated_date(i)
               WHERE p_order_number(i) = ORDER_NUMBER
               AND   p_line_number(i) = LINE_NUMBER;
      
         END LOOP;
        
         COMMIT;
        
            
      END IF;
      

EXCEPTION
WHEN OTHERS THEN
null;
END;

----------------------------------------------------------------------------------------------------------------------------

As you can see, the BaseDataEstProcedure.cfm was originally set up to update one form/record at a time, I now want to update multiple rows with 1 save.

My question is:

Is this the best way to go about this? If it is, do I just change the
0
joehodge
Asked:
joehodge
1 Solution
 
joehodgeAuthor Commented:
cfsqltype to the coldfusion equivalent of table?
0
 
dgrafxCommented:
yes
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now