This note provides a PL\/SQL programme that writes the the content of a table to a file. It also uses a steering table (emp_ids) that contains a series of ids that can be used in a susequent query from which the content can be generated.<\/p>\n\n\n\n
Let me first provide the code:<\/p>\n\n\n\n
create or replace PROCEDURE print_emp_name_dpmt AS
CURSOR id_cur
IS SELECT distinct department_id FROM HR.emp_ids;
emp_rec id_cur%ROWTYPE;
CURSOR id_dpt
IS SELECT employee_id, first_name FROM employees WHERE department_id = emp_rec.department_id;
dpt_rec id_dpt%ROWTYPE;
BEGIN
OPEN id_cur;
LOOP
FETCH id_cur INTO emp_rec;
EXIT WHEN id_cur%NOTFOUND;
OPEN id_dpt;
LOOP
FETCH id_dpt INTO dpt_rec;
EXIT WHEN id_dpt%NOTFOUND;
dbms_output.put_line('The employee ' || dpt_rec.first_name ||';'||emp_rec.department_id||';'||dpt_rec.employee_id);
END LOOP;
CLOSE id_dpt;
END LOOP;
END;<\/pre>\n\n\n\nI created two cursors. One cursor contains the query outcomes from the steering table. A second cursor contains the outcomes from a query that must be written to a file.<\/p>\n\n\n\n
The two cursors are interwoven in two loops. Each loop fetches a record from a query. In the centre, the results are written.<\/p>\n\n\n\n
If one executes this PL\/SQL procedure in SQL plus with a spool on-command, one captures the results. <\/p>\n","protected":false},"excerpt":{"rendered":"
This note provides a PL\/SQL programme that writes the the content of a table to a file. It also uses a steering table (emp_ids) that contains a series of ids that can be used in a susequent query from which the content can be generated. Let me first provide the code: create or replace PROCEDURE […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-2682","post","type-post","status-publish","format-standard","hentry","category-nice-to-know"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/2682","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2682"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/2682\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2682"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2682"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2682"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}