Home » SQL & PL/SQL » SQL & PL/SQL » How to display a record from an array(nested table) (O/S:Windows 7 PRO; Product: Oracle; version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
How to display a record from an array(nested table) [message #630452] Sun, 28 December 2014 16:28 Go to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
Hi there,

I am assigning rows from a table and then once this row is populated, I am trying to display it on the screen using DBMS_OUTPUT.put_line, but unable to. Can any one help?

Here's the code:
a)Employees table

CREATE TABLE EMPLOYEES 
   (	EMPLOYEE_ID NUMBER(6,0), 
	FIRST_NAME VARCHAR2(20 BYTE), 
	LAST_NAME VARCHAR2(25 BYTE),
	EMAIL VARCHAR2(25 BYTE),
	PHONE_NUMBER VARCHAR2(20 BYTE), 
	HIRE_DATE DATE,
	JOB_ID VARCHAR2(10 BYTE),
	SALARY NUMBER(8,2), 
	COMMISSION_PCT NUMBER(2,2), 
	MANAGER_ID NUMBER(6,0), 
	DEPARTMENT_ID NUMBER(4,0));
	


b)Row in the table

INSERT INTO employees(
	EMPLOYEE_ID, 
	FIRST_NAME,
	LAST_NAME,
	EMAIL,
	PHONE_NUMBER,
	HIRE_DATE,
	JOB_ID,          
	SALARY,         
	COMMISSION_PCT,              
	MANAGER_ID,       
	DEPARTMENT_ID )
      
VALUES (
        175,
        'Alyssa',
        'Hutton'
        'AHUTTON'
        '011.44.1644.429266',
         NULL
        'SA_REP',
         8800
         0.25,
        149,
        NULL)
COMMIT;


Data is committed.

Now the code to fetch record from a table using SELECT INTO clause

     --Using an implicit SELECT INTO to populate a single row of data in a collection:
SET SERVEROUTPUT ON
SET ECHO ON

DECLARE
	TYPE emp_copy_t IS TABLE OF employees%ROWTYPE;
	l_emps  emp_copy_t;

BEGIN
	l_emps:=emp_copy_t();
	l_emps.EXTEND;
	
	SELECT * INTO l_emps(1)
	FROM   	 employees
	WHERE  	 employee_id = 175;
	
	--DBMS_OUTPUPT.put_line('Employee details are'||' '|| l_emps(1));
	--DBMS_OUTPUPT.put_line('Employee details are'||' '|| l_emps);
	 --DBMS_OUTPUPT.put_line('Employee details are'||' '|| l_emps(l_emps(1));
	 --DBMS_OUTPUPT.put_line('Employee details are'||' '|| l_emps(l_emps());
END;
/


My question is how do I show the record details on the screen. I have tried with the above DBMS_OUTPUT's but to no avail; getting hit by compilation error. Can any one guide me on this? I am new to COLLECTIONS topic.

Thanks in advance
Sandeep

[Updated on: Sun, 28 December 2014 16:30]

Report message to a moderator

Re: How to display a record from an array(nested table) [message #630453 is a reply to message #630452] Sun, 28 December 2014 16:40 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
WHAT "compile error"?
Re: How to display a record from an array(nested table) [message #630454 is a reply to message #630453] Sun, 28 December 2014 16:44 Go to previous messageGo to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
These are the errors that I am getting hit by when I use:

Quote:
DBMS_OUTPUPT.put_line('Employee details are'||' '|| l_emps(1));


Error report -
ORA-06550: line 13, column 24:
PLS-00306: wrong number or types of arguments in call to '||'
ORA-06550: line 13, column 2:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:



When I use Quote:
DBMS_OUTPUPT.put_line('Employee details are'||' '|| l_emps);


same error as above.

[Updated on: Sun, 28 December 2014 16:45]

Report message to a moderator

Re: How to display a record from an array(nested table) [message #630455 is a reply to message #630454] Sun, 28 December 2014 17:00 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
As usual, did you RTFM? DBMS_OUTPUT.PUT_LINE takes one parameter which is of scalar type VARCHAR2 while l_emps(1) is of composite RECORD type. You must reference individual attributes separately:

DBMS_OUTPUPT.put_line('Employee details are'||' '|| l_emps(1).first_name || ' ' ||  l_emps(1).last_name || ' ' ||  l_emps(1).salary);


SY.

[Updated on: Sun, 28 December 2014 17:01]

Report message to a moderator

Re: How to display a record from an array(nested table) [message #630456 is a reply to message #630455] Sun, 28 December 2014 17:05 Go to previous messageGo to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
What is RTFM?
Re: How to display a record from an array(nested table) [message #630457 is a reply to message #630456] Sun, 28 December 2014 17:08 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Read The Fine Manual (you might find other interpretations in the web Laughing ).

SY.
Re: How to display a record from an array(nested table) [message #630458 is a reply to message #630457] Sun, 28 December 2014 17:16 Go to previous message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
Thanks SY for the abbreviation. I was practicing the above example from Steven Feuerstein's Oracle PL/SQL Programming, Fifth Edition.

He does not have the DBMS_OUTPUT line there. I was trying it on my own to see and populate the data on the screen. Anyways Thanks a lot for guiding me on this.
Previous Topic: When NOLOGGING will not be in effect
Next Topic: SQL query to calculate distance and update
Goto Forum:
  


Current Time: Fri Apr 19 18:21:01 CDT 2024