Advanced Datatype

At execution time Oracle will inspect Data Dictionary, locate datatype of customer table�s cust_name field and that will be the datatype for v_cust_name variable.

DECLARE

CURSOR cur_cust IS

SELECT last_name || �,� || first_name

FROM customer ;

rec_cust cur_cust%ROWTYPE

DEFINE TEMP-TABLE tt-customer LIKE customer.

CREATE tt-customer.

%ROWTYPE does not translates into Progress very good. That is a set of variables, one for each column of a table. Their names are variable name, followed by a period and column name. For example, rec_cust.last_name.

DECLARE

CURSOR cur_cust IS

SELECT cust_number,

last_name || �,� || first_name full_name

FROM customer ;

rec_cust cur_cust%ROWTYPE;

BEGIN

OPEN cur_cust;

LOOP

FETCH cur_cust INTO rec_cust;

EXIT WHEN cur_cust%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(rec_cust.cust_number);

DBMS_OUTPUT.PUT_LINE(rec_cust.full_name);

END LOOP;

CLOSE cur_cust;

END;

DEFINE QUERY q-cust FOR customer.

DEFINE TEMP-TABLE rec-cust LIKE customer.

CREATE rec-cust.

OPEN QUERY q-cast FOR EACH customer.

REPEAT:

GET NEXT q-cast.

IF QUERY-OFF-END('q-cust') THEN LEAVE.

BUFFER-COPY customer TO rec-cust.

DISPLAY rec-cust.first_name + rec-cust .last-name.

END.

CLOSE QUERY q-cast.

This format automatically fetches all of the columns defined in the cursor cur_cust into all the appropriate variables in the %ROWTYPE variable rec_cust.

rec_cust.cust_number datatype is inherited from customer table�s cust_number datatype.

rec_cust.full_name is the column alias of concatenated result of last_name and first_name columns. The datatype of rec_cust.full_name is determined by combining 2 datatypes. For ex, if last_name is VARCHAR2(20) and first_name is VARCHAR2(15) then rec_cust.full_name will be VARCHAR2(36). That is 20 + 15 + 1, note the coma.