HI,
I have this parameter table tb_parameter.
+----+----------+-----+-----------+---------------------------------------------------------------------------------------
| ID_SRC| TABLE_NAME | VIEW_NAME | COLUMN_VIEW | COLUMN_TABLE
+----+----------+-----+-----------+---------------------------------------------------------------------------------------
| 1 | CUSTOMER | VIEW_CUSTOMER | id,name,reference | id,name,reference
| 2 | PRODUCT | VIEW_PRODUCT | id_product,type_product | id_product,type_product
| 3 | OWNER | VIEW_OWNER | id_owner,product_owned | id_owner,product_owned
| 4 | CITY | VIEW_CITY | name,adress | name,adress
|----+----------+-----+-----------+---------------------------------------------------------------------------------------------+
I want to create a procedure pl/sql to do this:
insert table_name (column_tab) values
select column view from view_name@dblink
for each row of the parameter table tb_parameter.
I need a dblink to access to the views
--I try this but it'does not work
Error(40,1): PL/SQL: SQL Statement ignored
Error(40,13): PL/SQL: ORA-00942: Table or view does not exist
create or replace PROCEDURE pr_load_tab
IS
v_views tb_parameter.view_name%TYPE;
v_tab tb_parameter.table_name%TYPE;
v_column_view tb_parameter.column_view%TYPE;
v_column_table tb_parameter.column_table%TYPE;
v_source tb_parameter.id_src%TYPE;
i NUMBER(2) := 0;
CURSOR c1 IS SELECT view_name FROM tb_parameter where id_src=v_source;
CURSOR c2 IS select table_name from tb_parameter where id_src=v_source;
CURSOR c3 IS select column_view from tb_parameter where id_src=v_source;
CURSOR c4 IS select column_table from tb_parameter where id_src=v_source;
CURSOR c5 IS select column_table from tb_parameter where id_src=v_source;
BEGIN
OPEN c1;
OPEN c2;
OPEN c3;
OPEN c4;
LOOP
FETCH c1 INTO v_views;
FETCH c2 INTO v_tab;
FETCH c3 INTO v_column_view;
FETCH c4 INTO v_column_table;
IF (c1%FOUND) AND (c2%FOUND) AND (c3%FOUND) AND (c4%FOUND)THEN
v_source := v_source +1;
INSERT INTO v_tab VALUES (v_column_table);
ELSE
EXIT;
END IF;
END LOOP;
CLOSE c1;
CLOSE c2;
CLOSE c3;
CLOSE c4;
END;
/
If the table names and column names are not known at *compile* time, you need to use dynamic SQL to build the insert statement, for example:
SQL> declare
2 l_table varchar2(100) := 'EMP';
3 l_query varchar2(32767) := 'select * from '||l_table;
4
5 l_theCursor integer default dbms_sql.open_cursor;
6 l_descTbl dbms_sql.desc_tab;
7 l_colCnt number;
8 l_insert varchar2(4000) := 'insert into '||l_table||' select ';
9 begin
10 dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
11 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
12
13 for i in 1 .. l_colCnt loop
14 l_insert := l_insert || case when i > 1 then ',' end|| l_descTbl(i).col_name;
15 end loop;
16 l_insert := l_insert || chr(10) ||' from '||l_table||'@dblink';
17 dbms_output.put_line(l_insert);
18 end;
19 /
insert into EMP select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
from EMP@dblink