OraFAQ Forum: Client Tools » Handling DATETIME format using SQLPLUS COPY command

Home » Other » Client Tools » Handling DATETIME format using SQLPLUS COPY command (Oracle 11G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Handling DATETIME format using SQLPLUS COPY command [message #562128]

Sun, 29 July 2012 02:22

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

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Handling DATETIME format using SQLPLUS COPY command [message #562260 is a reply to message #562128]

Mon, 30 July 2012 18:00

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

Report message to a moderator

Re: Handling DATETIME format using SQLPLUS COPY command [message #562264 is a reply to message #562260]

Mon, 30 July 2012 19:16

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.

Report message to a moderator

Re: Handling DATETIME format using SQLPLUS COPY command [message #562338 is a reply to message #562264]

Tue, 31 July 2012 07:15

ThomasG
Messages: 3212
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

Report message to a moderator

Re: Handling DATETIME format using SQLPLUS COPY command [message #562493 is a reply to message #562260]

Wed, 01 August 2012 12:22

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.

Report message to a moderator

Re: Handling DATETIME format using SQLPLUS COPY command [message #562496 is a reply to message #562493]

Wed, 01 August 2012 12:33

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What do you mean you are not sure how to create insert statements?
Just use Alan's query.

Regards
Michel

Report message to a moderator

Re: Handling DATETIME format using SQLPLUS COPY command [message #562498 is a reply to message #562496]

Wed, 01 August 2012 12:40

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.

Report message to a moderator

Re: Handling DATETIME format using SQLPLUS COPY command [message #562499 is a reply to message #562498]

Wed, 01 August 2012 12:45

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.

Report message to a moderator

Re: Handling DATETIME format using SQLPLUS COPY command [message #562523 is a reply to message #562128]

Thu, 02 August 2012 00:12

Littlefoot
Messages: 21823
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).

Report message to a moderator

Re: Handling DATETIME format using SQLPLUS COPY command [message #562619 is a reply to message #562523]

Thu, 02 August 2012 18:52

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');

Report message to a moderator