Multi-line INSERT of array or list values 1

[peterswan]

Hello,
I have several comma-delimited lists that I'd like to INSERT into Oracle using PL/SQL. Each list has the same length, like this for example:

List 1 = 45, 'Joe', 'Smith', '123 Main St.';
List 2 = 46, 'Mary', 'Jones', '445 Maple St.';
List 3 = 47, 'Bill', 'Anderson', '443 Forest Lane';
List 4 = 48, 'Jane', 'Smith', '788 Wisteria Lane';

Is there a way for me to add these values into some sort of array, and then loop through them, and run an INSERT statement for each list?

I know I could just run an INSERT statement for each line, however, the procedure I'm running contains a lot more code for each insert, and I'm hoping that using an array of some sort will condense the code.

Thanks,

Peter

 

[SantaMufasa]

Peter,

Certainly you can use a PL/SQL array to effect your INSERTs, but I'm trying to imagine how that will condense the code (over simply performing the raw INSERTs themselves). In fact, it seems to me that 1) creating the array, 2) loading the array, then 3) unloading the array one line at a time into the INSERT statement, will, in fact, increase, rather than condense your code.

Let me know if you have a different strategy in mind, and we can pursue the code solution for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[peterswan]

Hi Mufasa,

Thanks for the reply. Sorry, I realize I didn't make myself clear. What I'm trying to do is insert each list, and for each insert, run a bunch more code. Here is the order of events as I'm envisioning it:

1. Declare variables
2. Declare big list of arrays, maybe 100 or so, and assign Customer values and Function/title values to these arrays.
If necessary, declare Function/title values as separate arrays.
--Start looping through arrays here--
3. Lookup CustID by getting max CustID from customer table.
4. Insert into customer table each customerID and all corresponding information from the first customer array.
5. Get total count of Roles in Role table where the function is the function from the array, and the title is the title from the array.
6. If this count is greater than 0 get the corresponding RoleID from the Role table and call it RoleID.
7. If this count is 0, insert a new role into the role table with the function/title values and get new ID from the role table and call it RoleID.
8. Insert the CustID and the RoleID into the Role Assignment table.
9. Increase the CustID by one and start over again, iterating through the next array.

I can do everything except create the arrays and loop through them.

Also, I have run this code already and it works fine, but the code is too long. I think if I create these arrays that the last part of each step in the process (looking up the RoleID values and then assigning the new RoleID) will be more condensed.

Thanks, if you have any time to answer.

Peter

 

[SantaMufasa]

I have run this code already and it works fine...

Can you post the code for us? We can offer suggestions of how to condense the code easier than composing other template code.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[peterswan]

OK, here is the first part:

declare
cust_function varchar2(50);
cust_title varchar2(50);
cust_div varchar2(50);
cust_org varchar2(50);
custID number;
RoleRecords number;
NextRoleID number;
RoleID number;

--Here's where I want to loop through the array of customers--

begin
cust_function := '';
cust_title := 'Captain';
cust_div := '';
cust_org := 'US Air Force';


select max(customerid) + 1 into custID from csq_customers;

--insert into customers here--

if cust_function is not null or cust_title is not null then
select count(*) into RoleRecords from csq_crm_crole where
nvl(crm_cr_org, 0) = nvl(cust_org, 0)
and nvl(crm_cr_branch, 0) = nvl(cust_div, 0)
and nvl(crm_cr_title, 0) = nvl(cust_title, 0)
and nvl(crm_cr_function, 0) = nvl(cust_function, 0);

if RoleRecords = 0 then

select max(crm_cr_id) + 1 into NextRoleID
from csq_crm_crole;

insert into csq_crm_crole values (NextRoleID,
null, cust_org, null, cust_div, null, sysdate,
null, sysdate, cust_title, null, 1646618,null, cust_function);
commit;

insert into csq_crm_cust_crole values (custID,
NextRoleID, trunc(sysdate), null, sysdate, null, null, 1646618, null);
commit;

else
select crm_cr_id into RoleID from csq_crm_crole where
nvl(crm_cr_org, 0) = nvl(cust_org, 0)
and nvl(crm_cr_branch, 0) = nvl(cust_div, 0)
and nvl(crm_cr_title, 0) = nvl(cust_title, 0)
and nvl(crm_cr_function, 0) = nvl(cust_function, 0)
and rownum between 0 and 1;

insert into csq_crm_cust_crole values (custID,
RoleID, trunc(sysdate), null, sysdate, null, null, 552887, null);
commit;

end if;
end if;
--Here's where I want to start the loop again--
end;

 

[peterswan]

I'm starting to get the feeling what I need is a stored procedure, with incoming parameters, and then run the SP once for each customer.

The customer table has many values that will need to be added, 20 or so.

Can I create an SP with 20 incoming parameters?

Can I put these values into several arrays, and then loop through each array and EXEC the stored procedure once for each array list?

Thanks,

Peter

 

[SantaMufasa]

I don't see why not to any of your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[peterswan]

Hi Mufasa,

If you have a moment could you please post any code that might be useful for me to understand the EXEC part of what I'm trying to achieve.

Create arrays or lists
Loop through arrays or lists
EXEC stored procedure
End Loop

Thanks,

Peter

 

[SantaMufasa]

Frankly, Peter, you don't need a stored procedure...a locally defined procedure in the DECLARE section of your PL/SQL code would be sufficient:

DECLARE
   x number;
   <array definitions>
   PROCEDURE y (arg-1 number, ..., arg-n varchar2) IS
      BEGIN
         <procedural code>;
      END;
BEGIN
   WHILE <some-condition> LOOP
      ...
      y (var-1, ..., var-n);
   END LOOP;
END;
/

Let us know if this provides enough of a model for you. (I must run out to an appointment presently, else I'd offer more.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[peterswan]

ok, looks good.

I think this is what I need.

Peter