SET ECHO ON
SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE MAKE_COPY_OF_TRIDY AUTHID CURRENT_USER AS
TYPE TRIDY_TABLE_TYPE IS TABLE OF Tridy%ROWTYPE INDEX BY PLS_INTEGER;
TRIDY_BACKUP TRIDY_TABLE_TYPE;
CURSOR TRIDY_CURSOR IS
SELECT *
FROM tridy
WHERE rocnik = 1
ORDER BY nazev;
v_rowcnt PLS_INTEGER := 0;
BEGIN
OPEN TRIDY_CURSOR;
FETCH TRIDY_CURSOR
BULK COLLECT INTO TRIDY_BACKUP;
CLOSE TRIDY_CURSOR;
FOR i IN TRIDY_BACKUP.FIRST..TRIDY_BACKUP.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(TRIDY_BACKUP(i).id
||chr(9)||'|'||chr(9)||TRIDY_BACKUP(i).mistnosti_id
||chr(9)||'|'||chr(9)||TRIDY_BACKUP(i).rocnik
||chr(9)||'|'||chr(9)||TRIDY_BACKUP(i).nazev);
END LOOP;
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('[Info] Table tridy_backup has '||TRIDY_BACKUP.COUNT||' rows.');
EXECUTE IMMEDIATE 'CREATE TABLE tridy_kopie AS (SELECT * FROM tridy WHERE 1 = 2)';
COMMIT;
DECLARE
insert_command VARCHAR2(200) := 'INSERT INTO tridy_kopie(id,mistnosti_id,rocnik,nazev) VALUES(:id, :m_id, :rocnik, :nazev)';
BEGIN
FOR i IN TRIDY_BACKUP.FIRST..TRIDY_BACKUP.LAST
LOOP
EXECUTE IMMEDIATE insert_command USING TRIDY_BACKUP(i).id, TRIDY_BACKUP(i).mistnosti_id, TRIDY_BACKUP(i).rocnik, TRIDY_BACKUP(i).nazev;
COMMIT;
END LOOP;
END;
DECLARE
select_command VARCHAR(80) := 'SELECT COUNT(*) FROM tridy_kopie';
BEGIN
EXECUTE IMMEDIATE select_command INTO v_rowcnt;
END;
DBMS_OUTPUT.PUT_LINE('[Info] Table tridy_kopie has '||v_rowcnt||' rows.');
EXECUTE IMMEDIATE 'DROP TABLE tridy_kopie';
COMMIT;
END MAKE_COPY_OF_TRIDY;
/
SHOW ERRORS
SET ECHO ON
SET SERVEROUTPUT ON
BEGIN
MAKE_COPY_OF_TRIDY;
END;
/
SHOW ERRORS