This content has been marked as final. Show 5 replies
You call your PL/SQL procedure with a parameter. The query string contains:
MOD_PLSQL wants to constructs a PL/SQL call look something as follows:
(note that it will actually use bind variables - this is just to illustrate what MOD_PLSQL's intentions are with your URL request)
Getdate.app( p_id => 24506 );
Before MOD_PLSQL constructs the anonymous PL/SQL block, it first describes your PL/SQL proc called Getdate.app to see what the parameter P_ID's data type is. Should it bind the value 24506 as a varchar2, number, date, etc?
If that procedure does not have any parameters, then MOD_PLSQL cannot construct the call and it fails.
There are also two other call methods that MOD_PLSQL will attempt. The 2 parameter and (old) 4 parameter "flexible" call methods. Cannot recall how the latter works.
The 2 call method expects only two parameters for the PL/SQL proc. The 1st parameter is an array of parameter names. The 2nd an array of parameter values. This is a flexible call method as the PL/SQL proc can handle any query string - irrespective of the number of query string parameters.
You need to make sure what call method you use for that DaD. And ensure that your PL/SQL code correctly support that call method.
Refer to the [url http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14337/toc.htm]Oracle® HTTP Server mod_plsql User's Guide for details. I think there is also a PL/SQL Webkit manual, but my quick search failed to turn it up. Anyway, the MOD_PLSQL manual briefly describes the call interfaces - that should provide some additional details.
I followed the document and still get the same error, I found more examples of procedures called from the browser and after successfully compiling them I get the same error:
ORA-06550: line 3, column 25:
PLS-00302: component 'VC_ARR' must be declared
ORA-06550: line 3, column 16:
PL/SQL: Item ignored
I even created a couple new DAD without success
this is the URL i´m using: http://freya:7778/pls/abanks/display_image?p_id=12
to call this procedure:
CREATE OR REPLACE PROCEDURE Display_Image(p_id NUMBER) IS
v_amt NUMBER DEFAULT 4096;
v_off NUMBER DEFAULT 1;
-- Get the blob image
WHERE IMAGEID = p_id;
-- Read the BLOB
dbms_lob.READ(Photo, v_amt, v_off, v_raw);
-- Display image
v_off := v_off + v_amt;
v_amt := 4096;
WHEN NO_DATA_FOUND THEN
The following procedure demonstrates a custom file download/display PL/SQL web procedure. I've used Oracle APEX's (HTMLDB's) file table as the file repository.
The WPG_DOCLOAD API and method below are described in the manual URL I've listed in my previous response.
create or replace procedure StreamFlowFile( fileID number ) AUTHID DEFINER is
from FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ f
where f.id = fileID;
-- format a very bare bones and basic HTTP header
OWA_UTIL.mime_header( mimeType, FALSE );
HTP.p( 'Content-Disposition: attachment; filename='||fileName );
HTP.p( 'Content-Length: ' || DBMS_LOB.GetLength(lobContent) );
-- now write the BLOB as a mime stream using the Web Procedural Gateway's
-- doc load API
WPG_DOCLOAD.download_file( lobContent );
exception when OTHERS then
HTP.prn( 'StreamFlowFile() failed with '||SQLERRM(SQLCODE) );
> PLS-00302: component 'VC_ARR' must be declared
As I've already mentioned, there are a couple of methods that one can use to define your procederual interface with MOD_PLSQL. The VC_ARR type is used with the flexible 2 call method interface.
The VC_ARR is an PL/SQL array type that is defined in the OWA_TEXT PL/SQL package header - part of the PL/SQL Web Cartridge/Gateway kit.
It is defined as follows:
type vc_arr is table of varchar2(32767) index by binary_integer;
Do you have the OWA_* packages installed? They are mandatory for MOD_PLSQL to work.
Login to your database as SYS or SYSTEM and run the following SQL to confirm:
select object_type, object_name from dba_objects where owner='SYS' and object_name like 'OWA%' order by 2,1
There should be at least 23 rows in response to this. (one package only consists of a header and no body)