SQL & PL/SQL :: Timestamp In Mail Generated From Oracle Is Different
Nov 2, 2012
I have found there is a wearied of email generated from my database. I have the data base server located in US (estern time) and the sysdate in the database is AUSTRALIA (AEST). But i am working from India (IST).I have one procedure, which will send email to some recipients once the job is done. It is using UTL_SMTP for sending the email from database.
Now problem is, the email i got from this procedure has a different timestamp which is NOT matching to any of the times:
Time in Mail : 2-Nov-2012 9.56 PM
IST Time : 2-Nov-2012 10.56 AM
Easter Time : 2-Nov-2012 01.30 AM
AEST Time : 2-Nov-2012 4.30 PM
I m using UTL_SMTP to send mail using my mail server. could i check the availability of 1st mail server, if not available then could i set alternate mail server.
C:UsersAdministrator>sqlplus SQL*Plus: Release 11.2.0.1.0 Production on Fri Jun 21 16:03:04 2013 Copyright (c) 1982, 2010, Oracle. All rights reserved. Enter user-name: sys@orcl as sysdba Enter password:
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database; NAME --------- ORCL SQL> @ F:oracleproduct10.2.0db_1RDBMSADMINutlmail.sql
I have enabled the below trace event before running query on my session.
ALTER SESSION SET events = '19027 trace name context forever, level 0x1000';ALTER SESSION SET tracefile_identifier = store_trace;
Then I ran my query, and upon the finishing of the query, I ran the below to disable trace.
ALTER SESSION SET events = '19027 trace name context off, level 0x1000';
Now I checked back on the trace folder in my Oracle DB directory structure, but cannot find any file created by using the above set tracefile_identifier. I had previously run the query with "sql_trace" ON with tracefile_identifier, and successfully got the tracefile generated by using the identifier.
if i click the column it shows the text output(various tags ,symbols)
if i click the HTML it shows the correct format .,
i m sending the mail using that message column .,the mail Received with the text format ,actually i need it in the html format., How to send HTML output?
I am trying the new Oracle 12c and its feature to create columns with the keyword IDENTITY. I create a table CREATE TABLE xt (a NUMBER GENERATED by default AS IDENTITY PRIMARY KEY, b VARCHAR2(10));
And populate it, sometimes specifying the value for a and sometimes relying on the system to generate the value:
INSERT INTO xt (b) values ('a');INSERT INTO xt (b, a) values ('b', default);INSERT INTO xt (b, a) values ('c', 3);INSERT INTO xt (b) values ('d');INSERT INTO xt (b) values ('e');
The problem is that the fourth INSERT fails because the system tries to use the value 3 which is already taken. The fifth statement gets the value 4.
And the table now contains: A B - -1 a 2 b 3 c 4 e Is there something I am missing? I understood that by specifying BY DEFAULT, I would be allowed to sometimes specify values on my own without them interfering with the generated values. I now that the same thing works correctly in MySQL (where I would get five rows from 1 to 5 with the same INSERT statements) /nikos
I need to email an excel file as an attachment in the mail through oracle procedure. I am using UTL_TCP option.
The remote_host i use in test environment is the 'name of the test server'. This test server is a oracle10g database on AIX server.In the test environment the mail works fine.
I have a table A with a column B timestamp(6). The tables contains around 300000 rows..I have created index 'idx' on the column B.When i compare column 'B' with systimestamp, it does not use the index, whereas if i compare 'B' with sysdate it uses the index.
Eg : select count(*) from a where b<=sysdate;
The above used the index 'idx' and executed in 1 second
select count(*) from a where b<=systimestamp;
The above does not use the index and executed in 19 seconds.
I'm trying to deduct x hours from a timestamp value. Everything works fine if x yields a result that is on the same day as the value I'm deducting it from. However, if it yields the day before then it gives the wrong answer using DML (although in SQL it works fine). Let me explain with an example:
Let's say current_timestamp is 20-MAR-12 09.40.00.000000 +00:00
If I deduct 8 hours from this I get the correct answer: 20-mar-12 01:40:00 (syntax: show current_timestamp-8/24)
If however, I deduct 10 hours from it (which takes it to the day before) I get garbage: 20-mar-12 00:20:00 (syntax: show current_timestamp-10/24). It should say: 19-mar-12 23:40:00
In SQL I get the correct result every time. So, my question is, is this a bug or intended behaviour? If the latter then what is it doing and how can I get it to give the answer I'm looking for?
What I'm trying to do is convert a timestamp from one timezone to another using DML. The NEW_TIME function will only give me what I want if I know whether the "to" timezone is currently in daylight saving or not. eg. Must I use CST or CDT?
The full syntax I'm using to try and achieve my aim is below.
show to_char( to_date( '2012-03-19--15-37-23' 'YYYY-MM-DD--HH24-MI-SS' )-(convert( extchars( tz_offset( 'AMERICA/CHICAGO' ) 3, 1 ) int )/24) 'YYYY-MM-DD--HH24-MI-SS' )
where 2012-03-19--15-37-23 is a user-supplied argument.
UDI-22303: operation generated ORACLE error 22303 ORA-22303: type "SYS"."DBMSOUTPUT_LINESARRAY" not found ORA-00600: internal error code, arguments: [kokaocr], [], [], [], [], [], [], [], [], [], [], [] ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_OUTPUT" ORA-06512: at line
The trace directory is full of trc files with the text below:
--------Dumping Sorted Master Trigger List -------- Trigger Owner : INPUT Trigger Name : CUS_TST --------Dumping Trigger Sublists --------
There is like a file generated every minute, and i cant stop it from happening.I have tried setting the trace_enabled parameter to FALSE but no success.
Forms 10g is insatalled recently on my machine. New form is developed,when we run the form it generates FMX file for the same but output is not dispalyed. The output window get closed automatically.
in eache record we are receiving information from differente city, we pretend to get an output where we have a row for each city (delimited by comma) that we have in column CITY
Input data
SELECT '1001001' as CLIENT_ID, 'LONDON, PARIS' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL UNION SELECT '1022201' as CLIENT_ID, 'MADRID, OSLO' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL UNION SELECT '1033001' as CLIENT_ID, 'PARIS' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL UNION SELECT '1033004' as CLIENT_ID, 'MADRID, OSLO, PARIS' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL
Output expected CLIENT_IDDT_REGCITY 100100105/11/2010 LONDON 100100105/11/2010 PARIS 102220105/11/2010 MADRID
How to find out how much undo will be generated by a dml / ddl statement in Oracle 9i? With Oracle 10g we can use the famous mystat.sql and mystat2.sql with argument as 'undo change vector size'.
However with Oracle 9i there is no statname as 'undo change vector size'
I am facing issue with my sequence number about sequence number generated..I want it to follow the sequence all the time, i.e. it should take next incremental number to last generated seauence, but it generates random one all the time, i.e
I have written a procedure to send a pdf file through mail. The directory was created and registered in SYS.DBA_DIRECTORIES with read,write grant access. But im getting the below error.
****** ORA-20014: Error in MAIL_FILES_PRO ORA-20012: Invalid Operation ORA-06512: at "ANAND.PRO_ICTPAYSLIPMAIL02", line 229 ORA-06512: at line 2 ******
i have faced one problem with reports. i had created 4 reports. recently we are designed new application using oracle forms6i. we are created more than 10 forms and added to application they were working fine. but when i was added these reports they are not generated. but in server report are running. i mean the report genereted in server but not in local system.
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of backup plus archivelog command at 09/06/2010 12:36:47 ORA-19715: invalid format F for generated name ORA-27302: failure occurred at: slgpn
As we know that, MV is generating more redo logs during the FAST refresh. but i need more clarifications on that.
see the below examples:
exec dbms_mview.REFRESH ( LIST => 'mv_test', method=>'F');
PL/SQL procedure successfully completed.
select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size';
NAME VALUE ---------------------------------------------------------------- ---------- redo size 147144
see the redo size is 147144 bytes. Immediately, i refreshed in MV view. now there is no update or insert or delete stats happened in source tables. but i do see redo log generation is high for NO DATA refresh.
select a.name, b.value-147144 from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size';
NAME VALUE ---------------------------------------------------------------- ---------- redo size 42352
For no rows refresh, it takes 42352 bytes.. why oracle generated redo logs when there is no DML operations happened in source table.
