Using trim - collection method, PL-SQL Programming

Using TRIM

This process has two forms. The TRIM removes an element from the end of the collection. The TRIM(n) removes the n elements from the end of the collection. For e.g. this statement removes all the last three elements from the nested table courses:

courses.TRIM(3);

If n is bigger than COUNT, then TRIM(n) raises SUBSCRIPT_BEYOND_COUNT.

TRIM operates on the internal size of the collection. Therefore, if TRIM encounters deleted elements, then it includes them in its tally. Consider the example shown below:

DECLARE

TYPE CourseList IS TABLE OF VARCHAR2(10);

courses CourseList;

BEGIN

courses := CourseList('Biol 4412', 'Psyc 3112', 'Anth 3001');

courses.DELETE(courses.LAST); -- delete element 3

/* At this point, COUNT equals 2, the number of valid

elements remaining. So, you might expect the next

statement to blank the nested table by trimming

elements 1 and 2. Instead, it trims valid element 2

and the deleted element 3 as TRIM includes deleted

elements in its tally. */

courses.TRIM(courses.COUNT);

DBMS_OUTPUT.PUT_LINE(courses(1)); -- prints 'Biol 4412'

In normal, do not depend on the interaction between the TRIM and DELETE. It is better to treat nested tables such as fixed-size arrays and use only DELETE, or to treat them like the stacks and use only TRIM and EXTEND.

The PL/SQL does not keep placeholders for the trimmed elements. As a result, you cannot replace a trimmed element just by assigning it a new value.

Posted Date: 10/4/2012 3:18:44 AM | Location : United States







Related Discussions:- Using trim - collection method, Assignment Help, Ask Question on Using trim - collection method, Get Answer, Expert's Help, Using trim - collection method Discussions

Write discussion on Using trim - collection method
Your posts are moderated
Related Questions
Table Literals - SQL One might expect SQL to support table literals in the manner illustrated in Example 2.2, but in fact that is not a legal SQL expression. Example: Not a

Bulk Fetching The illustration below shows that you can bulk-fetch from a cursor into one or more collections: DECLARE TYPE NameTab IS TABLE OF emp.ename%TYPE; TYPE S

First Step at defining type SID in SQL CREATE TYPE SID AS ( C VARCHAR(5) ) ; Explanation: TYPE SID announces that a type named SID is being defined to the syst

Cursors The Oracle uses work areas to execute the SQL statements and to store process information. A PL/SQL construct known as the cursor. Let's you assume name a work area and

Use Bulk Binds If SQL statements execute inside a loop using the collection elements as bind variables, context switching between the PL/SQL & SQL engines can slow down the ex

Use the PLS_INTEGER Datatype When you require to declare an integer variable, use the datatype PLS_INTEGER that is the most efficient numeric type. That is as the PLS_INTEGER

Grouping and Ungrouping in SQL Example specifying EXAM_MARK in place of COURSE in the main FROM clause. Example: Obtaining C_ER2 from EXAM_MARK SELECT CourseId, CAST

Providing Results of Queries Expressing queries in SQL is the (big) subject. Here I present just a simple example to give you the flavour of things to come in those chapters.

Transaction Visibility As the figure shows, the changes made by an autonomous transaction become visible to another transaction whenever the autonomous transaction commits. Th

Using Cursor Attributes To process the SQL data manipulation statements, the SQL engine must opens an implicit cursor named SQL. This cursor's attributes (%FOUND, %NOTFOUND, %