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:
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?
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?
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.
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.
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;
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?
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.
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.
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?
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.
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.
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.
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
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?
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.
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.
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.
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.
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.
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.
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?
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.
