SQL & PL/SQL :: Cannot Use Variable In Code For Timezone

Oct 10, 2012

I've written this code to present the problem:

declare
region_date varchar2(100);
region varchar2(100);
offset_result varchar2(100);
begin
region_date := '2013-04-07 01:59 Australia/Sydney';
region := 'Australia/Sydney';
select
to_char(

[code]....

What I want to do is to use the variable "region" for the timezone specification as well,And I seem to be unable to, and don't understand why.....

why when I get an "ORA-00907: missing right parenthesis" with this value,Whereas, it works when I put "AT TIME ZONE 'Australia/Sydney'" there?

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: HTML Code In Local Variable?

Mar 14, 2012

I am working on a procedure which will insert a concatenated field that will have HTML code and a paramater value into a procedure. For some reason I am having issues inserting the HTML code into the local variable for process. I am not getting an error but instead the insert is not including the value in the variable when code when it runs. Do I need to enclose anything around the HTML to make this work?

This is an example of how I am populating the variable:

Begin
Select '<a href="URL>...l;
End;

View 4 Replies View Related

Performance Tuning :: Avoiding Bind Variable From Sql In Java Code?

Jun 15, 2010

We are on oracle 10.2.0.4 on Solaris 10 and have a perf. issue with a bind variable using query. The query is in java application. I want to test its performance when the query doesn't use bind variable and instead uses the passed value as literal. How can it be done?

As example lets say the query is:

SQL> variable vn varchar2(20);
SQL> EXEC :vn :='ADAMS';

PL/SQL procedure successfully completed.

SQL> select * from emp where ename=:vn;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 12-JAN-83 1100 20

1 row selected.

SQL> EXEC :vn :='KING';

PL/SQL procedure successfully completed.

SQL> select * from emp where ename=:vn;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10

1 row selected.

But the statements will be taken as similar statements by oracle (due to :vn). Now I want oracle to take it as literal and the change for this has to be done in java code in my actual scenario which has a different query (but conceptually it uses bind variable and I want it to use passed value as literal). How can it be done?

View 9 Replies View Related

Reports & Discoverer :: Bar Code Reader Will Readout Code

Feb 12, 2009

I have no knowledge about Barcode. The problem is an issue of Loyalty Cards of a Hotel and Restaurant to various customers and then these cards will be presented by the customers time to time in the Hotel as well as Restaurant. The Owner of the Hotel and Restaurant wants to generate separate barcode for each card and when this card will be presented then the bar code reader will readout the code and the system will calculate the amount of discount/rebate. Because if the data entry operator enter the code of the card through key board the it will be a chance of leakage or misuse of that card.

View 8 Replies View Related

SQL & PL/SQL :: Compare SVN Source Code With Production Code In Database

May 30, 2012

I have to compare my SVN source code (packages, views etc) with the production code in the database like views etc (actually we are not sure that what we have in the svn is the final version of production code, we have objects created in the production database, but we don't have latest scripts for that. we have to deploy the svn code in the UNIX box).

So here the comparison is between the OS files and the database objects.

I thought I would get scripts of all the packages, views etc from the production database by using DBMS_METADATA or some utility and save the code in OS files then compare one svn file with OS file manually by using some comparison tools e.g toad provide one comparison tool.

View 5 Replies View Related

Getting DBs Timezone In Oracle 11.2.0.3?

May 23, 2013

I need the timezone of the server that the DB sits on. I don't want the offset. I need the region name. This is because some regions use daylight savings time and some do not. Plus an SA can change the time of the OS. Another company manages our servers and DBs in production and we don't have contact with them. We could use a lookup talbe and just populate it, then update it when we find out what it is in production. The problem is that I have seen cases where the timezone on servers change. Considering the lack of contact between the teams, we really need a reliable way to get the timezone out of the DB.

We tried several ways. My list is below and I explain why this is not working.

examples:

sessiontimezone: this is the timezone of my server. In theory it should be the same as the DB. We cannot take the risk that this will be out of sync. dbtimezone: This gives the offset. Such as -5:00 for US EST. There are multiple regions that have this. Some do not use daylight savings time and some do. We would need America/New York instead.

sessiontimezone gives the timezone setting for the client. This can be altered.
dbtimezone just gives the offset such as -5:00

We get data feeds from different parts of the world. We get some data based data that is local to that regions timezone. We need to partition on this field. So we need to add a field to the DB and normalize it to the time local to our DB Server. So if we get a record from california and the DB is on a server in US EAST, we add 3 hours. The offset won't work...

1. a timezone that we are getting from may not be in daylight savings time. We are partitioning by hour.

2. We would hit daylight savings time in New York before we hit it in California, so we would need to account for that in the math.

This hourly partition is a fixed and hard requirement. We need this to be absolutely accurate.

Here is what we tried:

What I want (pseudo-code): “Select XXX as timezone_region_name” to return “America/New_York” or “UTC”. It may be that the timezone was not set for the database at install time, and if it were, these queuries would work.

-- FAILED
SELECT DBTIMEZONE FROM dual;

--FAILED
select systimestamp, to_char(systimestamp, 'TZR'), extract (timezone_region from systimestamp) from dual;

--FAILED
SELECT systimestamp
     AT TIME ZONE DBTIMEZONE "DB Time"
FROM DUAL;

--FAILED
select to_char(systimestamp, 'TZR') from dual;

View 7 Replies View Related

Database Timezone Query

Jun 12, 2013

When I try to store the xml as clob with element:

<revisedDate version="1">2013-06-10T11:00:00+11:00</revisedDate>

It is throwing �ORA-01830: date format picture ends before converting entire input string�

But when I insert with

<revisedDate version="1">2013-06-10T11:00:00+05:30</revisedDate>,

it is getting inserted successfully.

PS: my db timezone is +00:00

View 1 Replies View Related

SQL & PL/SQL :: Timezone Region Not Found

Mar 6, 2013

If I run the following query it's executing fine.

SELECT CAST(( FROM_TZ(CAST(SYSDATE AS TIMESTAMP),'CST') AT TIME ZONE ('Japan'))
AS DATE) FROM DUAL;

If I run the following query,I am getting the error.

ORA-01882: timezone region not found

SELECT CAST(( FROM_TZ(CAST(SYSDATE AS TIMESTAMP),'LMT') AT TIME ZONE ('philippines'))
AS DATE) FROM DUAL;

What is the timezone for Philippines.

View 6 Replies View Related

SQL & PL/SQL :: Timezone Check Trigger?

Sep 14, 2012

I have an interesting problem. I have a server in the UK and I have three databases globally - UK (FINUKQ1), NY (FINNYQ1) and TK (FINTKQ1). The NY and TK databases will be migrated to the UK server as part of a consolidation project. Obviously, Time Zones and DST are the biggest concern for me.Here was my plan for the implementation (concentrating on Time Zone/DST issues).

Added to .profile:
--------------------
if [[ "$ORACLE_SID" = "FINTKQ1" ]]; then
export TZ="Asia/Tokyo"
elif [[ "$ORACLE_SID" = "FINNYQ1" ]]; then
export TZ="America/New_York"
else
unset TZ
fi
echo "TZ=$TZ"

Once environment has been set start database and listener.

Change database TZ:
-----------------
alter database set time_zone='Asia/Tokyo';
alter database set time_zone='America/New_York';

Check database TZ:
select dbtimezone from dual;

Add trigger:
------------
CREATE OR replace TRIGGER sys.timezone_check
AFTER startup ON DATABASE
DECLARE

[code]...

The trigger was there to put the database in restricted session if it is started in the wrong TZ and an error written to the alert log. The check was done on the dbtimezone and the dbname. Quite simple. Only issue is that it doesn't work as I wanted as dbtimezone and TZ are separate and disparate entities. i.e. I can start the TK database up in UK time (which will be incorrect for this project), and check the dbtimezone and it will show as 'Asia/Tokyo'.

Is there a better way of checking? Any trigger like this to compare TZs etc?

View 2 Replies View Related

PL/SQL :: Convert To Format With DD-MON-YY HH:MM:SS Timezone (GMT Or PST)

Jan 7, 2013

I am using the below query to get time stamp along with time zone format.

>>SELECT CURRENT_TIMESTAMP FROM DUAL

Now the requirement i got is i need to get convert the format in DD-MON-YY, HH:MM:SS, Timezone (GMT or PST)

how to convert this format. Or is there any alternative time stamp option to do conversion.

View 5 Replies View Related

SQL & PL/SQL :: Type In Code To Make Few Tables Then Run Code

Feb 13, 2012

I downloaded oracle sql developer, i type my code into a worksheet but if i use the run statement option, it asks me to make a connection. I dont want to make a connection, just test the data locally.However, even if I do try and make a connection, i get ora-12560 error (local connection).

I just want to type up some data to make some table and test to retrieve or manipulate the data. I'll use any program, command line or gui.

View 7 Replies View Related

Sysdate Showing Wrong Timezone?

May 24, 2012

I have one issue My server is in france and it is in french timezone but when I query for sysdate it returns US time.

In '/etc/sysconfig/clock/'
Zone= europe/paris
UTC= true

echo $TZ variable is returning nothing.

sysdate = us time
systimestamp= us time
current_timestamp = french time
current_date = french time
dbtimezone= europe/warsove, sessiontimezone=+2.00( which is also europe timezone offset)
tz_offset(dbtimezone)=+2.00, tz_offset(sessiontimezone)= +2.00 i.e europe
os timezone= europe/paris.

This command "./emctl config agent getTZ" is also returning timezone as europe/paris

Also in "emd.properties" file "agentTZRegion" parameter is set to europe/paris

Oracle version= 11.2.0

Now I don't understand why this sysdate and systimestamp is returning "US time zone" while everything else is returning french time zone.

View 9 Replies View Related

Backup & Recovery :: Timezone And SET UNTIL For Recover

May 15, 2011

I'm in the process of testing a restore/recover from a simulated full system and media loss. A level 0 backup was taken from the server, and I'm trying to restore/recover to a point in time on a second server. I have created the database (with the same name) and have been able to successfully restore both the controlfile and spfile from the autobackup.

how does RMAN treat the "set until time"? The level 0 was taken on a database/server that is in CST while the database I'm trying to do a restore/ recover to is in EST. So when trying to do a point in time recovery, should I specify the time in EST or CST?

View 2 Replies View Related

Upgrade :: 9.0.2.0.8 Timezone V1 Migrate  To 11.2.0.3 New Server

Jan 20, 2013

I'm going to migrate my current db 9.2.0.8 (Timezone version is 1!) to a new server 11.2.0.3, so I was wondering if I must upgrade timezone version of my current 9iR2 to version 4! Before upgrade it...Btw my new server has timezone v14.

View 4 Replies View Related

Server Administration :: How To Change DB Timezone Parameter

Aug 31, 2010

How to change db timezone parameter. My application team has asked me to change the db timzone parameter by following sql.

ALTER database SET TIME_ZONE = '+10:00';

Is this way is the correct one , and do we have any impact on database and does it require a db bounce.

View 2 Replies View Related

SQL & PL/SQL :: Local Timezone With Day Light Saving Time?

Oct 22, 2011

Order time is not properly displayed in the local timezone format and day light saving calculation is also another issue. Description:

We have web application & a centralized database where users can create orders globally. When orders are created time is saved in UTC format in the centralized global database.

somehow along with the UTC order time, timezone description is also being stored in one of the Database table as

UTC time as: '28-NOV-2010 11:24:54 PM'
Timezone description:
(GMT) Casablanca, Monrovia
(GMT) Greenwich Mean Time : Dublin, Edinburgh, Lisbon, London
(GMT+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna
(GMT+01:00) Belgrade, Bratislava, Budapest, Ljubljana, Prague
(GMT+01:00) Brussels, Copenhagen, Madrid, Paris
(GMT+01:00) Sarajevo, Skopje, Warsaw, Zagreb
(GMT+01:00) West Central Africa
(GMT+02:00) Athens, Beirut, Istanbul, Minsk
(GMT+02:00) Bucharest
(GMT+02:00) Cairo
(GMT+02:00) Harare, Pretoria
(GMT+02:00) Helsinki, Kyiv, Riga, Sofia, Tallin
.....
...

With this information how can I map timezone description to V_$TIMEZONE_NAMES. I read Oracle automatically converts to local timezones by considering day light saving timings when TZNAME is given as input with the datetime. by using above timezone descriptions how can I get TZNAMES which are in V_$TIMEZONE_NAMES.

View 1 Replies View Related

SQL & PL/SQL :: Timestamp Column Whose Values Are In CET Timezone Needs To Be Converted To EST

Mar 6, 2012

I have a requirement in which a particular a timestamp column (date1) whose values are in CET timezone needs to be converted to EST and day light savings should be taken care of in the conversion logic. I should not use any ddl statements for altering the timezone and all.

View 9 Replies View Related

Return Results Where Employee ID That Has 2 Different Timezone IDs On Same Date

Jun 21, 2012

I have a table with the following columns

EMPLOYEEIDNUMBER(12,0)
PUNCHDTM DATE
TIMEZONEIDNUMBER(12,0)

I want to return any results where any employee id that has 2 different timezoneid's on the same date. I would actually like, if its possible, to select these entries to display on one row per employee per day. So for example

EMPLOYEEID - PUNCHDTM - TIMEZONEID - PUNCHDTM - TIMEZONEID
12345 - 6/20/2012 5:00 am - 123 - 6/20/2012 10:00am - 456

To me who is newer with SQL this sounds like i would be 'joining' the table to itself so i've searched for that but not found what i need.

View 3 Replies View Related

C++ Call Interface :: ORA-01804 - Failure To Initialize Timezone Information

Jun 14, 2012

I am having an issue to connect my C++ application to my Oracle DB. When my code calls

oracle::occi::Environment::createEnvironment(),

I get an error "ORA-01804: failure to initialize timezone information".

The application works on other people's Windows machine with their Oracle DB. But it doesn't work from my machine connecting to their Oracle instance. So I guess the issue does not come from my instance but rather from my Oracle dlls maybe?

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

View 0 Replies View Related

Application Express :: Developing Multi-timezone Application With 4.2.2.00.11?

Jul 8, 2013

I am tasked to develop and application using Apex 4.2.2.00.11 and Oracle Database 10.2.0.4 on a Windows 2003 Server. This application needs to support different time zones. Briefly, a user in timezone A should be able to create a record with a date in timezone B and the users should be able to view the records in their timezones. What is the best method? Should I maintain a Timezone field for each user site or even per transaction, or is using "Automatic Time Zone" feature in Globalization section of Application Properties sufficient? I have developed Apex applications before, but they did not have to have different time zones, so I am not sure how I should build my database schema. Do you guys store everything as UTC and display values based on user's Timezone setting? I am aware of the Timestamp with Local Timezone data type. 

View 1 Replies View Related

SQL & PL/SQL :: How To Debug A Code

Nov 2, 2011

how to debug PL/SQL code.

I mean if
procedure1 --calls---> procedure2
procedure2 --calls---> procedure3
procedure3 --calls---> procedure4

If procedure1 is my main procedure how do we know in procedure there is an unhandled exception or bug.

View 2 Replies View Related

SQL & PL/SQL :: How To Unwrap A Code

May 9, 2011

how to unwrap a PL/SQL Code???

View 2 Replies View Related

Retrieve Records - Using Code?

May 28, 2008

I need to retrieve the records where the involvement is either a person or an organization. Using the code below i receive an error stating that the outer join operator is not allowed in the operand OR:

(( INVOL1.PERSON_IDENTIFIER(+)=ALL_PERSONS.IDENTIFIER )
OR ( INVOL1.OUNIT_IDENTIFIER_INV=INVOL1_ORG.IDENTIFIER(+) ))

If I change it to an and query it will only retrieve the records where there is a person and an organisation as the involvement but I need this option as well as one or the other.

View 7 Replies View Related

Backup Of Source Code?

Oct 15, 2009

I would like to backup all source code (packages, procedures, functions, etc) every day automatically. Is there a built-in feature of Oracle to do this, I've been going through the Toad IDE and its getting to be too time consuming.

I assume I could use the export command, but not entirely sure what to specify.

I would like to backup a few tables along all code and set something in place (trigger maybe?) to send me an email when a modification is made to the production code environment.

View 2 Replies View Related

Code Works In Firefox But Not IE7?

Jan 29, 2008

I am fighting with some code to work in IE7. It is currently working in Firefox.

A little bit more on the context of the situation is that I am entering the code as a personalization on an Oracle Self Service Page. I am entering in as a raw text personalization.

[Code]
<html>
<head>
<style>
</style>
</head>
<body lang=EN-US style='tab-interval:.5in'>

[code].....

View 3 Replies View Related

How To Catch Null Value With C# Code

Jan 5, 2010

how to catch a null value with C# code?

My Asp.Net/C# application is connected to an Oracle database where it retrieves database values and also values calculated/returned from Oracle functions. My problem is that sometimes an Oracle function I calling returns a null value because of not correct input parameters which will cause Oracle function to (I think) return an null value. My problem and question is how to catch/obtain if he Oracle function returns a null value? Oracle function should return a decimal value if correct input parameters. Below is my row of code that crashes when Oracle function returns a null value in case of incorrect input parameters.

decGrossFreightRevenue = (decimal)(OracleNumber)cmdCalculateRevenueCall.ExecuteOracleScalar();

how to change my code to catch a null value and avoid a crash. I have tried several checks (if statements) without success.

View 2 Replies View Related

SQL & PL/SQL :: Code For RESULT Column?

Aug 4, 2011

I have the following data (columns IDx, STARTx and DURATION) and want to create the column RESULT. Example:

create table zTEST
( IDx number,
STARTx number,
DURATION number,
RESULT number );
insert into zTEST (IDx, STARTx, DURATION, RESULT) values ( 1 , null , null , null );

[code]...

The logic behind the table is like this: when there is a Start-ID and a Duration, the field Result shall show the Start-ID for the next records (Duration period). Even if there is a new Start-ID in row 9, it is ignored since threre is already a current signal from row 6 which is still running.

What is the sql code for the RESULT-column?

View 3 Replies View Related

SQL & PL/SQL :: How To Select Last Code From A Column

Jun 27, 2012

i have a table name item.

itemid itemdate Amt
200 17/06/2012 2000
201 18/06/2012 3000
202 19/06/2012 1000
203 20/06/2012 6000
204 21/06/2012 9000
205 22/06/2012 1000
205 24/06/2012 2000
203 26/06/2012 1000

this is the table i have in which repeated itemids for different dates,for example itemid 205 and 203 comes 2 times because that item was consumed again and again in a different date. like this the table data has more than a 20 thousand records;

In which i need to select the last updated dates itemid from this table, because same item id could be consumed 'n' number of times so i need to select it as per the last date or latest dates itemid from this table for each and every item, so how can i select it?

View 6 Replies View Related

SQL & PL/SQL :: Writing A Code For When Condition

Feb 5, 2013

I have a problem in running a sql query.I have a dataset with the following details. Product name,product status,approval date in table product_details. I have a code as follows

select Product_name,
Product_status,
approval_date,
case
when product_status ='Cancelled' or product_status ='Stopped' then approval_date='N/A'
when product_status='Active' then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null')
when product_status='Completed' then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null1')
when product_status='Planned' then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null2')
end as DER_approval_date

from product_details

but i have a error in running this code saying character mismatch.

View 6 Replies View Related

SQL & PL/SQL :: Generating Unique Code

Jun 27, 2011

I have data like this:

a1 a2 a3 a4
ABC ABC xx zz
ABC xx mm mm
fg ui ok pl

I want to give them unique codes like :

Code for column
a1 Code1
ABCC-1
ABCC-1
fg C-2

then column
a2 Code2
ABC C-1
xxC-3
uiC-4

then column
a3 Code3
xxC-3
mmC-5
okC-6

then column
a4 Code4
zzC-7
mmC-5
plC-8

I can add these `columns in the table Code1 , Code2 etc columns to update.

View 1 Replies View Related







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