5 Replies Latest reply: Dec 7, 2006 11:28 AM by 463049 RSS

    DAD error

    463049
      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
          Billy~Verreynne
          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
            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
              Billy~Verreynne
              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
                Billy~Verreynne
                > 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
                  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