SQL & PL/SQL :: Date Conversion From Unix To Oracle

Jun 14, 2010

I have a UNIX shell script as following,

#runs the selected process
${ORACLE_HOME}/bin/sqlplus -s ${USER_PASS} >${TMP_FILE} <<EOF
set pause off
set verify off
set pagesize 0
set linesize 2000
set timing off
[code].........

I am passing a Date to the Oracle Procedure in `date +'%b_%d-%H:%M:%S'` format.

rmsqa813.2 /home/ashlaksh> echo `date +'%b_%d-%H:%M:%S'`
Jun_14-02:26:37

The oracle Procedure's SQL is as following,

SELECT ffdh.fiscal_doc_id INVOICE_ID,
ffdh.location_id LOCATION_ID
FROM fm_fiscal_doc_header ffdh,
fm_schedule fs
[code]........

The above SQL is not getting the data as the Date format "AND ffdh.last_update_datetime <=i_last_update_datetime " is not matching..

Do I need to convert the Date ? ( But i_last_update_datetime is DATE)

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: Oracle Date Conversion Errors

Mar 14, 2011

I am having a strange issue with date conversion. The errors encountered are :

PLS-00382: expression is of wrong type
PLS-00306: wrong number or types of arguments in call to '>'

The situation or test case is as follows:

create table sand_programs(prog_code varchar2(20), prog_end_dt date);

create table sand_program1(prog_code varchar2(20), filing_date number(15));
Result : Both the tables are created.

Now I create a procedure below as mentioned, to check if the filing_date is greater than the prog_end_dt or not.If the filing_date is greater than prog_ end_dt, then it should go to the 1st "dbms_output.put_line" message else it should go to the 2nd "dbms_output.put_line" message.

Here's my procedure:

create or replace procedure test_sand(p_program_cd in number) is
v_prog_end_dt date;
v_filing_date number;
begin
[code]....

View 6 Replies View Related

SQL & PL/SQL :: Oracle Functions Date Conversion

Jan 20, 2011

the data in the column is in below format
---------------------
2/10/2009 9:28:41 PM
mm/dd/yyyy

my requirement is
--------------------
20090210
YYYYMMDD

View 5 Replies View Related

SQL & PL/SQL :: Oracle Date / Time Zone Conversion

Aug 13, 2012

I would like to know how to convert the following:

13-AUG-12 03.30.06.146 PM

to the format:

"MM/DD/YYYY HH:MI:SS AM"...

I believe there is a time zone issue here as well, since the should actually be around 11:30am. I am not sure what time zone is being used.

View 2 Replies View Related

Convert Date Into UNIX Timestamp

Dec 17, 2008

how to convert date into UNIX time in oracle ?

View 1 Replies View Related

SQL & PL/SQL :: Date Conversion

Jan 12, 2012

I have an issue with date conversion. In my table dates are in this formate:

04-DEC-90 10:46:46

I need to convert it in To_date.

SQL> select to_date('04-DEC-90','dd-mon-yy') from dual;

TO_DATE('04-DEC-90','DD-MON-YY
------------------------------
04/12/2090

SQL>

But the year shoul be 1990 not 2090.

View 3 Replies View Related

Epoch Date Conversion

May 31, 2011

My company uses Remedy which stores date/time as an integer. I use the following formula to convert it to a readable date:

TO_CHAR(TO_DATE('01/01/1970', 'MM/DD/YYYY HH:MI:SS AM')+ ((createdate - 4*3600)/(60*60*24)),'MM/DD/YYYY HH:MI:SS AM') as Create_Date

I have to use the "4*3600" in order to get the date to show up correctly, but even then the date sometimes comes up wrong. If the date occurs in the morning, then the date shows up as the previous day. I am sure this is probably due to the offset I am adding in the above formula. If I don't add the 4 hour offset, then the date shows up 4 hours off.

View 3 Replies View Related

SQL & PL/SQL :: Char To Date Conversion?

Feb 7, 2013

converting a string to date, my string looks like '01022013' '04022013', I tried to_date('01022013', 'dd-mm-yyyy') but couldn't work.

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 / Time Conversion

Apr 4, 2010

I am converting data from an old paradox table to a new oracle table, one of the problems im having is incompatibility with date and time formats:

some columns contain times in the format : "00:00:00" eg..... "15:00:00"
some columns have date in the format: "dd/mm/yyyy" eg....... "21/08/2000"
some columns have time and date eg.. "05/09/2000 15:49:39"

Currently I have the data held in tables within an access database, and in CSV format.

eg, I have dates like 03/04/2010 which i need to be 03-APR-10....

how I can get the following into Oracle date formats? there is over 1000 records so manual conversion is out of the question

View 21 Replies View Related

PL/SQL :: String To Date Conversion

Jun 25, 2012

I recently became involved with databases, and i've came across with a little obstacle. I have strings that represent a date, they are very oddly formatted and need to store them as dates. the string format looks like this: 'Monday, May the 13th of 2001'

How can i effectively convert them to DATE type?

View 7 Replies View Related

SQL & PL/SQL :: Conversion Of Julian Date Into Dd / Mm / Yyyy

Mar 8, 2010

I have been given some data in excel sheet to be uploaded in an Oracle Table. The dates are in Julian. The date in Julian in excel sheet is as :-'110048'.

In the excel file, I found that the cell was formatted as General and when I changed the formatting to Date I got the result as '19/04/2211'.

tell me a way to convert this Julian to mm/dd/yyyy format to be inserted into a table in Oracle.

Tried this :-

SQL> SELECT to_char(to_date(to_char(110048), 'J'),'DD/MM/YYYY') FROM dual;

TO_CHAR(TO
----------
18/04/4411

Not sure how to go about it.

View 5 Replies View Related

Date To Timestamp Conversion Of Column With 150 Million Rows?

Oct 5, 2010

Due to some business requirements a table field needs to change from date to timestamp in order to handle the millisecs.

1>When i alter the row , for a table with 150 million recs will there be a conversion. Is there a recommended way to convert the field. Mind you this field is used as a part of composite PK.

2> There is a interfacing application which connects and copies the data to its system and is using the date type, will that application be able to continue to work without any changes, if it does not care about the millisecs.

3> Will there be performance impact on an existing application that uses the date field to sort

4> Will DB need more space due to the change

View 3 Replies View Related

Conversion Hours Incorrect When Revert To Actual Date

May 18, 2011

I can't seem to understand why the hour is incorrect. Below query "dte_computation_on_data" is the old function they use to convert date and insert it to the table. Problem is when I revert it to the actual date the hour
is incorrect.

CODE
SELECT -- THIS HERE IS MY TEST TO REVERT TIME AND DATE ON THE FORMULA OF WITH RESPECT TO THEIR FUNCTION
to_char(TO_DATE('19700101', 'YYYYMMDD')+(tb1.dte_computation_on_data/86400),'MM/DD/YYYY') || ' ' ||
to_char(to_date(mod  (tb1.dte_computation_on_data,86400) ,'sssss'),'hh24:mi:ss ') revert_test,
systimestamp,tb1.dte_computation_on_data
from
( SELECT -- THIS IS THE FORMULA OF THE OLD FUNCTION THEY USE TO CONVERT DATE TO NUMBER AND INSERTED ON THE ROW
    floor((CAST(SYS_EXTRACT_UTC(systimestamp) AS DATE) - TO_DATE('19700101', 'YYYYMMDD')) * 86400) dte_computation_on_data
  FROM dual)tb1;

results
---------------------------------------------------------------------------------------
REVERT_TEST             SYSTIMESTAMP                            DTE_COMPUTATION_ON_DATA
05/19/2011 03:46:18     5/19/2011 11:46:18.005171 AM +08:00     1305776778

View 1 Replies View Related

Unix Command Run Successfully Through PL/SQL But Showing No Impact In UNIX?

May 19, 2007

I am using Oracle 9i and Unix on my system and trying to execute a UNIX shell command through external procedure in C.I created a shared lib (libextproc.so) for the following function.

int sysrun(char *command)
{
return system(command);
}

This function runs fine when caled through a driver function in C, meaning that the shared lib is fine.In PL/SQL, I have used the following method to invoke a UNIX command:-
create or replace library shell_lib as '/home/ECETRAonsite/oracle/OraHome1/lib/libextproc.so';
/
create or replace function sysrun (syscomm in varchar2)
return binary_integer
as language C
name "sysrun"
library shell_lib
parameters(syscomm string);
/

Now when I call this PL/SQL function to invoke the command, it is run succesfully but does not create the file.

SQL>
1 declare
2 rc number;
3 begin
4 rc := sysrun('/bin/touch /home/ECETRAonsite/oracle/OraHome1/test/sach');
5 dbms_output.put_line('Return Code='||rc);
6* end;
SQL> /
Return Code=0

PL/SQL procedure successfully completed.I have verified that the path for 'touch' is correct.Following are my configuration files.
listener.ora
-------------
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =

[code]...

View 1 Replies View Related

Moving Oracle 11g UNIX Sun Solaries To Oracle 11g Linux Readhat OS

Aug 8, 2012

We would be moving oracle 11g unix sun solaries to oracle 11g Linux readhat OS. what would be the disadvantage and what are the item needs to be verified. Basically advantage of oracle 11g Linux readhat OS.

View 2 Replies View Related

Oracle 10G To 11G Upgrade On Unix Env

Jan 21, 2011

Iam planning to upgrade my Oracle Database from 10G to 11G .

Its on HP Unix Environment 11.23 and
Oracle CRS 10.2.0.4
Oracle ASM 10.2.0.4
Oracle RDBMS 10.2.0.4

Its in RAC env...and Active-Active DB running controlled by tnsnames.ora .

View 8 Replies View Related

RAC & Failsafe :: Oracle 11gR2 RAC On Unix

Aug 8, 2011

I'm trying to install Oracle 11gR2 RAC on AIX, do we need to turn on Multicasting, can we install without multicasting? Also can I have the ASM disk with external redundancy for OCR and Voting Disk?

View 3 Replies View Related

PL/SQL :: Execute Unix Command From Oracle

Oct 17, 2013

I'm on Oracle 11g R1. I've a requirement where user will be putting CSV files on Unix server and I've to create a job which runs periodically to check if any new file is added by user in the folder. If it finds a new file (s), then it needs to identify it and insert its name in an Oracle table. Once file's name is noted, it has to move file from that directory to another one.

View 2 Replies View Related

Forms :: Print Oracle Report 10g Using UNIX

Mar 25, 2010

I installed a printer(HP Laserjet 3390) in UNIX(Solaris).Followed the instructions while installing as to the path it should be present for Reports to read it etc.,Also modified the uiprint.txt file with the desired .hpd file.Logging on to the UNIX machine, I am using the below command

rwrun.sh report=xyz.rdf userid=XXXX/XXX@xxx paramform=no p_orig_org='0825' desformat=pdf destype=printer desname=xxxx batch=yes
This command invokes the printer and
^[%12345X@PJL
@PJL SET RESOLUTION=300
@PJL SET PAGEPROTECT=OFF
@PJL SET ENTER LANGUAGE=PCL
^[%1A^[E^
@PJL RESET
^[%12345X

is what it prints.Realizing that this is because of the missing parameter,tried to change paramform=yes but I am not sure how to pass the parameter.

View 11 Replies View Related

Forms :: Launch Application From Oracle In Unix OS

Jul 1, 2010

my problem is this. I created the java classes using itext to create a document library. Rtf, or. Odt. I integrated classes in Oralce form and everything works. I created a file. Odt application server on a folder and now only remains for me to open the file. The roads are two:

1. Directly from the server, you can?

2. From the client using the API or openoffice launching the executable with cmd?

Oracle Forms is a command to transfer files on the client and run the executable swriter of openoffice?

View 4 Replies View Related

Forms :: FRM-92100 Error In Oracle 10.1.0.4 Under UNIX B.11.11

Jul 30, 2010

We have recently migrated from IAS 9.0 to IAS 10.1.2 in one of our development Server hosted in UNIX B.11.11. We are using Oracle forms as front end. We have re-compiled all the existing forms and libraries using IAS 10. There were no changes in the forms and libraries. We deployed the forms in our development box and configured it, since we are using our own environment specific copy of default.env file. With few of forms we are facing the FRM-92100 error mentioning the "Connection to Server was interrupted". Most of the forms are running fine but with few forms we are facing this error. Some of details are:-

Oracle 10.1.0.4
UNIX B.11.11
IAS 10.1.2

NLS_LANG parameter in default.env is set to "american_america.WE8ISO8859P1"..We have even tried copying the libraries in our local system and then opening and saving them and ftp'ng them back in to UNIX server and then recompiling there with full permissions but still unable to resolve this error.

View 2 Replies View Related

PL/SQL :: Connectivity Code To Connect Oracle DB In UNIX?

Jun 13, 2013

i need a connectivity code to connect oracle DB in UNIX.pls send me a code exactly.

View 2 Replies View Related

SQL & PL/SQL :: Transfer Excel Files From Unix To Oracle Database?

Jun 6, 2013

I would like to ask you if you know which built-in can I use for transferring a excel file from our Unix box to a table in oracle database, right now we are using webutil_file_transfer.Client_To_DB_with_progress using forms developer, but I need to run as an automatic process uploading form unix into oracle directly without using forms.

View 22 Replies View Related

SQL & PL/SQL :: Attaching A File Residing On Unix And Emailing It Through Oracle?

Nov 11, 2011

I have a file which is placed on a certain location on unix server. I want to write a code which picks up that file and emails it to certain recipients.

View 3 Replies View Related

SQL & PL/SQL :: Insert Into Oracle Table Of UNIX Script Values

Dec 24, 2012

I want to insert STAT and ENDTIME values for each job in joblist into TBL_DAILY_STATUS table.

E.g. : insert into tbl_daily_status values(STAT,ENDTIME);

#!/bin/ksh
joblist="com_abc_job com_abc_dot_job com_abc_seq com_abc_det"
for i in $joblist
do
STAT=`./JobStatus.pl -i NP1 -z Z8PIMBN -p $i|awk '{print $6}'`
ENDTIME=`./JobStatus.pl -i NP1 -z Z8PIMBN -p $i|awk '{print $4" " $5}'`
echo "$STAT"
echo "$ENDTIME"
done

View 2 Replies View Related

Efficient Way To Connect SQL Server From Oracle Which Is Running On HP-UNIX

Mar 8, 2011

I am trying to Push the data from Oracle which is running in HP-UNIX to SQL Server. But don't know the efficient way to connect the SQL server from oracle which is running on HP-UNIX.I have heard about oracle Heterogeneous Connectivity but don't know exactly how to implement it in Unix environment.

View 1 Replies View Related

SQL Server To Oracle Conversion

Aug 29, 2013

I have one query in SQL server. I want to convert that query to Oracle. Well i am not that good with writing queries in Oracle. Following is the query in SQL server.

DECLARE @StartYear AS INT = 2010;
DECLARE @EndYear AS INT = 2014;

WITH
years
AS (SELECT YYYY = @StartYear
UNION ALL
SELECT yyyy + 1
FROM years
WHERE yyyy < @EndYear)
[code].......

Which Gives output in following format.

id_datedateYearMonthNumberMonthDayOfMonthDayOfWeekNumberDayOfWeekWorkingDay
201001011/1/201020101January16Friday1
201001021/2/201020101January27Saturday0
201001031/3/201020101January31Sunday0
201001041/4/201020101January42Monday1
201001051/5/201020101January53Tuesday1

View 3 Replies View Related

Conversion From SQL Server To Oracle

Nov 6, 2011

is there any licensed version tool available to convert objects from sql server to oracle especially procedures? I have tried with open source tools but that tool didn't convert properly.

View 7 Replies View Related

SQL & PL/SQL :: Sybase To Oracle Conversion

May 6, 2010

I am converting sybase to oracle database. during the migration, i have small doubt in triggers.

sybase source

create trigger testss_dt
on test
for delete
as
if @@rowcount = 0
return
insert into test
select id from deleted
return

I have converted oracle code here

create or replace trigger testss_dt
before delete on test
for each row
begin
insert into test
values(:old.test);
end;

But i have ignores @@rowcount concepts in oracle. what @@rowcount indicates in trigger?

View 10 Replies View Related







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