Hi Siva, Oracle's latest documentation says, "EXTEND(n,i) is the only form that you can use for a collection whose elements have the NOT NULL constraint". But following program works perfectly fine where EXTEND and EXTEND(n) collection methods are being used on a collection variable whose elements in TYPE definition have NOT NULL constraint defined. DECLARE TYPE typ_varray IS VARRAY(10) OF NUMBER NOT NULL; v_typ_varray typ_varray := typ_varray(); BEGIN v_typ_varray.extend; v_typ_varray(1) := 10; v_typ_varray.extend(2); v_typ_varray(2) := 20; v_typ_varray(3) := 30; v_typ_varray.extend(2,1); FOR indx IN v_typ_varray.first..v_typ_varray.last LOOP dbms_output.put_line(v_typ_varray(indx)); END LOOP; END; /
you are one hell of a guy. Kudos to u man.U have an exceptional talent to explain complicated concepts in a simple way. Keep doing a great job. I can even join classes if you are planning to take one.
@Karthik, Thanks bro, Thanks a lot your comment, it means a lot for me :-). Right now I am not talking class, however i have a long term plan to start online class, Please Drop me a mail, whenever I am planning to start class, I will definitely let you know. Thanks & Regards, Siva
Hi siva, hope you are doing good.... my query is....can we have a feature or functionality in Varray using that, there will be no need to initialise and array will get dynamically be extended upto the indexes which ever indexes or memory areas i need at run time?
for example.....lets say i need to store 3 elements at a particular time instance and next time i need 4 elements to get stored in varray.....how we can achieve it?
Hi Vijay, In that case you can use nested table, because the limit on varray is fixed at the definition time. Also, when you use bulk collect, it will automatically extend and load the data dynamically based on the number of values
Hi shiva garu, i was asked in one of the interview that, did you ever face a situation where element in present but the index is missing in a collection? Can you please let me know if that is even possible.
Thank u for ur wonderful vdos...Content is very good...highly recommendable...just have a doubt @4:42...whats d difference between count and last method...both fetches same result
Thank you, both return same value for varray, however for nested table, if we delete any elements in between, then the count and last will be different. count will return the number of elements in the collection, however last will return the subscript of last element.
You cannot delete in varray, however you can set the values to null, if your requirement is to delete elements in between, then you can use nested table
@@SivaAcademy i have a question(pl/sql) just want to understand the question...Write a PL/SQL code using CURSORS which can fetch and count the number of records from customers (SSN, NAME, PHONE, BILL, CITY) table who have salary range given by users as parameters of cursors. "the salary range gieven by users as parameters" i do not understand it..
The question is asking for usage of parameterized cursor, you can look into this video for more details on what and how to use parameterized cursor th-cam.com/video/917dFN2Cc6M/w-d-xo.html
Again, absolute class, clear, perfectly explained, thanks for such videos...loved it..have not found such explanation till date...🙏
My pleasure thanks 🙏💐
Thank you so much Siva. Your explanation of topics and concepts are awesome. Please make a video on bind variables and ref cursor.
@Vaibhav, Thanks for your comments, Sure, i will post on cursor related topics with bind variables included. Thanks,Siva
Hi Siva,
Oracle's latest documentation says, "EXTEND(n,i) is the only form that you can use for a collection whose elements have the NOT NULL constraint".
But following program works perfectly fine where EXTEND and EXTEND(n) collection methods are being used on a collection variable whose elements in TYPE definition have NOT NULL constraint defined.
DECLARE
TYPE typ_varray IS VARRAY(10) OF NUMBER NOT NULL;
v_typ_varray typ_varray := typ_varray();
BEGIN
v_typ_varray.extend;
v_typ_varray(1) := 10;
v_typ_varray.extend(2);
v_typ_varray(2) := 20;
v_typ_varray(3) := 30;
v_typ_varray.extend(2,1);
FOR indx IN v_typ_varray.first..v_typ_varray.last
LOOP
dbms_output.put_line(v_typ_varray(indx));
END LOOP;
END;
/
Superb siva u cleared most of my doubts ur teaching skills are soo good, kindly make video on bind variable and refcursor
@Abhishek, Thanks for your comments, Sure, Will post video on bind variable and refcursor soon.
Thanks, Siva
you are one hell of a guy. Kudos to u man.U have an exceptional talent to explain complicated concepts in a simple way. Keep doing a great job. I can even join classes if you are planning to take one.
@Karthik, Thanks bro, Thanks a lot your comment, it means a lot for me :-).
Right now I am not talking class, however i have a long term plan to start online class, Please Drop me a mail, whenever I am planning to start class, I will definitely let you know. Thanks & Regards, Siva
Your explanation is great, today you completely cleared my all doubts related to collection methods
Glad to hear that, Thank you for your comments bro
Hi siva, hope you are doing good....
my query is....can we have a feature or functionality in Varray using that, there will be no need to initialise and array will get dynamically be extended upto the indexes which ever indexes or memory areas i need at run time?
for example.....lets say i need to store 3 elements at a particular time instance and next time i need 4 elements to get stored in varray.....how we can achieve it?
Hi Vijay, In that case you can use nested table, because the limit on varray is fixed at the definition time.
Also, when you use bulk collect, it will automatically extend and load the data dynamically based on the number of values
Very nice explanation..Can u please tell on which real time scenario..we have to go for varray.
Whenever you have known list of values to be stored in collection for further processing one by one
So at any point of time will count and last show same value for varray
Yes
Hi shiva garu, i was asked in one of the interview that, did you ever face a situation where element in present but the index is missing in a collection? Can you please let me know if that is even possible.
Thank u for ur wonderful vdos...Content is very good...highly recommendable...just have a doubt @4:42...whats d difference between count and last method...both fetches same result
Thank you, both return same value for varray, however for nested table, if we delete any elements in between, then the count and last will be different. count will return the number of elements in the collection, however last will return the subscript of last element.
Grateful for your effrots.
Welcome bro
Super ... very informative 👌... keep uploading such videos
Thank you bro
so suppose i have a Varray of 26 numbers from 1--26 and i want to delete the between 116 and 19? so what will be the code ?
the delete operation will take 16---19
You cannot delete in varray, however you can set the values to null, if your requirement is to delete elements in between, then you can use nested table
@@SivaAcademy hi siri
@@SivaAcademy i have a question(pl/sql) just want to understand the question...Write a PL/SQL code using CURSORS which can fetch and count the number of records
from customers (SSN, NAME, PHONE, BILL, CITY) table who have salary range given by
users as parameters of cursors. "the salary range gieven by users as parameters" i do not understand it..
The question is asking for usage of parameterized cursor, you can look into this video for more details on what and how to use parameterized cursor
th-cam.com/video/917dFN2Cc6M/w-d-xo.html
Nice explanation siva
Welcome bro 💐
Thank you so much Sir 😊😊
Welcome 💐
Thanks Siva...
Welcome bro
Thank you Siva Sir
It's my pleasure,Thank you so much :-)
nicely explained
Thank you