Big confusion with Flexible Parameter Passing

From: prl <prlawrence_at_lehigh.edu>
Date: Wed, 18 Feb 2004 17:25:32 -0500
Message-ID: <c10ono$c1s$1@fidoii.CC.Lehigh.EDU>

Hi,

A couple of days ago Mark Stock kindly pointed me in the direction of Flexible Parameter Passing for OIS.

It's exactly what I need, but something isn't right... The example procedure header and body below compile fine, and the web page comes up nicely. After I fill in the text boxes (which are auto-generated by the example code) and submit, I get this error in my browser:

   Not Found
   The requested URL /pls/bnco/!bwwkintt.P_TravelReg was not found on this server.

   Oracle HTTP Server Powered by Apache/1.3.19 Server at...

The error log says:
[warn] mod_plsql: /pls/bnco/!bwwkintt.P_TravelReg is using older style flexible parameter passing. Refer to the PL/SQL GateWay documentation on using the 2-Parameter style Flexible Parameter passing mode

This is confusing, because I * am* using the 2 parameter method. However, if I try the 4 parameter method (commented out in example code below) I just get:

   ... ORA-0 No such object.

The problem may be in my choice of collection type...? I have tried Associative Arrays and Varrays and somethign called OWA.VC_ARR. I have tried initializing them. I have tried the 4 parm and the 2 parm method.

Perhaps someone more experienced can spot my error(s).

Thanks!
Phil

-------HEADER SQL----------

SET SCAN OFF; CREATE OR REPLACE PACKAGE bwwkintt
AS

    TYPE varchar2_tab is TABLE OF VARCHAR2(50);
-- empty_vc_arr OWA.VC_ARR;

PROCEDURE P_TravelReg ( in_names varchar2_tab DEFAULT varchar2_tab(), in_values varchar2_tab DEFAULT varchar2_tab() );
--PROCEDURE P_TravelReg ( in_names OWA.VC_ARR DEFAULT empty_vc_arr,
in_values OWA.VC_ARR DEFAULT empty_vc_arr );
--PROCEDURE P_TravelReg
--(

• num_entries IN NUMBER,
• in_names IN OWA.VC_ARR DEFAULT empty_vc_arr,
• in_values IN OWA.VC_ARR DEFAULT empty_vc_arr,
• reserved IN OWA.VC_ARR DEFAULT empty_vc_arr
  --);
  

END bwwkintt;
/

WHENEVER SQLERROR CONTINUE;
DROP PUBLIC SYNONYM bwwkintt;
WHENEVER SQLERROR EXIT ROLLBACK; CREATE PUBLIC SYNONYM bwwkintt FOR bwwkintt; WHENEVER SQLERROR CONTINUE

• take care of all permissions needed to make
• this package work with the webserver START gurgrtb bwwkintt START gurgrth bwwkintt

--WHENEVER SQLERROR EXIT ROLLBACK

SET SCAN ON;
SHOW ERRORS;
-------BODY SQL----------

SET SCAN OFF; CREATE OR REPLACE PACKAGE BODY bwwkintt
AS

    rcs_revision CONSTANT VARCHAR2 (20) := '$Revision: 1.2 $';     global_pidm NUMBER;

PROCEDURE lu_formtext ( in_table_name VARCHAR2, in_column_name VARCHAR2, in_column_value VARCHAR2 )
IS

	sql_query    VARCHAR2(200);
	col_length   ALL_TAB_COLUMNS.DATA_LENGTH%TYPE;
	col_desc     ALL_COL_COMMENTS.COMMENTS%TYPE;
	print_value  VARCHAR2(30);
	
	CURSOR c_col_info
	IS
		SELECT DATA_LENGTH
		      ,COMMENTS
		  FROM ALL_COL_COMMENTS
		      ,ALL_TAB_COLUMNS
		 WHERE ALL_TAB_COLUMNS.TABLE_NAME   = UPPER(in_table_name)
		   AND ALL_COL_COMMENTS.TABLE_NAME  = ALL_TAB_COLUMNS.TABLE_NAME
		   AND ALL_TAB_COLUMNS.COLUMN_NAME  = UPPER(in_column_name)
		   AND ALL_COL_COMMENTS.COLUMN_NAME = ALL_TAB_COLUMNS.COLUMN_NAME;

BEGIN         IF in_column_value IS NULL THEN                 

		sql_query :=
		    'SELECT '|| in_column_name
		 || '  FROM ' || in_table_name
		 || ' WHERE pidm = ' || global_pidm;

		BEGIN
			EXECUTE IMMEDIATE sql_query
			  INTO print_value;
		EXCEPTION
			WHEN NO_DATA_FOUND THEN
				print_value := '';
		END;
	ELSE
		print_value := in_column_value;
	END IF;



--	IF print_value IS NULL

--		-- this is a *new* traveller, so lets's print their default values

--		print_value := get_default_values( in_column_name );

--	END IF;


	OPEN c_col_info;
	FETCH c_col_info
	  INTO col_length
	      ,col_desc;

	htp.p( col_desc );
	twbkfrmt.p_formtext (
	  in_column_name,
	  col_length,
	  col_length,
	  print_value,
	  cattributes=> 'ID="'|| col_desc || '"'
	);
	htp.br;

END lu_formtext;

FUNCTION f_get_curr_value (

	in_names varchar2_tab DEFAULT varchar2_tab()


--	in_names OWA.VC_ARR

	, in_values varchar2_tab DEFAULT varchar2_tab()


--	, in_values OWA.VC_ARR

	, in_column_name ALL_TAB_COLUMNS.COLUMN_NAME%TYPE DEFAULT NULL

) RETURN VARCHAR2
IS
BEGIN

	IF in_names.EXISTS(1) THEN
		FOR i IN in_names.FIRST .. in_names.LAST
		LOOP
			IF in_names(i) = in_column_name THEN
				RETURN in_values(i);
			END IF;
		END LOOP;
	END IF;

	RETURN NULL;

END f_get_curr_value;

PROCEDURE P_TravelReg ( in_names varchar2_tab DEFAULT varchar2_tab(), in_values varchar2_tab DEFAULT varchar2_tab() )
--PROCEDURE P_TravelReg ( in_names OWA.VC_ARR DEFAULT empty_vc_arr,
in_values OWA.VC_ARR DEFAULT empty_vc_arr )
--PROCEDURE P_TravelReg
--(

• num_entries IN NUMBER,
• in_names IN OWA.VC_ARR DEFAULT empty_vc_arr,
• in_values IN OWA.VC_ARR DEFAULT empty_vc_arr,
• reserved IN OWA.VC_ARR DEFAULT empty_vc_arr
  --)
  IS

	CURSOR c_info (column_pattern ALL_TAB_COLUMNS.COLUMN_NAME%TYPE)
	IS
	SELECT COLUMN_NAME
	  FROM ALL_TAB_COLUMNS
	 WHERE COLUMN_NAME LIKE column_pattern
	   AND TABLE_NAME = 'GWBTRAV';

	trip_rowid        UROWID;
	trip_name         gwrtrip.trip_name%TYPE;
	us_depart_date    gwrtrip.us_depart_date%TYPE;
	us_return_date    gwrtrip.us_return_date%TYPE;

	col_name          ALL_TAB_COLUMNS.COLUMN_NAME%TYPE;
	curr_value        VARCHAR2(50);

BEGIN

• Validate the current user.
• (twbkwbis is vendor provided for low-level web page functinality) IF NOT twbkwbis.f_validuser (global_pidm) THEN RETURN; END IF;
• Start the web page twbkwbis.p_opendoc ( 'bwwkintt.P_TravelReg', '', ' ' );
• open form to allow editing of traveller info htp.formopen ( '!bwwkintt.P_TravelReg', 'get' );

	OPEN c_info( 'E_%' );
	LOOP
		FETCH c_info INTO col_name;
		EXIT WHEN c_info%NOTFOUND;

		curr_value := f_get_curr_value( in_names, in_values, col_name );
		lu_formtext( 'gwbtrav', col_name, curr_value );

	END LOOP;

	-- create button to submit fields
	htp.formsubmit (NULL, 'Update');

	-- close the form
	htp.formclose;

	-- Close the web page.
	twbkwbis.p_closedoc( rtrim( substr(rcs_revision,11), ' $') );

END P_TravelReg;

END bwwkintt;
/

WHENEVER SQLERROR CONTINUE;
DROP PUBLIC SYNONYM bwwkintt;
WHENEVER SQLERROR EXIT ROLLBACK; CREATE PUBLIC SYNONYM bwwkintt FOR bwwkintt; WHENEVER SQLERROR CONTINUE

• take care of all permissions needed to make
• this package work with the webserver START gurgrtb bwwkintt START gurgrth bwwkintt

--WHENEVER SQLERROR EXIT ROLLBACK

SET SCAN ON;
SHOW ERRORS; Received on Wed Feb 18 2004 - 16:25:32 CST