CODEWITHSUNDEEP
plsql
TriHard8
TriHard8

Reputation: 592

Define variable to to be type of record attribute pl/sql

Suppose I have the following code snippet in PL/SQL:

type my_record is record(
    CUST_ID   number(7);
    CUST_NAME varchar2(200);
);

Now i want to declare a variable which takes the same type as cust_name:

my_title my_record.cust_name%type;

This is what I tried and I get error 'PLS-00206:%TYPE must be applied to variable, column, field or attribute...I believe I'm doing what Oracle's documentation is showing...http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/type_attribute.htm

I'd prefer not to hard code the variable type, in the situation the CUST_NAME type is ever changed.

Upvotes: 0

Views: 652

Answers (3)

William Robertson
William Robertson

Reputation: 16001

You need to do it the other way around. If you declare a variable first (using %type if you want), the record definition can reference that as somevariable%type. Or else declare a subtype first and reference that in both places.

Upvotes: 1

Prashant Mishra
Prashant Mishra

Reputation: 647

see the very first line of the article, you posted. it reads as "The %TYPE attribute lets you declare a constant, variable, field, or parameter to be of the same data type a previously declared variable, field, record, nested table, or database column."

Here you tried applying %TYPE on a record type and not on record.

Declare

Type my_record is record(
    CUST_ID   number(7);
    CUST_NAME varchar2(200);
);

my_var my_record ;
my_new_var my_var.CUST_NAME%type;
begin
null;
end;

Upvotes: 1

Mike
Mike

Reputation: 2005

Create variable of record type. And apply %type to this variable;

DECLARE
 type my_record is record(
    CUST_ID   number(7),
    CUST_NAME varchar2(200)
 );
 myRec my_record;
 my_title myRec.cust_name%type;

BEGIN
   NULL;
END;

Upvotes: 0

Related Questions