cancel
Showing results for 
Search instead for 
Did you mean: 

NESTED TABLE TYPES AND VARRAY TYPES

Adventurer

NESTED TABLE TYPES AND VARRAY TYPES

Hi,

VERSION: EDB 9.6

I am getting error: table definition should be primitive or composite type.

Example:

a        CONSTANT NUMBER := -20003;
 c        CONSTANT NUMBER := -20004;

 b          CONSTANT NUMBER := -20005;
 

TYPE row_converted_codes IS VARRAY(2) OF NUMBER;

 

TYPE tab_converted_codes IS TABLE OF row_converted_codes;

    l_converted_codes CONSTANT tab_converted_codes := tab_converted_codes(
        row_converted_sql_codes(-1,     a), 
        row_converted_sql_codes(100,    b),   
        row_converted_sql_codes(-942,   c)   
    );

 

I am not quite sure how to fix this.

Tags (1)
4 REPLIES
Highlighted
EDB Team Member

Re: NESTED TABLE TYPES AND VARRAY TYPES

You have not shared the complete sample code, therefore I cannot help you in finding the issue.

 

I did spend some time to make a sample code out of whatever you have posted in the thread and it is working for me. Below is a snapshot

 

CREATE TYPE array_number IS VARRAY(2) of NUMBER;
CREATE OR REPLACE FUNCTION row_converted_sql_codes(NUMBER, NUMBER)
RETURN array_number
AS
DECLARE
   result ARRAY_NUMBER;
BEGIN
    result := array_number($1,$2);
    RETURN result;
END;

DECLARE
 a CONSTANT NUMBER := -20003;
 c CONSTANT NUMBER := -20004;
 b CONSTANT NUMBER := -20005;
 TYPE row_converted_codes IS VARRAY(2) OF NUMBER;
 validate_value row_converted_codes;
 TYPE tab_converted_codes IS TABLE OF row_converted_codes;
 l_converted_codes CONSTANT tab_converted_codes := tab_converted_codes(
    row_converted_sql_codes(-1,     a), 
    row_converted_sql_codes(100,    b),   
    row_converted_sql_codes(-942,   c)   
 );
BEGIN
    FOR i IN l_converted_codes.FIRST .. l_converted_codes.LAST LOOP
      DBMS_OUTPUT.PUT_LINE('tab_converted_codes element: '||i);
      validate_value := l_converted_codes(i);
      FOR j in validate_value.FIRST .. validate_value.LAST LOOP
          DBMS_OUTPUT.PUT_LINE('  inside row_converted_codes ['||j||'] => '||validate_value(j));
      END LOOP;
    END LOOP;
END;

Output:

edb=# DECLARE
edb-#  a CONSTANT NUMBER := -20003;
edb$#  c CONSTANT NUMBER := -20004;
edb$#  b CONSTANT NUMBER := -20005;
edb$#  TYPE row_converted_codes IS VARRAY(2) OF NUMBER;
edb$#  validate_value row_converted_codes;
edb$#  TYPE tab_converted_codes IS TABLE OF row_converted_codes;
edb$#  l_converted_codes CONSTANT tab_converted_codes := tab_converted_codes(
edb$#     row_converted_sql_codes(-1,     a), 
edb$#     row_converted_sql_codes(100,    b),   
edb$#     row_converted_sql_codes(-942,   c)   
edb$#  );
edb$# BEGIN
edb$#     FOR i IN l_converted_codes.FIRST .. l_converted_codes.LAST LOOP
edb$#       DBMS_OUTPUT.PUT_LINE('tab_converted_codes element: '||i);
edb$#       validate_value := l_converted_codes(i);
edb$#       FOR j in validate_value.FIRST .. validate_value.LAST LOOP
edb$#           DBMS_OUTPUT.PUT_LINE('  inside row_converted_codes ['||j||'] => '||validate_value(j));
edb$#       END LOOP;
edb$#     END LOOP;
edb$# END;
tab_converted_codes element: 1
  inside row_converted_codes [1] => -1
  inside row_converted_codes [2] => -20003
tab_converted_codes element: 2
  inside row_converted_codes [1] => 100
  inside row_converted_codes [2] => -20005
tab_converted_codes element: 3
  inside row_converted_codes [1] => -942
  inside row_converted_codes [2] => -20004

EDB-SPL Procedure successfully completed

Next time when you post, please include more information. Throwing some random code makes response slow and doesn't help in resolving the problem.

Adventurer

Re: NESTED TABLE TYPES AND VARRAY TYPES

Hi

Here is complete code

 

Create  Package Conversion_Util is

 

 a CONSTANT NUMBER := -20003;
c CONSTANT NUMBER := -20004;
b CONSTANT NUMBER := -20005;

 

TYPE row_converted_codes IS VARRAY(2) OF NUMBER;
validate_value row_converted_codes;


TYPE tab_converted_codes IS TABLE OF row_converted_codes;
l_converted_codes CONSTANT tab_converted_codes := tab_converted_codes(
    row_converted_sql_codes(-1,     a),
    row_converted_sql_codes(100,    b),  
    row_converted_sql_codes(-942,   c)  
);

 

Procedure clear_log(....);

Procedure insert_log(...);

 

End Conversion_Util;

 

EDB Team Member

Re: NESTED TABLE TYPES AND VARRAY TYPES

Where is complete code? I cannot see.

 

I have already responded in my previous response with an example. Please have a look at the sample code and try to change your code accordingly.

Community Manager

Re: NESTED TABLE TYPES AND VARRAY TYPES

Hi Angie,

 

Firstly, thank you for being so active in PostgresRocks!  This is exactly the sort of enthusiasm we're hoping for.  This is a good example of the sorts of complex scenarios which are likely best solved through a larger, more formal, effort - Vibhor, one of the preeminent PostgreSQL architects globally, can provide his insight in limited spurts, but your needs are definitely best viewed through the lens of a Postgres professional.  The reasoning is that, while Vibhor has done a stellar job of providing point-by-point reflections on the questions posed, the solution is only as good as how well anyone understands the overall intent.  

 

For example, providing insights on, say, nested tables and array types, will yield a possible answer or resolution to the single question - but what drives the question?  What is the design which drives the use of nested tables?  I would certainly suggest a separate conversation with the EDB team if possible, otherwise we can't guarantee that the success of questions and answers here will translate to success in your efforts.

 

Thanks and Regards,

 

Jamie