CODEWITHSUNDEEP
oracle-databaseplsql
James J
James J

Reputation: 11

Write a PL/SQL Script that fills a table with the help of a cursor

How do I write a PL / SQL program that creates and fills a table for example let's call the table "TABEMP1", it fills it with data from any other table.

What I have so far;

set serveroutput on
set verify off
DECLARE
Cursor cur_emp IS SELECT * FROM EMP;
v_emp EMP%ROWTYPE;
BEGIN
    OPEN cur_emp;
    LOOP
        FETCH cur_emp INTO v_emp;
        EXIT WHEN cur_emp%NOTFOUND;
        dbms_output.put_line('Ename: ' || v_emp.ename || ' Empno: ' || v_emp.empno);
    END LOOP;
    CLOSE cur_emp;
END;
/

Upvotes: 1

Views: 49

Answers (2)

Avrajit Roy
Avrajit Roy

Reputation: 3303

In this case you dont need a PLSQL to achieve. A single SQL statement will be more effective. Hope below snippet helps.

CREATE TABLE TEMP_EMP
AS
SELECT * FROM EMP;

Upvotes: 0

Hawk
Hawk

Reputation: 5170

No need for a cursor, using Native SQL with EXECUTE IMMEDIATE you can achieve that with flexible features:

DECLARE
lv_src_table_name VARCHAR2(30) := 'EMP';
pi_new_table_name  VARCHAR2(30) := 'emp_copy';
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE '||pi_new_table_name||' AS SELECT * FROM '||pi_src_table_name;
END;
/

You can turn that into procedure and pass the two parameters with any original/copy tables names you want

Upvotes: 1

Related Questions