{"id":11360,"date":"2016-03-09T10:02:38","date_gmt":"2016-03-09T15:02:38","guid":{"rendered":"http:\/\/jianmingli.com\/wp\/?p=11360"},"modified":"2016-03-10T12:49:10","modified_gmt":"2016-03-10T17:49:10","slug":"oracle-plsql-example-select-total-record-count-in-a-cursor","status":"publish","type":"post","link":"https:\/\/jianmingli.com\/wp\/?p=11360","title":{"rendered":"Oracle PL\/SQL Example: Select Total Record Counts in a Cursor"},"content":{"rendered":"<div class='toc wptoc'>\n<h2>Contents<\/h2>\n<ol class='toc-odd level-1'>\n\t<li>\n\t\t<a href=\"#Solution\">Solution<\/a>\n\t<\/li>\n\t<li>\n\t\t<a href=\"#Example\">Example<\/a>\n\t<\/li>\n<\/ol>\n<\/ol>\n<\/div>\n<div class='wptoc-end'>&nbsp;<\/div>\n<span id=\"Solution\"><h2>Solution<\/h2><\/span>\n<p>* Use Oracle analytical function, <strong>COUNT(*) OVER()<\/strong>, e.g.<\/p>\n<pre lang=\"sql\">\r\n  cursor c1 is\r\n    SELECT \r\n      DISTINCT EMPLOYEE_ID, \r\n      FIRST_NAME,\r\n      LAST_NAME,\r\n      HIRE_DATE,\r\n      COUNT(DISTINCT EMPLOYEE_ID) OVER() CNT\r\n    FROM EMPLOYEES;\r\n<\/pre>\n<span id=\"Example\"><h2>Example<\/h2><\/span>\n<pre lang=\"sql\">\r\ncreate or replace procedure pTest\r\nas\r\n\tlogfile_prefix varchar2(64) := 'pTest';\r\n\tlog_dir varchar2(64) := 'LOG_DIR';\r\n\tlogfile_name VARCHAR2(50);\r\n\tlogfile UTL_FILE.file_type;\r\n\t\r\n\tcursor c1 is\r\n        SELECT \r\n          DISTINCT EMPLOYEE_ID, \r\n          FIRST_NAME,\r\n          LAST_NAME,\r\n          HIRE_DATE,\r\n          COUNT(DISTINCT EMPLOYEE_ID) OVER() CNT\r\n        FROM EMPLOYEES;\r\n\r\n\tp_cnt integer := 0;\r\n\tc1_rec c1%ROWTYPE;\r\n\t\r\nbegin\r\n    -- Timestamp log file name:\r\n\tlogfile_name := logfile_prefix || '_' || TO_CHAR (SYSDATE, 'MMDDYYYY') || '.LOG';\r\n\t-- Open log file:\r\n\tlogfile := UTL_FILE.fopen (log_dir, logfile_name, 'W');\r\n\r\n\tUTL_FILE.put_line (logfile, '{');\r\n    \r\n\topen c1;\r\n\tloop\r\n\t\tbegin\r\n\t\t\tFETCH c1 INTO c1_rec;\r\n\t\t\texit when c1%NOTFOUND;\r\n\t\t\t\r\n\t\t\tUTL_FILE.put (logfile, '\"' || p_cnt || '\":{');\r\n\t\t\t\r\n\t\t\tUTL_FILE.put (logfile, '\"0\":\"' || c1_rec.EMPLOYEE_ID || '\",');\r\n\t\t\tUTL_FILE.put (logfile, '\"1\":\"' || c1_rec.FIRST_NAME || '\",');\r\n\t\t\tUTL_FILE.put (logfile, '\"2\":\"' || convert(c1_rec.LAST_NAME,'us7ascii') || '\",');\r\n\t\t\tUTL_FILE.put (logfile, '\"3\":\"' || c1_rec.HIRE_DATE || '\",');\r\n\t\t\t\r\n\t\t\tp_cnt := p_cnt + 1;\r\n\t\t\tif p_cnt = c1_rec.CNT then\r\n                -- Found last record\r\n\t\t\t\tUTL_FILE.put_line (logfile, '}');\r\n\t\t\telse\r\n\t\t\t\tUTL_FILE.put_line (logfile, '},');\r\n\t\t\tend if;\r\n\t\t\t\r\n            -- Flush file every 10th record:\r\n\t\t\tif mod(p_cnt, 10) = 0 then \r\n\t\t\t\tUTL_FILE.fflush(logfile);\r\n\t\t\tend if;\r\n\t\t\t\r\n\t\tend;\r\n\tend loop;\r\n\tclose c1;\r\n\tUTL_FILE.put_line (logfile, '}');\r\n  \r\n\tUTL_FILE.FFLUSH(logfile);\r\n\tUTL_FILE.FCLOSE(logfile);\r\nexception\r\n    when others then\r\n        UTL_FILE.put_line (logfile, ''Exception! ' || SQLCODE || '; ' || SQLERRM');\r\nend;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Solution * Use Oracle analytical function, COUNT(*) OVER(), e.g. cursor c1 is SELECT DISTINCT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, COUNT(DISTINCT EMPLOYEE_ID) OVER() CNT FROM EMPLOYEES; Example create or replace procedure pTest as logfile_prefix varchar2(64) := &#8216;pTest&#8217;; log_dir varchar2(64) := &#8216;LOG_DIR&#8217;; logfile_name &hellip; <a href=\"https:\/\/jianmingli.com\/wp\/?p=11360\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[35,74,47],"tags":[685,584],"class_list":["post-11360","post","type-post","status-publish","format-standard","hentry","category-oracle","category-plsql","category-sql","tag-cursor","tag-plsql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p8cRUO-2Xe","_links":{"self":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/11360","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=11360"}],"version-history":[{"count":5,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/11360\/revisions"}],"predecessor-version":[{"id":11367,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/11360\/revisions\/11367"}],"wp:attachment":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=11360"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=11360"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=11360"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}