Got it, let's sort this out. You can use Oracle's INSERT ALL statement to push rows to both tables in one single SQL call—this cuts down on database round-trips and lets you reuse the value-generating logic from your CONNECT BY clause instead of repeating it twice.

Here's the merged, clean script:

INSERT ALL
  -- First target table: test2
  INTO test2 (val)
  VALUES (generated_val)
  -- Second target table: test3
  INTO test3 (val, data_area, data_add)
  VALUES (generated_val, user_data_area, formatted_timestamp)
-- The core query generates all needed values once
SELECT
  -- Generate the sequential val string
  SUBSTR(:P1_FIRST, 1, 1) || TO_CHAR(TO_NUMBER(REGEXP_SUBSTR(:P1_FIRST, '\d+$')) + LEVEL - 1) AS generated_val,
  -- Fetch the user's data_area
  (SELECT data_area FROM SEC_USERS_LIST WHERE login = LOWER(:APP_USER)) AS user_data_area,
  -- Format the current date/time (no need for a dual subquery here)
  TO_CHAR(SYSDATE, 'dd.mm.yyyy hh24:mi:ss') AS formatted_timestamp
FROM dual
CONNECT BY LEVEL <= TO_NUMBER(REGEXP_SUBSTR(:P1_LAST, '\d+$')) - TO_NUMBER(REGEXP_SUBSTR(:P1_FIRST, '\d+$')) + 1;

Quick Breakdown: #

• The main SELECT block runs once, calculating all the values you need (generated_val, user_data_area, formatted_timestamp) instead of re-running the same string/number logic twice.
• Each INTO clause maps those pre-calculated values to the respective columns in test2 and test3.
• I simplified the date formatting by calling TO_CHAR(SYSDATE...) directly in the main select—no need for an extra subquery against dual.
• Don't forget to commit after executing this (unless your environment uses auto-commit).

内容的提问来源于stack exchange，提问作者Andrii Havrylyak