If I run script (3) within the FROM database and remove the ( - ), it will run. But when I turn it into a COPY FROM DB1 TO DB2 (my example uses DB1 TO DB1 for you) and try and execute it - it returns right away and does not create the table in the TO database. And I do not get an error.
I'm looking for 40+ different 3 character combos but the characters aren't separated in the table, it is just all squished together. So, I'm trying to parse the data and then look for the 3 character codes. (I know it is an ugly script).
Are there limitations using a COPY FROM DB1 TO DB2 script? It was mentioned to me that there were too many characters in my select statement. If that is the case, does anyone have any suggestions?
Thank you in advance ... Shelli
-- (1) CREATE TABLE RPO_EX
create table rpo_ex (
emp varchar2(25),
option_group varchar2(100));
-- (2) INSERT EXAMPLE DATA INTO TABLE RPO_EX
insert into rpo_ex values ('Gary','AL0AQ9ATHAT8AXJAYGCF5CJ2DCPDR5EF7FE2FE9FHOF46GGCGW6G7Q');
insert into rpo_ex values ('Gary','H2GIO3I15J55KA1KR1LALLTGMAHMYANP5NT7Q5URA6R6WR9NSLMTUJ');
insert into rpo_ex values ('Gary','UDDUE1UPFUQAU2KU80VH9VK3VRGVRHVRJVRKVRLVRMVRNVRRVT7V8D');
insert into rpo_ex values ('Gary','WMFXFE1SD1SZ4AA5A76SU7SU8GR9GR9L3');
insert into rpo_ex values ('Craig','AL0AQ9ATHAT8AXJAYGCJ2DCPDR5EF7FE2FE9FHOGANGGCGM3HHMIO3');
insert into rpo_ex values ('Craig','I15J55KR1LALLCVMAHMYANP5NT7Q5URA6R6WR9NSLMS08TUJUDDUE1');
insert into rpo_ex values ('Craig','UPFUQAU2KU80VH9VRGVRHVRJVRKVRLVRMVRNVRRVT7V8DWMFYM81SA');
insert into rpo_ex values ('Craig','1SZ4C15A76SA7SA8GM9GM9L3');
insert into rpo_ex values ('Fred','1SZ4AA5A76SA7SA8GM9GM9L3');
insert into rpo_ex values ('Fred','AL0AQ9ATHAT8AXJAYGCJ2DCPDR5EF7FE2FE9FHOGBAGGCGM3H2GIO3');
insert into rpo_ex values ('Fred','I15JE5KR1KTILALLCVMAHMYANP5NT7Q5WRA3R6WR9NSLMTUJUDDUE1');
insert into rpo_ex values ('Fred','UPFUQAU2KU80VH9VK3VRGVRHVRJVRKVRLVRMVRNVRRVT7V8DWMF1SA');
insert into rpo_ex values ('Frank','AL0AQ9ATHAT8AXJAYGCF5CJ2DCPDR5EF7FE2FHOGGCGW6G1MH2GIO3');
insert into rpo_ex values ('Frank','I15J55KR1LALLTGMAHMYANC7NP5NU5RA6R1UR6WR9NSLMTUJUDDUE1');
insert into rpo_ex values ('Frank','UPFUQAU2KU80VH9VK3VRGVRHVRJVRKVRLVRMVRNVRRVT7V8DWMFXFE');
insert into rpo_ex values ('Frank','YF51SC1SZ4AA5A76SA7SA8GM9GM9L3');
insert into rpo_ex values ('Jim','4C15A76SA7SA8GM9GM9L3');
insert into rpo_ex values ('Jim','AL0AQ9ATHAT8AXJAYGCJ2DCPDR5EF7FE2FE9FHOGGCGM3GTRHHMIO3');
insert into rpo_ex values ('Jim','I15JE5KR1KTILALLCVMAHMYANP5NT7Q5WRA3R6WR9NSLMTUJUDDUE1');
insert into rpo_ex values ('Jim','UPFUQAU2KU80VH9VRGVRHVRJVRKVRLVRMVRNVRRVT7V8DWMF1SA1SZ');
sqlplus usr/pw@db1 @'copy_rpo_ex.sql' usr/pw@db1 usr/pw@db1
--(3) COPY FROM CREATE TABLE SCRIPT
whenever sqlerror exit sql.sqlcode rollback
SET ARRAYSIZE 5000
SET COPYCOMMIT 20
set linesize 4000
SET TERMOUT OFF
COPY FROM usr/pw@db1 TO usr_pw_db1 -
CREATE rpo_ex2 USING -
SELECT * FROM ( -
WITH v1 as ( -
select emp, option_group, ','||-
substr(option_group,1,3) -
||','||substr(option_group,4,3) -
||','||substr(option_group,7,3) -
||','||substr(option_group,10,3) -
||','||substr(option_group,13,3) -
||','||substr(option_group,16,3) -
||','||substr(option_group,19,3) -
||','||substr(option_group,22,3) -
||','||substr(option_group,25,3) -
||','||substr(option_group,28,3) -
||','||substr(option_group,31,3) -
||','||substr(option_group,34,3) -
||','||substr(option_group,37,3) -
||','||substr(option_group,40,3) -
||','||substr(option_group,43,3) -
||','||substr(option_group,46,3) -
||','||substr(option_group,49,3) -
||','||substr(option_group,52,3)||',' RPO -
from rpo_ex) -
select emp, case -
when (rpo like ('%UFF%') and rpo like ('%UP9%')) then 'UFF+UP9' -
when rpo like ('%GMQ%') then 'GMQ' -
when rpo like ('%IO3%') then 'IO3' -
when rpo like ('%IO4%') then 'IO4' -
when rpo like ('%IO5%') then 'IO5' -
when rpo like ('%IO6%') then 'IO6' -
when rpo like ('%RAO%') then 'RAO' -
when rpo like ('%U0F%') then 'U0F' -
when rpo like ('%U0H%') then 'U0H' -
when rpo like ('%U1C%') then 'U1C' -
when rpo like ('%UCH%') then 'UCH' -
when rpo like ('%UCM%') then 'UCM' -
when rpo like ('%UF7%') then 'UF7' -
when rpo like ('%UFF%') then 'UFF' -
when rpo like ('%UFU%') then 'UFU' -
when rpo like ('%UG4%') then 'UG4' -
when rpo like ('%UGU%') then 'UGU' -
when rpo like ('%UGX%') then 'UGX' -
when rpo like ('%UGY%') then 'UGY' -
when rpo like ('%UH7%') then 'UH7' -
when rpo like ('%UHQ%') then 'UHQ' -
when rpo like ('%UHU%') then 'UHU' -
when rpo like ('%UHV%') then 'UHV' -
when rpo like ('%UHW%') then 'UHW' -
when rpo like ('%UI2%') then 'UI2' -
when rpo like ('%UI4%') then 'UI4' -
when rpo like ('%UI6%') then 'UI6' -
when rpo like ('%UI7%') then 'UI7' -
when rpo like ('%UI8%') then 'UI8' -
when rpo like ('%UL5%') then 'UL5' -
when rpo like ('%UM7%') then 'UM7' -
when rpo like ('%UMT%') then 'UMT' -
when rpo like ('%UNM%') then 'UNM' -
when rpo like ('%UPX%') then 'UPX' -
when rpo like ('%US8%') then 'US8' -
when rpo like ('%UUI%') then 'UUI' -
when rpo like ('%UUJ%') then 'UUJ' -
when rpo like ('%UUN%') then 'UUN' -
when rpo like ('%UXZ%') then 'UXZ' -
when rpo like ('%UYE%') then 'UYE' -
when rpo like ('%UYS%') then 'UYS' end RPO -
from v1 -
where rpo like ('%,GMQ,%') -
or rpo like ('%,IO3,%') -
or rpo like ('%,IO4,%') -
or rpo like ('%,IO5,%') -
or rpo like ('%,IO6,%') -
or rpo like ('%,RAO,%') -
or rpo like ('%,U0F,%') -
or rpo like ('%,U0H,%') -
or rpo like ('%,U1C,%') -
or rpo like ('%,UCH,%') -
or rpo like ('%,UCM,%') -
or rpo like ('%,UF7,%') -
or rpo like ('%,UFF,%') -
or rpo like ('%,UFU,%') -
or rpo like ('%,UG4,%') -
or rpo like ('%,UGU,%') -
or rpo like ('%,UGX,%') -
or rpo like ('%,UGY,%') -
or rpo like ('%,UH7,%') -
or rpo like ('%,UHQ,%') -
or rpo like ('%,UHU,%') -
or rpo like ('%,UHV,%') -
or rpo like ('%,UHW,%') -
or rpo like ('%,UI2,%') -
or rpo like ('%,UI4,%') -
or rpo like ('%,UI6,%') -
or rpo like ('%,UI7,%') -
or rpo like ('%,UI8,%') -
or rpo like ('%,UL5,%') -
or rpo like ('%,UM7,%') -
or rpo like ('%,UMT,%') -
or rpo like ('%,UNM,%') -
or rpo like ('%,UPX,%') -
or rpo like ('%,US8,%') -
or rpo like ('%,UUI,%') -
or rpo like ('%,UUJ,%') -
or rpo like ('%,UUN,%') -
or rpo like ('%,UXZ,%') -
or rpo like ('%,UYE,%') -
or rpo like ('%,UYS,%') -
or rpo like ('%,RAO,%'))
/
exit