I have a table with zip codes and their plus four values. For ex: zip code of 10000, which has corresponding plus four values of 001, 002, 003, and 008, 009, 010. The issue is just that--a zip code can have sequential plus four values, and then it will skip several potential plus four values, and then start again. I would like to assign a low plus 4 value and high plus four value to a zip code, keeping in mind that the plus four values are not always sequential. So, it would be similar to this:
zip plus4 low plus4 high 10000 001 003 10000 008 010
I currently have a problem where I have two date fields with time stamps. The only bit i am currently interested in in these fields is the time factor. When i display them in their field they have a format of HH24:MI .
I have a start time and end time as well as a duration and duration type. What I am trying to do is the following: when the user inputs the start time, along with the duration say 1 for example and the duration type of say HRS for example I would like to have the end_datetime default to 1 HR from the current start time. This is the code I use on a when validate item trigger to acheive this:
case :blk.duration_type when 'HRS' then :blk.end_datetime := :blk.start_datetime + ((1/24)* :blk.duration); when 'MINS' then :blk.end_datetime := :blk.start_datetime + ((1/24/60)* :blk.duration);
However, every time it triggers the value put into end_datetime is 0:00 is it something to do with the datatypes im using .
i have 2 data block (maintenance & maintenanceparts) in one form and i will like to add the values of the attribute "LineCost" in the maintenanceparts datablock and put the sum into 'Totalcost' attribute in the maintenance datablock.
I tried use the code
"begin select sum(maintenanceparts.LineCost)into :maintenance.totalcost from MaintenanceParts; end;"
But it ended up adding all the records that was save in that attribute and putting it in totalcost. I am new to oracle
I have a column where I have time stamp with date and time(8-May-10 10:20:55 Am) from that i will get only time(10:20:55 Am)[query for this mentioned below] Now i need to append the time to current date and insert into a Timestamps column only .
so my result should be 17-May-11 10:20:55 Am(Assuming current Date is 17-May-11) . I tried search it by i found a way in sql server by using DataAdd and DataDiff .
query to get the time is:select to_char(to_date('11-MAY-11 05.00.00 PM', 'DD-Mon-YY HH:MI:SSAM'),'HH24:MI:SS') "Time Now" from dual
i am fairly new in the oracle arena, but what would cause a statement such as
ALTER TABLE TEST_TABLE MODIFY text_field1 varchar2(100) DEFAULT 'testval' NULL
to change a column's type from VARCHAR2(100) to VARCHAR2(100 byte)? i found a few mentions of the 100 byte concept online but nothing that jumped out at me.
is it possible to compare two time values in oracle sql ie there is a column say 'tot' with values 8:29,11:35 etc(8hrs29 mins etc) can i compare this column with 03:00hrs i tried select case tot>=3 then 1 as days end from tablename;
I need to create a DB on a pc with 4GB (RAM). I can use the assistant but where I need to set correctly a value related to the memory? Is it necessary to set values for best performances in response time?
I studied a document about lexical parameter in that it says "Lexical parameters are used to substitute multiple values at run time and are identified by a preceding '&'. Lexical s can consist of as little a one line where clause to an entire select statement"
Select * from emp, deptno &where.
and i know about substitution variables using & is this are same (lexical and substitution) or different.
I have a column named "col1" with datatype "varchar2(10)" and row wise entries like "1,1A, 2,3...,10,2A,..." like. I want to order it like "1, 1A ,2,2A, 2B,3... 10...".I tried it with to_number() but it gives me
in our application we are using clob column instead of varchar2 because varchar2 does not allow more that 4000 chars, so Using clob allows to put data of any length, will it cause performance issues ? we have this column in almost in all tables .
Once a year in the application we have a specific query that gets used a lot. It's an UPDATE that updates a single record in a single table with a few different datatypes, but the issue is happening with one of the VARCHAR2 fields. It updates one VARCHAR2(2000) and three VARCHAR2(4000) fields at the same time.
This year, 9 of the 95 times it was used resulted in one of the VARCHAR2(4000) fields as null in the database. The users would not want this field to be null and 5 of the 9 have told us they entered something (the form they're filling out is a research proposal and leaving this field empty would be pointless because it's part of the funding request, so they're not doing it). The application isn't doing it because it's not consistent. I've checked the application and these fields can't be nulled any other way.
We just found the issue so I looked back over the past years back to 2005. Last year it didn't happen at all. In 2010 it happened a handful of times. Some years there were even more times. It's not always the same field but it's always a VARCHAR2 of at least 2000 characters.
I have a lot more information but it's all just details (let me know if you need to know more). I'm wondering if there is a bug in 10g with these types of fields. I don't believe it's malicious behavior on an individual's part but I suppose that's always possible.
how to research something like this. I tried to get access to Oracle Support and the Knowledge Base I heard they have but it doesn't look like I can do that
I have 2 tables.The column in table A is number and Column in table B is a varchar2 datatype.I have to use the Column of table B as a filter to column of Table A.Below is the example.
create table A(Col1 number); Inert into A values(1); Inert into A values(2); Inert into A values(3); Inert into A values(4);
Create table B(Col1 Varchar2(100)); Insert into b value ('1,2,3');
Select * from A where col1 in (select col1 from b) Error: Invalid Number
Is there a way to convert the varchar to number.The varchar field have multiple characters (numbers) seperated by commas.
When I try to convert numeric values � number(19) p.s 111111111111111111, the to_char function returns �1111111111111110000� because the to_char functions doesn�t support precision bigger than 15.
I 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 ?
I have a stored proc which takes IN parameter of datatype varchar2.When I am trying to run the proc it is throwing error that "input buffer too small".The datatype assigned to IN parameter is of varchar2(200) but actually the length of the parameter passed is around 500 characters.the way to increase the length of Input parameter to 500 characters??