Insert Or Select Date Data Type Value In AM Or PM?
Oct 26, 2006How do i insert or select date data type value in am or pm Eg 10:30:00 am or 10:35:00 pm
View 3 Replies
ADVERTISEMENT
SQL & PL/SQL :: How To Insert Varying Type Of Data
Jul 14, 2010I want to insert varying type of data from one table to another like i want to insert name from one table and salary from another,sysdate too.How should i consider doing it?
View 3 Replies View RelatedSQL & PL/SQL :: How To Get Date From The Timestamp Data Type
Oct 13, 2011How can i get just date from the timestamp data type.
Suppose i have a column timestamp with has data like "2011-05-16 16:19:22.579764-07" when i select from table i just want the date like 2011-05-16.
Precompilers, OCI & OCCI :: Unable To Insert XML Data Into Type Table?
Dec 16, 2008I want to insert XML data into my ( Oracle 11G Release 1 ) XMLType table using OCCI. I'm getting
ORA-01461: can bind a LONG value only for insert into a LONG column
My XML data size is around 1.5 to 2MB. I have also tried using setMaxParamSize before calling the setString method. But, still I'm getting the same exception.
How to resolve this issue?
SQL & PL/SQL :: Convert VARCHAR2 To Date Data Type
Jun 13, 2012I have problem to convert simply varchar to date data type.
For example: 2012-05-28 22:36:08 and I would like to get format 28.5.2012 22:36:08
However I try to do it I get always some errors.
select '2012-05-28 22:36:08',
to_date( '2012-05-28 22:36:08', 'dd.mm.yyyy HH24:MI:SS') ,
to_char('2012-05-28 22:36:08', 'dd.mm.yyyy HH24:MI:SS')
from dual
PL/SQL :: Equals (=) Versus LIKE For Date Data Type
Sep 2, 2013First, I'm aware that the equals (=) operator is a "comparison operator compares two values for equality." In other words, in an SQL statement, it won't return true unless both sides of the equation are equal. For example:
SELECT * FROM Store WHERE Quantity = 200; The LIKE operator "implements a pattern match comparison" that attempts to match "a string value against a pattern string containing wild-card characters."
For example:
SELECT * FROM Employees WHERE Name LIKE 'Chris%';
Here,I query about date type data on ORACLE database, I found the following, when I write select statment in this way:
SELECT ACCOUNT.ACCOUNT_ID, ACCOUNT.LAST_TRANSACTION_DATE FROM ACCOUNT WHERE ACCOUNT.LAST_TRANSACTION_DATE LIKE '30-JUL-07';
I get all rows I'm looking for. but when I use the sign equal =
instead :SELECT ACCOUNT.ACCOUNT_ID, ACCOUNT.LAST_TRANSACTION_DATE FROM ACCOUNT WHERE ACCOUNT.LAST_TRANSACTION_DATE = '30-JUL-07';
I get nothing even though nothing is different except the equal sign.
JDeveloper, Java & XML :: Select Data From XML Type Column
Jan 24, 2013I have a table Table_xml_tmp which has a column MESSAGECONTENTS as XMLType data which contains data as follows
"<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<xclimSchemaMain xmlns="http://clones.telcordia.com">
<response>
<clHeader>
<msgName>queryPlace</msgName>
[code]......
I need to get all rows where node <operationStatus> value as SUCCESS. I am running following query but it is returning null value insted of data of columns
SELECT a.MESSAGECONTENTS.extract('/clHeader/msgName/object/msgType/sender/senderInstance/operationStatus/text()').getStringVal()
FROM Table_xml_tmp a
WHERE a.MESSAGECONTENTS.extract('/clHeader/msgName/object/msgType/sender/senderInstance/operationStatus/text()').getStringVal()= 'SUCCESS'
SQL & PL/SQL :: Custom Date Or Data Type To Compare Semesters
Jul 26, 2011I have a table with two columns called startsem and gradsem they are both CHAR(3). Within those colums are rows that are listed as SemesterYear. For example, F09 is Fall 2009, S09 Spring 2009, and M09 is Summer 2009. I would like to create a constraint that says GradSem must be greater than StartSem b/c no one can travel back in time to graduate. However, as you know you can compare S09 > F09 because it will treat it as a string. I thought I could use a substring and compare the last two digits as a year and that would work but how do I compare the semesters as a time frame? Because in my schema F > S because Spring 2011 comes before Fall 2011 but in reality F < S because to Oracle it is a string and the ASCII value of F is less than S. I cannot chage the coding of the database so editing the rows so they are more date friendly is not an option.
So how can I modify this database to acruately compare StartSem and GradSem.
SQL & PL/SQL :: Table With Modifieddate Column With DATE Data Type
Jun 4, 2012I have a table with modifieddate column with 'DATE' data type.I am facing date format exception and tried with to_char, to_date but its throwing invalid number exception. how to format date accordingly.
SELECT * FROM EMP WHERE modifieddate > '31-Dec-2011 18:30';
ORA-01722: invalid number
01722. 00000 - "invalid number"
PL/SQL :: Check If Data Stored In Varchar2 Type Column Is Date Or Not
Jun 29, 2012Is there a seeded function by which I can check all the rows which stored dates in varchar column.
I have a table say test (test_data varchar2(100));
Now I will insert all types of records into the table varchar,number dates and then i will write q query to etch all those records only which has dates only
INSERT INTO test(1);
INSERT INTO test('ABC');
INSERT INTO test(SYSDATE);
INSERT INTO test(TO_CHAR(SYSDATE,'DD-MON-YYYY'));
INSERT INTO test(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
INSERT INTO test('15/01/2012');
...
commit;
Server Utilities :: Import Data Type Date From Excel To Oracle
Feb 20, 2013I have imported data from excel to oracle 11g. But i found an error like
"Ensure format is entered for datatypes 'Date' and 'TIMESTAMP' on data type pane".
after that i try to modify type date in oracle become 'dd-mmm-yyyy'
SQL & PL/SQL :: Select Data From 2 Tables And Insert Into Another Table
Apr 25, 2013I want to select data from different tables and insert this into one table based on some conditions:
SELECT *
FROM welltest_msr
WHERE well_s = 3419740
AND check_ind = 1
[Code]....
So I tried doing this with selecting the data and looping through it to do the insert.
DECLARE
--
-- WELLS
--
CURSOR c_well
[code].....
Select Data Based On Date
Apr 27, 2010I have a script which is used to run a job based on the users choice. For example: I have two table, Files and Requests
User select the files to be executed for each request. This data will be stored in Requests table.
Table 1: Files
files
======
file-1
file-2
file-3
..
..
file-n
Table 2: Requests
request file lup_date
==================================
request-1 file1,file2,file3 04-JAN-2009
request-2 file1,file4,file5 06-JAN-2009
request-3 file6,file2 021-JAN-2009
request-4 file1,file2 04-FEB-2009
request-5 file1,file2 08-JAN-2009
request-6 file1,file2 04-MAR-2009
.......... ........... ................
request-n-1 file6,file2,file4 04-DEC-2009
request-n file6,file3,file4 04-DEC-2009
how to get the output in below format. Count how many times each file is selected in a month.
Output format should be like below..
==============================================
File_Name Jan Feb Mar Apr ---------- Dec
==============================================
file1 2 1 3 0 ---------- 2
file2 1 0 2 1 ---------- 3
file-n 8 2 3 0 ---------- 2
Server Administration :: Functions To Convert The Long Type Field Data To Varchar2 Type
Oct 17, 2011Is there some functions to convert the long type field data to varchar2 type?
View 2 Replies View RelatedSelect Insert Failing To Insert In 11g?
May 7, 2013In 11g, When I am trying to insert the records with select insert option it is failing.
Below is my Query:
insert into table_1 select * from table_2
The above query is not inserting any records into table_1. But when i query select * from table it is returning records.
The same select Insert query is inserting records properly in 10g.
SQL & PL/SQL :: Changing DATE Type?
Oct 25, 2011oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
In the database, NLS_DATE_FORMAT = DD-MON-RR.I have a date field called LOAD_DATE in one of the tables. I want to store the value as '25-OCT-11 01:16:25 PM' in LOAD_DATE field. Is there a way to do it without changing the NLS parameter (ALTER SESSION...) ?
SQL & PL/SQL :: Select All The Elements Of A Record Type
May 24, 2011Is there any way that I can check what are the elements present in a pl sql record type by querying in table?
For example if I want to check what are elements present in oe_order_pub.header_rec_type and I don't want to open the package, then in which table I should query? Is it possible?
SQL & PL/SQL :: Insert Into Statement Doesn't Insert All Rows Return By Select Statement?
Jan 12, 2011If i inserted the values in table it gets inserting very few rows only.I dont know y it is?
View 15 Replies View RelatedGetting Time From Date Type Column?
Mar 3, 2011I am working with Oracle 8i and I have a problem while applying the below query:
select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_BANDWIDTH_READS
where ENTRYDATE > sysdate-1
and rownum <10
03-03-2011 00:00:00
03-03-2011 00:00:00
03-03-2011 00:00:00
03-03-2011 00:00:00
[code].....
The time appears as 00:00:00. I said it's something weird because if I take the where condition off, then something like this appears:
select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_BANDWIDTH_READS
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
I am using the same query on a different table and there's no problem on it:
select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_XX_PROGRAM
where ENTRYDATE > sysdate -10 and rownum <10
03-03-2011 17:06:48
03-03-2011 17:06:48
03-03-2011 16:59:08
03-03-2011 16:59:08
03-03-2011 12:04:21
I checked the DDL of both tables and the only difference is that the TBL_XX_PROGRAM have the ENTRYDATE field defined as not null and the ENTRYDATE on the TBL_BANDWIDTH_READS does not have the same constraint, could it affect?
How To Order Date Which Has Its Type As Varchar2
Jan 26, 2011I have table called INFO and the column called CREATED_DATE . Now the datatype of CREATED_DATE is of varchar2 . Now If I need to query the table through select statement where I need to order the result based on CREATED_DATE , how can i achieve this ?
View 1 Replies View RelatedSQL & PL/SQL :: Difference Between Two Date Type Fields
Aug 15, 2011I'm having trouble comparing and subtract two date type fields, can not get a significant result.
e.g.
select id, date.started, date.closed, datediff (day, date.started, date.closed) differences diference from Table1 order by desc;
XML DB :: XMLTable With Date Type Column
Sep 5, 2012I am trying to run select query on xml using xmltable. One of the tag returns date and I want to take it in date format in xmltable column.
select t.* from XMLTYPETEST xt ,
XMLTable ('/TestData/trade' passing xt.data
columns
test varchar2(100) path 'GenTest/Id',
testdate date path 'DateTest/Date')t;
But its throwing error as -
SQL Error: ORA-29958: fatal error occurred in the execution of ODCIINDEXCREATE routine
ORA-01830: date format picture ends before converting entire input string
29958. 00000 - "fatal error occurred in the execution of ODCIINDEXCREATE routine"
*Cause: Failed to successfully execute the ODCIIndexCreate routine.
SQL & PL/SQL :: Insert Values Fetched From Object Type Into Empty Table?
May 4, 2012I need to insert values fetched from an object type into an empty table.
-- >> table creation:
create table ASSET
(
ASSETID NUMBER(5),
ASSETTYPE VARCHAR2(20));
-- >> created an object_type
SQL> create or replace type obj1 as object(v_ASSETID NUMBER(20),
2 v_ASSETTYPE varchar2(20));
3 /
Type created
--- >> create the package
SQL> create or replace package test_arr is
2 type nest_tab is table of obj1;
3 procedure insert_data(t_nest IN nest_tab);
4 end test_arr;
5 /
Package created
SQL> create or replace package body test_arr is
2 procedure insert_data(t_nest IN nest_tab) is
3 begin
4 for i in t_nest.first..t_nest.last loop
5 insert into asset(ASSETID,
6 ASSETTYPE) values (t_nest(i).v_ASSETID,
[code]....
-- >> a block to execute the above package:
SQL> declare
2 type ref_tab is table of obj1;
3 ref_tab1 ref_tab;
4 begin
5 ref_tab1 := ref_tab(1,'a');
6 test_arr.insert_data(ref_tab1);
7 end;
8 /
But I am getting the below error when executing the package:
ORA-06550: line 5, column 13:
PLS-00306: wrong number or types of arguments in call to 'REF_TAB'
ORA-06550: line 5, column 13:
PLS-00306: wrong number or types of arguments in call to 'REF_TAB'
ORA-06550: line 5, column 1:
[code]....
SQL & PL/SQL :: Store The Date In String Type Format
Nov 23, 2010We store the date in String Type format of "MON-YYYY", we require to run the report base on that string contain date column, During the Query execute the Error Display of (ora-01858: a non-numeric character was found where a numeric was expected)
The Query is :
select period_name,doc_sequence_value,doc_sequence_id,date_created,name,JE_SOURCE,XX
from
(
select period_name,doc_sequence_value,doc_sequence_id,date_created,name,JE_SOURCE,TO_DATE(XX,'DD/MM/RRRR') XX
from
(
select TO_CHAR(TO_DATE(('01-'||substr(gl.period_name,1,3)||'-'||substr(gl.period_name,5,2)),'DD/MM/RR'),'DD-MON-RRRR') xx,
[code].....
SQL & PL/SQL :: Write Select Query To Get Sum Of Quantity For Each Type Of Item?
May 26, 2010I want to write select query to get sum of quantity for each type of item ,in the table I have itemnum,itemtype and quantity ,type have value(A,B,C,....)
the result will be like that ex:
item.No A B C D ....
10 50 60 80 20
--- 50 will be sum(quantity) for item type A
how can I do that, I mean how can I retrieve many column from the same field?
SQL & PL/SQL :: Call Function With Row Type Return In Oracle Select Statement
Jul 3, 2012How to call a function with a row type return in an Oracle select statement.
For e.g. :
If I had this function with a rowtype return:
------------------------------
create function abc
return xyz%rowtype
is
rec xyz%rowtype;
begin
select * into rec from xyz where col1 = n;
return rec;
end;
--------------------------------
How could I use this in a select clause, as there is a multi column return by the function ?
Server Utilities :: Insert Data Without Writing Insert Statement In Oracle?
May 15, 2010how to insert data in oracle table without writing insert statement in oracle 9i or above. i am not going to write insert all, merge, sqlloder and import data.
View 2 Replies View RelatedForms :: When Insert Detail The Data Insert Twice Time
Feb 27, 2012when i tried to insert the details from oracle froms..the data inserts twice to the DB..
my table structure:
create table app_sri
(a_id integer primary key,
p_first_name varchar2(30),
p_last_name varchar2(20),
p_age number(3)
);
here a_id can be genarated through simple sequence(pid_seq)...
trigger on app_sri
create or replace trigger pid_trg
[Code]....
form insertion code..
Begin
insert into app_sri(null,'robo','Big',100);
commit
End;
the data inserted...but twice
what is the reason behind the double insertion?
Client Tools :: Date Format And Calendar Type Are Not Changing In Oracle Form?
Oct 30, 2011i have changed date's format and the calendar type in sqlplus through the following command so the date's format and the calender type both changed successfully and work, but in oracle form the they haven't changed.
alter session set nls_calendar='persian';
ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD';
ALTER SYSTEM SET NLS_DATE_FORMAT='YYYY/MM/DD' SCOPE=SPFILE;
Note: i know session would change the format temporally but i want to change it permanently so i tried alter_system but still no result
Insert With Nested Select
Jul 24, 2007I have here my SQL
-> INSERT into myTable (col1, col2, col3) values(SELECT table_seq.nextval from dual, 'value2', 'value3');
That's not working...