For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Hello,
I use a windows 7 workstation. Can SQLcl be utilized to schedule a task that can be run hourly to export the results of sql select statement to a text or csv file?
Thank you,
DECLARE TYPE partrec IS RECORD ( table_name varchar2 (30), partition_name varchar2 (30), upperbound varchar2 (83), tablespace_name varchar2 (70) ); CURSOR p_cur IS SELECT table_name, partition_name, high_value, tablespace_name FROM user_tab_partitions WHERE high_value_length > 2 ORDER BY partition_position DESC; CURSOR part_cur (i_table_name IN varchar2) IS SELECT table_name, partition_name, high_value, tablespace_name FROM user_tab_partitions WHERE table_name = i_table_name ORDER BY partition_position DESC; prec partrec; v_last_partition varchar2 (50); v_next_partition varchar2 (50); v_last_part_date varchar2 (50); v_next_part_date date; v_part_tmp_date date; v_part_date date; v_initial_date date; i_initial_date date; v_sql varchar2 (300); v_initial_part varchar2 (30); i_initial_part varchar2 (30); c_dummy_partition CONSTANT varchar2 (5) := 'DUMMY'; parent_tname varchar2 (30); v_missing_months number; i number; BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT="YYYYMMDD"'; -- -- Adding partitions to existing range partitioned tables -- FOR cur IN p_cur LOOP OPEN part_cur (cur.table_name); FETCH part_cur INTO prec; CLOSE part_cur; v_last_partition := prec.partition_name; -- DBMS_OUTPUT.put_line ('Last partition Name=' || v_last_partition); v_last_part_date := SUBSTR (prec.upperbound, 10, 20); -- DBMS_OUTPUT.put_line ('Last partition date' || v_last_part_date); v_part_date := TO_DATE (v_last_part_date, 'YYYY-MM-DD HH24:MI:SS'); -- DBMS_OUTPUT.put_line ('Last Partition Date = ' || v_part_date); v_part_tmp_date := v_part_date; v_missing_months := MONTHS_BETWEEN (SYSDATE, v_part_date); -- NOTE : MODIFY following line to create partition in one month or n month in advance. v_missing_months := v_missing_months + 12; -- DBMS_OUTPUT.put_line -- ( 'No of monthly partitions will be created is:' -- || CEIL (v_missing_months) -- ); FOR i IN 1 .. v_missing_months LOOP IF (v_part_date = LAST_DAY (v_part_date + i)) THEN v_part_date := v_part_date + i; v_next_part_date := ADD_MONTHS (v_part_date, i); ELSE v_next_part_date := ADD_MONTHS (v_part_date, i); END IF; v_part_tmp_date := v_part_tmp_date + i; DBMS_OUTPUT.put_line ( 'Next partition Date = ' || TO_CHAR (v_next_part_date, 'YYYYMM') ); v_next_partition := REPLACE (v_last_partition, SUBSTR (v_last_partition, -6), TO_CHAR (v_next_part_date - i, 'YYYYMM') ); DBMS_OUTPUT.put_line ('New Partition Name = ' || v_next_partition); v_sql := 'ALTER TABLE ' || prec.table_name || ' ADD PARTITION ' || v_next_partition || ' VALUES LESS THAN ( TO_DATE(''' || v_next_part_date || ' 00:00:00'', ''YYYYMMDD HH24:MI:SS''))' || ' tablespace ' || prec.tablespace_name; DBMS_OUTPUT.put_line (v_sql); EXECUTE IMMEDIATE v_sql; COMMIT; END LOOP; END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line (SUBSTR (SQLERRM, 1, 300)); RAISE; END;