Home » SQL & PL/SQL » SQL & PL/SQL » How to print (v)array (merged)
How to print (v)array (merged) [message #206770] Fri, 01 December 2006 07:14 Go to next message
mnrnjn
Messages: 61
Registered: November 2006
Location: india
Member
Hi
i have just declared a collection as array . how to print tat array . can any body tell


DECLARE
TYPE EmpTabTyp IS TABLE OF enter%ROWTYPE INDEX BY BINARY_INTEGER;
emp_tab EmpTabTyp;
BEGIN
SELECT * INTO emp_tab(2) FROM enter WHERE name ='mano';
for i in 1..3 loop
DBMS_OUTPUT.PUT_LINE (emp_tab); end loop;
end;

iam getting error in th red line plz help me
Re: how to print array [message #206771 is a reply to message #206770] Fri, 01 December 2006 07:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Please reread your code.
How did you select a row into the array??
The answer you are looking for is in your code..
Re: how to print array [message #206772 is a reply to message #206771] Fri, 01 December 2006 07:19 Go to previous messageGo to next message
mnrnjn
Messages: 61
Registered: November 2006
Location: india
Member
Hi
Yes i selected a row . plz be clear


bye
Re: how to print array [message #206776 is a reply to message #206772] Fri, 01 December 2006 07:31 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
How did you select a row into the array??

"Yes" is not the answer.

While displaying the value also you need to do the same way.
./fa/1598/0/
By
Vamsi
Re: how to print array [message #206777 is a reply to message #206772] Fri, 01 December 2006 07:32 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
DBMS_OUTPUT.PUT_LINE can not print the whole array that simple; you'll have to split it into columns you have selected earlier, for example

DBMS_OUTPUT.PUT_LINE (emp_tab(i).name);

Besides that, it would perhaps be a better idea not to loop through "1 .. 3", but "emp_tab.first .. emp_tab.last".
Re: how to print array [message #206778 is a reply to message #206777] Fri, 01 December 2006 07:38 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Want to add something.
While using "emp_tab.first .. emp_tab.last" check for emp_tab.count > 0

By
Vamsi
Re: how to print array [message #206779 is a reply to message #206777] Fri, 01 December 2006 07:38 Go to previous messageGo to next message
mnrnjn
Messages: 61
Registered: November 2006
Location: india
Member
Hi
DECLARE
2 TYPE EmpTabTyp IS TABLE OF enter%ROWTYPE INDEX BY BINARY_INTEGER;
3 emp_tab EmpTabTyp;
4 BEGIN
5 SELECT * INTO emp_tab(100) FROM enter WHERE name ='test';
6 for i in 1..3 loop
7 DBMS_OUTPUT.PUT_LINE (emp_tab(i).name);
8 end loop;
9 end;
10
11
12 /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 7


SQL> select * from enter;

NAME PASSWORD CATEGORY
---------- ---------- ----------
test test user
mano mano admin



still i get error plz helpme
Re: how to print array [message #206780 is a reply to message #206779] Fri, 01 December 2006 07:39 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Didn't I tell you NOT to use 1 .. 3?
Re: how to print array [message #206890 is a reply to message #206770] Sat, 02 December 2006 00:48 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member


Hi,

You are using a loop from 1..3 but u have entered data into 100
location

so, you are getting no data found

try this

DBMS_OUTPUT.PUT_LINE (emp_tab(100).name);


but i didn't understand your requirement.





Re: how to print array [message #206895 is a reply to message #206890] Sat, 02 December 2006 01:16 Go to previous messageGo to next message
mnrnjn
Messages: 61
Registered: November 2006
Location: india
Member
Hi
I worked . Thanks ..
but wen i created a varray its showing error

DECLARE
TYPE DeptFile IS VARRAY(20) OF enter.name%type not null;
emp_value DeptFile;
begin
select name into emp_value(20) from enter where name='mano';
end;

2 3 4 5 6 7 8
9 /
DECLARE
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 5



can u help me onut wats wrong in this .....
Re: how to print array [message #206898 is a reply to message #206770] Sat, 02 December 2006 01:56 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member


VARRAY(20) 20 is its limit.

Try this


DECLARE
TYPE DeptFile IS VARRAY(20) OF emp.name%type not null;
emp_value DeptFile := DeptFile();
begin
emp_value.extend;
select name into emp_value(1) from emp where name='mano';
dbms_output.put_line(emp_value(1));
end;
Re: how to print array [message #206902 is a reply to message #206895] Sat, 02 December 2006 02:13 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Avoid using varrays if possible. index by pl/sql tables are so much easier to use.
Re: how to print array [message #206908 is a reply to message #206902] Sat, 02 December 2006 03:28 Go to previous messageGo to next message
mnrnjn
Messages: 61
Registered: November 2006
Location: india
Member
Hi
Y indexby tables are easy to use....

emp_value DeptFile := DeptFile();

emp_value.extend;


what does the two lines mean .....
can you explain what these two line does


byee
how to print varray [message #206914 is a reply to message #206770] Sat, 02 December 2006 04:24 Go to previous messageGo to next message
mnrnjn
Messages: 61
Registered: November 2006
Location: india
Member
Hi
I have written varray to print the a row values .

DECLARE
CURSOR c1 IS SELECT * FROM enter;
TYPE DeptFile IS VARRAY(20) OF c1%ROWTYPE;
begin
DBMS_OUTPUT.PUT_LINE ('***** test ********' || DeptFile(1).name);
end;

2 3 4 5 6 7 8
9
10 /
DBMS_OUTPUT.PUT_LINE ('***** test ********' || DeptFile(1).name);
*
ERROR at line 5:
ORA-06550: line 5, column 49:
PLS-00306: wrong number or types of arguments in call to 'DEPTFILE'
ORA-06550: line 5, column 2:
PL/SQL: Statement ignored


can u help me
plz

byeee
Re: How to print (v)array (merged) [message #207190 is a reply to message #206770] Mon, 04 December 2006 09:39 Go to previous message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
Declaring an array does not put any values into it. Look at the code you wrote and note the comments I've placed inside

-- Declaration section of the code
DECLARE
   CURSOR c1 IS SELECT * FROM enter;
   TYPE DeptFile IS VARRAY(20) OF c1%ROWTYPE;
-- Begin the execution

begin
  DBMS_OUTPUT.PUT_LINE ('***** test ********' || DeptFile(1).name);
end;



Where in this code to you select any values and place them in DeptFile? You were closer to your solution with the original code you posted...

DECLARE
2 TYPE EmpTabTyp IS TABLE OF enter%ROWTYPE INDEX BY BINARY_INTEGER;
3 emp_tab EmpTabTyp;
4 BEGIN
5 SELECT * INTO emp_tab(100) FROM enter WHERE name ='test';
6 for i in 1..3 loop
7 DBMS_OUTPUT.PUT_LINE (emp_tab(i).name);
8 end loop;
9 end;


Your mistake here was putting something in a specific array spot. There have also been several recommendations on how to do this with previous posts. Here's some comments and corrections on the original code which will probably do what you want...

-- Declaration section
DECLARE
  TYPE EmpTabTyp IS TABLE OF enter%ROWTYPE INDEX BY BINARY_INTEGER;
  emp_tab EmpTabTyp;
-- Main executable section
  BEGIN
  -- Select the data from the table and place it 
  -- into my array
  SELECT * INTO emp_tab FROM enter WHERE name ='test';
    for i in emp_tab.first..emp_tab.last loop
      DBMS_OUTPUT.PUT_LINE (emp_tab(i).name);
    end loop;
  end;


If you read what the other fine posters have provided to you, you would have had your solution.
Previous Topic: creation of Schema
Next Topic: ORA-22813: operand value eceeds system limits
Goto Forum:
  


Current Time: Fri Dec 09 04:06:10 CST 2016

Total time taken to generate the page: 0.11251 seconds