Use function in insert Statement Oracle

Question

I am having a functions of this type:

 FUNCTION mfi_cust_details (vacid VARCHAR2)
      RETURN VARCHAR2
   IS
      vcustdetails   VARCHAR2 (300);
   BEGIN
      BEGIN
         SELECT    a.cust_title_code
                || ','
                || a.cust_id
                || ','
                || b.address_line1
                || ','
                || b.address_line2
                || ','
                || mfi_citycountry (b.country, b.city)
                || ','
                || b.zip
           INTO vcustdetails
           FROM tbaadm.cmg a, crmuser.address b
          WHERE TRIM (a.cif_id) = TRIM (b.orgkey)
            AND UPPER (b.addresscategory) IN ('MAILING', 'REGISTERED')
            AND cust_id IN (SELECT cust_id
                            FROM tbaadm.gam
                            WHERE acid = vacid);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            vcustdetails :=
                    NULL
               || ','
               || NULL
               || ','
               || NULL
               || ','
               || NULL
               || ','
               || NULL
               || ','
               || NULL
               || ','
               || NULL;
      END;

      RETURN vcustdetails;
   END mfi_cust_details;

and I need to insert data from these into a table for example:

insert into my_table values(mfi_cust_details(myacid),anotherFunction());

but My procedure is not even compiling with an error:

 not enough values

is whatever I am trying to do even possible?

EDIT My table definitions

    create table my_table cust_title_code varchar2(10),
cust_id varchar2(10),
address1 varchar2(10),
address_2 varchar2(10),
city_code varchar2(5),
country_code varchar2(5),
zip_code varchar2(10));

how many columns are there in MY_TABLE?, if it's not 2 (as defined by your values statement) it won't work — davegreen100
– davegreen100, Commented Jul 13, 2015 at 10:35
@davegreen100 I am using the insert inside a cursor with other values. — Stanley Mungai
– Stanley Mungai, Commented Jul 13, 2015 at 10:37
Specify the columns before inserting INSERT INTO A(col1,col2) VALUES(val1.val2) — Mihai
– Mihai, Commented Jul 13, 2015 at 10:38

Richard · Accepted Answer · 2015-07-13 11:11:22Z

3

Your function is defined as returning one column - which is the string "value, value, value" etc (or "Null, null, null" etc) [ although i can't see the use of inserting all null values into my_table on error!]. The insert is failing because inserting into a table (without a specified column list) would default to insert all columns, in the order defined in the table, but since your function is returning one column it fails with "not enough values".

It appears you are trying to craft multiple columns into the insert statement - I not sure if there's a way to do that.

You could define your function as that which returns a ROWTYPE which is essentially represents one record from that table:

FUNCTION mfi_cust_details(vacid VARCHAR2) return my_table%ROWTYPE as 
  my_table%ROWTYPE vcustdetails;
BEGIN
  SELECT a.cust_title_code,
    a.cust_id,
    b.address_line1,
    b.address_line2,
    mfi_citycountry (b.country, b.city),
    b.zip
  INTO vcustdetails 
  FROM tbaadm.cmg a, crmuser.address b
            WHERE TRIM (a.cif_id) = TRIM (b.orgkey)
              AND UPPER (b.addresscategory) IN ('MAILING', 'REGISTERED')
              AND cust_id IN (SELECT cust_id
                              FROM tbaadm.gam
                              WHERE acid = vacid);  

  RETURN vcustdetails;
END;

But then you will need to do the actual insert statements inside PL/SQL blocks (ie. BEGIN/END ).

declare 
  my_table%ROWTYPE rec;
begin

  rec := mfi_cust_details('id');

  insert into my_table( cust_title_code, cust_id, address1, address_2, city_code, country_code, zip_code) 
  values ( rec.cust_title_code, rec.cust_id, rec.address1, rec.address_2, rec.city_code, rec.country_code, rec.zip_code );

  -- don't forget to commit;

end;

answered Jul 13, 2015 at 11:11

Richard

3,1501 gold badge20 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Stanley Mungai Over a year ago

Will this work if I have other values that do not come from the Function? The table has more columns than returned in one function. I have another two functions to insert into the same table.

Richard Over a year ago

Yes, but you will need to declare separate variables and call your functions separately. Then you can just append those variables into your insert list. Or, if the function returns one column, you can inline it directly .... ( ...., zip, anotherFunction() );

Collectives™ on Stack Overflow

Use function in insert Statement Oracle

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related