This discussion is archived
5 Replies Latest reply: Dec 7, 2006 9:28 AM by 463049 RSS

DAD error

463049 Newbie
Currently Being Moderated
Hi I'm trying to execute a procedure using a DAD on a OAS 10gR2 and a 10gR2 database when ever i call the procedure in my browser using
http://localhost:7778/mydad/Getdate.app?p_id=24506

I get the following error:

ORA-06550: line 3, column 25:
PLS-00302: component 'VC_ARR' must be declared

I thought it was the procedure I was calling so I created a much simpler one as follows:

CREATE OR REPLACE PROCEDURE TEST
AS
BEGIN
Htp.p('Hello');
END;


I called it using the same sintax as above with the same results, I'm thinking it has something to do with the OWA package, but cant seem to find a solution, any help is greatly appreciated
  • 1. Re: DAD error
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    You call your PL/SQL procedure with a parameter. The query string contains:
    p_id=24506.

    MOD_PLSQL wants to constructs a PL/SQL call look something as follows:

    begin
    Getdate.app( p_id => 24506 );
    end;
    (note that it will actually use bind variables - this is just to illustrate what MOD_PLSQL's intentions are with your URL request)

    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.
  • 2. Re: DAD error
    463049 Newbie
    Currently Being Moderated
    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
    Photo BLOB;
    v_amt NUMBER DEFAULT 4096;
    v_off NUMBER DEFAULT 1;
    v_raw RAW(4096);
    BEGIN
    -- Get the blob image
    SELECT image
    INTO Photo
    FROM PHOTOS
    WHERE IMAGEID = p_id;
    Owa_Util.mime_header('images/gif');
    BEGIN
    LOOP
    -- Read the BLOB
    dbms_lob.READ(Photo, v_amt, v_off, v_raw);
    -- Display image
    Htp.prn(utl_raw.cast_to_varchar2(v_raw));
    v_off := v_off + v_amt;
    v_amt := 4096;
    END LOOP;
    dbms_lob.CLOSE(Photo);
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
    END;
    END;
    /

    Thanks
  • 3. Re: DAD error
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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
    mimeType varchar2(48);
    fileName varchar2(400);
    lobContent BLOB;
    begin
    select
    f.filename,
    f.mime_type,
    f.blob_content
    into
    fileName,
    mimeType,
    lobContent
    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) );
    OWA_UTIL.http_header_close;

    -- 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) );

    end;
    /
  • 4. Re: DAD error
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    > 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)
  • 5. Re: DAD error
    463049 Newbie
    Currently Being Moderated
    Thanks for the info, I double checked everything and came upon the packages installed on the user that the DAD is created for, I removed such packages and now everything works fine.

    Thanks