Forum Stats

  • 3,768,987 Users
  • 2,252,890 Discussions
  • 7,874,826 Comments

Discussions

How to pass a variable with index(i) into a Procedure?

Jasper Tanglib
Jasper Tanglib Member Posts: 67 Green Ribbon

Hi,

In PLSQL, my code (please refer below) will assign a ZIP file into variable l_files(type is apex_zip.t_files). I then have a loop(i) that stores the ZIP file contents into l_files(i). And this l_files(i) is what I insert into my column.


I want the code above to look like this. It will call a procedure like ZIP_FEATURE_USAGE_STATISTICS.


My Procedure ZIP_FEATURE_USAGE_STATISTICS currently looks like this but it displays the error below.

I tried declaring the variable i that's in l_files(i) to remove the error but it does not work as well.


How do I pass the variable with an index into my Procedure such that the i index in the procedure should hold the value coming from l_files(i)?


Any idea or suggestion is appreciated.


-Jazz

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,302 Gold Trophy
    edited Apr 7, 2021 9:43AM

    I understand your procedure should not have in parameter of type apex_zip.t_files, which is a collection, but should have an in parameter of the type which is used for that collection. So, you will have nothing to do with that i, for the in parameter will not be a collection. I do not know how that apex_zip.t_files collection is defined.

    See: https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_zip.htm#AEAPI29943


    Therefore the in parameter for that procedure should be of varchar2 type, the procedure processing one file and not all that collection of files.

  • Jasper Tanglib
    Jasper Tanglib Member Posts: 67 Green Ribbon

    The l_files variable is of type apex_zip.t_files.






    Changing l_files to varchar2 would not allow me to use Apex's zip package and would return this error.





    Also tried to change l_files type in my procedure but returned the following error,

  • BEDE
    BEDE Oracle Developer Member Posts: 2,302 Gold Trophy

    Would have appreciated if you copied and pasted the text of your code and not pictures. Thus it would have been much easier for others...

    So, I understand that apex_zip.get_files requires a parameter of type apex_zip.t_zip_files, so that you will simply call that constructor for the colelction using the varchar parameter for that function, like below:

    l_zip_files:=apex_zip.get_files(p_zipped_blob=>apex_zip.t_zip_files(l_zip_file));

  • Jasper Tanglib
    Jasper Tanglib Member Posts: 67 Green Ribbon

    Yes that's how it goes. Now I'm trying to get the values indexed in l_files(i) into my Procedure zip_feature_usage_statistics.

    DECLARE

    l_zip_file      blob;

        l_unzipped_file blob;

        l_files         apex_zip.t_files;

    BEGIN

    l_files := apex_zip.get_files (

                    p_zipped_blob => l_zip_file );

            

            FOR i IN 1 .. l_files.count 

            LOOP

                l_unzipped_file := apex_zip.get_file_content (

                    p_zipped_blob => l_zip_file,

                    p_file_name   => regexp_substr(l_files(i), '[a-z][^/0-9]+$',1,1,'i'));


                IF l_files(i) LIKE '%feature_usage_statistics.csv' THEN 

                    zip_feature_usage_statistics(l_files);

    END IF;

    END LOOP;

    END;


    my Procedure zip_feature_usage_statistics:

    create or replace PROCEDURE zip_feature_usage_statistics(l_files IN apex_zip.t_files) AS

        v_xt    varchar2(100);

        xtid    varchar2(50);

        loadid  number(22);

        i number;

    BEGIN

        INSERT INTO data_source ( xt, moat_file_name )

        VALUES ('features_stats', regexp_substr(l_files(i), '[a-z][^/0-9]+$',1,1,'i'));

    END;

  • User_H3J7U
    User_H3J7U Member Posts: 663 Silver Trophy

    Assign collection element expression to a simple variable or use a separate forall insert outside of main for i in .. loop

  • Jasper Tanglib
    Jasper Tanglib Member Posts: 67 Green Ribbon

    Hi, I'll try that, though I'm still new to PLSQL so I'm not familiar on the collection element. Can you please show a pseudocode on the collection element expression related to my code.

  • jflack
    jflack Member Posts: 1,516 Bronze Trophy

    The problem is that you are passing the entire collection to the procedure, then trying to do something with the member indexed by "i". And it doesn't know what "i" is.

    Three solutions:

    1. Since your procedure is intended to do something with one and only one member of the collection, pass it one member. The parameter would be VARCHAR2, since that is the datatype of one member of an "apex_zip.t_files" collection. When you call it, pass it l_files(i) - i.e. the member, not the whole collection. Then use the parameter in the INSERT at the place that you now have "l_files(i)". This is my preferred solution.
    2. Pass "i" as another parameter.
    3. Make "i" a global variable so both the calling procedure and the called procedure can see it. This is my least favorite solution.
  • Jasper Tanglib
    Jasper Tanglib Member Posts: 67 Green Ribbon
    edited Apr 7, 2021 1:25PM

    Hi @jflack thank you for this.

    For number 1, changing my l_files type into varchar would result to this error (please refer to screenshot below). I think these IN parameters should have the same type as seen in my base code wherein l_files is of type apex_zip.t_files.

    l_files from base code




    For #2. Sorry I am new to PLSQL and just found out today on how to create Procedures. How or what does it mean to "Pass 'i' as another parameter"?


    For #3. Is this global variable method the one you configure in Shared Components -> Application Items?

  • jflack
    jflack Member Posts: 1,516 Bronze Trophy

    Let's forget solutions 2 and 3 and concentrate on 1.

    When the procedure (and PLEASE post code, not images) is defined like this:

    CREATE OR REPLACE PROCEDURE zip_feature_usage_statistics (l_files IN VARCHAR2);
    

    You call it with a VARCHAR2 argument like this:

    DECLARE
      l_files apex_zip.t_files; /* Each member of this collection is a VARCHAR2. */
      i PLS_INTEGER;
    BEGIN
      FOR i IN l_files.FIRST .. l_files.LAST LOOP
        IF l_files(i) LIKE '%feature_usage_statistics.csv' THEN
           /* Notice that we're passing the current member, not the whole collection.*/
          zip_feature_usage_statistics(l_files(i));
        END IF;
      END LOOP;
    END;
    

    Now I wouldn't use l_files as the name for the parameter - I'd call it something like "the_file".