I want to generate insert scripts with PL / SQL but i cant find a way how to do this. We want to transfer data between two databases.
CUSTOMER TABLE
ID
CUSTOMER_NUMBER
CUSTOMERINFO_ID
FOREIGN KEY (CUSTOMERINFO_ID) REFERENCES CUSTOMERINFO (ID)
CUSTOMERINFO TABLE
ID
CUSTOMER_NAME
CUSTOMER_SURNAME
We have sample data in tables and we know CUSTOMER_NUMBER and we want to generate 2 insert script like these:
INSERT INTO CUSTOMER (ID, CUSTOMER_NUMBER, CUSTOMERINFO_ID)
VALUES(1,12345,1);
INSERT INTO CUSTOMERINFO (ID, CUSTOMER_NAME, CUSTOMER_SURNAME)
VALUES(1,'NAME','SURNAME');
Since i can't find a way that will generate it now, I proceed as follows. I use DBeaver as database administration tool.
SELECT * FROM CUSTOMER WHERE CUSTOMER_NUMBER = 12345;
Then right click the row => Generate SQL => INSERT
Generated SQL => INSERT INTO CUSTOMER (ID, CUSTOMER_NUMBER, CUSTOMERINFO_ID) VALUES(1,12345,1);
SELECT * FROM CUSTOMERINFO WHERE ID = 1
Then right click the row => Generate SQL => INSERT
Generated SQL => INSERT INTO CUSTOMERINFO (ID, CUSTOMER_NAME, CUSTOMER_SURNAME) VALUES(1,'NAME','SURNAME');
Now I have a script like below. I process the output here as in the above process.
DECLARE
lv_customerNo varchar2(20):=:customerNumber;
lv_customer CUSTOMER%ROWTYPE;
BEGIN
SELECT * INTO lv_customer FROM CUSTOMER WHERE CUSTOMER_NUMBER = lv_customerNo ;
dbms_output.put_line('SELECT * FROM CUSTOMER bp WHERE ID =' || lv_customer.ID);
dbms_output.put_line('SELECT * FROM CUSTOMERINFO WHERE ID =' || lv_customer.CUSTOMERINFO_ID);
END;
I want to generate and print insert scripts with full data instead of these:
dbms_output.put_line('SELECT * FROM CUSTOMER bp WHERE ID =' || lv_customer.ID);
dbms_output.put_line('SELECT * FROM CUSTOMERINFO WHERE ID =' || lv_customer.CUSTOMERINFO_ID);
I explained this through 2 sample tables and a few columns, but our tables and columns are much more than that. So I want to prepare a PL / SQL script to automate this. But I couldn't find anything similar to this on the internet. How can i generate insert query with PL / SQL ?
CodePudding user response：
You do not appear to need to dynamically generate the code and then manually run it in a two-step process.
Instead, you can use sequences to generate the identifiers:
CREATE SEQUENCE customer__id__seq;
CREATE SEQUENCE customer_info__id__seq;
Then if you know customer_number, customer_name and customer_surname you can use a PL/SQL block to perform the inserts with the next sequence values:
DECLARE
v_customer_number CUSTOMER.CUSTOMER_NUMBER%TYPE := 12345;
v_first_name CUSTOMERINFO.CUSTOMER_NAME%TYPE := 'NAME';
v_last_name CUSTOMERINFO.CUSTOMER_SURNAME%TYPE := 'SURNAME';
v_customer_id CUSTOMERINFO.ID%TYPE;
BEGIN
-- insert into customer_info first.
INSERT INTO customerinfo (id, customer_name, customer_surname)
VALUES (customer_info__id__seq.NEXTVAL, v_first_name, v_last_name)
RETURNING id INTO v_customer_id;
-- Then insert into customer as it has a foreign key to customer_info.
INSERT INTO customer (id, customer_number, customerinfo_id)
VALUES (customer__id__seq.NEXTVAL, v_customer_number, v_customer_id);
END;
/
If you want to perform this with multiple customer number/name/surname tuples then you could create a cursor or collections with the values and loop over that:
DECLARE
v_customer_id CUSTOMERINFO.ID%TYPE;
BEGIN
FOR i IN (
SELECT 12345 AS customer_number, 'NAME' AS name, 'SURNAME' AS surname FROM DUAL UNION ALL
SELECT 23456, 'NAME1', 'SURNAME1' FROM DUAL
)
LOOP
-- insert into customer_info first.
INSERT INTO customerinfo (id, customer_name, customer_surname)
VALUES (customer_info__id__seq.NEXTVAL, i.name, i.surname)
RETURNING id INTO v_customer_id;
-- Then insert into customer as it has a foreign key to customer_info.
INSERT INTO customer (id, customer_number, customerinfo_id)
VALUES (customer__id__seq.NEXTVAL, i.customer_number, v_customer_id);
END LOOP;
END;
/
Update
I need insert scripts as text for transfer to another database.
This seems like an XY-problem and rather than manually trying to generate INSERT statements you can use the export data-pump expdp utility to backup the database (or individual tables) and then import them to the other database.
CodePudding user response：
Well, below you can find the code that could help you to make your own solution. It is not tuned nor clensed but it could give you the option to adjust it to your needs. And it works.
There is a new type (used by split() function) defined as:
-- ---------------------------------------------------------------
CREATE OR REPLACE TYPE STRING_ARRAY AS VARRAY(4000) OF VARCHAR2(1000);
-- ---------------------------------------------------------------
... then there is a small package ...
create or replace PACKAGE SCRIPTS2 AS
Procedure Init;
--
FUNCTION split(csvString IN VarChar2, delimiter IN VarChar2 := ',') RETURN STRING_ARRAY;
--
Function Get_Select(p_tab_1 VarChar2, p_key_col VarChar2, p_key_value Number, p_what VarChar2 := 'INSERT') RETURN VARCHAR2;
--
Function Get_Link(p_tab_1 VarChar2) RETURN VARCHAR2;
--
END SCRIPTS2;
... and package body ...
create or replace PACKAGE BODY SCRIPTS2 AS
m_select VarChar2(2000) := '';
m_where VarChar2(512) := '';
m_insert VarChar2(2000) := '';
m_cols VarChar2(512) := '';
m_vals VarChar2(32000) := '';
m_val VarChar2(512) := '';
m_types VarChar2(512) := '';
m_tab_1 VarChar2(64) := '';
m_tab_2 VarChar2(64) := '';
--
m_link_col VarChar2(64) := '';
m_key_col VarChar2(64) := '';
m_key_val Number := 0;
--
m_selects VarChar2(4000) := '';
m_inserts VarChar2(32000) := '';
-- ---------------------------------------------------------------------------------------------------
PROCEDURE Init IS
BEGIN
m_select := '';
m_where := '';
m_insert := '';
m_cols := '';
m_vals := '';
m_val := '';
m_types := '';
m_tab_1 := '';
m_tab_2 := '';
--
m_link_col := '';
m_key_col := '';
m_key_val := 0;
--
m_selects := '';
m_inserts := '';
END Init;
-- ---------------------------------------------------------------------------------------------------
FUNCTION split ( csvString IN VarChar2, delimiter IN VarChar2 := ',') RETURN STRING_ARRAY AS
BEGIN
Declare
elements STRING_ARRAY := STRING_ARRAY();
CURSOR c IS
SELECT COL1
FROM ( SELECT INDX, MY_STR1, COL1_ELEMENTS, COL1
FROM ( SELECT 0 "INDX", COL1 "MY_STR1", COL1_ELEMENTS, COL1
FROM(
SELECT
REPLACE(COL1, delimiter || ' ', delimiter) "COL1",
Trim(Length(Replace(COL1, delimiter || ' ', delimiter))) - Trim(Length(Translate(REPLACE(COL1, delimiter || ' ', delimiter), 'A' || delimiter, 'A'))) 1 "COL1_ELEMENTS"
-- Trim(Length(COL1)) - Trim(Length(Translate(COL1, 'A' || delimiter, 'A'))) 1 "COL1_ELEMENTS"
FROM (SELECT csvString "COL1" FROM DUAL)
)
)
MODEL
DIMENSION BY(0 as INDX)
MEASURES(COL1, COL1_ELEMENTS, CAST('a' as VarChar2(4000)) as MY_STR1)
RULES ITERATE (1000) --UNTIL (ITERATION_NUMBER <= COL1_ELEMENTS[ITERATION_NUMBER 1]) -- If you don't know the number of elements this should be bigger then you aproximation. Othewrwise it will split given number of elements
(
COL1_ELEMENTS[ITERATION_NUMBER 1] = COL1_ELEMENTS[0],
MY_STR1[0] = COL1[CV()],
MY_STR1[ITERATION_NUMBER 1] = SubStr(MY_STR1[ITERATION_NUMBER], InStr(MY_STR1[ITERATION_NUMBER], delimiter, 1) Length(delimiter)),
COL1[ITERATION_NUMBER 1] = SubStr(MY_STR1[ITERATION_NUMBER], 1, CASE WHEN InStr(MY_STR1[ITERATION_NUMBER], delimiter) <> 0 THEN InStr(MY_STR1[ITERATION_NUMBER], delimiter)-1 ELSE Length(MY_STR1[ITERATION_NUMBER]) END)
)
)
WHERE INDX > 0 And INDX <= COL1_ELEMENTS; -- And COL1 Is Not Null;
cStr VarChar2(1000);
i Number := 1;
Begin
If c%ISOPEN Then CLOSE c; End If;
OPEN c;
LOOP
FETCH c Into cStr;
EXIT WHEN c%NOTFOUND;
elements.extend;
elements(i) := cStr;
i := i 1;
END LOOP;
CLOSE c;
RETURN elements;
End;
END split;
-- ---------------------------------------------------------------------------------------------------
Function Get_Select(p_tab_1 VarChar2, p_key_col VarChar2, p_key_value Number, p_what VarChar2 := 'INSERT') RETURN VARCHAR2 AS
BEGIN
Declare
CURSOR c_tbl IS
Select TABLE_NAME From sys.all_tables Where TABLE_NAME IN('A_TBL_1', 'A_TBL_2');
m_tbl_name VarChar2(32);
--
CURSOR c_col IS
Select tc.TABLE_NAME, tc.COLUMN_ID, tc.COLUMN_NAME, tc.DATA_TYPE
From sys.all_tab_columns tc
Where tc.TABLE_NAME = m_tbl_name
Order By tc.COLUMN_ID;
colSet c_col%ROWTYPE;
--
m_cursor SYS_REFCURSOR;
--
m_return VarChar2(32000) := '';
m_status VarChar2(255) := '';
sq varChar2(1) := '''';
m_cmd VarChar2(512) := '';
m_count_1 Number(6) := 0;
m_count_2 Number(6) := 0;
m_rows Number(6) := 1;
m_cols_count Number(6) := 0;
Begin
m_tab_1 := p_tab_1;
m_key_col := p_key_col;
m_key_val := p_key_value;
m_status := Get_Link(m_tab_1);
If SubStr(m_status, 1, 2) != 'OK' Then
m_return := m_status;
GoTo send_it;
End If;
--
OPEN c_tbl;
LOOP
FETCH c_tbl Into m_tbl_name;
EXIT WHEN c_tbl%NOTFOUND;
m_select := '';
m_where := '';
m_cols := '';
m_types := '';
m_vals := '';
--
If NOT c_col%ISOPEN Then
OPEN c_col;
End If;
LOOP
FETCH c_col Into colSet;
EXIT WHEN c_col%NOTFOUND;
m_cols_count := c_col%ROWCOUNT;
--
END LOOP;
CLOSE c_col;
--
If m_tbl_name = m_tab_1 Then
m_where := ' Where ' || m_key_col || ' = ' || m_key_val;
m_cmd := 'Select Count(*) From ' || m_tbl_name || m_where;
execute immediate m_cmd Into m_count_1;
m_rows := m_count_1;
-- ------------------------------------------------------------------------------------------------------------
For i in 1..m_count_1 LOOP
Begin
If NOT c_col%ISOPEN Then
OPEN c_col;
End If;
m_cols := '';
m_types := '';
LOOP
FETCH c_col Into colSet;
EXIT WHEN c_col%NOTFOUND;
m_cols := m_cols || colSet.COLUMN_NAME || ', ';
m_types := m_types || colSet.DATA_TYPE || ', ';
m_val := '';
m_where := ' Where ' || m_key_col || ' = ' || m_key_val;
m_cmd := 'Select ' || To_Char(colSet.COLUMN_NAME) || ' From ' || m_tab_1 || m_where || ' And ROWNUM = ' || To_Char(i) ;
execute immediate m_cmd Into m_val;
If colSet.DATA_TYPE = 'NUMBER' Then
m_vals := m_vals || m_val || ', ';
Else
m_vals := m_vals || sq || m_val || sq || ', ';
End If;
--
If m_cols_count = c_col%ROWCOUNT Then
m_vals := SubStr(m_vals, 1, Length(m_vals) - 2);
m_inserts := m_inserts || 'INSERT INTO ' || m_tbl_name || ' VALUES(' || m_vals || ');' || Chr(10);
--
m_cols := SubStr(m_cols, 1, Length(m_cols) - 2);
m_types := SubStr(m_types, 1, Length(m_types) - 2);
m_where := ' Where ' || m_key_col || ' = ' || m_key_val;
m_select := 'Select ' || m_cols || ' From ' || m_tbl_name || m_where || ';' || Chr(10);
m_selects := m_selects || m_select;
End If;
END LOOP;
CLOSE c_col;
--
Exception When NO_DATA_FOUND Then Null;
End;
End Loop;
-- -----------------------------------------------------------------------------------------------------------
Else
m_where := ' Where ' || m_link_col || ' IN (Select ' || m_link_col || ' From ' || m_tab_1 || ' Where ' || m_key_col || ' = ' || m_key_val || ')';
m_cmd := 'Select Count(*) From ' || m_tbl_name || m_where;
execute immediate m_cmd Into m_count_2;
m_rows := m_count_2;
-- -----------------------------------------------------------------------------------------------------------
For j in 1..m_count_2 LOOP
Begin
If NOT c_col%ISOPEN Then
OPEN c_col;
End If;
m_cols := '';
m_types := '';
LOOP
FETCH c_col Into colSet;
EXIT WHEN c_col%NOTFOUND;
m_cols := m_cols || colSet.COLUMN_NAME || ', ';
m_types := m_types || colSet.DATA_TYPE || ', ';
m_val := '';
m_where := ' Where ' || m_link_col || ' IN (Select ' || m_link_col || ' From ' || m_tab_1 || ' Where ' || m_key_col || ' = ' || m_key_val || ')';
m_cmd := 'Select ' || To_Char(colSet.COLUMN_NAME) || ' From ' || m_tab_2 || m_where || ' And ROWNUM = ' || To_Char(j);
execute immediate m_cmd Into m_val;
If colSet.DATA_TYPE = 'NUMBER' Then
m_vals := m_vals || m_val || ', ';
Else
m_vals := m_vals || sq || m_val || sq || ', ';
End If;
If m_cols_count = c_col%ROWCOUNT Then
m_vals := SubStr(m_vals, 1, Length(m_vals) - 2);
m_inserts := m_inserts || 'INSERT INTO ' || m_tbl_name || ' VALUES(' || m_vals || ');' || Chr(10);
--
m_cols := SubStr(m_cols, 1, Length(m_cols) - 2);
m_types := SubStr(m_types, 1, Length(m_types) - 2);
m_where := ' Where ' || m_link_col || ' = (Select ' || m_link_col || ' From ' || m_tab_1 || ' Where ' || m_key_col || ' = ' || m_key_val || ');';
m_select := 'Select ' || m_cols || ' From ' || m_tbl_name || m_where || Chr(10);
m_selects := m_selects || m_select;
End If;
END LOOP;
CLOSE c_col;
--
Exception When NO_DATA_FOUND Then Null;
End;
End Loop;
-- -----------------------------------------------------------------------------------------------------------
End If;
END LOOP;
CLOSE c_tbl;
--
If p_what = 'INSERT' Then
m_return := m_inserts;
Else
m_return := m_selects;
End If;
<<send_it>>
RETURN m_return;
<<end_it>>
Null;
End;
END Get_Select;
-- ---------------------------------------------------------------------------------------------------
Function Get_Link(p_tab_1 VarChar2) RETURN VARCHAR2 AS
BEGIN
Declare
m_count_link Number := 0;
m_return VarChar2(255) := 'ERR - NO_TABLE_LINK';
Begin
SELECT Count(*) Into m_count_link
FROM all_cons_columns a
INNER JOIN
all_constraints c ON (a.OWNER = c.OWNER AND a.CONSTRAINT_NAME = c.CONSTRAINT_NAME)
INNER JOIN
all_constraints c_pk ON (c.R_OWNER = c_pk.OWNER AND c.R_CONSTRAINT_NAME = c_pk.CONSTRAINT_NAME)
WHERE c.CONSTRAINT_TYPE = 'R' AND a.TABLE_NAME = m_tab_1;
--
If m_count_link = 1 Then
SELECT a.COLUMN_NAME, c_pk.TABLE_NAME Into m_link_col, m_tab_2
FROM all_cons_columns a
INNER JOIN
all_constraints c ON (a.OWNER = c.OWNER AND a.CONSTRAINT_NAME = c.CONSTRAINT_NAME)
INNER JOIN
all_constraints c_pk ON (c.R_OWNER = c_pk.OWNER AND c.R_CONSTRAINT_NAME = c_pk.CONSTRAINT_NAME)
WHERE c.CONSTRAINT_TYPE = 'R' AND a.TABLE_NAME = m_tab_1;
m_return := 'OK - link to table ' || m_tab_2 || ' established by ccolumn ' || m_link_col;
End If;
RETURN m_return;
<<end_it>>
Null;
End;
END Get_Link;
-- ---------------------------------------------------------------------------------------------------
END SCRIPTS2;
It starts with parameters table name, key column, value of key column and 'INSERT' (default) or 'SELECT' parameter that will get you SQL commands of your choice. Here is haow to call it with results for both p_what parameters:
SET SERVEROUTPUT ON
Declare
m_status VarChar2(32000) := '';
Begin
SCRIPTS.Init;
m_status := SCRIPTS.Get_Select('A_TBL_1', 'CUSTOMER_NUMBER', 12345, 'INSERT');
DBMS_OUTPUT.PUT_LINE(m_status);
End;
--
-- ----- Result for INSERT ---------------
anonymous block completed
INSERT INTO A_TBL_1 VALUES(1, 12345, 1);
INSERT INTO A_TBL_2 VALUES(1, 'NAME', 'SURNAME');
--
-- ----- Result for SELECT ---------------
Select ID, CUSTOMER_NUMBER, CUSTOMER_INFO_ID From A_TBL_1 Where CUSTOMER_NUMBER = 12345;
Select ID, CUSTOMER_NAME, CUSTOMER_SURNAME From A_TBL_2 Where ID = (Select ID From A_TBL_1 Where CUSTOMER_NUMBER = 12345);
NOTE:
It works in a way that the given table name's columns are read from sys tables, then the Select statement is generated with where clause costructed as key column is equal to key column value from parameters. Next, the package is looking for foreign key and gets the table that is within the foreign key constraint - generates the second select statement that has joined data. Then gets the data using those select statements and with the data collected generates insert statements. Return is either select or insert statements generated.
As said, you should probably correct the code to meet your needs - but it could be something to help you start. There are some issues to deal with such as that foreign key column - in this case ID - should have same name in both tables...