How To Dynamically Insert To Table From SQL View Using PL/SQL

Question

How To Dynamically Insert To Table From SQL View Using PL/SQL:

• I want to dynamically insert to table from sql view using PLSQL Cursor when sql view has been changed!!!

• vi_customer is a sqlview(main data)

• customers_1 is a table

• IDENTITY_CUSTOMERS is a identity column.

• v_cur_cust_view cur_customer_view%rowtype

Code:

CREATE OR REPLACE PROCEDURE p_customer_1
IS
  CURSOR cur_customer_view
  IS
    SELECT *
    FROM vi_customer
    WHERE IDENTITY_CUSTOMERS IN
      (SELECT vi_cus.IDENTITY_CUSTOMERS FROM vı_customer vi_cus
    MINUS
    SELECT vı_customer.IDENTITY_CUSTOMERS
    FROM vı_customer
    INNER JOIN customers_1
    ON customers_1.IDENTITY_CUSTOMERS=vı_customer.IDENTITY_CUSTOMERS
      );
    v_cur_cust_view cur_customer_view%rowtype;
  BEGIN
    FOR v_cur_cust_view IN cur_customer_view
    LOOP
      INSERT
      INTO customers_1
        (
          cust_last_name,
          cust_emaıl,
          phone_number1,
          phone_number2,
          order_ıd,
          order_tımestamp,
          order_total,
          quantıty,
          unıt_prıce,
          category,
          fılename,
          ımage_last_update,
          product_ımage,
          product_name,
          lıst_prıce,
          mımetype,
          admın_user,
          created_on,
          expıres_on,
          password,
          products,
          user_ıd,
          user_name,
          st,
          state_name,
          customer_ıd,
          cust_fırst_name,
          IDENTITY_CUSTOMERS
        )
        VALUES
        (
          v_cur_cust_view.cust_last_name,
          v_cur_cust_view.cust_emaıl,
          v_cur_cust_view.phone_number1,
          v_cur_cust_view.phone_number2,
          v_cur_cust_view.order_ıd,
          v_cur_cust_view.order_tımestamp,
          v_cur_cust_view.order_total,
          v_cur_cust_view.quantıty,
          v_cur_cust_view.unıt_prıce,
          v_cur_cust_view.category,
          v_cur_cust_view.fılename,
          v_cur_cust_view.ımage_last_update,
          v_cur_cust_view.product_ımage,
          v_cur_cust_view.product_name,
          v_cur_cust_view.lıst_prıce,
          v_cur_cust_view.mımetype,
          v_cur_cust_view.admın_user,
          v_cur_cust_view.created_on,
          v_cur_cust_view.expıres_on,
          v_cur_cust_view.password,
          v_cur_cust_view.products,
          v_cur_cust_view.user_ıd,
          v_cur_cust_view.user_name,
          v_cur_cust_view.st,
          v_cur_cust_view.state_name,
          v_cur_cust_view.customer_ıd,
          v_cur_cust_view.cust_fırst_name,
          v_cur_cust_view.IDENTITY_CUSTOMERS
        );
    END LOOP;
    COMMIT;
  END;

Error:

Connecting to the database db_kg_0.
ORA-00917: missing comma
ORA-06512: at "DB_KG_0.P_CUSTOMER_1", line 15
ORA-06512: at line 2
Process exited.
Disconnecting from the database db_kg_0.

Answer 1

RESOLVED!!!

insert into customers_1

(
                        cust_last_name,
                        cust_emaıl,
                        phone_number1,
                        phone_number2,
                        order_ıd,
                        order_tımestamp,
                        order_total,
                        quantıty,
                        unıt_prıce,
                        category,
                        fılename,
                        ımage_last_update,
                        product_ımage,
                        product_name,
                        lıst_prıce,
                        mımetype,
                        admın_user,
                        created_on,
                        expıres_on,
                        password,
                        products,
                        user_ıd,
                        user_name,
                        st,
                        state_name,
                        customer_ıd,
                        cust_fırst_name,
                        IDENTITY_CUSTOMERS)

select cust_last_name,
                        cust_emaıl,
                        phone_number1,
                        phone_number2,
                        order_ıd,
                        order_tımestamp,
                        order_total,
                        quantıty,
                        unıt_prıce,
                        category,
                        fılename,
                        ımage_last_update,
                        product_ımage,
                        product_name,
                        lıst_prıce,
                        mımetype,
                        admın_user,
                        created_on,
                        expıres_on,
                        password,
                        products,
                        user_ıd,
                        user_name,
                        st,
                        state_name,
                        customer_ıd,
                        cust_fırst_name,
                        IDENTITY_CUSTOMERS from vi_customer where IDENTITY_CUSTOMERS in (SELECT vi_cus.IDENTITY_CUSTOMERS
FROM vı_customer vi_cus
      MINUS
        SELECT
          vı_customer.IDENTITY_CUSTOMERS
        FROM
            vı_customer  INNER JOIN customers_1 on customers_1.IDENTITY_CUSTOMERS=vı_customer.IDENTITY_CUSTOMERS);

Answer 2

The right way to write this logic is to use insert . . . select, not a cursor and a loop:

INSERT INTO customers_1 ( . . . )
    SELECT . . .
    FROM vi_customer 
    WHERE IDENTITY_CUSTOMERS IN (SELECT c.IDENTITY_CUSTOMERS
                                 FROM vı_customer c
                                 MINUS
                                 SELECT c.IDENTITY_CUSTOMERS
                                 FROM vı_customer c INNER JOIN 
                                      customers_1 c1
                                      ON c1.IDENTITY_CUSTOMERS = c.IDENTITY_CUSTOMERS
                                );