Home » SQL & PL/SQL » SQL & PL/SQL » Using Nested Table (merged) (Oracle 10g)
Using Nested Table (merged) [message #574338] Wed, 09 January 2013 03:07 Go to next message
userora
Messages: 55
Registered: December 2012
Location: Chennai
Member
Hi All,

Can any one of you please suggest me a material related to collections with Examples and also how to declare table type values. Thanks
Re: Nested tables in collection [message #574339 is a reply to message #574338] Wed, 09 January 2013 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Using Nested Table [message #574958 is a reply to message #574338] Thu, 17 January 2013 02:49 Go to previous messageGo to next message
userora
Messages: 55
Registered: December 2012
Location: Chennai
Member
Hi All,

I have a table named Signal_name in which the data looks like this..

Signal_name
-------------
case_name signal Cit_name Cit_name2
--------- ------- -------- ---------
123 Neutral 123456 123456
122 Positive 789101 789101

Here by using this table i need to check for Cit_name and Cit_name2 is equal . and give the corresponding signal and this should be done using nested table concept in Collections...

Re: Using Nested Table [message #574960 is a reply to message #574958] Thu, 17 January 2013 03:05 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
are you sure about Nested table? Shocked or did u mean Associative arrays...

[Updated on: Thu, 17 January 2013 03:05]

Report message to a moderator

Re: Using Nested Table [message #574962 is a reply to message #574958] Thu, 17 January 2013 03:52 Go to previous messageGo to next message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I merge this topic with your previous one which is on the same subject.
The same answer also applies.

In addition I remind you the rules:
Quote:
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.


In the end you MUST feedback, if not thank, people that helped you, thing you seem to not being able to do in your previous topics and this is a rude behaviour that will not encourage people to help you further.

Regards
Michel
Re: Using Nested Table [message #575020 is a reply to message #574962] Thu, 17 January 2013 22:56 Go to previous messageGo to next message
userora
Messages: 55
Registered: December 2012
Location: Chennai
Member
I am Sorry Michel and Thanks a lot for everyone who helped me.. yes Rishwinger I am sure that it should be in nested table.
Re: Using Nested Table [message #575025 is a reply to message #575020] Thu, 17 January 2013 23:53 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Vnithya,

I didn't understood the requirement properly, so i am unable to digest usage of Nested table
According to what i understood, below may help



SQL> create table signal(case_name number,signal_name varchar2(20),cit_name num
er,cit_name2 number)
  2  ;

Table created.

SQL> insert into signal values(122,'Neutral',111,111);

1 row created.

SQL>
SQL> insert into signal values(123,'Positive',112,112);

1 row created.

SQL>
SQL> insert into signal values(124,'Negative',113,113);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from signal;

 CASE_NAME SIGNAL_NAME            CIT_NAME  CIT_NAME2
---------- -------------------- ---------- ----------
       122 Neutral                     111        111
       123 Positive                    112        112
       124 Negative                    113        113



Used Associative arrays below


SET serveroutput ON
DECLARE
type lst_case_name
IS
  TABLE OF signal.case_name%type INDEX BY pls_integer;
type lst_signal
IS
  TABLE OF signal.signal_name%type INDEX BY pls_integer;
type lst_cit_name
IS
  TABLE OF signal.cit_name%type INDEX BY pls_integer;
type lst_cit_name2
IS
  TABLE OF signal.cit_name2%type INDEX BY pls_integer;
  v_case_name lst_case_name;
  v_signal lst_signal;
  v_cit_name lst_cit_name;
  v_cit_name2 lst_cit_name2;
BEGIN
  SELECT * bulk collect
  INTO v_case_name,
    v_signal,
    v_cit_name,
    v_cit_name2
  FROM signal
  ORDER BY case_name;
  --dbms_output.put_line('Count'||v_case_name.count);
  FOR i IN 1..v_case_name.count
  LOOP
    IF v_cit_name(i)=V_cit_name2(i) THEN
      dbms_output.put_line('Signal_val='||v_signal(i) || ' for case name '||v_case_name(i));
    END IF;
  END LOOP;
END;
/


O/P

Signal_val=Neutral for case name 122
Signal_val=Positive for case name 123
Signal_val=Negative for case name 124

[Updated on: Thu, 17 January 2013 23:55]

Report message to a moderator

Re: Using Nested Table [message #575026 is a reply to message #575025] Fri, 18 January 2013 00:08 Go to previous messageGo to next message
userora
Messages: 55
Registered: December 2012
Location: Chennai
Member
Thanks Rishwinger.. I will try to implement this is in my code and come back with the positive result.. Smile
Re: Using Nested Table [message #575669 is a reply to message #575026] Fri, 25 January 2013 07:50 Go to previous messageGo to next message
Trinityr
Messages: 2
Registered: January 2013
Junior Member
Hey what if

create or replace type aobj is object of(

name varchar2(100),
value varchar2(100)
)

create or replace type aarray is table of aobj ;

create or replace type atable is table of aarray ;
CREATE OR REPLACE PROCEDURE DTLSTEST (

psid IN valarray , -- varray(100) of varchar2(100)
pvid IN VARCHAR2,

pattrs OUT aarray,
O_STATUS OUT VARCHAR2,
)





--I also have a cursor D which fetches two values

TYPE vrow IS TABLE OF d%rowtype;
vrowtable vrow ;



OPEN D;

FETCH D BULK COLLECT INTO vrowtable ;

CLOSE D;

HOW TO ASSIGN vrowtable INTO pattrs ???


Re: Using Nested Table [message #575671 is a reply to message #575669] Fri, 25 January 2013 07:56 Go to previous message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

With any SQL or PL/SQL question, please, Post a working AND COMPLETE Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Use SQL*Plus and copy and paste your session, the WHOLE session, inline in test mode, no image, no attehced file.

Regards
Michel
Previous Topic: Sequence creation
Next Topic: External Table for CSV delimited file skipping columns
Goto Forum:
  


Current Time: Fri Apr 18 08:16:16 CDT 2014

Total time taken to generate the page: 0.13369 seconds