Scripts to write scripts 3

[THoey]

I have gotten pretty good at writing SQL that generates other SQL. But try as I might, I can't figure out how to add this twist to it.

I have a call history table that has a record with data for each call into our system. I need to move this data from one schema to another in. Since there may be hundreds of thousands of rows, I want to move a days worth, commit it, move another days worth, etc. In the past, I have written out the script for one day, copy and pasted it for the number of days to move, and then editted each line manually to set the date.

I get the feeling this would be done using an IN clause and a sub-query to populate the DISTINCT TRUNC dates of the data. The basic query for one day would be:

insert into easl_prod_history.call_history 
   select * from easl_prod.call_history 
   where trunc(call_date) = '09-APR-07'; 
commit;

Any Suggestions?

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[hoinz]

What about a variable date, using sysdate ?
Are you restricted to SQL only?
Maybe a loop in PL/SQL: sysdate-1, ..., sysdate-n
You needn't adjust dates every time you call the program.

hope this helps

 

[SantaMufasa]

Terry,

Is yours a script that you run manually on an ad hoc basis? Do you want the script to prompt for the earliest date or a date range?

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

 

[THoey]

Thanks for responding. In this instance it would be run on an adhoc basis. But really, I am just wanting to learn how to do this for any adhoc instance.

So, in this case, for every grouping of trunc(date) data in the table, I want to move the data, and commit it. An example would be I have data like the following in a table:

DATE        CUSTID
01-JAN-07   0001
01-JAN-07   0002
02-JAN-07   0002
03-JAN-07   0003

Based off this sample data in a table, I would run this script and it would create a set of scripts like the following:

insert into easl_prod_history.call_history 
   select * from easl_prod.call_history 
   where trunc(call_date) = '01-JAN-07'; 
commit;
insert into easl_prod_history.call_history 
   select * from easl_prod.call_history 
   where trunc(call_date) = '02-JAN-07'; 
commit;
insert into easl_prod_history.call_history 
   select * from easl_prod.call_history 
   where trunc(call_date) = '03-JAN-07'; 
commit;

Let me know if you need any more info...

Thanks again.

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[engineer2100]

Try this out

set define off
spool C:\insertscript.sql
declare
cursor cr1 is select distinct call_date call_date from call_history order by 1;
v_str varchar2(2000);
begin
dbms_output.enable(100000);
for crec in cr1
loop
  v_str := 'insert into easl_prod_history.call_history ';
  v_str := v_str || ' select * from easl_prod.call_history' ;
  v_str := v_str || ' where trunc(call_date) = to_date(''';
  v_str := v_str || to_char(crec.updt_dt,'dd-mon-yyyy') || ''',''dd-mon-yyyy'')';
  dbms_output.put_line(v_str);
end loop;
end;
spool off

and then run C:\insertscript.sql from SQL prompt

HTH

Thanks
Engi

 

[THoey]

Sorry for the late response. Thanks Engi. I will give this a try and post a response if it does what I am looking for.

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[Beilstwh]

Why not setup a third schema that has select permissions on the first table and insert permissions on the second. Then all you would have to do is a simple

insert into easl_prod_history.call_history
select * from easl_prod.call_history
where call_date between to_date('01-jan-2007','dd-mon-yyyy') and to_date('05-jan-2007','dd-mon-yyyy');


Bill
Oracle DBA/Developer
New York State, USA

 

[THoey]

Sorry Bill, I think you are missing the question. There are too many records in this table to move them over at once like you describe. So I am wanting to learn how to write a script that will go through a large table and create a set of "move and commit" scripts so that I don't blow any rollback buffers, etc.

I tried the suggestion above, but it keeps erroring out. I created a couple test tables to play with this script and then modified the script as follows:

 set define off;
spool C:\test.sql;
declare
cursor cr1 is select distinct call_date call_date from call_history order by 1;
v_str varchar2(10000);
begin
dbms_output.enable(1000000);
for crec in cr1
loop
  v_str := 'insert into test2 ';
  v_str := v_str || ' select * from test' ;
  v_str := v_str || ' where trunc(call_date) = to_date(''';
  v_str := v_str || to_char(crec.call_date,'dd-mon-yyyy') || ''',''dd-mon-yyyy'')';
  dbms_output.put_line(v_str);
end loop;
end;
spool off;

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[THoey]

Duh... I guess it might help to know what the errors are. With the above script, I get the following error:

spool off;
*
ERROR at line 15:
ORA-06550: line 15, column 1:
PLS-00103: Encountered the symbol "SPOOL"

If I comment out the SPOOL commands, the procedure runs, but I get no output.

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[SantaMufasa]

Terry,

Your code should, instead, look like the following (with changes in bold):

set define off;
spool C:\test.sql;
[b]set serveroutput on[/b]
declare
cursor cr1 is select distinct call_date call_date from call_history order by 1;
v_str varchar2(10000);
begin
dbms_output.enable(1000000);
for crec in cr1
loop
  v_str := 'insert into test2 ';
  v_str := v_str || ' select * from test' ;
  v_str := v_str || ' where trunc(call_date) = to_date(''';
  v_str := v_str || to_char(crec.call_date,'dd-mon-yyyy') || ''',''dd-mon-yyyy'')';
  dbms_output.put_line(v_str);
end loop;
end;
[b]/[/b]
spool off;

Note the "set serveroutput on" SQL*Plus command that opens an output stream to your screen, and note the terminating slash ("/") following the "end;" statement. The slash tells the PL/SQL interpretter to treat the previous code as a block..."spool off" is certainly not part of the PL/SQL block since it is SQL*Plus (and PL/SQL cannot recognize/process any SQL*Plus commands).

Also note that no SQL*Plus commands require a semi-colon (";")...it doesn't hurt to use them, but they are extraneous. Therefore, the semi-colons at the end of your...

set define off;
spool C:\test.sql;
spool off;

...lines are all extraneous.

Let us know what happens following the changes, above.

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

 

[THoey]

Okay, we are getting close. The below result snippet is just the end of what was generated:

insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('26-jun-2007','dd-mon-yyyy')
insert into test2  select * from test where trunc(call_date) = to_date('26-jun-2007','dd-mon-yyyy')

Issues:

1. I would want it to create one query for 25-JUN-07 and another for 26-JUN-07. Is this done by some sort of grouping?

2. Need the semi-colon at the end of the statement. I could probably figure out how to do that.

3. Also want a commit statement to follow each insert statement. I can probably also figure this out.

Thanks for the assistance so far. We are almost to what I was looking for.

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[Beilstwh]

try the following

set define off;
spool C:\test.sql;
set serveroutput on
declare
cursor cr1 is select trunc(call_date) call_date from call_history
group by trunc(call_date) 
order by 1;
v_str varchar2(10000);
begin
dbms_output.enable(1000000);
for crec in cr1
loop
  v_str := 'insert into test2 ';
  v_str := v_str || ' select * from test' ;
  v_str := v_str || ' where trunc(call_date) = to_date(''';
  v_str := v_str || to_char(crec.call_date,'dd-mon-yyyy') || ''',''dd-mon-yyyy'')';
  dbms_output.put_line(v_str);
  dbms_output.put_line('commit;');
end loop;
end;
/
spool off;

Bill
Oracle DBA/Developer
New York State, USA

 

[THoey]

Bill, Engi and Dave,

Thanks so much. As I said, this was just one example of a case where I needed to do something like this, so it was a learning process. I appreciate everone's input as it all helped to end up with the final result.

Code:

set define off;
spool C:\test.sql;
set serveroutput on
declare
cursor cr1 is select trunc(call_date) call_date from call_history
group by trunc(call_date) 
order by 1;
v_str varchar2(10000);
begin
dbms_output.enable(1000000);
for crec in cr1
loop
  v_str := 'insert into test2 ';
  v_str := v_str || ' select * from test' ;
  v_str := v_str || ' where trunc(call_date) = to_date(''';
  v_str := v_str || to_char(crec.call_date,'dd-mon-yyyy') || ''',''dd-mon-yyyy'')' || ';';
  dbms_output.put_line(v_str);
  dbms_output.put_line('commit;');
end loop;
end;
/
spool off;

Result:

insert into test2  select * from test where trunc(call_date) = to_date('20-jun-2007','dd-mon-yyyy');
commit;
insert into test2  select * from test where trunc(call_date) = to_date('21-jun-2007','dd-mon-yyyy');
commit;
insert into test2  select * from test where trunc(call_date) = to_date('22-jun-2007','dd-mon-yyyy');
commit;
insert into test2  select * from test where trunc(call_date) = to_date('25-jun-2007','dd-mon-yyyy');
commit;
insert into test2  select * from test where trunc(call_date) = to_date('26-jun-2007','dd-mon-yyyy');
commit;

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...