CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
s250695
s250695

Reputation: 39

How to pass table as a parameter and populate that table using plsql procedure

I want to populate a table using plsql. It will get table name as a parameter and insert records that are already exist the table. The problem is when there is primary key in the table there can't be duplicate records. I don't know how to solve this problem. This code generates 20 rows and insert into specific table. Since employee_id is primary key I generated sequence for that. But my problem is when I want to insert records for any table. To make it short I want to execute dynamic insert into statement.

This code works fine to insert records for employees table.

create or replace procedure proc ( number_of_records IN number )
IS
BEGIN
INSERT INTO employees (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
SELECT generate.nextval,FIRST_NAME,LAST_NAME,DBMS_RANDOM.STRING('A', 20),PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID
FROM employees where rownum<=number_of_records;
end proc;

execute proc(20);

Upvotes: 1

Views: 614

Answers (5)

Chetan649
Chetan649

Reputation: 21

Please check my another approach, I think this approach will work for you.

  1. Create a procedure with two-parameter

    CREATE OR REPLACE PROCEDURE PROC ( P_TABLE_NAME IN VARCHAR2(100),P_number_of_records 
IN NUMBER)

  2. create two dynamic SQL

    i. First dynamic SQL for cursor 

    REC_STATE_STATEMENT := SELECT 'FOR REC IN (SELECT ' || RTRIM (xmlagg (xmlelement 
(e, COLUMN_NAME || ',')).extract ('//text()'),',') || ' FROM '||P_TABLE_NAME || ' 
WHERE ROWNUM <= ' ||P_number_of_records||' )' COLUMN_NAMES
FROM ALL_TAB_COLS 
WHERE TABLE_NAME='P_TABLE_NAME' 
GROUP BY
TABLE_NAME;

    ii. Secon Dynamic Sql for Insert into (Execute this statement in loop)

    INSERT_INTO_STATEMENT:SELECT 'INSERT INTO ' ||P_TABLE_NAME||' (' 
|| RTRIM (xmlagg (xmlelement (e, COLUMN_NAME || ',')).extract ('//text()'),',') 
||' )' ||' VALUES (' 
|| RTRIM (xmlagg (xmlelement (e, COLUMN_NAME || ',REC.')).extract 
('//text()'),',REC.')||' )' COLUMN_NAMES
FROM ALL_TAB_COLS 
WHERE TABLE_NAME='P_TABLE_NAME'
GROUP BY
TABLE_NAME;

Please let me know if this will helps you.

Regards, Chetan

Upvotes: 0

Belayer
Belayer

Reputation: 14934

While you may not like it I suggest you abandon this idea, it is a very bad idea. First because you want to pass the table name as a parameter you cannot write an SQL statement; you need dynamic sql, either via EXECUTE IMMEDIATE or DBMS_SQL, which is itself never actually simply. Second, to fully implement properly it becomes complicated vary fast. Beforehand you need to consider what if:

  1. PK is not auto generated.
  2. On 12c and higher PK uses IDENTITY but it's defined as ON DEFAULT.
  3. PK is multiple columns.
  4. Are there UNIQUE constraints other than PK (or instead of PK).
  5. Are there unique index(s) that are not defined as constraints.
  6. Others?

Thirdly, dynamic SQL tends to be a drag or performance.

There is an alternative, but not a very good one. Create a procedure for each table then your PROC procedure looks at the table name and calls the appropriate lower level procedure. But this tends to become a maintenance headache very quickly.
Good luck!

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13517

Oracle 12C or higher version now allowed to use Identity column. So you can insert multiple rows without using Sequences anymore. But before 12C, I guess you have to use CURSOR to achieve it -

CREATE OR REPLACE PROCEDURE PROC ( number_of_records IN NUMBER)
IS
BEGIN
    FOR REC IN (SELECT FIRST_NAME
                      ,LAST_NAME
                      ,DBMS_RANDOM.STRING('A',20) EMAIL
                      ,PHONE_NUMBER
                      ,HIRE_DATE
                      ,JOB_ID
                      ,SALARY
                      ,COMMISSION_PCT
                      ,MANAGER_ID
                      ,DEPARTMENT_ID
                FROM employees
                WHERE ROWNUM <= number_of_records)
        LOOP
             INSERT INTO employees(EMPLOYEE_ID
                                  ,FIRST_NAME
                                  ,LAST_NAME
                                  ,EMAIL
                                  ,PHONE_NUMBER
                                  ,HIRE_DATE
                                  ,JOB_ID
                                  ,SALARY
                                  ,COMMISSION_PCT
                                  ,MANAGER_ID
                                  ,DEPARTMENT_ID)
             VALUES(generate.nextval
                   ,REC.FIRST_NAME
                   ,REC.LAST_NAME
                   ,REC.EMAIL
                   ,REC.PHONE_NUMBER
                   ,REC.HIRE_DATE
                   ,REC.JOB_ID
                   ,REC.SALARY
                   ,REC.COMMISSION_PCT
                   ,REC.MANAGER_ID
                   ,REC.DEPARTMENT_ID);
        END LOOP;
END PROC;
/
execute proc(20);

Upvotes: 1

Chetan649
Chetan649

Reputation: 21

I would suggest you can create a dynamic query in your procedure using the below queries:

Below query will give you a Table name and columns for input parameter (V_TABLE_NAME)

    select  aa.table_name, 
            aa.column_name  
    from    all_tab_columns aa
    where   (instr(aa.table_name, '$') = 0 
    and     aa.owner = 'V_SCHEMA_NAME')
    and     aa.table_name = 'V_TABLE_NAME';

using below query you can identify the primary key available on that input table name

    select  aa.table_name, 
            aa.column_name ,
            ac.constraint_type
    from   all_tab_columns aa,
           ALL_CONSTRAINTS ac
    where  aa.table_name = ac.table_name
    and    (instr(aa.table_name, '$') = 0 
    and    aa.owner = 'V_SCHEMA_NAME')
    and    aa.table_name = 'V_TABLE_NAME'
    and    ac.constraint_type = 'P';

Check this solution is works for you. If you have any more queries on this then please do let me know. I will try to solve it.

thanks.

Upvotes: 1

S B
S B

Reputation: 394

Since the question is tagged under Oracle, it is assumed that you are using Oracle database. Based on this, you can explore the all_tables and all_tab_columns views to get the table information and corresponding column information about the table whose name is passed as the parameter.

You can further explore the Oracle documentation for a corresponding view regarding constraints which can be used to know the primary keys of the table.

Upvotes: 1

Related Questions