Home » SQL & PL/SQL » SQL & PL/SQL » insert in query ?
insert in query ? [message #331380] Thu, 03 July 2008 03:53 Go to next message
ArvindBhope
Messages: 55
Registered: June 2008
Member
Guys,
In a scenario where in I have to insert 5 different columns where in the one of it shd be sysdate and the oythers shd be fetched from source query.But Iam unable to do that.Please help me here.

Target Table


Name Null? Type
----------------------------------------- -------- ----------------------------
DATED DATE
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME VARCHAR2(40)
OBJECT_TYPE VARCHAR2(40)
STATUS NOT NULL VARCHAR2(30)



insert into PQRST.INVALID_JUNK
(select sysdate from dual) DATED,select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where STATUS in('INVALID','N/A') and owner='MNBS' ;

Thanks,
AB
Re: insert in query ? [message #331387 is a reply to message #331380] Thu, 03 July 2008 04:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why not select sysdate from dba_objects as well?
Re: insert in query ? [message #331396 is a reply to message #331380] Thu, 03 July 2008 04:14 Go to previous messageGo to next message
ArvindBhope
Messages: 55
Registered: June 2008
Member
yeah it can but gets this at .....



select to_char(SYSDATE, 'dd:mm:yyyy hh24:mi'),OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS
*
ERROR at line 2:
ORA-01843: not a valid month


where as this is sucessful !!

SQL> select to_char(SYSDATE, 'dd:mm:yyyy hh24:mi') from dual;

TO_CHAR(SYSDATE,
----------------
03:07:2008 09:13


thanks
Arvind
Re: insert in query ? [message #331398 is a reply to message #331396] Thu, 03 July 2008 04:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
select to_char(SYSDATE, 'dd:mm:yyyy hh24:mi'),OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS
*
ERROR at line 2:
ORA-01843: not a valid month

Copy and paste the full statement.

Regards
Michel
Re: insert in query ? [message #331404 is a reply to message #331398] Thu, 03 July 2008 04:36 Go to previous messageGo to next message
ArvindBhope
Messages: 55
Registered: June 2008
Member
SQL> insert into INSITE2_MONITOR.INVALID_JUNK
2 SELECT TO_CHAR(sysdate,'DD.MM.YYYY:HH24:MI:SS') ,OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS
3 from dba_objects where STATUS in('INVALID','N/A') and owner='INSITE2' ;
SELECT TO_CHAR(sysdate,'DD.MM.YYYY:HH24:MI:SS') ,OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS
*
ERROR at line 2:
ORA-01843: not a valid month


And

SQL> desc INSITE2_MONITOR.INVALID_JUNK
Name Null? Type
----------------------------------------- -------- ----------------------------
DATED DATE
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME VARCHAR2(40)
OBJECT_TYPE VARCHAR2(40)
STATUS NOT NULL VARCHAR2(30)

when ever I query for * from INSITE2_MONITOR.INVALID_JUNK iam not sure will I be getting the insereted date in the "DATED" column.

Thanks,
Arvind
Re: insert in query ? [message #331408 is a reply to message #331404] Thu, 03 July 2008 04:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If DATED is of DATE type and you give a string as input value (TO_CHAR(sysdate,'DD.MM.YYYY:HH24:MI:SS')), the error is obvious.

Regards
Michel
Re: insert in query ? [message #331409 is a reply to message #331396] Thu, 03 July 2008 04:41 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ArvindBhope wrote on Thu, 03 July 2008 11:14
yeah it can but gets this at .....



select to_char(SYSDATE, 'dd:mm:yyyy hh24:mi'),OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS
*
ERROR at line 2:
ORA-01843: not a valid month



Why do you to_char it, if you are going to insert it into a date-column again?
Re: insert in query ? [message #331414 is a reply to message #331409] Thu, 03 July 2008 04:46 Go to previous messageGo to next message
ArvindBhope
Messages: 55
Registered: June 2008
Member
Coz I need the time as well in that along with the date.

Thx.
Re: insert in query ? [message #331415 is a reply to message #331408] Thu, 03 July 2008 04:47 Go to previous messageGo to next message
ArvindBhope
Messages: 55
Registered: June 2008
Member
Mike,Please Let me know how do I correct this case!!

thx
Re: insert in query ? [message #331418 is a reply to message #331414] Thu, 03 July 2008 04:54 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ArvindBhope wrote on Thu, 03 July 2008 11:46
Coz I need the time as well in that along with the date.

Thx.

That does not make sense. Sysdate (as a date) contains the time-part as well; how else could a to_char add this?

Try to add a column-list to your insert. It's always dangerous to rely on column-order without explicitly naming the columns:
insert into your_tab (col1, col2, col3) (select sysdate, col2, etc)
Previous Topic: Retrieve one record for one id
Next Topic: REF Cursor Issue
Goto Forum:
  


Current Time: Tue Dec 06 12:06:03 CST 2016

Total time taken to generate the page: 0.14117 seconds