SQL & PL/SQL :: Date Stored As Number

May 7, 2010

I have a table

CREATE TABLE TEST
(
X NUMBER(8),
SNUM NUMBER
)
SET DEFINE OFF;
Insert into TEST (X, SNUM) Values (20100409, 1);
Insert into TEST (X, SNUM) Values (20100317, 1);

[Code]...

For some reason, column x is a number data type, but the data is actually date.

My aim:

1. select the maximum of the dates stored in column x (in numeric data)..for a given snum.

2. check if it is within 3 days range and return 1 or 0

My program

CREATE OR REPLACE FUNCTION f_chk (in_num number)
RETURN NUMBER
IS
v_chk NUMBER;
v_max_dat number;
BEGIN
SELECT COUNT (*) INTO v_chk

[Code]....

I have following assumptions, correct me if I am wrong:

SELECT max(x) INTO v_max_dat FROM test WHERE snum = in_num;

1. The above will actually give me the maximum date for a given snum (correct result), since the data is stored in yyyymmdd format, it does actually picks up the maximum date (stored in number format) for a given snum ... or should i use to_date to convert it to date and declare v_max_dat as date ?

View 7 Replies


ADVERTISEMENT

SQL & PL/SQL :: Date Datatype Being Stored As DD:MM:YY HH:MI:SS?

Feb 21, 2012

I have a table in my database with a column called theme_night_date that i want to store just a date and no time.

View 2 Replies View Related

SQL & PL/SQL :: Date Values Are Not Stored In Specific Pattern

Oct 15, 2010

I have a date column, where the date values are not stored in a specific pattern. following are the sample value from the column.

8/10/10 12:00 AM
9/22/2010 1:00AM
01/01/2001
9/1/10 6:00 PM
9/22/2009 1:00AM

i want to convert this to a standard format, 'dd/mm'yyyy'.

View 14 Replies View Related

SQL & PL/SQL :: Stored Procedure Using NVL To Allow Using Optional Date Parameter?

Mar 26, 2012

I have need to know the best (least expensive) way to create a stored procedure that creates a new records in a table using a sequence and return the primary key (sequence value) for this inserted record:

CREATE TABLE TEST_A (SERIAL NUMBER PRIMARY KEY, NAME VARCHAR2(20));
CREATE SEQUENCE SEQ_TESTA_PK START WITH 1
NOCACHE
NOCYCLE;
CREATE OR REPLACE TRIGGER TRG_TESTA_PK
BEFORE INSERT ON TEST_A

[code]....

View 8 Replies View Related

SQL & PL/SQL :: Oracle Stored Procedure Which Sorts Date

Apr 23, 2010

oracle stored procedure which sorts date, meaning the recent records needs to be shown first.

View 6 Replies View Related

SQL & PL/SQL :: Execute Stored Procedure With Table Of Number As In Parameter?

May 31, 2011

I have one stored proc with three in parameters as

number, varchar and table of number

what statement I need to write in pl/sql to execute it ...

execute getdetails(1,'xyz', ????????????)

View 5 Replies View Related

SQL & PL/SQL :: Stored Procedure - Character To Number Conversion Error

Mar 14, 2011

I have written a stored procedure that has started returning the error:

Error starting at line 1 in command:
call p_glpost('DSTUK', 'L', '2008-01-01', '2008-01-01', '2011-02-18', 1, 1, 1, 0, 'Hi there')

Error report:
SQL Error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "CLARITY.P_GLPOST", line 173
06502. 00000 - "PL/SQL: numeric or value error%s"

I can't seem to find a tool that will let me step into the actual stored procedure line by line to see where the error occurs. It mentions line 173, which seems to be a red-herring, as line 173 is simply one of the 'END IF' lines within this block:

IF NVL(r_dist.transtype,'wild') = 'wild' THEN
NULL;
elsif r_wip.transtype = r_dist.transtype THEN
v_matchCount := v_matchCount+1;
elsif r_wip.transtype <> r_dist.transtype THEN
[code]......

Tell me if it is possible to trace through a SP, and which tool is best (I am trying to use Oracle SQL Developer).

View 8 Replies View Related

DBMS_JOB - Pass Date As Parameter To Another Stored Procedure?

Mar 28, 2012

I have created a stored procedure that checks if a file exists and gets a date from the file if it exists. The date is then used as a paramter. See below:

CODEcreate or replace
PROCEDURE                     "P_Load_Stamp" AS
v_exists BOOLEAN;
v_length NUMBER;
v_blocksize NUMBER;

[code]...

The above codes works perfectly and I scheduled it using SQLPLUS as follows:

CODEvariable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;

[code]...

My problem is that I need to pass the date from the above procedure as a parameter to another stored procedure. So I modified my code as follows (the parts in red):

CODEcreate or replace
PROCEDURE                     "P_Load_Stamp" (vCTIDATE OUT varchar2) AS
v_exists BOOLEAN;
v_length NUMBER;

[code]...

Now it doesn't strike me as a rights issue since I created it in the schem schema. What could I be doing wrong here?

View 1 Replies View Related

SQL & PL/SQL :: Updating Table For Different Number Of Fields Via Single Stored Procedure?

Jun 14, 2012

I've a table with fields:

create table test
( f1 varchar2(10),
f2 varchar2(10),
f3 varchar3(10)
)
insert into test values ('d1','d2','d3');
insert into test values ('d10','d20','d30');

I want to update the fields of the table as per need i.e update only one field leaving all the data of the fields as it is. Suppose I want to update only f1 (from d1 to x1) field leaving f2, and f3 as it is. I've written stored procedure to update all the fields but do not know how to do it?

Quote:CREATE OR REPLACE PROCEDURE UPDATE_TEST
( U_F1 TEST.F1%TYPE,
U_F2 TEST.F2%TYPE,

[Code]....

View 7 Replies View Related

PL/SQL :: Check If Data Stored In Varchar2 Type Column Is Date Or Not

Jun 29, 2012

Is 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;

View 17 Replies View Related

SQL & PL/SQL :: Oracle Stored Function / Procedure To Calculate Number Of Working Days Between Two Dates

Feb 17, 2010

I want Oracle stored function/procedure to calculate number of working days between two dates. We need to exclude Firdays and Saturdays as there are weekend holidays and also exclude official holidasy that lie between two dates.

View 7 Replies View Related

Precompilers, OCI & OCCI :: PLS-00306 / Wrong Number Or Types Of Arguments In Call To Existing Stored Procedure

Feb 1, 2010

I'm using an existing stored procedure in my C code. The stored procedure in question has been compiled and is proven to work without any errors. However, when I use the same in my C code, its failing with the above error.

The Store procedure definition looks like :

CREATE OR REPLACE FUNCTION SP(
srq_id integer ,
unid IN SPkg.arr_parmid,
parm_typ IN SPkg.arr_parm_typ,

[code].....

Type definitions

TYPE arr_parm_typ IS TABLE OF char INDEX BY BINARY_INTEGER;
TYPE arr_parmid IS TABLE OF tbl_parm.UNID%TYPE INDEX BY BINARY_INTEGER;
TYPE arr_parm_lbl IS TABLE OF tbl_parm.PARM_LBL%TYPE INDEX BY BINARY_INTEGER;
TYPE arr_parm_vlu IS TABLE OF tbl_parm.PARM_VLU%TYPE INDEX BY BINARY_INTEGER;
TYPE arr_vlu_hint IS TABLE OF tbl_parm.VLU_HINT%TYPE INDEX BY BINARY_INTEGER;

My C code looks like :

typedef struct param
{
char lbl[30][81];
char vlu[30][256];
char typ[30];
ub8 seq_no[30];

[code].....

The way I invoke the stored procedure:

char command[250] = "begin
:retval := SSP_srq_parm_all(:srq_id,:unid,:parm_typ,:parm_lbl,:parm_vlu,:commit_flag,:vlu_hint,:create_flag);
end;";
OCIStmtPrepare2((OCISvcCtx *)svchp, (OCIStmt **)&(stmthp),
(OCIError *)errhp, (OraText *)command,

[code].....

OCIStmtExecute() fails with the above error.

View 3 Replies View Related

SQL & PL/SQL :: How To Convert Number To Date

Oct 28, 2011

how can convert this number 00001021992 to this format

1-02-1992

i used thie query but no result

select substr(to_date('00001021992','dd-mm-yyyy'),(6,6)) from dual;

View 6 Replies View Related

SQL & PL/SQL :: Date To Number Conversion

May 6, 2010

I want to change a table datatype from date to number where already existing data should get convert.any possibility of doing where i tried like this but no get changing. Even as Julian format is working a bit i want the data to come as GMT format

Scenarios where i tried are like this.

ALTER TABLE Contacts ADD ALERT_DATE1 NUMBER(20,0)
/
UPDATE Contacts SET ALERT_DATE1 = TO_NUMBER(ALERT_DATE)
/
ALTER TABLE Contacts DROP COLUMN ALERT_DATE
/
ALTER TABLE Contacts RENAME COLUMN ALERT_DATE1 TO ALERT_DATE
/

but second statement failing.

Julian fomat like

SELECT sysdate,
TO_CHAR(sysdate, 'J'),
TO_DATE(TO_CHAR(sysdate, 'J'),'J')
FROM dual;

View 10 Replies View Related

SQL & PL/SQL :: Date Check On A Number Column

May 16, 2011

I have a column defined as Number( 8 ) which is supposed to have date values. I would like to check if all the rows in that table have valid dates. We could use to_date(coulmn_name, 'YYYYMMDD') and catch the rownums for error conditions using pl/sql. I would like to know if we could just do it using sql only and return the row numbers for those that are invalid dates?

View 5 Replies View Related

SQL & PL/SQL :: Convert Varchar2 To Date Or Number

Jun 18, 2011

I created table Contains then following columns

cheq varchar2(50)
date_due varchar2(50)

and data entry in this columns

cheq 500,1500,5000 all values numbers in this columns

date_due 1-1-2012 , 15-9-2010 all values in this columns date

i want sum the column "cheq"

when used this code but it's not working

select sum(to_number(cheq))
from table_name but the code not working

second column "date_due"

i want search between to date i used this code but also not working

select cloumn1,cloum2
from table_name
where to_char(date_due,'dd-mm-yyyy')
between to_char(date_due,'dd-mm-yyyy') and
(date_due,'dd-mm-yyyy')

but not work

View 13 Replies View Related

PL/SQL :: How To Convert Number (15 Characters) To Date

Jun 23, 2013

One table ACCOUNT_T.CREATED_T is number (38). It is mapped to /ACCOUNT's PIN_FLD_CREATED_T, which is timestamp. ACCOUNT_T table has one record, CREATED_T = 1362063600. This field is displayed at the GUI (Customer Center) as Feb 28, 2013 So my question, how to convert the number value (1362063600) to a date value (Feb 28, 2013)?

View 4 Replies View Related

Get Month Number From Date Format

Oct 31, 2012

I am trying to get the month number from this date format '19-OCT-12' . when i try to run the below statement i am getting an error like "not a valid month"

select to_date('19-OCT-12','MM') from dual

i need to get value as 10

View 4 Replies View Related

SQL & PL/SQL :: Convert Year / Quarter Number To Date Format

Oct 5, 2010

I have year/quarter number field (200903 3-rd quarters of 2009) and I need to convert to data format.

View 5 Replies View Related

PL/SQL :: Query To Find First And Last Call Made By Selected Number For Date Range

Apr 27, 2013

create table call(id number(10) primary key,mobile_no number(10), other_no number(10), call_type varchar2(10),call_date_time date, duration number(10));

insert into call values(1,9818764535,9899875643,'IN','24-APR-13 02:10:43',10);
insert into call values(1,9818764535,9898324234,'IN','24-APR-13 05:06:78',10);
insert into call values(1,9818764535,9215468734,'IN','24-APR-13 15:06:78',10);
insert into call values(1,9818764535,9899875643,'OUT','25-APR-13 01:06:78',10);
insert into call values(1,9899875643,9899875643,,'OUT','25-APR-13 22:06:78',10);

Query : need to find first and last call of '9818764535' mobile number and of call_date between '24-apr-13' and '25-apr-13';

Result :

date ,mobile_no , other_no, call_type, duration
24/apr/13 , 9818764535,9899875643,'IN',10
24/apr/13 ,9818764535,9215468734,'IN',10

[Code]....

View 5 Replies View Related

SQL & PL/SQL :: Display Date Ranges In One Column As Separate Date Periods (start And End Date) In Two?

Jun 1, 2010

I'm trying to work out how to take a table like this:

IDDate
12502-Feb-07
12516-Mar-07
12523-May-07
12524-May-07
12525-May-07
33302-Jan-09
33303-Jan-09
33304-Jan-09
33317-Mar-09

And display the data like this:

IDPeriodPeriod StartPeriod End
125102-Feb-0702-Feb-07
125216-Mar-0716-Mar-07
125323-May-0725-May-07
333102-Jan-0904-Jan-09
333217-Mar-0917-Mar-09

As you can see, it's split the entries into date ranges. If there is a 'lone' date, the 'period start' and the 'period end' are the same date.

View 13 Replies View Related

Forms :: Expiry Date To Automatically Show A Date 15 Years After Initial Date

Apr 12, 2010

I have a two date fields in my form; valid from date and expiry date.

Currently my valid from date has an inital value property of $$date$$ which automaitcally brings up todays date.

I need my expiry date to automatically show a date 15 years after this date?

View 8 Replies View Related

SQL & PL/SQL :: Converting Number Column To Date Column

Dec 25, 2012

I have a partitioned table with ~50 million rows that was setup with a number(10) instead of a date column. All the data in the table is ALWATS in this format YYYYMMDD

CREATE TABLE T1.monthly
(
SEQ_NUM NUMBER(10) NOT NULL,
DAY_DK NUMBER(10) NOT NULL
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
[code]........

some sample data

SEQ_NUM DAY_DK
---------- ----------
990 20121225
991 20121225
992 20121225
993 20121225
994 20121225
995 20121225
996 20121225
997 20121225
998 20121225
999 20121225

When I use the exchange partition method the parition is able to move the data from "monthly" table to "mth" table.

desc t1.mth; ### my temorary table
Name Null? Type
----------------------------------------- -------- ----------------------------
SEQ_NUM NUMBER(10)
DAY_DK NUMBER(10)

Than when I try to alter my temp table "mth". I get an error table must be empty to change column types.

alter table n546830.mth modify (DAY_DK date);

Next I tried making my temporary table "mth" a date column. When I an the exchange partition command I get the following error:

alter table t1.monthly exchange partition DEC_2012
with table t1.mth without validation;
alter table n546830.monthly exchange partition DEC_2012 with table n546830.mth without validation
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

Method I can use to convert a number(10) to date column and keep the information in a table. Note, I don't care about HH:MM:SS as I never had that information to begin with and would be happy to set that part of the date column to all zeroes "00:00:00"

View 12 Replies View Related

Reports & Discoverer :: How To Find Page Number And Total Number Of Pages

Jan 26, 2010

i am using oracle developer 6i report builder i required this type of query

example

if (:page number LIKE '1')
then
srw.set_text_color('darkred');
end if;

return (TRUE);
end;

but page number is not my table database item how can i use builtan page &<pagenumber> use for conditional format.

View 34 Replies View Related

SQL & PL/SQL :: Replacing 4 Digit Number In A Given String With The Same Number Incremented By 10000?

Jun 17, 2010

i want to replace 4 digit number in a given string with the same number incremented by 10000.

That mean in the given sting 1201 should be replace by 11201 (Icremented BY 10000).

Input String:

<query><matchAll>true</matchAll><row><columnId>1201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>1224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>

Required output :

<query><matchAll>true</matchAll><row><columnId>11201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>11224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>

View 7 Replies View Related

SQL & PL/SQL :: Extract Number And Previous Character From Where 5 Digit Number Starting

Oct 21, 2011

I have a text field and if the text field has 5 consecutive numbers then I have to extract the number and the previous character from where the 5digit number starting

For example i/p asdfasfsdS251432dasdasd o/p should be S251432

View 10 Replies View Related

Express Edition (XE) :: Why Maximum Number Of Voting Disk Is Even Number (32)

Oct 3, 2013

I gone through many forums and found that the number of voting disks should be always in odd number. Then why the maximum number of voting disk is 32?

View 1 Replies View Related

PL/SQL :: Call More Than One Stored Procedure In New Stored Procedure

Dec 24, 2012

Execute sp1 param1...param6
Execute sp2 param1...param8
Execute sp3 param1...param4

All these stored procedures deals with insert/updated transactions . i need to create a new stored procedure to execute all this in a single stored procedure which will be something like

create procedure sp4(param1...param8)
as
begin
Execute sp1 param1...param6
rollback if any error
Execute sp2 param1...param8
rollback if any error
Execute sp3 param1...param4
rollback if any error
end;

View 6 Replies View Related

SQL & PL/SQL :: Count A List Of Number Value And Find Maximum Number?

May 21, 2010

how do I count a list of number value eg 1,1,1,1,3,3,6,4 and find the one with maximum number which is 1

View 5 Replies View Related

Application Express :: Unable To Set Default Date Value For Tabular Form DB Date Field?

Dec 3, 2012

Version : 4.1.1, I have a tabular form on a DB table. One of the columns is a date field. When the user hits the "add Row" button on the tabular form, I want the Date field to be defaulted to sysdate. Here is what I have tried so far,

1. Created a "hidden" item P1_SYSDATE and populated the default value with sysdate. After this, under the DB tabular report date field, I used default type - Item/application on this page and entered P1_sYSDATE

2. Instead of populating the default value of the P1_SYSDATE hidden item, I created a before regions process and added

:P1_SYSDATE := sysdate

and added P1_SYSDATE to default type of the tabular date field with default type as "ITem/application on this page.

I get the error

ORA-01790: expression must have same datatype as corresponding expression

I tried to_Char(sysdate,'dd-mon-yyyy') and then converting it back to to_date. still no luck.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved