How to display a record from an array(nested table) [message #630452] |
Sun, 28 December 2014 16:28 |
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 #630454 is a reply to message #630453] |
Sun, 28 December 2014 16:44 |
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 #630458 is a reply to message #630457] |
Sun, 28 December 2014 17:16 |
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.
|
|
|