Home » Other » Client Tools » Handling DATETIME format using SQLPLUS COPY command (Oracle 11G)
Handling DATETIME format using SQLPLUS COPY command [message #562128] Sun, 29 July 2012 02:22 Go to next message
infinitenumbers
Messages: 33
Registered: November 2010
Member
Hi Guys,

I am using the SQL*PLUS COPY command to move the data from my database to another remote database. The data in my database also contains DATETIME format. But since COPY command cannot handle DATETIME format, I am wondering is there any workaround for this.


Note: Due to some limitations, I cannot use other methods like DATABASE LINK or EXPDP/IMPDP commands.


Thanks in Advance,
shil

[Updated on: Sun, 29 July 2012 02:26]

Report message to a moderator

Re: Handling DATETIME format using SQLPLUS COPY command [message #562130 is a reply to message #562128] Sun, 29 July 2012 02:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no DATETIME datatype in Oracle.
There is a DATE datatype, TIMESTAMP datatypes but no DATETIME.

Note that SQL*Plus COPY command is obsolete.
If you cannot create database link (by the way, COPY command uses database link) nor export/import, you just can spool into a file and load the table in the other database.

Regards
Michel
Re: Handling DATETIME format using SQLPLUS COPY command [message #562260 is a reply to message #562128] Mon, 30 July 2012 18:00 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
Shil,

I create insert statements that I spool to a flat file. Then I run the insert
statements on the other database.

The complicated part is that I insert with sqlplus "O'hara" as "O''hara" because sqlplus
will convert the 2 quotes to 1 quote. There are ways to get around this but I find that
code releases work much better with just the two quotes instead of one.

What is really weird is that my code is also run in sqlplus so when I run "temp.sql"
I need 4 quotes that will be converted to 2 and 2 quotes that will be converted to 1.
See "temp.sql" below.

SCOTT > select * from alan;

     EMPNO ENAME      JOB              MGR HIREDATE
---------- ---------- --------- ---------- --------------------------------------------
      7369 SMITH      CLERK           7902 17-DEC-80 12.00.00.000000 AM
      7499 O'hara     SALESMAN        7698 20-FEB-81 12.00.00.000000 AM
      7521 WARD       SALESMAN        7698 22-FEB-81 12.00.00.000000 AM
      7566 JONES      MANAGER         7839 02-APR-81 12.00.00.000000 AM
      7654 MARTIN     SALESMAN        7698 28-SEP-81 12.00.00.000000 AM
      7698 BLAKE      MANAGER         7839 01-MAY-81 12.00.00.000000 AM
      7782 CLARK      MANAGER         7839 09-JUN-81 12.00.00.000000 AM
      7788 SCOTT      ANALYST         7566 19-APR-87 12.00.00.000000 AM

SCOTT > @temp

insert into ALAN values(7369,'SMITH','CLERK',7902,'17-DEC-80 12.00.00.000000 AM');
insert into ALAN values(7499,'O''hara','SALESMAN',7698,'20-FEB-81 12.00.00.000000 AM');
insert into ALAN values(7521,'WARD','SALESMAN',7698,'22-FEB-81 12.00.00.000000 AM');
insert into ALAN values(7566,'JONES','MANAGER',7839,'02-APR-81 12.00.00.000000 AM');
insert into ALAN values(7654,'MARTIN','SALESMAN',7698,'28-SEP-81 12.00.00.000000 AM');
insert into ALAN values(7698,'BLAKE','MANAGER',7839,'01-MAY-81 12.00.00.000000 AM');
insert into ALAN values(7782,'CLARK','MANAGER',7839,'09-JUN-81 12.00.00.000000 AM');
insert into ALAN values(7788,'SCOTT','ANALYST',7566,'19-APR-87 12.00.00.000000 AM');

SCOTT > list
  1  select 'insert into ALAN values('
  2  ||nvl(to_char(EMPNO),'null')
  3  ||','||''''||replace(ENAME,'''','''''')||''''
  4  ||','||''''||replace(JOB,'''','''''')||''''
  5  ||','||nvl(to_char(MGR),'null')
  6  ||','||''''||replace(HIREDATE,'''','''''')||''''
  7* ||');' from SCOTT.ALAN

For tables with few rows I just cut and paste the insert
statements from one session to the other to run them.

If you describe one of your tables, I would be happy to put together the first sql for you.
Alan
Re: Handling DATETIME format using SQLPLUS COPY command [message #562264 is a reply to message #562260] Mon, 30 July 2012 19:16 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
I see that I could have used chr(39) instead of two single quotes.

ECSESBP1 > select chr(39) from dual;

C
-
'

1 row selected.
Re: Handling DATETIME format using SQLPLUS COPY command [message #562338 is a reply to message #562264] Tue, 31 July 2012 07:15 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Starting with Oracle 10 there is also the "extended quoting" mechanism.

SQL> select q'(who'da thunk it, it's workin'.)' str from dual;

STR
------------------------------
who'da thunk it, it's workin'.

SQL>


http://askanantha.blogspot.de/2007/12/q-quote-operator-introduced-in-oracle.html

Re: Handling DATETIME format using SQLPLUS COPY command [message #562493 is a reply to message #562260] Wed, 01 August 2012 12:22 Go to previous messageGo to next message
infinitenumbers
Messages: 33
Registered: November 2010
Member
Thanks for your reply.
But I am not sure how to create insert statements using SQL*PLUS. Please let me know.
Re: Handling DATETIME format using SQLPLUS COPY command [message #562496 is a reply to message #562493] Wed, 01 August 2012 12:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you mean you are not sure how to create insert statements?
Just use Alan's query.

Regards
Michel
Re: Handling DATETIME format using SQLPLUS COPY command [message #562498 is a reply to message #562496] Wed, 01 August 2012 12:40 Go to previous messageGo to next message
infinitenumbers
Messages: 33
Registered: November 2010
Member
Thanks Michel.

But the thing is I have a list of tables to loop through my shell script. And I need to copy the data from each of these tables to a remote database. They contain different number of data types and number of columns.

I am not sure how to do that.
Re: Handling DATETIME format using SQLPLUS COPY command [message #562499 is a reply to message #562498] Wed, 01 August 2012 12:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>But the thing is I have a list of tables to loop through my shell script.
>And I need to copy the data from each of these tables to a remote database.
>They contain different number of data types and number of columns.
>I am not sure how to do that.

Above is much different than original posted problem.
Consider using PL/SQL & UTL_FILE to accomplish the expanded requirements.
Re: Handling DATETIME format using SQLPLUS COPY command [message #562523 is a reply to message #562128] Thu, 02 August 2012 00:12 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
infinitenumbers wrote on Sun, 29 July 2012 09:22
Note: Due to some limitations, I cannot use other methods like DATABASE LINK or EXPDP/IMPDP commands.

Could you explain that (limitations, I mean)?

If you can't access database server (for Data pump you need a directory which is located on the server), then UTL_FILE won't work either (as it needs the same (sort of a) directory - on a server).
Re: Handling DATETIME format using SQLPLUS COPY command [message #562619 is a reply to message #562523] Thu, 02 August 2012 18:52 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
You can simply connect to the database, spool the contents to a file and then connect to the other database and run the inserts.
SCOTT > connect scott/tiger@cscdap1p
Connected.
SCOTT > select * from alan;
      7369 SMITH      CLERK           7902 17-DEC-80 12.00.00.000000 AM
      7499 O'hara     SALESMAN        7698 20-FEB-81 12.00.00.000000 AM
      7521 WARD       SALESMAN        7698 22-FEB-81 12.00.00.000000 AM
      7566 JONES      MANAGER         7839 02-APR-81 12.00.00.000000 AM
      7654 MARTIN     SALESMAN        7698 28-SEP-81 12.00.00.000000 AM
      7698 BLAKE      MANAGER         7839 01-MAY-81 12.00.00.000000 AM
      7782 CLARK      MANAGER         7839 09-JUN-81 12.00.00.000000 AM
      7788 SCOTT      ANALYST         7566 19-APR-87 12.00.00.000000 AM

SCOTT > connect scott/tiger@cscdas1s
Connected.
SCOTT > select * from alan;

no rows selected

Watch me run the following "temp.sql" script to spool out the
rows from production and load them into stage.
"temp.sql" looks like the following:
connect scott/tiger@cscdap1p
set lines 300
set pages 0
set feedback off
spool temp.lst
select 'insert into ALAN values('
||nvl(to_char(EMPNO),'null')
||','||''''||replace(ENAME,'''','''''')||''''
||','||''''||replace(JOB,'''','''''')||''''
||','||nvl(to_char(MGR),'null')
||','||''''||replace(HIREDATE,'''','''''')||''''
||');' from SCOTT.ALAN;
spool off
set pages 50
set feedback on
connect scott/tiger@cscdas1s
@temp.lst
commit;
SCOTT > @temp
Connected.
insert into ALAN values(7369,'SMITH','CLERK',7902,'17-DEC-80 12.00.00.000000 AM');
insert into ALAN values(7499,'O''hara','SALESMAN',7698,'20-FEB-81 12.00.00.000000 AM');
insert into ALAN values(7521,'WARD','SALESMAN',7698,'22-FEB-81 12.00.00.000000 AM');
insert into ALAN values(7566,'JONES','MANAGER',7839,'02-APR-81 12.00.00.000000 AM');
insert into ALAN values(7654,'MARTIN','SALESMAN',7698,'28-SEP-81 12.00.00.000000 AM');
insert into ALAN values(7698,'BLAKE','MANAGER',7839,'01-MAY-81 12.00.00.000000 AM');
insert into ALAN values(7782,'CLARK','MANAGER',7839,'09-JUN-81 12.00.00.000000 AM');
insert into ALAN values(7788,'SCOTT','ANALYST',7566,'19-APR-87 12.00.00.000000 AM');
Connected.

1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.

Commit complete.

SCOTT > connect scott/tiger@cscdas1s
Connected.
SCOTT > select * from alan;

     EMPNO ENAME      JOB              MGR HIREDATE
---------- ---------- --------- ---------- --------------------------------------------
      7369 SMITH      CLERK           7902 17-DEC-80 12.00.00.000000 AM
      7499 O'hara     SALESMAN        7698 20-FEB-81 12.00.00.000000 AM
      7521 WARD       SALESMAN        7698 22-FEB-81 12.00.00.000000 AM
      7566 JONES      MANAGER         7839 02-APR-81 12.00.00.000000 AM
      7654 MARTIN     SALESMAN        7698 28-SEP-81 12.00.00.000000 AM
      7698 BLAKE      MANAGER         7839 01-MAY-81 12.00.00.000000 AM
      7782 CLARK      MANAGER         7839 09-JUN-81 12.00.00.000000 AM
      7788 SCOTT      ANALYST         7566 19-APR-87 12.00.00.000000 AM

And the temp.lst that temp.sql created looks like the following:
insert into ALAN values(7369,'SMITH','CLERK',7902,'17-DEC-80 12.00.00.000000 AM');
insert into ALAN values(7499,'O''hara','SALESMAN',7698,'20-FEB-81 12.00.00.000000 AM');
insert into ALAN values(7521,'WARD','SALESMAN',7698,'22-FEB-81 12.00.00.000000 AM');
insert into ALAN values(7566,'JONES','MANAGER',7839,'02-APR-81 12.00.00.000000 AM');
insert into ALAN values(7654,'MARTIN','SALESMAN',7698,'28-SEP-81 12.00.00.000000 AM');
insert into ALAN values(7698,'BLAKE','MANAGER',7839,'01-MAY-81 12.00.00.000000 AM');
insert into ALAN values(7782,'CLARK','MANAGER',7839,'09-JUN-81 12.00.00.000000 AM');
insert into ALAN values(7788,'SCOTT','ANALYST',7566,'19-APR-87 12.00.00.000000 AM');
Re: Handling DATETIME format using SQLPLUS COPY command [message #562620 is a reply to message #562619] Thu, 02 August 2012 18:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
INSERT INTO ALAN@cscdas1s SELECT * FROM ALAN@cscdas1P;
-- Above does the same with less complication & overhead.
Re: Handling DATETIME format using SQLPLUS COPY command [message #562632 is a reply to message #562620] Fri, 03 August 2012 00:17 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right, but the OP says he can't use database link (didn't explain why. Yet).
Previous Topic: How to have optional parameters and default values in SQL*Plus script?
Next Topic: remove spaces (4 Merged)
Goto Forum:
  


Current Time: Thu Mar 28 16:34:17 CDT 2024