Home » SQL & PL/SQL » SQL & PL/SQL » Selecting from a varray (Merged)
Selecting from a varray (Merged) [message #227868] Thu, 29 March 2007 22:29 Go to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
HI All,

CREATE TYPE v_deptno IS VARRAY(20) OF NUMBER(10);

CREATE TABLE emp_like(empno VARCHAR2(10),ename VARCHAR2(10),deptno v_deptno);

INSERT INTO emp_like VALUES(111,'SAM',v_deptno(10,20,30));
INSERT INTO emp_like VALUES(122,'SAM1',v_deptno(11,22,33));


Now i just want to see the values of Varray alone.How can I do it.Can i do it with the help of cast and Table Keywords.

Thanks
Srini...
Re: How to View Values of a Varray. [message #227869 is a reply to message #227868] Thu, 29 March 2007 22:34 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Using pure SQL, I don't think you can avoid TABLE/CAST.

In PL/SQL, you could select it into a VARRAY collection and then manually iterate through the collection.

Ross Leishman
How to use Varray as a table.. [message #227873 is a reply to message #227868] Thu, 29 March 2007 23:05 Go to previous messageGo to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Hi All,

 DECLARE
 revised v_deptno:=v_deptno(10,20);
 cnt NUMBER(2);
 cnt1 NUMBER(2);
 BEGIN
 SELECT *  INTO cnt,cnt1
 FROM TABLE(CAST(revised AS v_deptno)) ;
 DBMS_OUTPUT.PUT_LINE(cnt);
 END;
 /


It is said that Varray can be used as a normal table when we use Table/cast,if so why the code above is not working when i am trying to insert the values into the variables.I tried this way and it is not working.Please help me out in making me understand how we can use varray as a normal table with some examples.

Thanks in advance..
Re: How to View Values of a Varray. [message #227877 is a reply to message #227869] Thu, 29 March 2007 23:11 Go to previous messageGo to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Hi rleishman,

When Varray can be used as a normal table using Table/cast why can't we do it.OK,do you know how to view the vales of varray alone in PLSQL.Can you tell me which way we can do it..

Best regards
Srini...
Re: How to use Varray as a table.. [message #227879 is a reply to message #227873] Thu, 29 March 2007 23:42 Go to previous messageGo to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
HI,

I have tried something like this and it is working..


  DECLARE
revised v_deptno:=v_deptno(10,20);
cnt NUMBER(2);
cnt1 NUMBER(2);
BEGIN
SELECT column_value into cnt1
     FROM TABLE(CAST(revised AS v_deptno))
       WHERE COLUMN_VALUE = 10;
DBMS_OUTPUT.PUT_LINE(cnt1);
exception
when no_data_found then
NULL;
END;
/


Now I would like to kow how i could send the two values of Varray into the cnt,cnt1 variables at a time without using the condition
where column_value=10
,like the way we move the table values into the variables . How could i do this.

Best regards
Srini.....

[Updated on: Thu, 29 March 2007 23:43]

Report message to a moderator

Re: Selecting from a varray (Merged) [message #227882 is a reply to message #227868] Fri, 30 March 2007 00:37 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Have you tried a simple SQL select (without using PL/SQL)
SELECT column_value FROM TABLE(CAST(v_deptno(10,20) as v_deptno));

Does it not satisfy you?

If you still insist on using it in PL/SQL block, then realize, that you already have the values in a variable. I do not think it is wise to assign it to another variable(s) by SQL.
Use referencing as described in documentation.
Also use collection methods to check whether the array contains desired element.

By the way, for what do you want to use CNT, CNT1 variables?
Re: Selecting from a varray (Merged) [message #228034 is a reply to message #227882] Fri, 30 March 2007 16:27 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Also, the explicit CAST may not be necessary depending on Oracle version.

Why VARRAY anyway? What's wrong with nested tables?
Re: Selecting from a varray (Merged) [message #228039 is a reply to message #227868] Fri, 30 March 2007 21:16 Go to previous messageGo to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Hi William

Let it be with Nested Tables also,not a problem.Can u tell me how to do that.

Regards
Srini..
Re: Selecting from a varray (Merged) [message #228040 is a reply to message #227882] Fri, 30 March 2007 21:33 Go to previous messageGo to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Hi flyboy,

I just want them in cnt and cnt1 for further manipulations...IS it possible?? Please give me a solution how to put them into the variables cnt and cnt1

Regards
srinin..
Re: Selecting from a varray (Merged) [message #228053 is a reply to message #227868] Sat, 31 March 2007 01:25 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hi,

have you looked at the links I provided? The manipulation with collection is described there clearly including some examples.
Quote:
Now i just want to see the values of Varray alone.

The framework is in Example 5-15 in the documentation.
Quote:
how i could send the two values of Varray into the cnt,cnt1 variables at a time

As stated in the first link, it is simple reference (eg. REVISED(1) for the first element). However you should use the EXISTS method as described in Example 5-28.
If you use CNT and CNT1 in another processing, why do you get it as a VARRAY? Also what shall you do if the VARRAY would contain 1, 3, 4 etc. elements?

Just as addition to the previous post: you can use SELECT statement to BULK COLLECT a VARRAY into ... another collection, so I find it totally useless.
Re: Selecting from a varray (Merged) [message #228071 is a reply to message #228040] Sat, 31 March 2007 03:38 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
SQL> CREATE OR REPLACE TYPE integer_vtt AS VARRAY(20) OF INTEGER
  2  /

Type created.

SQL> CREATE TABLE emp_like
  2  ( empno INTEGER PRIMARY KEY, ename VARCHAR2(30), departments INTEGER_VTT );

Table created.

SQL> set lines 50
SQL> desc emp_like
 Name                    Null?    Type
 ----------------------- -------- ----------------
 EMPNO                   NOT NULL NUMBER(38)
 ENAME                            VARCHAR2(30)
 DEPARTMENTS                      INTEGER_VTT

SQL> set lines 120

SQL> SELECT empno, ename, column_value AS deptno
  2  FROM   emp_like, TABLE(departments);

     EMPNO ENAME                              DEPTNO
---------- ------------------------------ ----------
       111 SAM                                    10
       111 SAM                                    20
       111 SAM                                    30
       222 CHRIS                                  11
       222 CHRIS                                  22
       222 CHRIS                                  33

6 rows selected.

SQL> BEGIN
  2      FOR r IN (SELECT * FROM emp_like)
  3      LOOP
  4          DBMS_OUTPUT.PUT_LINE(r.empno || ', ' || r.ename || ', ' ||
  5          r.departments.COUNT || ' departments' );
  6      END LOOP;
  7  END;
  8  /
111, SAM, 3 departments
222, CHRIS, 3 departments

PL/SQL procedure successfully completed.

What isn't working?

btw, I'm not convinced that multivalue columns like this are ever a good idea for tables.
Previous Topic: sql help( how can use floor and ceil)
Next Topic: DBMS_session.set_context
Goto Forum:
  


Current Time: Thu Dec 08 14:41:08 CST 2016

Total time taken to generate the page: 0.73553 seconds