Home » SQL & PL/SQL » SQL & PL/SQL » varray(unable to print)
varray(unable to print) [message #219035] Mon, 12 February 2007 11:33 Go to next message
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hello sir

my table's structure is like tis
 desc department
       Name                                        Null?    Type
       ------------------------------------------- -------- ----------------
1      DEPT_ID                                              NUMBER(2)
2      NAME                                                 VARCHAR2(15)
3      BUDGET                                               NUMBER(11,2)
4      PROJECTS                                             PROJECTLIST
5    4   PROJECT_NO                                         NUMBER
6    4   LIST                                               VARCHAR2(12)
7    4   COST                                               NUMBER

but i m getting error while running my pl/sql block

please help me to overcome by this problem

SQL> ed
Wrote file afiedt.buf

  1  begin
  2   for i in (select * from department)
  3    loop
  4    dbms_output.put_line(i.dept_id||' '||i.name ||'  '||i.budget);
  5    for j in 1..i.projects.count
  6     loop
  7     dbms_output.put_line(i.projects(j));
  8    end loop;
  9   end loop;
 10* end;
SQL> /
ERROR at line 7:
ORA-06550: line 7, column 4:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 7, column 4:
PL/SQL: Statement ignored

Re: varray(unable to print) [message #219105 is a reply to message #219035] Mon, 12 February 2007 23:56 Go to previous messageGo to next message
Messages: 1836
Registered: November 2006
Senior Member

I am not sure (create table script would be better) but it seems your varray contains records. However the parameter of DBMS_OUTPUT.PUT_LINE should be varchar2. So you should manually convert it to varchar2, eg. concatenate the record fields:
dbms_output.put_line( i.projects(j).project_no
               ||' '||i.projects(j).list
               ||' '||i.projects(j).cost );
Re: varray(unable to print) [message #219111 is a reply to message #219105] Tue, 13 February 2007 00:16 Go to previous messageGo to next message
Messages: 36
Registered: November 2006

Yes, it needs varchar2 in older version of oracle 8, 8i. But in 9i and 10g, there is an implicit conversion takes place. However, it is better to use explicit conversion if you know the type in advance. This is better for code portability too.

Jay - Programming with Oracle & Visual C++, Author of http://www.lightsql.com
Re: varray(unable to print) [message #219118 is a reply to message #219035] Tue, 13 February 2007 00:47 Go to previous message
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

why use pl/sql when you can get the same with plain sql.just my opinion.


Previous Topic: Getting Client IP ?
Next Topic: Group by on nested query
Goto Forum:

Current Time: Sun Apr 30 04:24:19 CDT 2017

Total time taken to generate the page: 0.10805 seconds