Displaying records using %ROWTYPE

[dilippanda]

Hi Experts,

I want to display all the elements of a %ROWTYPE type.How can i achieve that.

I have written one sample code.

DECLARE
TYPE dept_info IS record dept%ROWTYPE;
temp_dept_info dept_info;
BEGIN
SELECT *
BULK COLLECT
INTO temp_dept_info
FROM DEPT;
FOR i IN 1 .. temp_dept_info.LAST LOOP
DBMS_OUTPUT.PUT_LINE(temp_dept_info(i));
END LOOP;
END;

But it is showing error.

Here i am trying to display values of each column of dept Table.

Please guide me how to do this?
Waiting for your quick response.

Thanks,
Dilip

[debasisdas]

try this sample code
-------------------------------------

declare
type tt is table of emp%rowtype;
aa tt;
begin
select * bulk collect into aa from emp;
for i in aa.first .. aa.last loop
dbms_output.put_line (aa(i).ename||'  '||aa(i).sal);
end loop;
end;
 

[dilippanda]

Hi,

Thanks for the reply.

I am trying to fetch each element using your suggestion with out knowing the column names to make it dynamic.

I have written one code for this.

DECLARE
TYPE dept_info IS TABLE OF dept%ROWTYPE;
temp_dept_info dept_info;
TYPE tab_objects IS TABLE OF VARCHAR2(50);
temp_objects tab_objects;
BEGIN
SELECT column_name
BULK COLLECT
INTO temp_objects
FROM user_tab_columns
WHERE table_name='DEPT';

SELECT *
BULK COLLECT
INTO temp_dept_info
FROM DEPT;

FOR i IN 1 .. temp_dept_info.LAST LOOP
DBMS_OUTPUT.PUT_LINE(temp_dept_info(i).temp_object s(1)||temp_dept_info(i).temp_objects(2)||temp_dept _info(i).temp_objects(3));
END LOOP;

END;

But it is throwing me the error:
ORA-06550: line 25, column 44:
PLS-00302: component 'TEMP_OBJECTS' must be declared
ORA-06550: line 25, column 5:
PL/SQL: Statement ignored

Please help me.

Thanks,
Dilip

try this sample code
-------------------------------------

declare
type tt is table of emp%rowtype;
aa tt;
begin
select * bulk collect into aa from emp;
for i in aa.first .. aa.last loop
dbms_output.put_line (aa(i).ename||'  '||aa(i).sal);
end loop;
end;