dynamic creation of insert statements | SQL |

June 5, 2023

I have been working hard how to create dynamic INSERT statement the table which as CLOB data

Table – employee

firstname CLOB,
lastname CLOB,
age CLOB,
creation CLOB,
description CLOB

The above all the column data type is CLOB and as data in it

I have one more table called main_employe

firstname varchar2(255),
lastname varchar2(255),
age number,
creation date,
description CLOB

I need to create a dynamic insert statement. This I getting it through PLSQL and its generating

Insert into main_employee values(firstname,lastname,age,creation,description) as 
select firstname,lastname,age,creation,description from employee

But the problem is my employee table as data which is clob and it throws error when pushing to main_employee

Error :

clob cannot be inserted to number , clob cannot be inserted to varchar

How do I need to create dynamic insert statement which can handle clob data. I heard their is something called cast

Below is the Sample data which is in table
And just for explaining I mentioned in comma seperated

'ABC',XYZ',24,20230930,'Testing in progess'

>Solution :

Here’s one option.

Source table:

SQL> create table employee
  2    (firstname   clob,
  3     lastname    clob,
  4     age         clob,
  5     creation    clob
  6    );

Table created.

SQL> insert into employee values ('Little', 'Foot', '7', '25.03.2023');

1 row created.

Target table:

SQL> create table main_employee
  2    (firstname   varchar2(255),
  3     lastname    varchar2(255),
  4     age         number,
  5     creation    date
  6    );

Table created.

Insert:

SQL> insert into main_employee (firstname, lastname, age, creation)
  2    select to_char(firstname),
  3           to_char(lastname),
  4           to_number(age),
  5           to_date(creation, 'dd.mm.yyyy')
  6    from employee;

1 row created.

Result:

SQL> select * from main_employee;

FIRSTNAME            LASTNAME                    AGE CREATION
-------------------- -------------------- ---------- ----------
Little               Foot                          7 2023-03-25

SQL>

I’m not sure what you meant to say by creating a "dynamic" insert statement; there’s nothing "dynamic" in what you’re doing.