CODEWITHSUNDEEP
oracle-databaseplsqloracle11goracle10g
sadabat
sadabat

Reputation: 3

dynamic procedure in oracle

I want to use a dynamic procedure. The data will be sent from one table to another according to the table name I will give. I know it cannot be written this way. How can I overcome this?

create or replace PROCEDURE personelleri_getir (table_name_source varchar2,limit_in number,table_name_target varchar2)
IS
  CURSOR cur_1 IS
 SELECT * FROM table_name_source where rownum<limit_in;
TYPE fetch_1 IS TABLE OF cur_1%ROWTYPE;
v_1 fetch_1;
BEGIN
 OPEN cur_1;
 LOOP
 FETCH cur_1 BULK COLLECT INTO v_1 LIMIT 10000;

FORALL i IN 1..v_1.COUNT
 INSERT INTO table_name_target VALUES v_1(i);

EXIT WHEN cur_1%NOTFOUND;
 END LOOP;
 CLOSE cur_1;

COMMIT;

EXCEPTION
 WHEN NO_DATA_FOUND THEN
 DBMS_OUTPUT.put_line (SQLERRM);
 WHEN OTHERS THEN
 DBMS_OUTPUT.put_line (SQLERRM);

END;

execute  personelleri_getir('cars',100000,'cars2')

Upvotes: 0

Views: 52

Answers (1)

Littlefoot
Littlefoot

Reputation: 143003

So, you want to copy data between two tables whose descriptions match, right? If so, you're slightly overcomplicating it. Here's a simpler example.

Test case first:

SQL> create table cars1 as select * From dept;

Table created.

SQL> create table cars2 as select * From dept where 1 = 2;

Table created.

SQL> select * from cars1;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select * From cars2;

no rows selected

Procedure:

SQL> create or replace procedure pers (tsource in varchar2, ttarget in varchar2) is
  2  begin
  3    execute immediate 'insert into '    || dbms_assert.sql_object_name(ttarget) ||
  4                      ' select * from ' || dbms_assert.sql_object_name(tsource);
  5  end;
  6  /

Procedure created.

Testing:

SQL> exec pers('cars1', 'cars2');

PL/SQL procedure successfully completed.

SQL> select * from cars2;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

Upvotes: 3

Related Questions