oracle trim function with numeric pram-CodePudding

Declare a Type as a table of number(38,0). In a procedure of function.

code example:

ids_list := Type(....); // load data to variable
ids_list.trim(count);

I don't quite understand what will do to trim a list with a numeric list with numeric parameter. What will ids_list.trim(count) happens?

CodePudding user response：

If I understood you correctly, you're talking about a collection and its trim method. If that's so, then documentation says:

Decreasing the Size of a Collection (TRIM Method)

This procedure has two forms:

TRIM removes one element from the end of a collection.

TRIM(n) removes n elements from the end of a collection.

If you want to remove all elements, use DELETE without parameters.

Code you posted doesn't do anything because of

SQL> declare
  2     type numlist is table of number;
  3     n    numlist := numlist(1,2,3, 10);
  4  begin
  5    n.trim(count);
  6  end;
  7  /
  n.trim(count);
         *
ERROR at line 5:
ORA-06550: line 5, column 10:
PLS-00204: function or pseudo-column 'COUNT' may be used inside a SQL statement only
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored


SQL>

Perhaps you meant to use the count method instead (n.count)?

SQL> declare
  2     type numlist is table of number;
  3     n    numlist := numlist(1,2,3, 10);
  4  begin
  5    dbms_output.put_line('Number of elements in that collection = ' || n.count);
  6
  7    n.trim(n.count);
  8
  9   dbms_output.put_line('Number of elements in that collection AFTER trim = ' || n.count);
 10  end;
 11  /
Number of elements in that collection = 4
Number of elements in that collection AFTER trim = 0

PL/SQL procedure successfully completed.

SQL>

If so, then - as you can see - it removes all elements from the collection.