Home » SQL & PL/SQL » SQL & PL/SQL » How to check whether Varray is Null or Not.--V.Urgent
How to check whether Varray is Null or Not.--V.Urgent [message #224049] Mon, 12 March 2007 14:14 Go to next message
sireeshraju
Messages: 32
Registered: October 2005
Member

Hi,

I have to create a proceudre which takes varray as an Input and returns null if the varray is null or if values exists in the varray then it will be inserted into a table.How can i check the varray input is null or not..if the varray has values then the values needs to be inserted one by one into a table.

CREATE OR REPLACE  
TYPE UITGeneralAuditObj AS OBJECT
(
Field_name	varchar2(30)
, Old_value	varchar2(50)
, New_value	varchar2(50)
)
;
/

CREATE OR REPLACE
TYPE UITGeneralAudit_Varray  IS VARRAY(100) OF UITGeneralAuditObj 
;
/

f1	10	20
f2	20	30
.
.
f5	10	20

Please help me with sample code as this is very urgent.
Thanks in Advance...

Re: How to check whether Varray is Null or Not.--V.Urgent [message #224057 is a reply to message #224049] Mon, 12 March 2007 15:24 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
I guess you have graduated from URGENT to VERY URGENT. Maybe a few more VERY's in there would help.
Do you ever post a question that is not URGENT? You don't seem to be managing your time too well.

[Updated on: Mon, 12 March 2007 15:26]

Report message to a moderator

Re: How to check whether Varray is Null or Not.--V.Urgent [message #224060 is a reply to message #224057] Mon, 12 March 2007 16:26 Go to previous messageGo to next message
sireeshraju
Messages: 32
Registered: October 2005
Member

Hi JoY,

I posted some Queries,which i never mentioned Urgent..If u know the answer,then reply or else mind your work.In the Faq manual it says i can address the urgent posts as 'Urgent',so i did.Once again stop lecturing about my time management and mind your work.

Re: How to check whether Varray is Null or Not.--V.Urgent [message #224064 is a reply to message #224060] Mon, 12 March 2007 17:04 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Actually, it says
Sticky
Avoid the following keywords: urgent/please/help/now/immediately (and derivatives)
Re: How to check whether Varray is Null or Not.--V.Urgent [message #224065 is a reply to message #224064] Mon, 12 March 2007 17:08 Go to previous messageGo to next message
sireeshraju
Messages: 32
Registered: October 2005
Member

Hi Littlefoot,

Can I use "URGNT PLZ!!!!!!!!!!!!" Keyword
Re: How to check whether Varray is Null or Not.--V.Urgent [message #224092 is a reply to message #224065] Tue, 13 March 2007 01:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You could better read about varrays and nulls then rant about whether or not you can use "urgent" in your topic title or post. If your work is indeed that urgent and you will fail to get it done in time, will you please (not plz!) point your manager to this thread?
I am sure he will understand it is our fault you are overdue. We should have helped you!
Re: How to check whether Varray is Null or Not.--Urgent Please [message #224281 is a reply to message #224092] Tue, 13 March 2007 13:17 Go to previous messageGo to next message
sireeshraju
Messages: 32
Registered: October 2005
Member

Hi All,

Can someone answer my query please...It is urgent
Re: How to check whether Varray is Null or Not.--V.Urgent [message #224314 is a reply to message #224049] Tue, 13 March 2007 19:19 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>Can someone answer my query please...It is urgent
Please explain why it is urgent for me to solve this problem for you?
Have you actually tried reading the fine manuals found at http://tahiti.oracle.com?
With advice, you can have it good, fast or cheap. Pick any two.

FWIW - http://asktom.oracle.com has many fine coding examples.
Re: How to check whether Varray is Null or Not.--V.Urgent [message #224436 is a reply to message #224314] Wed, 14 March 2007 05:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm obviously missing something here.
What is wrong with simply checking the value of <varray>.count?
SQL> set serveroutput on size 10000
SQL> 
SQL> declare
  2    type ty_arr is varray(100) of varchar2(10);
  3    v_arr  ty_arr := ty_arr();
  4  begin
  5    dbms_output.put_line('Rows in Varray: '||v_arr.count);
  6  end;
  7  /
Rows in Varray: 0

PL/SQL procedure successfully completed.
Re: How to check whether Varray is Null or Not.--V.Urgent [message #224462 is a reply to message #224436] Wed, 14 March 2007 06:48 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
...or (considering OP issue) what is wrong with:

SQL> declare
  2      type ty_arr is varray(100) of varchar2(10);
  3      v_arr  ty_arr;
  4  begin
  5      if v_arr is null then
  6       dbms_output.put_line('Varray is null');
  7      end  if;
  8  end;
  9  /
Varray is null

PL/SQL procedure successfully completed.


?

Rgds.
Re: How to check whether Varray is Null or Not.--V.Urgent [message #224483 is a reply to message #224462] Wed, 14 March 2007 07:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
True - I'd forgotten that 'Has no rows' isn't the same as 'IS NULL' for a VARRAY.
Re: How to check whether Varray is Null or Not [message #224583 is a reply to message #224049] Wed, 14 March 2007 13:09 Go to previous messageGo to next message
sireeshraju
Messages: 32
Registered: October 2005
Member

Hi Jrow & dmitry

Thanks a lot for your replies.I have another doubt, how would we insert the varray elemenets(UITGeneralAudit_Varray) based on the Object (UITGeneralAuditObj) into a dummy table. The rows will be like
f1 10 20
f2 20 30
;
;
;
f5 10 20

After checking whether Varray is null or not these rows should be inserted into a table.I used a for loop to insert the rows into the table,but it's not working fine.I am not sure about the syntax.So please help me resolve this issue.How could i code this?

Thanks in advance
Re: How to check whether Varray is Null or Not [message #224595 is a reply to message #224583] Wed, 14 March 2007 13:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:
but it's not working fine

Maybe you could post your code and tell why you think it is not working fine. Then we might be able to pinpoint your problem in your code.
Re: How to check whether Varray is Null or Not [message #224611 is a reply to message #224049] Wed, 14 March 2007 14:12 Go to previous messageGo to next message
sireeshraju
Messages: 32
Registered: October 2005
Member

Hi Frank,

Can i do something like this.

create or replce procedure varray_exist(varray_in in UITGeneralAudit_Varray) IS
Begin
if varray_in is null then
  dbms_output.put_line('Varray is null');
else
for  i in 1..100 
loop
insert into dummy_varray values(varray_in(i).Field_name,varray_in(i).Old_value,varray_in(i).New_value);
end loop;
  end  if;
end;
Re: How to check whether Varray is Null or Not [message #224699 is a reply to message #224611] Thu, 15 March 2007 03:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I don't know. What happens if you do that?
I would not use the hardcoded 1..100, but I assume this is just an example..
Re: How to check whether Varray is Null or Not [message #224701 is a reply to message #224611] Thu, 15 March 2007 03:25 Go to previous message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
Why do you use fixed boundaries in loop ?

for i in 1..100

VARRAY(100) OF ... doesn't mean varray variable really has 100 elements - it's just an upper limit of element number.

Use for i in 1..varray_in.count loop

SQL> create or replace type dummy_obj
  2  is object (
  3  field_name varchar2(10), old_value varchar2(10), new_value varchar2(10)
  4  );
  5  /

Type created.

SQL> create or replace type dummy_var is varray(100) of dummy_obj;
  2  /

Type created.

SQL> create table dummy_tab (col1 varchar2(10),
  2  col2 varchar2(10), col3 varchar2(10));

Table created.

SQL> declare
  2   dv dummy_var := dummy_var(dummy_obj('A','10','20'),dummy_obj('B','1','2'));
  3  begin
  4   for i in 1..100 loop
  5    insert into dummy_tab values(dv(i).field_name, dv(i).old_value,dv(i).new_value);
  6   end loop;
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 5


SQL> edit
Wrote file afiedt.buf

  1  declare
  2   dv dummy_var := dummy_var(dummy_obj('A','10','20'),dummy_obj('B','1','2'));
  3  begin
  4   for i in 1..dv.count loop
  5    insert into dummy_tab values(dv(i).field_name, dv(i).old_value,dv(i).new_value);
  6   end loop;
  7* end;
SQL> /

PL/SQL procedure successfully completed.

Rgds.
Previous Topic: Top 20% of Customers
Next Topic: PL/SQL: numeric or value error: character to number conversion error
Goto Forum:
  


Current Time: Wed Dec 07 14:52:23 CST 2016

Total time taken to generate the page: 0.15823 seconds