This title might seem confusing and might not make a lot of sense. But here is what I want to do.
Source Data
Formula: ="Into Codes_t (Staff_num,Code) Values((='"&A3&"'),(:Code)"
Staff_number: 32456 has 3 codes we need to assign ABC, DEF & KLM but since its on one row how can I get multiple insert statements from that data NOTE: At any given time the excel sheet could contain 1 - 1000 records so I need to minimize time spent on this as much as possible.
I was wondering if we can. A: Get multiple insert statements as described above B: Use Accept statements(Var), Placeholders and recursively check those placeholders to prompt for input.
Here is what I have(Hope this gives you an idea of what I want to do)
--ALTER SESSION TO CHANGE DT--
alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS';
set linesize 10000
set pagesize 20000
PROMPT :new_comment
PROMPT :new_date
--Break
Accept new_date PROMPT 'Enter new Exp date' CHAR FORMAT HIDE
SET serveroutput on;
select 'new_date' from dual;
--Break
Accept new_AP1 PROMPT 'Enter new code1 date' FORMAT varchar(20) HIDE
SET serveroutput on;
select 'new_AP1' from dual;
--Break
Accept new_AP2 PROMPT 'Enter new code2 date' FORMAT varchar(20) HIDE
SET serveroutput on;
select 'new_AP2' from dual;
PROMPT
PROMPT
PROMPT 'INSERT STATEMENT RUNNING'
PROMPT
INSERT ALL
Into Codes_t (STAFF_NUM,CODE) VALUES(('32456'),(:new_AP1))
Into Codes_t (STAFF_NUM,CODE) VALUES(('32456'),(:new_AP2))
select *
From Dual;
PROMPT
PROMPT
PROMPT 'INSERT STATEMENT COMPLETED'
PROMPT
PROMPT
PROMPT 'SELECT QUERY START'
PROMPT