VARRAY 50 Queries

Basic VARRAY Operations

 

1. Create a VARRAY type and initialize it:

  

   DECLARE

     TYPE varray_type IS VARRAY(5) OF NUMBER;

     my_varray varray_type := varray_type(1, 2, 3, 4, 5);

   BEGIN

     NULL;

   END;

  

 

2. Access elements in a VARRAY:

  

   DECLARE

     TYPE varray_type IS VARRAY(5) OF NUMBER;

     my_varray varray_type := varray_type(10, 20, 30, 40, 50);

   BEGIN

     DBMS_OUTPUT.PUT_LINE('First element: ' || my_varray(1));

     DBMS_OUTPUT.PUT_LINE('Second element: ' || my_varray(2));

   END;

  

 

3. Iterate over a VARRAY:

  

   DECLARE

     TYPE varray_type IS VARRAY(5) OF NUMBER;

     my_varray varray_type := varray_type(10, 20, 30, 40, 50);

   BEGIN

     FOR i IN 1..my_varray.COUNT LOOP

       DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || my_varray(i));

     END LOOP;

   END;

  

 

4. Check the count and limit of a VARRAY:

  

   DECLARE

     TYPE varray_type IS VARRAY(3) OF VARCHAR2(20);

     my_varray varray_type := varray_type('Oracle', 'PL/SQL');

   BEGIN

     DBMS_OUTPUT.PUT_LINE('Count: ' || my_varray.COUNT);

     DBMS_OUTPUT.PUT_LINE('Limit: ' || my_varray.LIMIT);

   END;

  

 

5. Extend a VARRAY:

  

   DECLARE

     TYPE varray_type IS VARRAY(5) OF NUMBER;

     my_varray varray_type := varray_type(10, 20);

   BEGIN

     my_varray.EXTEND;

     my_varray(3) := 30;

     FOR i IN 1..my_varray.COUNT LOOP

       DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || my_varray(i));

     END LOOP;

   END;

  

 

6. Trim elements from a VARRAY:

  

   DECLARE

     TYPE varray_type IS VARRAY(5) OF NUMBER;

     my_varray varray_type := varray_type(10, 20, 30, 40, 50);

   BEGIN

     my_varray.TRIM;

     FOR i IN 1..my_varray.COUNT LOOP

       DBMS_OUTPUT.PUT_LINE('Element after trim ' || i || ': ' || my_varray(i));

     END LOOP;

   END;

  

 

7. Initialize an empty VARRAY:

  

   DECLARE

     TYPE varray_type IS VARRAY(5) OF NUMBER;

     my_varray varray_type := varray_type();

   BEGIN

     NULL;

   END;

  

 

8. Assign values to a VARRAY:

  

   DECLARE

     TYPE varray_type IS VARRAY(5) OF VARCHAR2(20);

     my_varray varray_type;

   BEGIN

     my_varray := varray_type('One', 'Two', 'Three');

     FOR i IN 1..my_varray.COUNT LOOP

       DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || my_varray(i));

     END LOOP;

   END;

  

 

9. Concatenate VARRAY elements into a single string:

  

   DECLARE

     TYPE varray_type IS VARRAY(5) OF VARCHAR2(20);

     my_varray varray_type := varray_type('One', 'Two', 'Three');

     concatenated VARCHAR2(100);

   BEGIN

     FOR i IN 1..my_varray.COUNT LOOP

       concatenated := concatenated || my_varray(i) || ' ';

     END LOOP;

     DBMS_OUTPUT.PUT_LINE('Concatenated string: ' || concatenated);

   END;

  

 

10. Delete elements in a VARRAY:

   

    DECLARE

      TYPE varray_type IS VARRAY(5) OF NUMBER;

      my_varray varray_type := varray_type(10, 20, 30, 40, 50);

    BEGIN

      my_varray.DELETE(2);

      FOR i IN 1..my_varray.COUNT LOOP

        DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || my_varray(i));

      END LOOP;

    END;

   

 

Intermediate VARRAY Operations

 

11. Sort elements in a VARRAY:

   

    DECLARE

      TYPE varray_type IS VARRAY(5) OF NUMBER;

      my_varray varray_type := varray_type(50, 20, 40, 10, 30);

    BEGIN

      FOR i IN 1..my_varray.COUNT LOOP

        FOR j IN i+1..my_varray.COUNT LOOP

          IF my_varray(i) > my_varray(j) THEN

            DECLARE

              temp NUMBER := my_varray(i);

            BEGIN

              my_varray(i) := my_varray(j);

              my_varray(j) := temp;

            END;

          END IF;

        END LOOP;

      END LOOP;

 

      FOR i IN 1..my_varray.COUNT LOOP

        DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || my_varray(i));

      END LOOP;

    END;

   

 

12. Find the maximum value in a VARRAY:

   

    DECLARE

      TYPE varray_type IS VARRAY(5) OF NUMBER;

      my_varray varray_type := varray_type(50, 20, 40, 10, 30);

      max_value NUMBER := my_varray(1);

    BEGIN

      FOR i IN 2..my_varray.COUNT LOOP

        IF my_varray(i) > max_value THEN

          max_value := my_varray(i);

        END IF;

      END LOOP;

      DBMS_OUTPUT.PUT_LINE('Maximum value: ' || max_value);

    END;

   

 

13. Find the minimum value in a VARRAY:

   

    DECLARE

      TYPE varray_type IS VARRAY(5) OF NUMBER;

      my_varray varray_type := varray_type(50, 20, 40, 10, 30);

      min_value NUMBER := my_varray(1);

    BEGIN

      FOR i IN 2..my_varray.COUNT LOOP

        IF my_varray(i) < min_value THEN

          min_value := my_varray(i);

        END IF;

      END LOOP;

      DBMS_OUTPUT.PUT_LINE('Minimum value: ' || min_value);

    END;

   

 

14. Reverse elements in a VARRAY:

   

    DECLARE

      TYPE varray_type IS VARRAY(5) OF NUMBER;

      my_varray varray_type := varray_type(10, 20, 30, 40, 50);

    BEGIN

      FOR i IN 1..my_varray.COUNT / 2 LOOP

        DECLARE

          temp NUMBER := my_varray(i);

        BEGIN

          my_varray(i) := my_varray(my_varray.COUNT - i + 1);

          my_varray(my_varray.COUNT - i + 1) := temp;

        END;

      END LOOP;

 

      FOR i IN 1..my_varray.COUNT LOOP

        DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || my_varray(i));

      END LOOP;

    END;

   

 

15. Sum elements in a VARRAY:

   

    DECLARE

      TYPE varray_type IS VARRAY(5) OF NUMBER;

      my_varray varray_type := varray_type(10, 20, 30, 40, 50);

      total NUMBER := 0;

    BEGIN

      FOR i IN 1..my_varray.COUNT LOOP

        total := total + my_varray(i);

      END LOOP;

      DBMS_OUTPUT.PUT_LINE('Total sum: ' || total);

    END;

   

 

16. Average of elements in a VARRAY:

   

    DECLARE

      TYPE varray_type IS VARRAY(5) OF NUMBER;

      my_varray varray_type := varray_type(10, 20, 30, 40, 50);

      total NUMBER := 0;

      average NUMBER;

    BEGIN

      FOR i IN 1..my_varray.COUNT LOOP

        total := total + my_varray(i);

      END LOOP;

      average := total / my_varray.COUNT;

      DBMS_OUTPUT.PUT_LINE('Average: ' || average);

    END;

   

 

17. Check if an element exists in a VARRAY:

   

    DECLARE

      TYPE varray_type IS VARRAY(5) OF NUMBER;

      my_varray varray_type := varray_type(10, 20, 30, 40, 50);

 

 

      element NUMBER := 30;

      exists BOOLEAN := FALSE;

    BEGIN

      FOR i IN 1..my_varray.COUNT LOOP

        IF my_varray(i) = element THEN

          exists := TRUE;

          EXIT;

        END IF;

      END LOOP;

 

      IF exists THEN

        DBMS_OUTPUT.PUT_LINE('Element ' || element || ' exists in the VARRAY.');

      ELSE

        DBMS_OUTPUT.PUT_LINE('Element ' || element || ' does not exist in the VARRAY.');

      END IF;

    END;

   

 

18. Find the index of an element in a VARRAY:

   

    DECLARE

      TYPE varray_type IS VARRAY(5) OF NUMBER;

      my_varray varray_type := varray_type(10, 20, 30, 40, 50);

      element NUMBER := 30;

      index NUMBER := -1;

    BEGIN

      FOR i IN 1..my_varray.COUNT LOOP

        IF my_varray(i) = element THEN

          index := i;

          EXIT;

        END IF;

      END LOOP;

 

      IF index != -1 THEN

        DBMS_OUTPUT.PUT_LINE('Element ' || element || ' found at index ' || index);

      ELSE

        DBMS_OUTPUT.PUT_LINE('Element ' || element || ' not found in the VARRAY.');

      END IF;

    END;

   

 

19. Merge two VARRAYs:

   

    DECLARE

      TYPE varray_type IS VARRAY(10) OF NUMBER;

      varray1 varray_type := varray_type(1, 2, 3, 4, 5);

      varray2 varray_type := varray_type(6, 7, 8, 9, 10);

      merged_varray varray_type := varray_type();

    BEGIN

      FOR i IN 1..varray1.COUNT LOOP

        merged_varray.EXTEND;

        merged_varray(merged_varray.COUNT) := varray1(i);

      END LOOP;

      FOR i IN 1..varray2.COUNT LOOP

        merged_varray.EXTEND;

        merged_varray(merged_varray.COUNT) := varray2(i);

      END LOOP;

 

      FOR i IN 1..merged_varray.COUNT LOOP

        DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || merged_varray(i));

      END LOOP;

    END;

   

 

20. Remove duplicates from a VARRAY:

   

    DECLARE

      TYPE varray_type IS VARRAY(10) OF NUMBER;

      my_varray varray_type := varray_type(1, 2, 2, 3, 4, 4, 5);

      unique_varray varray_type := varray_type();

      exists BOOLEAN;

    BEGIN

      FOR i IN 1..my_varray.COUNT LOOP

        exists := FALSE;

        FOR j IN 1..unique_varray.COUNT LOOP

          IF unique_varray(j) = my_varray(i) THEN

            exists := TRUE;

            EXIT;

          END IF;

        END LOOP;

        IF NOT exists THEN

          unique_varray.EXTEND;

          unique_varray(unique_varray.COUNT) := my_varray(i);

        END IF;

      END LOOP;

 

      FOR i IN 1..unique_varray.COUNT LOOP

        DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || unique_varray(i));

      END LOOP;

    END;

   

 

Advanced VARRAY Operations

 

21. Use VARRAY in a procedure:

   

    CREATE OR REPLACE PROCEDURE process_varray(p_varray IN number_varray) IS

    BEGIN

      FOR i IN 1..p_varray.COUNT LOOP

        DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || p_varray(i));

      END LOOP;

    END;

    /

 

    DECLARE

      my_varray number_varray := number_varray(5, 10, 15);

    BEGIN

      process_varray(my_varray);

    END;

   

 

22. Return a VARRAY from a function:

   

    CREATE OR REPLACE FUNCTION get_varray RETURN number_varray IS

      my_varray number_varray := number_varray(100, 200, 300);

    BEGIN

      RETURN my_varray;

    END;

    /

 

    DECLARE

      my_varray number_varray;

    BEGIN

      my_varray := get_varray;

      FOR i IN 1..my_varray.COUNT LOOP

        DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || my_varray(i));

      END LOOP;

    END;

   

 

23. Pass a VARRAY to a function:

   

    CREATE OR REPLACE FUNCTION sum_varray(p_varray IN number_varray) RETURN NUMBER IS

      total NUMBER := 0;

    BEGIN

      FOR i IN 1..p_varray.COUNT LOOP

        total := total + p_varray(i);

      END LOOP;

      RETURN total;

    END;

    /

 

    DECLARE

      my_varray number_varray := number_varray(5, 10, 15);

      result NUMBER;

    BEGIN

      result := sum_varray(my_varray);

      DBMS_OUTPUT.PUT_LINE('Sum of VARRAY elements: ' || result);

    END;

   

 

24. Update elements in a VARRAY column in a table:

   

    CREATE OR REPLACE TYPE number_varray IS VARRAY(5) OF NUMBER;

   

    CREATE TABLE varray_table (

      id NUMBER,

      numbers number_varray

    );

 

    BEGIN

      INSERT INTO varray_table VALUES (1, number_varray(10, 20, 30));

      COMMIT;

    END;

    /

 

    DECLARE

      my_varray number_varray;

    BEGIN

      SELECT numbers INTO my_varray FROM varray_table WHERE id = 1;

      my_varray(1) := 100;

      UPDATE varray_table SET numbers = my_varray WHERE id = 1;

      COMMIT;

    END;

   

 

25. Delete elements from a VARRAY column in a table:

   

    DECLARE

      my_varray number_varray;

    BEGIN

      SELECT numbers INTO my_varray FROM varray_table WHERE id = 1;

      my_varray.TRIM;

      UPDATE varray_table SET numbers = my_varray WHERE id = 1;

      COMMIT;

    END;

   

 

26. Aggregate VARRAY elements in a table:

   

    SELECT id, SUM(CAST(column_value AS NUMBER)) AS total

    FROM varray_table, TABLE(numbers)

    GROUP BY id;

   

 

27. Use VARRAY in a trigger:

   

    CREATE OR REPLACE TRIGGER varray_trigger

    AFTER INSERT ON varray_table

    FOR EACH ROW

    BEGIN

      FOR i IN 1..:NEW.numbers.COUNT LOOP

        DBMS_OUTPUT.PUT_LINE('Inserted element: ' || :NEW.numbers(i));

      END LOOP;

    END;

    /

 

    BEGIN

      INSERT INTO varray_table VALUES (2, number_varray(40, 50));

      COMMIT;

    END;

   

 

28. Store VARRAY values in a nested table:

   

    CREATE OR REPLACE TYPE number_varray IS VARRAY(5) OF NUMBER;

   

    CREATE TABLE nested_table (

      id NUMBER,

      numbers number_varray

    );

 

    BEGIN

      INSERT INTO nested_table VALUES (1, number_varray(10, 20, 30));

      COMMIT;

    END;

   

 

29. Retrieve elements from a nested table VARRAY column:

   

    DECLARE

      my_varray number_varray;

    BEGIN

      SELECT numbers INTO my_varray FROM nested_table WHERE id = 1;

      FOR i IN 1..my_varray.COUNT LOOP

        DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || my_varray(i));

      END LOOP;

    END;

   

 

30. Update elements in a nested table VARRAY column:

   

    DECLARE

      my_varray number_varray;

    BEGIN

      SELECT numbers INTO my_varray FROM nested_table WHERE id = 1;

      my_varray(1) := 100;

      UPDATE nested_table SET numbers = my_varray WHERE id = 1;

      COMMIT;

    END;

   

 

31. Merge two VARRAYs in a function:

   

    CREATE OR REPLACE FUNCTION merge_varrays(

      varray1 IN number_varray,

      varray2 IN number_varray

    ) RETURN number_varray IS

      merged_varray number_varray := number_varray();

    BEGIN

      FOR i IN 1..varray1.COUNT LOOP

        merged_varray.EXTEND;

        merged_varray(merged_varray.COUNT) := varray1(i);

      END LOOP;

      FOR i IN 1..varray2.COUNT LOOP

        merged_varray.EXTEND;

        merged_varray(merged_varray.COUNT) := varray2(i);

      END LOOP;

      RETURN merged_varray;

    END;

    /

 

    DECLARE

      varray1 number_varray := number_varray(1, 2, 3);

      varray2 number_varray := number_varray(4, 5

 

, 6);

      merged_varray number_varray;

    BEGIN

      merged_varray := merge_varrays(varray1, varray2);

      FOR i IN 1..merged_varray.COUNT LOOP

        DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || merged_varray(i));

      END LOOP;

    END;

   

 

32. Sort elements in a VARRAY column in a table:

   

    DECLARE

      my_varray number_varray;

    BEGIN

      SELECT numbers INTO my_varray FROM nested_table WHERE id = 1;

      FOR i IN 1..my_varray.COUNT LOOP

        FOR j IN i+1..my_varray.COUNT LOOP

          IF my_varray(i) > my_varray(j) THEN

            DECLARE

              temp NUMBER := my_varray(i);

            BEGIN

              my_varray(i) := my_varray(j);

              my_varray(j) := temp;

            END;

          END IF;

        END LOOP;

      END LOOP;

 

      UPDATE nested_table SET numbers = my_varray WHERE id = 1;

      COMMIT;

    END;

   

 

33. Find the maximum value in a VARRAY column in a table:

   

    DECLARE

      my_varray number_varray;

      max_value NUMBER;

    BEGIN

      SELECT numbers INTO my_varray FROM nested_table WHERE id = 1;

      max_value := my_varray(1);

      FOR i IN 2..my_varray.COUNT LOOP

        IF my_varray(i) > max_value THEN

          max_value := my_varray(i);

        END IF;

      END LOOP;

      DBMS_OUTPUT.PUT_LINE('Maximum value: ' || max_value);

    END;

   

 

34. Find the minimum value in a VARRAY column in a table:

   

    DECLARE

      my_varray number_varray;

      min_value NUMBER;

    BEGIN

      SELECT numbers INTO my_varray FROM nested_table WHERE id = 1;

      min_value := my_varray(1);

      FOR i IN 2..my_varray.COUNT LOOP

        IF my_varray(i) < min_value THEN

          min_value := my_varray(i);

        END IF;

      END LOOP;

      DBMS_OUTPUT.PUT_LINE('Minimum value: ' || min_value);

    END;

   

 

35. Reverse elements in a VARRAY column in a table:

   

    DECLARE

      my_varray number_varray;

    BEGIN

      SELECT numbers INTO my_varray FROM nested_table WHERE id = 1;

      FOR i IN 1..my_varray.COUNT / 2 LOOP

        DECLARE

          temp NUMBER := my_varray(i);

        BEGIN

          my_varray(i) := my_varray(my_varray.COUNT - i + 1);

          my_varray(my_varray.COUNT - i + 1) := temp;

        END;

      END LOOP;

 

      UPDATE nested_table SET numbers = my_varray WHERE id = 1;

      COMMIT;

    END;

   

 

36. Sum elements in a VARRAY column in a table:

   

    DECLARE

      my_varray number_varray;

      total NUMBER := 0;

    BEGIN

      SELECT numbers INTO my_varray FROM nested_table WHERE id = 1;

      FOR i IN 1..my_varray.COUNT LOOP

        total := total + my_varray(i);

      END LOOP;

      DBMS_OUTPUT.PUT_LINE('Total sum: ' || total);

    END;

   

 

37. Average of elements in a VARRAY column in a table:

   

    DECLARE

      my_varray number_varray;

      total NUMBER := 0;

      average NUMBER;

    BEGIN

      SELECT numbers INTO my_varray FROM nested_table WHERE id = 1;

      FOR i IN 1..my_varray.COUNT LOOP

        total := total + my_varray(i);

      END LOOP;

      average := total / my_varray.COUNT;

      DBMS_OUTPUT.PUT_LINE('Average: ' || average);

    END;

   

 

38. Check if an element exists in a VARRAY column in a table:

   

    DECLARE

      my_varray number_varray;

      element NUMBER := 30;

      exists BOOLEAN := FALSE;

    BEGIN

      SELECT numbers INTO my_varray FROM nested_table WHERE id = 1;

      FOR i IN 1..my_varray.COUNT LOOP

        IF my_varray(i) = element THEN

          exists := TRUE;

          EXIT;

        END IF;

      END LOOP;

 

      IF exists THEN

        DBMS_OUTPUT.PUT_LINE('Element ' || element || ' exists in the VARRAY.');

      ELSE

        DBMS_OUTPUT.PUT_LINE('Element ' || element || ' does not exist in the VARRAY.');

      END IF;

    END;

   

 

39. Find the index of an element in a VARRAY column in a table:

   

    DECLARE

      my_varray number_varray;

      element NUMBER := 30;

      index NUMBER := -1;

    BEGIN

      SELECT numbers INTO my_varray FROM nested_table WHERE id = 1;

      FOR i IN 1..my_varray.COUNT LOOP

        IF my_varray(i) = element THEN

          index := i;

          EXIT;

        END IF;

      END LOOP;

 

      IF index != -1 THEN

        DBMS_OUTPUT.PUT_LINE('Element ' || element || ' found at index ' || index);

      ELSE

        DBMS_OUTPUT.PUT_LINE('Element ' || element || ' not found in the VARRAY.');

      END IF;

    END;

   

 

40. Remove duplicates from a VARRAY column in a table:

   

    DECLARE

      my_varray number_varray;

      unique_varray number_varray := number_varray();

      exists BOOLEAN;

    BEGIN

      SELECT numbers INTO my_varray FROM nested_table WHERE id = 1;

      FOR i IN 1..my_varray.COUNT LOOP

        exists := FALSE;

        FOR j IN 1..unique_varray.COUNT LOOP

          IF unique_varray(j) = my_varray(i) THEN

            exists := TRUE;

            EXIT;

          END IF;

        END LOOP;

        IF NOT exists THEN

          unique_varray.EXTEND;

          unique_varray(unique_varray.COUNT) := my_varray(i);

        END IF;

      END LOOP;

 

      UPDATE nested_table SET numbers = unique_varray WHERE id = 1;

      COMMIT;

    END;

   

 

Complex VARRAY Operations

 

41. Use VARRAY with dynamic SQL:

   

    DECLARE

      TYPE number_varray IS VARRAY(5) OF NUMBER;

      my_varray number_varray := number_varray(1, 2, 3);

      dynamic_sql VARCHAR2(100);

    BEGIN

      dynamic_sql := 'BEGIN ' ||

                     ' FOR i IN 1..:1.COUNT LOOP ' ||

                     ' DBMS_OUTPUT.PUT_LINE(''Element '' || i || '': '' || :1(i)); ' ||

                     ' END LOOP; ' ||

                     'END;';

      EXECUTE IMMEDIATE dynamic_sql USING my_varray;

    END;

   

 

42. Use VARRAY in a package:

   

    CREATE OR REPLACE PACKAGE varray_pkg IS

      TYPE number_varray IS VARRAY(5) OF NUMBER;

      PROCEDURE display_varray(p_varray IN number_varray);

    END;

    /

 

    CREATE OR REPLACE PACKAGE BODY varray_pkg IS

      PROCEDURE display_varray(p_varray IN number_varray) IS

      BEGIN

        FOR i IN 1..p_varray.COUNT LOOP

          DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || p_varray(i));

        END LOOP;

      END;

    END;

    /

 

    DECLARE

      my_varray varray_pkg.number_varray := varray_pkg.number_varray(10, 20, 30);

    BEGIN

      varray_pkg.display_varray(my_varray);

    END;

   

 

43. Combine VARRAY with associative arrays:

   

    DECLARE

      TYPE number_varray IS VARRAY(5) OF NUMBER;

      TYPE assoc_array IS TABLE OF number_varray INDEX BY VARCHAR2(20);

      my_assoc_array assoc_array;

    BEGIN

      my_assoc_array('First') := number_varray(1, 2, 3);

      my_assoc_array('Second') := number_varray(4, 5, 6);

 

      FOR i IN 1..my_assoc_array('First').COUNT LOOP

        DBMS_OUTPUT.PUT_LINE('First array element ' || i || ': ' || my_assoc_array('First')(i));

      END LOOP;

      FOR i IN 1..my_assoc_array('Second').COUNT LOOP

        DBMS_OUTPUT.PUT_LINE('Second array element ' || i || ': ' || my_assoc_array('Second')(i));

      END LOOP;

    END;

   

 

44. Use VARRAY in object types:

   

    CREATE OR REPLACE TYPE number_varray IS VARRAY(5) OF NUMBER;

   

    CREATE OR REPLACE TYPE person_type AS OBJECT (

      name VARCHAR2(20),

      age NUMBER,

      scores number_varray

    );

 

    DECLARE

      person person_type;

    BEGIN

      person := person_type('John Doe', 30, number_varray(80, 85, 90));

    

 

 DBMS_OUTPUT.PUT_LINE('Name: ' || person.name);

      DBMS_OUTPUT.PUT_LINE('Age: ' || person.age);

      FOR i IN 1..person.scores.COUNT LOOP

        DBMS_OUTPUT.PUT_LINE('Score ' || i || ': ' || person.scores(i));

      END LOOP;

    END;

   

 

45. Serialize VARRAY to a JSON string:

   

    CREATE OR REPLACE FUNCTION varray_to_json(p_varray IN number_varray) RETURN CLOB IS

      json_clob CLOB;

    BEGIN

      json_clob := '{ "varray": [';

      FOR i IN 1..p_varray.COUNT LOOP

        json_clob := json_clob || p_varray(i);

        IF i < p_varray.COUNT THEN

          json_clob := json_clob || ', ';

        END IF;

      END LOOP;

      json_clob := json_clob || ' ] }';

      RETURN json_clob;

    END;

    /

 

    DECLARE

      my_varray number_varray := number_varray(1, 2, 3);

      json_string CLOB;

    BEGIN

      json_string := varray_to_json(my_varray);

      DBMS_OUTPUT.PUT_LINE(json_string);

    END;

   

 

46. Deserialize JSON string to a VARRAY:

   

    CREATE OR REPLACE FUNCTION json_to_varray(p_json IN CLOB) RETURN number_varray IS

      my_varray number_varray := number_varray();

      json_element VARCHAR2(100);

      json_clob CLOB;

    BEGIN

      json_clob := p_json;

      WHILE DBMS_LOB.INSTR(json_clob, ',') > 0 LOOP

        json_element := DBMS_LOB.SUBSTR(json_clob, DBMS_LOB.INSTR(json_clob, ',') - 1, 1);

        my_varray.EXTEND;

        my_varray(my_varray.COUNT) := TO_NUMBER(json_element);

        json_clob := DBMS_LOB.SUBSTR(json_clob, LENGTH(json_clob) - LENGTH(json_element) - 1);

      END LOOP;

      RETURN my_varray;

    END;

    /

 

    DECLARE

      json_string CLOB := '{ "varray": [1, 2, 3] }';

      my_varray number_varray;

    BEGIN

      my_varray := json_to_varray(json_string);

      FOR i IN 1..my_varray.COUNT LOOP

        DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || my_varray(i));

      END LOOP;

    END;

   


No comments:

Post a Comment