Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Referencing an array by column values

UW (Germany)Feb 14 2025

This test case is a bit more comprehensive. I have got a table, similar to this one:

create table t1
(id        number,
 ref_1     number,
 ref_2     number,
 d_year    number,
 val_1_jan number,
 val_1_feb number,
 val_1_mrz number,
 val_1_apr number,
 val_1_may number,
 val_1_jun number,
 val_1_jul number,
 val_1_aug number,
 val_1_sep number,
 val_1_oct number,
 val_1_nov number,
 val_1_dec number,
 val_2_jan number,
 val_2_feb number,
 val_2_mrz number,
 val_2_apr number,
 val_2_may number,
 val_2_jun number,
 val_2_jul number,
 val_2_aug number,
 val_2_sep number,
 val_2_oct number,
 val_2_nov number,
 val_2_dec number);

and I wrote a procedure to transform the data into a table similar to this:

create table t2
( id      number generated always as identity(start with 1 increment by 1),
  ref_1   number,
  ref_2   number,
  d_month date,
  val_id  number,
  d_value number,
  idate   date,
  udate   date);

So, when I have this two lines in my first table, with 24 “val_1” and “val_2” values each:

insert into t1 values(1,101,22,2025,23,25,19,29,14,31,28,27,26,26,23,32,26,22,20,30,19,29,28,26,27,25,23,30);
insert into t1 values(2,101,23,2025,17,34,11,26,18,27,25,25,17,17,24,31,29,20,18,39,23,40,41,16,20,27,24,29);

they will be transformed into 48 rows in my second table using this procedure:

-- first a small help table for some logging

create table load_log
( load_date date,
  rows_merged number,
  rows_deleted number);

------------------------------------------------------------------------------------

create or replace procedure data_load is

type val_list is table of number index by pls_integer;
type year_values is table of val_list index by pls_integer;
val year_values;

i_month number;
i_val number;

num_rows_merged number;
num_rows_deleted number;

cursor c is
select
  id        ,
  ref_1     ,
  ref_2     ,
  d_year    ,
  val_1_jan ,
  val_1_feb ,
  val_1_mrz ,
  val_1_apr ,
  val_1_may ,
  val_1_jun ,
  val_1_jul ,
  val_1_aug ,
  val_1_sep ,
  val_1_oct ,
  val_1_nov ,
  val_1_dec ,
  val_2_jan ,
  val_2_feb ,
  val_2_mrz ,
  val_2_apr ,
  val_2_may ,
  val_2_jun ,
  val_2_jul ,
  val_2_aug ,
  val_2_sep ,
  val_2_oct ,
  val_2_nov ,
  val_2_dec 
from t1;

begin

num_rows_merged :=0;
num_rows_deleted :=0;

for c_rec in c loop
  val(1)( 1) := c_rec.val_1_jan;
  val(1)( 2) := c_rec.val_1_feb;
  val(1)( 3) := c_rec.val_1_mrz;
  val(1)( 4) := c_rec.val_1_apr;
  val(1)( 5) := c_rec.val_1_may;
  val(1)( 6) := c_rec.val_1_jun;
  val(1)( 7) := c_rec.val_1_jul;
  val(1)( 8) := c_rec.val_1_aug;
  val(1)( 9) := c_rec.val_1_sep;
  val(1)(10) := c_rec.val_1_oct;
  val(1)(11) := c_rec.val_1_nov;
  val(1)(12) := c_rec.val_1_dec;
  val(2)( 1) := c_rec.val_2_jan; 
  val(2)( 2) := c_rec.val_2_feb;
  val(2)( 3) := c_rec.val_2_mrz;
  val(2)( 4) := c_rec.val_2_apr;
  val(2)( 5) := c_rec.val_2_may;
  val(2)( 6) := c_rec.val_2_jun;
  val(2)( 7) := c_rec.val_2_jul;
  val(2)( 8) := c_rec.val_2_aug;
  val(2)( 9) := c_rec.val_2_sep;
  val(2)(10) := c_rec.val_2_oct;
  val(2)(11) := c_rec.val_2_nov; 
  val(2)(12) := c_rec.val_2_dec; 

  for i_month in 1..12 loop
    for i_val in 1..2 loop

      merge into t2 
         using ( 
         select c_rec.d_year as s_year,
                i_month as s_month,
                c_rec.ref_1 as s_ref_1,
                c_rec.ref_2 as s_ref_2,
                to_date('01'||'.'||trim(to_char(i_month,'00'))||
                        '.'||trim(to_char(c_rec.d_year,'0000')),'dd.mm.yyyy') as s_date,
                i_val as s_val_id,
                val(i_val)(i_month) as s_val           -- add „from dual“ here
                ) src                                  -- for a database before 23ai 
          on (    extract(year from t2.d_month) = src.s_year   
              and extract(month from t2.d_month) = src.s_month
              and t2.ref_1 = src.s_ref_1 
              and t2.ref_2 = src.s_ref_2 
              and t2.val_id = src.s_val_id)
      when matched then
           update set d_value = src.s_val,
                      udate = sysdate
           where d_value <> src.s_val 
      when not matched then
        insert (ref_1, ref_2, d_month, val_id, d_value, idate, udate ) 
        values(src.s_ref_1,src.s_ref_2, src.s_date, src.s_val_id, src.s_val, sysdate, sysdate );

num_rows_merged := num_rows_merged + sql%rowcount;

    end loop;
  end loop;
  commit;
end loop;

delete from t2 where (extract(year from d_month),ref_1, ref_2) not in (select d_year, ref_1, ref_2 from t1);

num_rows_deleted := num_rows_deleted + sql%rowcount;

insert into load_log values(sysdate, num_rows_merged, num_rows_deleted);
commit;

end;
/

Some test:

exec data_load;

select * from load_log;

LOAD_DATE        ROWS_MERGED ROWS_DELETED
---------------- ----------- ------------
14.02.2025 19:48          48            0

Please note that only one row is merged into T2 and gets a new „udate“, when only one value in the table T1 is modified:

update t1 set val_1_jun = 15 where ref_1 = 101 and ref_2 = 22;

exec data_load;

select * from load_log;

LOAD_DATE        ROWS_MERGED ROWS_DELETED
---------------- ----------- ------------
14.02.2025 19:48          48            0
14.02.2025 19:49           1            0

So far so good! But I then I thought that this three nested loops are not very performant and that the merge statement only inserts or updates one single row, each time it is called. I tried to modify my procedure, to handle all 24 values of the input table with one merge statement and I tried this:

create or replace procedure data_load_v2 is

type val_list is table of number index by pls_integer;
type year_values is table of val_list index by pls_integer;
val year_values;

num_rows_merged number;
num_rows_deleted number;

cursor c is
select
  id        ,
  ref_1     ,
  ref_2     ,
  d_year    ,
  val_1_jan ,
  val_1_feb ,
  val_1_mrz ,
  val_1_apr ,
  val_1_may ,
  val_1_jun ,
  val_1_jul ,
  val_1_aug ,
  val_1_sep ,
  val_1_oct ,
  val_1_nov ,
  val_1_dec ,
  val_2_jan ,
  val_2_feb ,
  val_2_mrz ,
  val_2_apr ,
  val_2_may ,
  val_2_jun ,
  val_2_jul ,
  val_2_aug ,
  val_2_sep ,
  val_2_oct ,
  val_2_nov ,
  val_2_dec
from t1;

begin

num_rows_merged :=0;
num_rows_deleted :=0;

for c_rec in c loop
  val(1)( 1) := c_rec.val_1_jan;
  val(1)( 2) := c_rec.val_1_feb;
  val(1)( 3) := c_rec.val_1_mrz;
  val(1)( 4) := c_rec.val_1_apr;
  val(1)( 5) := c_rec.val_1_may;
  val(1)( 6) := c_rec.val_1_jun;
  val(1)( 7) := c_rec.val_1_jul;
  val(1)( 8) := c_rec.val_1_aug;
  val(1)( 9) := c_rec.val_1_sep;
  val(1)(10) := c_rec.val_1_oct;
  val(1)(11) := c_rec.val_1_nov;
  val(1)(12) := c_rec.val_1_dec;
  val(2)( 1) := c_rec.val_2_jan;
  val(2)( 2) := c_rec.val_2_feb;
  val(2)( 3) := c_rec.val_2_mrz;
  val(2)( 4) := c_rec.val_2_apr;
  val(2)( 5) := c_rec.val_2_may;
  val(2)( 6) := c_rec.val_2_jun;
  val(2)( 7) := c_rec.val_2_jul;
  val(2)( 8) := c_rec.val_2_aug;
  val(2)( 9) := c_rec.val_2_sep;
  val(2)(10) := c_rec.val_2_oct;
  val(2)(11) := c_rec.val_2_nov;
  val(2)(12) := c_rec.val_2_dec;

merge into t2
using (
  select c_rec.d_year as s_year,
         i_month as s_month,
         c_rec.ref_1 as s_ref_1,
         c_rec.ref_2 as s_ref_2,
         to_date('01'||'.'||trim(to_char(i_month,'00'))||
                       '.'||trim(to_char(c_rec.d_year,'0000')),'dd.mm.yyyy') as s_date,
         i_val as s_val_id,
         val(i_val)(i_month) as s_val
  from (select level as i_val connect by level < 3)                 -- this is my approach
        cross join (select level as i_month connect by level < 13)  -- to replace two loops
       ) src 
   on (    extract(year from t2.d_month) = src.s_year
       and extract(month from t2.d_month) = src.s_month
       and t2.ref_1 = src.s_ref_1
       and t2.ref_2 = src.s_ref_2
       and t2.val_id = src.s_val_id)     
when matched then
     update set d_value = src.s_val,
                udate = sysdate
     where d_value <> src.s_val
when not matched then
     insert (ref_1, ref_2, d_month, val_id, d_value, idate, udate )
     values(src.s_ref_1,src.s_ref_2, src.s_date, src.s_val_id, src.s_val, sysdate, sysdate );

num_rows_merged := num_rows_merged + sql%rowcount;

  commit;
end loop;

delete from t2 where (extract(year from d_month),ref_1, ref_2) not in (select d_year, ref_1, ref_2 from t1);

num_rows_deleted := num_rows_deleted + sql%rowcount;

insert into load_log values(sysdate, num_rows_merged, num_rows_deleted);
commit;

end;
/

But compiling this procedure gives an error:

LINE/COL ERROR
-------- -----------------------------------------------------------------
74/10 PL/SQL: SQL Statement ignored
83/28 PLS-00201: identifier 'I_VAL' must be declared
83/34 PL/SQL: ORA-03066: invalid PL/SQL expression

The problem here is only the reference of the array “val(i_val)(i_month)”. In the lines before it is possible to reference the columns “i_val” and “i_month” that are created by the two subqueries. How can I solve this problem? Is it possible to reference the array via the columns of the “connect by”-subqueries? Any suggestions for a completely different approach to the problem are also welcome.

This post has been answered by James Su on Feb 15 2025
Jump to Answer

Comments

James Su Feb 14 2025

Do you have a MOS account? You can see the solution here:

https://support.oracle.com/knowledge/Oracle%20Cloud/2767882_1.html

BTW the toad behavior is normal and I usually choose rollback.

ronald_2017 Feb 15 2025

No, I don't have. Can you please copy paste the solution? Thanks.

James Su Feb 15 2025

Please leave an email address.

ronald_2017 Feb 15 2025

I log in. However, it asks Support Identifier. What should I write?

Thanks

James Su Feb 16 2025

You need to purchase the service to see the content.

I may be able to help you in private but if you don't want to post your email I have no way to contact you.

ronald_2017 Feb 16 2025

First of all thanks for your help. My words are not for you. The illogical thing is why should I purchase the service in order to solve the problem which caused by Oracle. What is Oracle support is used for? Besides, it used to be send dm in Oracle forum. Apparently, it is not active now. These kinds of restrictions are really ridiculous in today's world.

BluShadow Feb 17 2025

Oracle support is for paying customers. Like most businesses, not everything is free.

A part of the contract with having support is that customers are not permitted to share the content that is given within support anywhere in public, hence why James cannot post the solution for you here.

For that error you're getting though, it's a bit of a deceptive message. You may want to look at your Firewall (or get your network guys to) as that's a likely cause.

ronald_2017 Feb 17 2025

For that error you're getting though, it's a bit of a deceptive message. You may want to look at your Firewall (or get your network guys to) as that's a likely cause.

After I login, I am redirected to registration page. I don't think it is related to firewall. It is written “Connect your User Account” and there is an item called Support Identifier. What should I type there?

Thanks.

Cookiemonster76 Feb 17 2025

I believe that BluShadow is talking about the error you're getting in toad there, not your issues with oracle support.

BluShadow Feb 17 2025

Indeed, I'm referring to the ORA-24757: duplicate transaction identifier
I'm not trying to tell you how to get into Oracle Support. For that you would need to have a customer support identifier CSID which you only get when you pay for support. If you're working for a company then maybe your company has support with Oracle? In which case your company would have someone who is the support administrator and can assign your login to have support with Oracle (they will tie up your login with the company's support identifier for you). In many companies this is a DBA who has this access.

ronald_2017 Feb 17 2025

First of all thanks for your support. The thing is, even I just select via db link I get ORA-24757 duplicate transaction identifier error. I didn't do any DML. I use it in my laptop, not for the company. It is for the educational purposes. Where should I check in firewall?

Cookiemonster76 Feb 17 2025

So where are these two DB's that you're linking together?

Is one of them on your laptop?

ronald_2017 Feb 17 2025

One is in my laptop. It is a test db. The other one is in my friend's virtual windows server. Maybe somethings need to be changed in the virtual server.

ronald_2017 6 days ago

What do you recommend? Thanks.

Cookiemonster76 5 days ago

Any and all firewalls between your laptop and the virtual windows server need to have the port(s) you're trying to use completely open.

Alternatively stop trying to link those 2 DBs.

Is there a specific reason you need to link to that DB?

If you're just trying out how DB links work you could just create a loopback link (one that connects to the same DB).

ronald_2017 3 days ago

Yes, I want to use dblink between each other. I just recreated the db link as shared this time. However, it gives another error. It works at first but then gives an error.

ronald_2017 3 days ago

I am just wondering that should I replace 'localhost' in the server's listener file with the server's IP address?

1 - 17

Post Details

Added on Feb 14 2025
11 comments
133 views