PL-SQL loop throug values to get dynamic query and store results

Question

I've got code as follows:

CREATE OR REPLACE TYPE cat_array_t is varray(2) of varchar(10);
--    cat_array cat_array_t:=cat_array_t('retired','worker');
/
CREATE OR REPLACE FUNCTION Get_Data_Faster(in_cat IN cat_array, in_kw_crt IN kw_crt_array) RETURN get_data_faster_data PIPELINED AS
    TYPE r_cursor IS REF CURSOR;
    query_results r_cursor;
    results_out get_data_faster_row := get_data_faster_row(NULL, NULL);

    query_str VARCHAR2(4000);

    cat_value VARCHAR2(10);
    kw_crt_value VARCHAR2(10);
    

BEGIN
    FOR i IN 1..cat_array.COUNT
    LOOP
        cat_value := cat_array(i);
        kw_crt_value := kw_crt_array(i);
        

--        query_str := 'SELECT distinct '||seq_number||' as seq, value, item
--        FROM my_table ai';                    
--
--        query_str := query_str || '
--        WHERE ai.value = '''||the_value||''' AND ai.item = '''||the_item||'''
--        AND ai.param = ''BOOK''
--        AND ai.prod in (' || list || ');
        
         query_str := 'select owner_id,property_id ' ||
               'from owners ' ||
               'where substr(PROPERTY_ID,1,4) =' || chr(39) || kw_crt_value     || chr(39) ||
               '  and Owner_category    = ' || chr(39) || cat_value || chr(39);

        OPEN query_results FOR query_str;

        LOOP
            FETCH query_results INTO 
                results_out.owner_id,
                results_out.property_id;
            EXIT WHEN query_results%NOTFOUND;
            PIPE ROW(results_out);
        END LOOP;

    CLOSE query_results;

    END LOOP;

END;
/

The problem is when I run this I get error. In log there is "Error: PLS-00201: identifier "CAT_ARRAY" should be declared". Don't how to improve this code. How to pass this line with elements of varray to code -- cat_array cat_array_t:=cat_array_t('retired','worker');

"I tried declare this on the beginning" - does that mean you add the type declaration to the code you showed because you got this error, and you already have the SQL type defined? If so should IN cat_array just be IN cat_array_t? — Alex Poole
– Alex Poole, Commented Jun 29, 2020 at 7:36

Boneist · Accepted Answer · 2020-06-29 07:39:54Z

1

In addition to the other answers, you're referencing your array with (almost) the name of the type, not the name of the array, e.g.:

FOR i IN 1..cat_array.COUNT

should be

FOR i IN 1..in_cat.COUNT

since you have defined the name of the parameter as in_cat here:

CREATE OR REPLACE FUNCTION Get_Data_Faster(in_cat IN cat_array_t, ....
                                           ^^^^^^

You must replace all instances of the variable name cat_array with the correct name in_cat.

answered Jun 29, 2020 at 7:39

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex Poole · Accepted Answer · 2020-06-29 07:44:32Z

1

You have

CREATE OR REPLACE TYPE cat_array_t ...

but then your function has

CREATE OR REPLACE FUNCTION Get_Data_Faster(in_cat IN cat_array, ...

The type name doesn't match; it should be

CREATE OR REPLACE FUNCTION Get_Data_Faster(in_cat IN cat_array_t, ...

and you don't need/want the separate local PL/SQL type declaration, or the local variable; and you need to refer to the passed-in parameter name (as @Boneist beat me too):

FOR i IN 1..in_cat.COUNT

You may have done the same thing with in_kw_crt IN kw_crt_array.

edited Jun 29, 2020 at 7:44

answered Jun 29, 2020 at 7:39

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Comments

Littlefoot · Accepted Answer · 2020-06-29 07:15:29Z

0

Of course it returns an error.

CREATE OR REPLACE FUNCTION Get_Data_Faster (in_cat IN cat_array)
   RETURN NUMBER                                      ^^^^^^^^^^ type is used here
IS
   TYPE cat_array_t IS VARRAY (2) OF VARCHAR (10);    --> type is declared here
   cat_array cat_array_t:=cat_array_t('emeryt','pracownik');

You're first referencing a type which is declared within the function. You can't do that; create type at SQL level, not within the function.

answered Jun 29, 2020 at 7:15

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Comments

Popeye · Accepted Answer · 2020-06-29 07:15:32Z

0

Your type cat_array and cat_array_t must be SQL type defined as an individual as follows:

CREATE [OR REPLACE ] TYPE type_name AS | IS
    VARRAY(max_elements) OF element_type [NOT NULL];

Now, this type can be used globally in your PL/SQL code.

answered Jun 29, 2020 at 7:15

Popeye

36k4 gold badges12 silver badges31 bronze badges

2 Comments

data_b77 Over a year ago

Ok, I've updated the code but still error is the same. How to pass list of varray's element into the code?

Popeye Over a year ago

While calling the function from PL/SQL, You need to create the variable of the cat_array and input them into function IN parameter.

Collectives™ on Stack Overflow

PL-SQL loop throug values to get dynamic query and store results

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related