Home » SQL & PL/SQL » SQL & PL/SQL » problem in record type (oracle 11g)
problem in record type [message #640392] Tue, 28 July 2015 00:34 Go to next message
somu1984
Messages: 2
Registered: July 2015
Location: chennai
Junior Member
DECLARE
   TYPE emp_name_rec IS RECORD (
      firstname   VARCHAR2 (100),
      lastname    VARCHAR2 (100),
      hiredate    VARCHAR2 (100)
   );
   TYPE t1 IS VARRAY (10) OF emp_name_rec;
   t11   t1;
   TYPE emp_name_rec1 IS RECORD (
      firstname   VARCHAR2 (100),
      lastname    t1,
      hiredate    VARCHAR2 (100)
   );
   TYPE t2 IS VARRAY (10) OF emp_name_rec1;
   t21   t2;
   features_idx pls_integer;
   t12   t1;
BEGIN
   t11 := t1 ();
   FOR i IN 1 .. 10
   LOOP
      t11.EXTEND (1);
      t11 (t11.LAST).firstname := i;
      t11 (t11.LAST).lastname := i;
      t11 (t11.LAST).hiredate := i;
dbms_output.put_line(t11(t11.last).firstname);
   END LOOP;
dbms_output.put_line(t11(t11.last).lastname);
   t21 := t2 ();
   t21.EXTEND (1);
   t21 (t21.LAST).firstname := 'hi';
   t21 (t21.LAST).lastname := t11;
   t21 (t21.LAST).hiredate := 'ho2';
   dbms_output.put_line(t21 (t21.LAST).lastname);
END;


how to print the last name


Lalit : Added code tags

[Updated on: Tue, 28 July 2015 00:52] by Moderator

Report message to a moderator

Re: problem in record type [message #640394 is a reply to message #640392] Tue, 28 July 2015 00:56 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Hi,

Welcome to the forum!

Please read and follow the OraFAQ Forum Guide and How to use [code] tags.
Re: problem in record type [message #640395 is a reply to message #640392] Tue, 28 July 2015 01:04 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
somu1984 wrote on Tue, 28 July 2015 11:04


how to print the last name


SQL> set serveroutput on
SQL> DECLARE
  2  TYPE emp_name_rec
  3  IS
  4    RECORD
  5    (
  6      firstname VARCHAR2 (100),
  7      lastname  VARCHAR2 (100),
  8      hiredate  VARCHAR2 (100) );
  9  TYPE t1 IS VARRAY (10) OF emp_name_rec;
 10  t11 t1;
 11  TYPE emp_name_rec1
 12  IS
 13    RECORD
 14    (
 15      firstname VARCHAR2 (100),
 16      lastname t1,
 17      hiredate VARCHAR2 (100) );
 18  TYPE t2 IS VARRAY (10) OF emp_name_rec1;
 19  t21 t2;
 20  features_idx pls_integer;
 21  t12 t1;
 22  BEGIN
 23    t11 := t1 ();
 24    FOR i IN 1 .. 10
 25    LOOP
 26      t11.EXTEND (1);
 27      t11 (t11.LAST).firstname := i;
 28      t11 (t11.LAST).lastname  := i;
 29      t11 (t11.LAST).hiredate  := i;
 30      dbms_output.put_line(t11(i).lastname);
 31    END LOOP;
 32    --dbms_output.put_line(t11(1).lastname);
 33
 34    t21 := t2 ();
 35    t21.EXTEND (1);
 36    t21 (t21.LAST).firstname := 'hi';
 37    t21 (t21.LAST).lastname  := t11;
 38    t21 (t21.LAST).hiredate  := 'ho2';
 39    --dbms_output.put_line(t11(1).lastname);
 40  END;
 41  /
1
2
3
4
5
6
7
8
9
10

PL/SQL procedure successfully completed.

SQL>
Re: problem in record type [message #640398 is a reply to message #640395] Tue, 28 July 2015 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I think OP problem is not about t11 but about t21 where lastname is not a string but a table of records.

[Updated on: Tue, 28 July 2015 01:21]

Report message to a moderator

Re: problem in record type [message #640399 is a reply to message #640392] Tue, 28 July 2015 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> DECLARE
  2     TYPE emp_name_rec IS RECORD (
  3        firstname   VARCHAR2 (100),
  4        lastname    VARCHAR2 (100),
  5        hiredate    VARCHAR2 (100)
  6     );
  7     TYPE t1 IS VARRAY (10) OF emp_name_rec;
  8     t11   t1;
  9     TYPE emp_name_rec1 IS RECORD (
 10        firstname   VARCHAR2 (100),
 11        lastname    t1,
 12        hiredate    VARCHAR2 (100)
 13     );
 14     TYPE t2 IS VARRAY (10) OF emp_name_rec1;
 15     t21   t2;
 16     features_idx pls_integer;
 17     t12   t1;
 18  BEGIN
 19     t11 := t1 ();
 20     FOR i IN 1 .. 10
 21     LOOP
 22        t11.EXTEND (1);
 23        t11 (t11.LAST).firstname := i;
 24        t11 (t11.LAST).lastname := i;
 25        t11 (t11.LAST).hiredate := i;
 26  dbms_output.put_line(t11(t11.last).firstname);
 27     END LOOP;
 28  dbms_output.put_line(t11(t11.last).lastname);
 29     t21 := t2 ();
 30     t21.EXTEND (1);
 31     t21 (t21.LAST).firstname := 'hi';
 32     t21 (t21.LAST).lastname := t11;
 33     t21 (t21.LAST).hiredate := 'ho2';
 34     dbms_output.put_line(t21 (t21.LAST).lastname);
 35  END;
 36  /
   dbms_output.put_line(t21 (t21.LAST).lastname);
   *
ERROR at line 34:
ORA-06550: line 34, column 4:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 34, column 4:
PL/SQL: Statement ignored


SQL> DECLARE
  2     TYPE emp_name_rec IS RECORD (
  3        firstname   VARCHAR2 (100),
  4        lastname    VARCHAR2 (100),
  5        hiredate    VARCHAR2 (100)
  6     );
  7     TYPE t1 IS VARRAY (10) OF emp_name_rec;
  8     t11   t1;
  9     TYPE emp_name_rec1 IS RECORD (
 10        firstname   VARCHAR2 (100),
 11        lastname    t1,
 12        hiredate    VARCHAR2 (100)
 13     );
 14     TYPE t2 IS VARRAY (10) OF emp_name_rec1;
 15     t21   t2;
 16     features_idx pls_integer;
 17     t12   t1;
 18  BEGIN
 19     t11 := t1 ();
 20     FOR i IN 1 .. 10
 21     LOOP
 22        t11.EXTEND (1);
 23        t11 (t11.LAST).firstname := i;
 24        t11 (t11.LAST).lastname := i;
 25        t11 (t11.LAST).hiredate := i;
 26  dbms_output.put_line(t11(t11.last).firstname);
 27     END LOOP;
 28  dbms_output.put_line(t11(t11.last).lastname);
 29     t21 := t2 ();
 30     t21.EXTEND (1);
 31     t21 (t21.LAST).firstname := 'hi';
 32     t21 (t21.LAST).lastname := t11;
 33     t21 (t21.LAST).hiredate := 'ho2';
 34     dbms_output.put_line(t21(t21.LAST).lastname(t21(t21.LAST).lastname.last).lastname);
 35  END;
 36  /
1
2
3
4
5
6
7
8
9
10
10
10

PL/SQL procedure successfully completed.

Re: problem in record type [message #640600 is a reply to message #640399] Fri, 31 July 2015 08:33 Go to previous messageGo to next message
somu1984
Messages: 2
Registered: July 2015
Location: chennai
Junior Member
thank Michel Cadot......
Re: problem in record type [message #640601 is a reply to message #640600] Fri, 31 July 2015 08:36 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
So what were you actually looking for? How did you solve your problem? Did Michel's solution solve your problem?

Please feedback in a way which would help others searching for similar issues.
Previous Topic: Joining Two View
Next Topic: SQL Query
Goto Forum:
  


Current Time: Thu Apr 25 00:41:01 CDT 2024