you should if you want to run it. You either need:

insert into t values ( 1 );
insert into t values ( 2 );


or you need:

insert into t values ( 1 )
/
insert into t values ( 2 )
/


either one works.


BTW -- just had an after thought on this topic as well. You'll love a new feature in 9i called a flashback query. Using that, this person would have been able to open up a query AFTER they committed that "flashed back" to the point in time right before they DELETED the data -- and just query it back out of the database! Instant recovery, no need to reload.

Well, you said

"but I didnt have access to the server on which the database resides."

that sort of precludes using that utility as it relies 100% on utl_file and utl_file can ONLY read files on the server.

So, for you -- plsql won't be much help here as I said.

Load data is only useful if you have the file on the server.

1) Here is an annotated demo of this feature. You can flashback either based on the SCN (system change number) or based on time (time is within a 5 minute window however -- not exact. SCN is exact)

scott@ORA9I.WORLD> @demo012
=================================================================

We'll begin by creating a table of "keep scns"

As we run through our updates, we'll remember the scn in place when
when we began/ended. We can use this to flash back to various points
in time


scott@ORA9I.WORLD>
scott@ORA9I.WORLD>
scott@ORA9I.WORLD> create table keep_scn( msg varchar2(25), scn number );

Table created.

=================================================================


Now, we'll update and commit the EMP table manmy times in a loop. We
remember the system change number before we start and when we are done.
this way, we can flash back to the beginning, middle or end

remember each commit in the system will up the SCN, its our "clock"


scott@ORA9I.WORLD>
scott@ORA9I.WORLD> begin
2 insert into keep_scn values ( 'start', dbms_flashback.get_system_change_number );
3
4 for i in 1 .. 100
5 loop
6 update SCOTT.EMP set sal = sal * 1.01;
7 commit;
8 end loop;
9
10 insert into keep_scn values ( 'stop', dbms_flashback.get_system_change_number );
11 commit;
12 end;
13 /

PL/SQL procedure successfully completed.


=================================================================

Now, lets start flashing back. We'll start with a flash back to the
beginning first:


scott@ORA9I.WORLD>
scott@ORA9I.WORLD> variable x refcursor
scott@ORA9I.WORLD> set autoprint on
scott@ORA9I.WORLD>
scott@ORA9I.WORLD>
scott@ORA9I.WORLD> declare
2 l_scn number;
3 begin
4 select scn into l_scn from keep_scn where msg = 'start';
5 dbms_flashback.enable_at_system_change_number( l_scn );
6 open :x for select ename, sal from SCOTT.EMP;
7 dbms_flashback.disable;
8 end;
9 /

PL/SQL procedure successfully completed.


ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300

14 rows selected.


=================================================================

Now we'll get the SCN that was in effect in the "middle" of update
and view the data at that point in time


scott@ORA9I.WORLD>
scott@ORA9I.WORLD> declare
2 l_scn number;
3 begin
4 select trunc(avg(scn)) into l_scn from keep_scn;
5 dbms_flashback.enable_at_system_change_number( l_scn );
6 open :x for select ename, sal from SCOTT.EMP;
7 dbms_flashback.disable;
8 end;
9 /

PL/SQL procedure successfully completed.


ENAME SAL
---------- ----------
SMITH 1302.67
ALLEN 2605.36
WARD 2035.47
JONES 4844.33
MARTIN 2035.47
BLAKE 4640.79
CLARK 3989.45
SCOTT 4885.04
KING 8141.76
TURNER 2442.51
ADAMS 1791.19
JAMES 1546.9
FORD 4885.04
MILLER 2116.85

14 rows selected.


=================================================================

Now we'll get the SCN that was in effect at the end
and view the data at that point in time


scott@ORA9I.WORLD> declare
2 l_scn number;
3 begin
4 select scn into l_scn from keep_scn where msg = 'stop';
5 dbms_flashback.enable_at_system_change_number( l_scn );
6 open :x for select ename, sal from SCOTT.EMP;
7 dbms_flashback.disable;
8 end;
9 /

PL/SQL procedure successfully completed.


ENAME SAL
---------- ----------
SMITH 2163.82
ALLEN 4327.72
WARD 3381.09
JONES 8046.79
MARTIN 3381.09
BLAKE 7708.73
CLARK 6626.78
SCOTT 8114.43
KING 13524.08
TURNER 4057.24
ADAMS 2975.28
JAMES 2569.53
FORD 8114.43
MILLER 3516.27

14 rows selected.


=================================================================

lastly, we'll flash back back to the start, open a cursor on the
data and then use that cursor to restore our data to that point in
time. we'll rollback the work in effect


scott@ORA9I.WORLD>
scott@ORA9I.WORLD> declare
2 cursor emp_cur is select empno, sal from SCOTT.emp;
3 l_rec emp_cur%rowtype;
4 l_scn number;
5 begin
6 select scn into l_scn from keep_scn where msg = 'start';
7 dbms_flashback.enable_at_system_change_number( l_scn );
8 open emp_cur;
9 dbms_flashback.disable;
10 loop
11 fetch emp_cur into l_rec;
12 exit when emp_cur%notfound;
13 update SCOTT.emp set sal = l_rec.sal where empno = l_rec.empno;
14 end loop;
15 close emp_cur;
16 commit;
17 end;
18 /

PL/SQL procedure successfully completed.


=================================================================

Just to see that it worked...


scott@ORA9I.WORLD>
scott@ORA9I.WORLD> select ename, sal from SCOTT.emp;

ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300

14 rows selected.



2) Well, thats a fact of everyday life for everything. I teach a course internally on new 9i features -- just a bullet on each with a demo here and there to make it real (like the one above). Its about 170 slides and takes eight solid hours and it only touches on new features (and doesn't get them all). There is alot in htere thats for sure.

3) its done the same exact way we do a consistent read -- using the UNDO we have to store to rollback queries in the first place. In 9i, there are UNDO tablespaces (no need for rollback segments anymore) where by you set up an undo retention period of N minutes and that dictates how far back you can "flash back" to.

The flashback query uses the UNDO information (aka rollback data). In 9i, you would set up an UNDO tablespace with a retention period. This retention period is specified in minutes and tells us how long to hang onto rollback for (no need for rollback segments, just an undo tablespace).

If you have the room for a months worth of UNDO online -- you could do it (i'll not be able to test that for you however ;)

The flashback query is a very complete solution for a "smallish" window of time, hours of time. You could use it in conjunction with fine grained auditing (another new 9i feature where by we capture the SQL, the bind variables AND the SCN when you want us to). With that information, you can see what they saw since you can flashback. So, its very useful for watching someone you suspect. It is very useful to recover data you didn't mean to drop and so on.


If I had a need to go back to last month, I would consider a tool such as Workspace Management. Using that, I can simply create a "branch" of my data every month and I'll have the ability to go back to that point in time whenever I want. This is accomplished by versioning records. You can use this to automagically get an audit trail if you like (we can keep the entire change history of a record), you can use this to create versions of your data to "what if", you can use this to create point in time snapshots of your data. Your application doesn't know this is happening (totally transparent). See

</code> http://www.oracle.com/pls/db901/db901.to_toc?pathname=appdev.901/a88806/toc.htm <code>

for details. It would be more applicable to what you wanted to do and would not require having 30 days of UNDO online.

correct, typo on my part -- it is seconds.

sqlldr works on OS files. if you have an OS file, sqlldr would be appropriate.

if you have a variable (a big string), parse/insert is appropriate.

why not have cold fusion bulk insert it? or cannot that API do bulk inserts? Never programed cold fusion -- is it incapable of array inserts?

there is slightly insufficient data to comment here. need an example -- control file, create table (remove most columns, make it small) and sample data.

no way I can answer then.

you might be able to put a trigger on the table to remove it as well...

that'll work just fine. as long as it is quoted like that.

ops$tkyte%ORA11GR2> !cat cm_a.ctl
LOAD DATA
infile *
replace
INTO TABLE CM_A
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
 STREET CHAR "RTRIM(:STREET)",
  NAME CHAR "RTRIM(:NAME)",
   PHONE CHAR "RTRIM(:PHONE)"
)
begindata
"A Street","T/A, slow","123 4567 8901"

ops$tkyte%ORA11GR2> !sqlldr / cm_a

SQL*Loader: Release 11.2.0.3.0 - Production on Fri Aug 2 13:20:08 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 1

ops$tkyte%ORA11GR2> select * from cm_a;

STREET                         NAME
------------------------------ ------------------------------
PHONE
------------------------------
A Street                       T/A, slow
123 4567 8901