Loading...
articles: 

package databasetesting;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class Dbjdbc {

public static void main(String[] args) {
DBcreate();
}
public static void DBcreate()
{
Connection conn = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","system","tiger");
String Str = "Create table Persons"+("PersonID int"+"LastName varchar(255)"+"FirstName varchar(255)"+"Address varchar(255)"+"City varchar(255)");
PreparedStatement state = conn.prepareStatement(Str);
state.executeUpdate();

}catch(Exception e)
{
e.printStackTrace();
}
}
/*public static void DBinsert()
{
Connection conn= null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","system","tiger");
String Str1 = "insert into Persons values(1+'Dhoni'+'Mahendra'+'New Road'+'Bangalore')";
String Str2 = "insert into Persons values(2+'Kohli'+'Virat'+'New Road'+'Bangalore')";
String Str3 = "insert into Persons values(3+'Gambhir'+'Gautam'+'New Road'+'Bangalore')";
String Str4 = "insert into Persons values(4+'Utthappa'+'Robin'+'New Road'+'Bangalore')";
String Str5 = "insert into Persons values(5+'Tendulkar'+'Sachin'+'New Road'+'Bangalore')";
String Str6 = "insert into Persons values(6+'Khan'+'Zaheer'+'New Road'+'Bangalore')";
PreparedStatement state1 = conn.prepareStatement(Str1);
PreparedStatement state2 = conn.prepareStatement(Str2);
PreparedStatement state3 = conn.prepareStatement(Str3);
PreparedStatement state4 = conn.prepareStatement(Str4);
PreparedStatement state5 = conn.prepareStatement(Str5);
PreparedStatement state6 = conn.prepareStatement(Str6);
state1.executeUpdate();
state2.executeUpdate();
state3.executeUpdate();
state4.executeUpdate();
state5.executeUpdate();
state6.executeUpdate();

}catch(Exception e)
{
e.printStackTrace();
}
}*/
}

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
articles: 

A Foolproof Query Execution plan with incorrect statistics

Introduction: The DBA team in a company gets many iritic visitors these days. A day in the life of an application DBA is to collect database table statistics during non-peak hours. The application of Capstone loads a lot of data into tables and truncates them which means the pattern of data in the tables change hysterically. Since the statistics are collected only once during the day, the query optimizer of the database comes to know about the table data patterning only post statistics collection. The data patterns see a lot of variances. Which is why the query execution plans revealed by the optimizer are not very reliable resulting in query performance to drop. Developers in company are alarmed by the query performance dropping without any prior signaling questioning, in turn, the DBA team’s competence to not being able to avert such dropping. The DBA team, on the other hand, are equally frustrated and wants avoidance of sampling statistics throughout the day which will eventually lead to a negative concussion on the overall database performance.

Solution: The solution to this problem lies in using adaptive plans in Oracle Database 12c.

Change in Plans

The adaptive plan concept can be explained by an example of the GPS devices in automobiles. The destination is fed to the GPS device and it comes out with the most faster route to reach the end-point. End-route if you experience a sudden traffic congestion or if your speed is slow, the GPS tracks to suggest a better route. The same methodology works on optimizer in the Oracle Database 12c. Optimizer relies on the latest available statistics to compute a query execution plan. The adaptive plan makes the optimizer during the query execution analyze if the actual conditions differ from existing statistics and come up with a better plan. Hence the optimizer starts following a separate plan post query execution. Not only that it comes with an array of alternative plans and chooses the best plan basis the data obtained on query execution.

Adaptive Plans

Adaptive plans hence come to the rescue. In the determination phase of a query, the optimizer computes an execution result basis the information gathered from statistics as per the usual practice of prior Oracle Database Releases. However, Oracle Database 12c optimizer is advanced as it computes substitute plans called subplans. Upon query execution, collector would cluster runtime statistics and any subplan which looks better than the resultant computed plan the optimizer makes a course correction and opts for the best subplan on offer.

The initialization parameter in the database_optimizer_adaptive_reporting_ only controls such behavior. If the parameter is set to true, the optimizer will not adapt the execution plans (the default is false). The parameter was set to ‘true’ makes the optimizer plans not being adapted. Setting the parameter value to ‘true’ is an excellent way to inspect the behavior of the optimizer when it is not using adaptive plans using the following SQL:

alter session set optimizer_adaptive_reporting_only = true;

Conclusion

The computation by an optimizer results in the execution plan for a query. The execution plan determines the query’s performance. In previous releases of Oracle Database, statistics overshadowed quality of the plans made by the optimizer and unhealthy statistics often caused bad performance and further concerns. But with Oracle Database 12c, the optimizer is now smart to re-compute better plans by judging the effectiveness of the plan during query execution.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
articles: 

Configuring Oracle Financial Accounting Hub

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Do you know about multiple domain certificates? If not you may have to learn quickly, because Oracle has changed the way they are handled in release 12.2. This is going to break a lot of applications.

A multiple domain certificate (aka "Unified Communications Certificate", a UCC) is an SSL certificate that secures multiple domain and host names. There are a lot of them about. Even www.oracle.con is secured by one. In release 12.1 and earlier, there was no problem. You would download the website's root certificate, load it into a wallet, and then you could use UTL_HTTP.REQUEST or UTL_SMTP.STARTTLS or APEX_WEB_SERVICE.MAKE_REQUEST to make the call. It would work if you were going to any of the domains that the certificate secures.

Not in 12.2.

Take this example, using eBay. In 12.1 I can do this,

select utl_http.request(url=>'https://www.ebay.com',wallet_path=>'file:\tmp\wallet') from dual;

or, because I'm based in England, this:
select utl_http.request(url=>'https://www.ebay.co.uk',wallet_path=>'file:\tmp\wallet') from dual;

but in 12.2, only www.ebay.com works. The UK name gives me this:
orclx> select utl_http.request(url=>'https://www.ebay.co.uk',wallet_path=>'file:\tmp\wallet') from dual;
select utl_http.request(url=>'https://www.ebay.co.uk',wallet_path=>'file:\tmp\wallet') from dual
       *
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1501
ORA-24263: Certificate of the remote server does not match the target address.
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1441
ORA-06512: at line 1

There is a solution - specify a new parameter introduced in 12.2, like this:
select utl_http.request(url=>'https://www.ebay.co.uk',wallet_path=>'file:\tmp\wallet',https_host=>'www.ebay.com') from dual;

It is the same with APEX_WEB_SERVICE.MAKE_REQUEST, the latest release has a new parameter P_HTTPS_HOST.

There are some MOS articles that help, such as Doc ID 2275666.1 and Doc ID 2339601.1.

This may complicate your 12.2 upgrades. It is certainly complicating ours.

--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com
https://www.skillbuilders.com/oracle-dba-training/

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
articles: 

How to send Email in Oracle using Gmail.
Email sending by using Oracle through Gmail:

BEGIN EMAIL_GMAIL.SEND ('shabbier.sa@gmail.com','shabbier.sa@gmail.com','Your email subject','Emaisaaasdasal Message body'); END;

*
ERROR at line 1:
ORA-20000: Failed to send mail due to the following error: ORA-29278: SMTP
transient error: 421 Service not available
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.EMAIL_GMAIL", line 52
ORA-06512: at line 1

In oracle we can send email by using two ways.

1- Send Email through Gmail

2- Send Email through Email Exchange server.

In this post we will know how to send email by using oracle through gmail.

Small compnies can not purchase and manage exchange serves so they can use this method by using gmail.

1- We have to install this small software that wil use for as a small exchnage server for gmail, it is third party tool but very usefull tool for gmail.

stunnel-4.56-installer

2- After installing this software got to the installed location like C:\Program Files\stunnel and open this file in notepad “stunnel.conf” and set this configuration.

[SSMTP]
CLIENT = YES
ACCEPT = 1925
CONNECT = SMTP.GMAIL.COM:465

3- Check that Gmail is Pinging with this command in MS Dos

TELNET LOCALHOST 1925
PING SMTP.GMAIL.COM

4- Create Package in Oracle Data Base

— Package Specs

CREATE OR REPLACE PACKAGE EMAIL_GMAIL
IS
G_SMTP_HOST VARCHAR2 (256) := ‘localhost’;
G_SMTP_PORT PLS_INTEGER := 1925;
G_SMTP_DOMAIN VARCHAR2 (256) := ‘gmail.com’;
G_MAILER_ID CONSTANT VARCHAR2 (256) := ‘Mailer by Oracle UTL_SMTP’;
— SEND MAIL USING UTL_SMTP
PROCEDURE SEND (
P_SENDER IN VARCHAR2
, P_RECIPIENT IN VARCHAR2
, P_SUBJECT IN VARCHAR2
, P_MESSAGE IN VARCHAR2
);
END;
/

— Package Body

CREATE OR REPLACE PACKAGE BODY HR.EMAIL_GMAIL IS
— WRITE A MIME HEADER
PROCEDURE WRITE_MIME_HEADER ( P_CONN IN OUT NOCOPY UTL_SMTP.CONNECTION, P_NAME IN VARCHAR2, P_VALUE IN VARCHAR2) IS
BEGIN
UTL_SMTP.WRITE_DATA ( P_CONN , P_NAME || ‘: ‘ || P_VALUE || UTL_TCP.CRLF);
END;
PROCEDURE SEND (
P_SENDER IN VARCHAR2
, P_RECIPIENT IN VARCHAR2
, P_SUBJECT IN VARCHAR2
, P_MESSAGE IN VARCHAR2
)
IS
L_CONN UTL_SMTP.CONNECTION;
NLS_CHARSET VARCHAR2(255);
BEGIN
— GET CHARACTERSET
SELECT VALUE
INTO NLS_CHARSET
FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER = ‘NLS_CHARACTERSET’;
— ESTABLISH CONNECTION AND AUTHETICATE
L_CONN := UTL_SMTP.OPEN_CONNECTION (G_SMTP_HOST, G_SMTP_PORT);
UTL_SMTP.EHLO(L_CONN, G_SMTP_DOMAIN);
UTL_SMTP.COMMAND(L_CONN, ‘auth login’);
UTL_SMTP.COMMAND(L_CONN,UTL_ENCODE.TEXT_ENCODE(‘yourgmailaddress@gmail.com’, NLS_CHARSET, 1));
UTL_SMTP.COMMAND(L_CONN, UTL_ENCODE.TEXT_ENCODE(‘your_gmail_pswd, NLS_CHARSET, 1));
— SET FROM/RECIPIENT
UTL_SMTP.COMMAND(L_CONN, ‘MAIL FROM: <‘||P_SENDER||’>’);
UTL_SMTP.COMMAND(L_CONN, ‘RCPT TO: <‘||P_RECIPIENT||’>’);
— WRITE MIME HEADERS
UTL_SMTP.OPEN_DATA (L_CONN);
WRITE_MIME_HEADER (L_CONN, ‘From’, P_SENDER);
WRITE_MIME_HEADER (L_CONN, ‘To’, P_RECIPIENT);
WRITE_MIME_HEADER (L_CONN, ‘Subject’, P_SUBJECT);
WRITE_MIME_HEADER (L_CONN, ‘Content-Type’, ‘text/plain’);
WRITE_MIME_HEADER (L_CONN, ‘X-Mailer’, G_MAILER_ID);
UTL_SMTP.WRITE_DATA (L_CONN, UTL_TCP.CRLF);
— WRITE MESSAGE BODY
UTL_SMTP.WRITE_DATA (L_CONN, P_MESSAGE);
UTL_SMTP.CLOSE_DATA (L_CONN);
— END CONNECTION
UTL_SMTP.QUIT (L_CONN);
EXCEPTION
WHEN OTHERS
THEN
BEGIN
UTL_SMTP.QUIT(L_CONN);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
RAISE_APPLICATION_ERROR(-20000,’Failed to send mail due to the following error: ‘ || SQLERRM);
END;
END;
/

In this package put your Gmail Address and Password in it in these two lines.

UTL_SMTP.COMMAND(L_CONN,UTL_ENCODE.TEXT_ENCODE(‘YourGmailAddress@gmail.com’, NLS_CHARSET, 1));
UTL_SMTP.COMMAND(L_CONN, UTL_ENCODE.TEXT_ENCODE(‘YourGmailPassword’, NLS_CHARSET, 1));

But At the End I am Getting This Error

BEGIN EMAIL_GMAIL.SEND ('shabbier.sa@gmail.com','shabbier.sa@gmail.com','Your email subject','Emaisaaasdasal Message body'); END;

*
ERROR at line 1:
ORA-20000: Failed to send mail due to the following error: ORA-29278: SMTP
transient error: 421 Service not available
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.EMAIL_GMAIL", line 52
ORA-06512: at line 1

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
articles: 

Will upgrade from 12.1 to 12.2 break your applications? It may if your developers are using JSON.
In both release 12.1 and 12.2, there are these keywords:

orclx> select * from v$reserved_words where keyword like 'JSON%' order by 1;

KEYWORD                            LENGTH R R R R D     CON_ID
------------------------------ ---------- - - - - - ----------
JSON                                    4 N N N N N          0
JSONGET                                 7 N N N N N          0
JSONPARSE                               9 N N N N N          0
JSON_ARRAY                             10 N N N N N          0
JSON_ARRAYAGG                          13 N N N N N          0
JSON_EQUAL                             10 N N N N N          0
JSON_EXISTS                            11 N N N N N          0
JSON_EXISTS2                           12 N N N N N          0
JSON_OBJECT                            11 N N N N N          0
JSON_OBJECTAGG                         14 N N N N N          0
JSON_QUERY                             10 N N N N N          0
JSON_SERIALIZE                         14 N N N N N          0
JSON_TABLE                             10 N N N N N          0
JSON_TEXTCONTAINS                      17 N N N N N          0
JSON_TEXTCONTAINS2                     18 N N N N N          0
JSON_VALUE                             10 N N N N N          0

16 rows selected.

orclx>

The SQL functions are the also same in both releases:
orclx> select distinct name from v$sqlfn_metadata where name like 'JSON%' order by 1;

NAME
------------------------------
JSON
JSON_ARRAY
JSON_ARRAYAGG
JSON_EQUAL
JSON_EXISTS
JSON_OBJECT
JSON_OBJECTAGG
JSON_QUERY
JSON_SERIALIZE
JSON_TEXTCONTAINS2
JSON_VALUE

11 rows selected.

orclx>
The problem comes with PL/SQL. According to the 12.2 docs:
Quote:
SQL/JSON functions json_value, json_query, json_object, and json_array, as well as SQL/JSON condition json_exists, have been added to the PL/SQL language as built-in functions (json_exists is a Boolean function in PL/SQL).
This means that if, within your PL/SQL code, you created a function called (for example) JSON_VALUE, it will compile and run in releases up to 12.1, but in 12.2 it will throw errors. This is what our client had done: they had written PL/SQL equivalents of the SQL functions.
That was a nasty problem to detect, and the only solution is to re-write the functions to have different names and adjust all the code that uses them.
Lesson learnt - never use a keyword as an identifier.
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

It is clear that the Securities and Exchange Commission will not be requiring U.S. companies to use IFRS in the near future, but it is inevitable that IFRS will eventually become a more significant part of the U.S. financial reporting environment. This is actually occurring to an extent as more and more global companies based outside the U.S. are being allowed to file US Securities and Exchange financial reports. For overseas companies who do not file in the U.S. with the SEC, IFRS is becoming the world standard for financial reporting. With Release 11i and subsequently with Release 12, Oracle E-Business Suite has functionality to allow users to live in both the IFRS and U.S. GAAP worlds.

Under U.S. GAAP, fixed assets are recorded at historic cost and are then depreciated to a disposal or residual value. If there are certain indicators that the realizable value of the fixed asset has negatively changed, then the asset is written down and a loss is recorded. This is referred to as impairment.
Under IFRS, financial statement issuers have the option to choose either the cost model (which is in most respects similar to the U.S.GAAP model) or the revaluation model (for which there is no parallel reporting under U.S.GAAP). Under the revaluation model, fixed assets may be periodically written up to reflect fair market value – something that is specifically prohibited under U.S.GAAP and SEC authority.

Cost Model
In the cost model, the fixed assets are carried at their historical cost less accumulated depreciation and accumulated impairment losses. There is no revaluation or upward adjustment to value due to changing circumstances. This is similar to the model currently in use by U.S. GAAP.
Revaluation Model
In the revaluation model, an asset is initially recorded at cost, but subsequently its carrying amount may be increased to account for appreciation in value. The difference between the cost model and revaluation model is that the revaluation model allows both downward and upward adjustment in value of an asset, while the cost model allows only downward adjustment due to impairment loss. This is the model currently adopted by IFRS.
EBS Considerations for the Revaluation Model
In EBS, you can revalue all categories in a fixed asset book, all assets in a category, or just individual assets. Revaluing all categories in a book or all assets in a category is considered “Mass Revaluation” Revaluing individual assets is effected on an asset-by-asset basis.
Regardless of the method used, a prerequisite to performing an asset revaluation is setting up your revaluation rules and accounts, which is done when you define your depreciation books. If you choose to allow revaluations, you must specify your revaluation rules – specifically, whether you will allow EBS to revalue accumulated depreciation. If you do not revalue accumulated depreciation, Oracle Assets transfers the accumulated depreciation to the revaluation reserve account.

Revaluation Functionality is not for Fixed Asset Revaluation under Purchase Accounting Rules
One area of confusion that we have encountered several times is whether the asset revaluation functionality of Oracle EBS can be used to revalue assets of an acquired company.
Under Financial Accounting Standard for Business Combinations, companies are required to revalue their assets to fair value at date of acquisition. This is mandatory for all companies that have to file annual reports with the SEC or roll-up into an SEC reporting company. For our purposes, this means just about everyone. The same applies to IFRS reporting companies. It used to be that companies could use pooling of interest where they could just leave the fixed assets from the acquired company’s books as-is for cost, accumulated depreciation, and date in-service. This is no longer allowable.
Since under both U.S. GAAP and IFRS there is no such thing as a "merger," there will always be a company identified as the acquired. For this company, all fixed assets need to
(1) Be restated to fair value at date of acquisition;
(2) Have the date in service restated to date of acquisition; and
(3) Have any accumulated depreciation zeroed out. If a company can support a contention that netting accumulated depreciation with cost is materially the same as fair value, the acquired company may do that, but the net value has to be migrated to the original cost field, the accumulated depreciation zeroed, and the date in-service still reset to date of acquisition. If the company is restating to fair value and cannot use the netting method, then a number of new fair values must be done for each of the fixed assets.
Mass Revaluation functionality will not work and is not recommended for these types of revaluations for the following reasons:
1. The date placed in-service must be changed to date of the business combination.
2. Mass and category revaluation will not accommodate netting accumulated depreciation or loading multiple, non-percentage driven fair value restatements.
3. Individual asset revaluation is impractical for large numbers of assets and also does not accommodate netting accumulated depreciation or resetting the date placed in service.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview