{"id":22102,"date":"2013-05-13T10:37:36","date_gmt":"2013-05-13T05:07:36","guid":{"rendered":"http:\/\/vskills.in\/certification\/tutorial\/?p=22102"},"modified":"2024-04-12T14:16:44","modified_gmt":"2024-04-12T08:46:44","slug":"varray-and-bulk-collect","status":"publish","type":"page","link":"https:\/\/www.vskills.in\/certification\/tutorial\/varray-and-bulk-collect\/","title":{"rendered":"Varray and bulk collect"},"content":{"rendered":"\n

Varray and bulk collect in PL\/SQL<\/strong><\/p>\n\n\n\n

In PL\/SQL, a VARRAY (variable-size array) is a custom data type that allows you to store a fixed number of elements of the same data type. VARRAYs are similar to arrays in other programming languages, but they have some differences in how they are defined and used.<\/p>\n\n\n\n

Here is an example of how to define a VARRAY type:<\/p>\n\n\n\n

CREATE TYPE number_varray AS VARRAY(10) OF NUMBER;\r<\/code><\/pre>\n\n\n\n
In this example, we are creating a VARRAY type called “number_varray” that can hold up to 10 elements of type NUMBER.<\/p>\n\n\n\n
You can declare a variable of this type and use it in your PL\/SQL code, like this:<\/p>\n\n\n\n
DECLARE\r\n  v_numbers number_varray := number_varray(1, 2, 3, 4, 5, 6, 7, 8, 9, 10);\r\nBEGIN\r\n  -- do something with v_numbers\r\nEND;\r<\/pre>\n\n\n\n
VARRAYs are useful when you need to work with a fixed number of elements of the same data type, but they can be inefficient when working with large datasets. This is where bulk collect comes in.<\/p>\n\n\n\n
Bulk collect is a feature of PL\/SQL that allows you to fetch multiple rows of data at once into a collection, instead of one row at a time. This can significantly improve performance when working with large datasets. Here is an example of how to use bulk collect:<\/p>\n\n\n\n
DECLARE\r\n  TYPE emp_tab IS TABLE OF employees%ROWTYPE;\r\n  l_emps emp_tab;\r\nBEGIN\r\n  SELECT *\r\n  BULK COLLECT INTO l_emps\r\n  FROM employees;\r\n  -- do something with l_emps\r\nEND;\r<\/code><\/pre>\n\n\n\n
In this example, we are selecting all rows from the “employees” table and using bulk collect to fetch them into a collection called “l_emps”. We can then use this collection to perform operations on the data, such as updating or inserting multiple rows at once.<\/p>\n\n\n\n
Overall, VARRAYs and bulk collect are two important features of PL\/SQL that can help you work with data efficiently and effectively.<\/p>\n\n\n\n
Apply for PL\/SQL Certification<\/strong><\/h2>\n\n\n\n
https:\/\/www.vskills.in\/certification\/certified-pl-sql-developer<\/a><\/p>\n\n\n\n
Back to Tutorials<\/a><\/strong><\/h4>\n","protected":false},"excerpt":{"rendered":"
Varray and bulk collect in PL\/SQL In PL\/SQL, a VARRAY (variable-size array) is a custom data type that allows you to store a fixed number of elements of the same data type. VARRAYs are similar to arrays in other programming languages, but they have some differences in how they are defined and used. Here is…<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"categories":[288],"tags":[],"class_list":["post-22102","page","type-page","status-publish","hentry","category-pl-sql"],"acf":[],"yoast_head":"\nVarray and bulk collect tutorials<\/title>\n<meta name=\"description\" content=\"Varray and bulk collect tutorials\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.vskills.in\/certification\/tutorial\/varray-and-bulk-collect\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Varray and bulk collect tutorials\" \/>\n<meta property=\"og:description\" content=\"Varray and bulk collect tutorials\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.vskills.in\/certification\/tutorial\/varray-and-bulk-collect\/\" \/>\n<meta property=\"og:site_name\" content=\"Tutorial\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/vskills.in\/\" \/>\n<meta property=\"article:modified_time\" content=\"2024-04-12T08:46:44+00:00\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/varray-and-bulk-collect\/\",\"url\":\"https:\/\/www.vskills.in\/certification\/tutorial\/varray-and-bulk-collect\/\",\"name\":\"Varray and bulk collect tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/#website\"},\"datePublished\":\"2013-05-13T05:07:36+00:00\",\"dateModified\":\"2024-04-12T08:46:44+00:00\",\"description\":\"Varray and bulk collect tutorials\",\"breadcrumb\":{\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/varray-and-bulk-collect\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.vskills.in\/certification\/tutorial\/varray-and-bulk-collect\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/varray-and-bulk-collect\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.vskills.in\/certification\/tutorial\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Varray and bulk collect\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/#website\",\"url\":\"https:\/\/www.vskills.in\/certification\/tutorial\/\",\"name\":\"Tutorial\",\"description\":\"Vskills - A initiative in elearning and certification\",\"publisher\":{\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.vskills.in\/certification\/tutorial\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/#organization\",\"name\":\"Vskills\",\"url\":\"https:\/\/www.vskills.in\/certification\/tutorial\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.vskills.in\/certification\/tutorial\/wp-content\/uploads\/2017\/07\/vskills-min-logo.jpg\",\"contentUrl\":\"https:\/\/www.vskills.in\/certification\/tutorial\/wp-content\/uploads\/2017\/07\/vskills-min-logo.jpg\",\"width\":73,\"height\":55,\"caption\":\"Vskills\"},\"image\":{\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/vskills.in\/\",\"https:\/\/x.com\/vskills_in\",\"https:\/\/www.linkedin.com\/company-beta\/1371554\/\",\"https:\/\/www.youtube.com\/channel\/UCMWnscxPwRF_PqXo9B7q_Tw\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Varray and bulk collect tutorials","description":"Varray and bulk collect tutorials","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.vskills.in\/certification\/tutorial\/varray-and-bulk-collect\/","og_locale":"en_US","og_type":"article","og_title":"Varray and bulk collect tutorials","og_description":"Varray and bulk collect tutorials","og_url":"https:\/\/www.vskills.in\/certification\/tutorial\/varray-and-bulk-collect\/","og_site_name":"Tutorial","article_publisher":"https:\/\/www.facebook.com\/vskills.in\/","article_modified_time":"2024-04-12T08:46:44+00:00","twitter_misc":{"Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.vskills.in\/certification\/tutorial\/varray-and-bulk-collect\/","url":"https:\/\/www.vskills.in\/certification\/tutorial\/varray-and-bulk-collect\/","name":"Varray and bulk collect tutorials","isPartOf":{"@id":"https:\/\/www.vskills.in\/certification\/tutorial\/#website"},"datePublished":"2013-05-13T05:07:36+00:00","dateModified":"2024-04-12T08:46:44+00:00","description":"Varray and bulk collect tutorials","breadcrumb":{"@id":"https:\/\/www.vskills.in\/certification\/tutorial\/varray-and-bulk-collect\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.vskills.in\/certification\/tutorial\/varray-and-bulk-collect\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.vskills.in\/certification\/tutorial\/varray-and-bulk-collect\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.vskills.in\/certification\/tutorial\/"},{"@type":"ListItem","position":2,"name":"Varray and bulk collect"}]},{"@type":"WebSite","@id":"https:\/\/www.vskills.in\/certification\/tutorial\/#website","url":"https:\/\/www.vskills.in\/certification\/tutorial\/","name":"Tutorial","description":"Vskills - A initiative in elearning and certification","publisher":{"@id":"https:\/\/www.vskills.in\/certification\/tutorial\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.vskills.in\/certification\/tutorial\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.vskills.in\/certification\/tutorial\/#organization","name":"Vskills","url":"https:\/\/www.vskills.in\/certification\/tutorial\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.vskills.in\/certification\/tutorial\/#\/schema\/logo\/image\/","url":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-content\/uploads\/2017\/07\/vskills-min-logo.jpg","contentUrl":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-content\/uploads\/2017\/07\/vskills-min-logo.jpg","width":73,"height":55,"caption":"Vskills"},"image":{"@id":"https:\/\/www.vskills.in\/certification\/tutorial\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/vskills.in\/","https:\/\/x.com\/vskills_in","https:\/\/www.linkedin.com\/company-beta\/1371554\/","https:\/\/www.youtube.com\/channel\/UCMWnscxPwRF_PqXo9B7q_Tw"]}]}},"_links":{"self":[{"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/pages\/22102","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/comments?post=22102"}],"version-history":[{"count":7,"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/pages\/22102\/revisions"}],"predecessor-version":[{"id":129926,"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/pages\/22102\/revisions\/129926"}],"wp:attachment":[{"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/media?parent=22102"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/categories?post=22102"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/tags?post=22102"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}