Home » Posts tagged 'Oracle'
Tag Archives: Oracle
DB – So, what’s a Transaction?
I posted a Gotcha in Oracle Commit some time back. This is one of the posts that gets visited a lot. Also, at work, I saw some SQL scripts that showed a complete lack of understanding of how transactions or commits work in Oracle (or any database for that matter). Hence this post.
Dictionary.com defines Transact as “To carry or conduct business to a conclusion or settlement”. And a Transaction is an act of Transacting! BusinessDictionary.com goes a bit further to explain it as a contract or agreement. It even goes further to define it as an event in accounting, such as entries that go into a cash register. A Transaction then is a self-contained unit of work, that is completed (either with success or failure). This is what is adapted in Database terminology. A transaction must be complete, reliable and independent of other actions.
In Database parlance, we call it an ACID – atomic, consistent, isolated and durable. See Microsoft’s post about ACID here or this Wikipedia article. When a DB transaction succeeds, we COMMIT it and when it fails, we ROLLBACK it.
Transactions in real life
A transaction typically happens between 2 parties and it typically concludes to the satisfaction of both the parties involved. The parties involved either agree to go all the way, or cancel/settle somehow. Think about it. Even if there are multiple parties involved, you can always break it down to smaller and smaller transaction until you can no longer split and that will among 2 parties.
A typical transaction we all can relate is the cash transaction at a bank (at a Teller counter the ATM): You give your card. The teller (or the machine) validates it and asks you to enter your pin number. Once you have done that successfully, then the teller (or the machine) asks you the type of transaction you would like to perform – deposit or withdraw. If you are withdrawing, you tell the amount and the teller gives you the money and deducts it from the account and finally gives you a receipt.
Courtesy life2point1 on toondoo.com
For example, imagine you are at an ATM depositing money into your account. Say you had $1000 in the account before you deposited another $100. This is your transaction with the bank at the moment. (You may have other transactions at the same bank and elsewhere, but each is an independent unit of work). If everything goes well, at the end of the “transaction”, your account will have $1100. What if all is not well? What if Power goes down in the middle of this transaction? Will your account now have $1000 or $1100 or even $900 (incorrect)? What happened to your deposit? The answer in all these cases should be, your deposit is safely completed irrespective of external issues. That’s where the concept of transactions help.
For another e.g., slightly longer transaction, let’s assume your transaction is building a house. To do this, you go to the bank and get the loan, go to the builder and decide the plan, builder cashes his check upon which his bank transacts with yours, builder goes to construction company and gets the building built and so on. And if any of these individual transactions fail, there are repercussions in others. For .e.g., if your transaction with the bank failed, your transaction with the builder does not happen, even if you previously agreed.
Imagine such transactions – they have to be atomic – a collection of small steps each of which is completed and the teller or the system will take you to the next step. For e.g., when you are at the ATM, if the password step doesn’t complete, you don’t stand a chance of seeing the money (worse case, may even trigger a new transaction with the security guard!). Your transaction with the teller is isolated or independent, we don’t want it to be mixed with your other transactions or someone else’s! And it is durable – once done, the results are set in stone (or computer). We certainly expect the house to be durable at the end of that transaction!! And these concepts are typically applied to the Database, since that’s where your results of your transactions are stored (persisted).
Transactions in a DB
The concept of transactions is not new to Databases. Mainframe databases had them for a long time. Modern Relational Databases (and SQL) are all transactional by nature. See this about.com post for a description of database transactions in general. Every major SQL database vendor support transactions. Some may support Autocommit by default. Some, like Oracle, may leave it to the SQL developer (you!).
In a relational database, every transaction may contain one or more database operations. In RDBMS these operations are grouped as Data Manipulation Language (DML), Query and Data Declaration Language (DDL). As I mentioned in my previous post, Commits work differently in DMLs and DDLs in Oracle.
The DML statements are the atomic operations that perform a single database operation like INSERT, UPDATE and DELETE. These operations can be performed individually or combined in a group of statements. Together these make a “Transaction”. At the end of such a transaction, you will have to COMMIT or ROLLBACK to make the effect of transaction permanent or ROLLBACK to revert back to before the transaction block, so the database is left unaffected. See here for more on COMMIT etc. Again, COMMIT can be automatic or manual.
I would like to point out one gotcha here: Since DDL automatically commits, you don’t really need to use an explicit COMMIT after a DDL statement (CREATE, ALTER etc). I am somewhat puzzled to see even experienced developers and DBAs sometimes make this mistake. This is true for even CTAS (Create Table As) statements in Oracle.
CREATE TABLE dup_employee AS SELECT * FROM employee; -- You don't really need this COMMIT; it's already committed above. <span style="text-decoration: line-through;">COMMIT;</span>
We say that this is in an implicitly committed transaction. If we expanded the above statement, it may look like this:
COMMIT; CREATE TABLE dup_employee AS SELECT * FROM employee; COMMIT;
Just you or the database don’t have to show it explicitly, as a matter of convenience. But, this definitely
TRANSACTION Block
When a bunch of SQL statements participate in a single transaction, they are in one single Transactional block. To indicate beginning and end Transaction block, SQL standard talks about transaction control statements. For e.g, a START TRANSACTION (MySQL), BEGIN TRANSACTION (SQL Server) or BEGIN WORK(Informix) marks the beginning of a transaction block. Typically a COMMIT or ROLLBACK would mark the end. To be able commit or rollback to a specific section in the transactional block, databases offer sort of bookmarks, the SAVE POINTS. When you start a transaction block, make sure there is no other transaction block is already open, meaning the last block was committed or rolled back. Otherwise, you will get an error from the database.
In Oracle SQL, you don’t need an explicit “START TRANSACTION” or “BEGIN WORK” statement to mark the beginning of a transaction. Typically, the first non-committed executable SQL statement, can be DML or DDL, (implicitly) marks the beginning of a new transaction block. (otherwise, each statement joins the previously open transaction block). A commit or Rollback will end the transaction.
For e.g,
<<implicit beginning of transaction>>
INSERT INTO department (dept_id, dept_name) VALUES (10, 'HR'); INSERT INTO employee (emp_id, employee_name, dept_id) VALUES (100, 'Employee', 10); INSERT INTO payroll(emp_id, emp_type, pay_rate, pay_rate_type) VALUES(100, 'TEMP', 100, 'HOURLY');
COMMIT;
INSERT INTO lotto_pool (pool_id, emp_id) VALUES (1, 100);
These imaginary inserts are done for each new employee that joins a company. The employee gets added to the employee table and also cross-referenced in a department, payroll tables. Come to think about it, each SQL statement above depends on the previous one to succeed. Thus, they can be part of a single transaction block. If the department does not exist, he cannot be added and if the employee doesn’t exist, he cannot be added to payroll etc.
The above example is just for the illustration of a transaction. Really speaking, the department could have been added long before 100th employee arrives. An employee record could be added well before, his payroll information is setup).
To add little more control to the transaction handling, Databases also provides a marker. In Oracle this is called a SAVEPOINT.
In the above example, if you assume a save point after each DML statement, then you will be able to rollback or commit to a specific save point.
INSERT INTO department (dept_id, dept_name) VALUES (10, 'HR'); SAVEPOINT dept_save; INSERT INTO employee (emp_id, employee_name, dept_id) VALUES (100, 'Employee', 10); SAVEPOINT emp_save; INSERT INTO payroll(emp_id, emp_type, pay_rate, pay_rate_type) VALUES(100, 'TEMP', 100, 'HOURLY'); SAVEPOINT payroll_save;
In this example, we could simply save the department, even if there is an error in employee or payroll.
References
http://www.oracle-dba-online.com/sql/commit_rollback_savepoint.htm
https://www-304.ibm.com/support/knowledgecenter/SSGU8G_11.50.0/com.ibm.sqls.doc/ids_sqs_0329.htm
http://dev.mysql.com/doc/refman/5.7/en/commit.html
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4010.htm
Troubleshooting ODBC (Kofax)
A friend in our desktop support team is trying to install Kofax software on a Windows 2008 machine. He is using Kofax capture 10.2. Apparently, our earlier version (6.x) is working fine in production. I really didn’t know what Kofax was before. I only went to help him with the database connection issue. I often try to learn something new, by helping others with something I know.
From what I understood, the software, called Kofax Capture (previously Ascent Capture), is used to batch scan images and by OCR or even looking up in a database, it can classify the batches automatically. Here is a snippet from their own description:
Kofax Capture™ automates and accelerates business processes by capturing all types of paper and electronic documents and forms, transforming them into accurate and actionable information, and delivering it all into core business applications, processes and workflows.
Kofax with Oracle (through ODBC)
My friend was using the database look up process to be able to identify each batch. Apparently, a document with a barcode is scanned along with the batch of documents. The Bar code contains one of the keys in our database. This is what the process uses to look up the meta data in the database. To do this, he was setting up Kofax to connect to our Oracle database to pick up some meta data from our table to be attached to the image file he picked up. The software is interesting in that, you can attach a query to a document set and that will attach the corresponding meta data automatically. And all these are scriptable.
He had first set up with Oracle 11g driver. This seemed to have failed initially. When he talked to Kofax, they had suggested to downgrade to 10.2. and he installed Oracle client 10.2.3. This broke it completely, in the sense, even the ODBC datasource setup for Oracle kept giving unsupported method error. I looked at this setup a few days ago and thought there was some incompatibility with Oracle drivers on Windows 2008. In any case, I felt we had to make it to work with the latest version of Oracle client (our Server is Oracle 11g). So, I asked him to re-install the latest 11g client.
I came back again with some preparation and tools. We tried the new combination (Kofax Capture 10.2 with Oracle 11.2 driver). It kept failing – Interactive Query in the Kofax GUI client (called index.exe) against Oracle kept failing silently while the script seemed to succeed intermittently.
I really wanted to see the SQLs he was sending to the database. I didn’t know if there was any tool to capture the SQLs from ODBC. I resorted to Oracle v$ tables on the database side. I was able to see see his session and the SQLs. This was comforting. At least we know, it was actually connecting to the database. Using other tools (like sqlplus), I am now certain that Oracle client was working fine. Now, it was time to debug the ODBC/Oracle bridge.
Windows ODBC Setup
To connect to any database (or file) using ODBC, you need to setup Data Sources. To do this, you can go into Control Panel -> Administrative Tools -> ODBC Data Sources (32-bit*) option. It shows you several tabs, including File DSN, User/Machine level Data sources, Available drivers etc. One of the options is to enable trace. This comes in handy immensely while trying to troubleshoot the ODBC driver/connection. Note that the trace file is for all ODBC data sources, so make sure you are using onlyone data source at a time, to be able to debug properly.
“User DSN” refers to datasources that are available only to the current user. “Machine DSN” means it’s visible to all users logging into this machine. File DSN is a file that contains the settings saved in a file. This comes in handy to use the same setup on different machines. Drivers tab contains list of the database drivers available on this machine. There is also an option to trace entire machine. Again use with caution, if there are multiple users logged on to the machine.
Way back, ODBC settings used to be stored in a couple of ini files – ODBC.ini and ODBCinst.ini. These are now stored in registry in the following location:
HKEY_CURRENT_USER\Software\ODBC\ODBC.INI
This contains a list of drivers and their settings. HKCU contains all the datasources for the currently logged in user where as, HKLM contains the settings for the entire machine. This link shows this information for a Sybase driver.
ODBCINST contains information about the drivers installed on this machine/user.
*Our operating system was Windows 2008 32-bit. This made things a bit easier. If this was a 64-bit OS, things will be slightly different. The program that’s tied to the Control Panel item is typically odbcad32.exe. This is actually the 64-bit version of the software. If you are trying to get to the 32-bit ODBC administrator on a 64-bit machine, you will need to run the program in SysWOW64. You cannot mix the bitness. If your database client is 32-bit, then you will have to use 32-bit ODBC admin and 64-bit admin, otherwise. See here for more information. The drivers tab will only list drivers with the same bitness as the admin software. See this Microsoft note for more info.
Troubleshooting ODBC driver
First, my goal was to find out how compatible was the Oracle driver with the new ODBC drivers on Windows 2008. This way, I can find out if the driver would even work for the current setup. One of the tools I found earlier, called ConfTool from Microsoft (?), which shows the conformance/compatibility of a certain database driver to ODBC levels. Unfortunately, this only tests ODBC Levels 1 and 2.
The tool showed, while Oracle driver seems to support most of the functions, there were some features and attributes that were failing.
Next, I really wanted to see if the data source used by Kofax was working. To do this, I used was SQLRun from SQLEdit.This is a command line utility that lets you test any SQL against a specific ODBC Data Source. This is a real cool tool. I set it up, so it will use the ODBC data source we setup earlier for use with Kofax. I also used the query coming from Kofax that I captured in the database above. The goal was to see the result of issuing this query worked OK through the data source outside of Kofax. I had already made sure this query worked in Oracle SQL*Plus. Now, I am trying the same query through ODBC. This will guarantee that the ODBC to Oracle connection is OK.
You can setup all the required parameters for a test run in a file called SQLRun.ini. These parameters can also passed on commandline. SQLRun has a lot commandline options.
Here is what SQLRun.ini looks like for my test:
SQLRun.ini
[Connection]
DSN=testDB1
User=<user>
Password=<pwd>
[Settings]
Output=out.txt
Errors=error.log
Delimiter=t
Header=1
OverwriteOutput=0
OverwriteLog=0
NullIsEmptyString=0
[Script]
;File=source.sql
Statement=Select * from latax.sysparm where rownum < 2;
When it completes, if all goes well, the record(s) fetched will be placed in Out.txt file. Errors.txt file contains any errors.
I turned on ODBC Trace just before this step. I turned it off right after, so the trace file now contains all the ODBC calls generated for transaction in SQLRun. Since this worked fine, I will keep this trace as my baseline to compare against.
Troubleshooting Kofax ODBC/Oracle connection
After confirming that the ODBC setup worked fine outside of Kofax, I returned to verifying the connection from Kofax. I turned on a fresh ODBC Trace with a new trace file. Then I asked my friend to run Kofax query in the Index tool.
At the end of this test, I have 2 trace files for the same SQL run through ODBC connection to Oracle – one from command line and another through Kofax. I diffed the 2 files using Winmerge. There was the difference I was looking for.
The Kofax software was sending several additional ODBC calls. One of them was to SQLSetStmtAttr function to set SQL_ATTR_QUERY_TIMEOUT to 0 (see here for more on this). This seemed to cause the query to fail immediately. Now, why they would set the timeout to 0, I don’t know.
I wanted to increase the timeout value. I couldn’t figure out if Kofax has a setting to change this. I tried the next best thing – can this feature be disabled? I looked around and found Enable Timeout Checkbox on the data source details in ODBC Admin screen. I unchecked this and ran Kofax again with new ODBC Trace restarted. Bingo!! That seemed to have done the trick!!!! The screen got populated this time!!
When I diffed the new trace file with the one from the previous run, I noticed this time, it did try to set the Query Timeout to which the Oracle driver returned a -1, saying it’s an unsupported optional feature. From then on, Kofax seemed to have stopped setting the timeout setting before issuing a query!!!! After we did this, the query passed each time, we tried. (Oracle driver didn’t seem to support the function anyway. But, according to this page, it can be used to avoid infinite wait time to resolve locks).
When we did the same exercise with the scripted (automated) run he had in Kofax, it seemed to go through fine. The Trace output more or less matched my trace file from running the same query in SQLRun.
I really don’t know how the Kofax script was different from running the query interactively in the GUI tool. Whatever it was, the tool was creating a problem and we found a solution. But, I know, this is just a workaround. What if a query really needed timeout? I’ve asked my friend to check with Kofax as to why the discrepancy. That’s for another day 🙂
Gotcha: “BEGIN” in Oracle
If you are coming to Oracle from another database, you are in for some surprises. One of them is the key word BEGIN. They don’t mean the same in Oracle. Read on.
A TRANSACTION block
In SQL Standards they list Transaction control statements such as START TRANSACTION, END TRANSACTION and SAVE TRANSACTION. Different database vendors implement this as they see fit. For e.g., Informix always had a “BEGIN [WORK]” statement to mark the beginning of a transaction block. The block will end when an explicit COMMIT or ROLLBACK is issued or SQL ends abnormally (in which case it’s rolled back). See here.
So in non-ORACLE database (Informix for e.g) you may have,
BEGIN [WORK] --(1) INSERT.... UPDATE... DELETE... .. COMMIT [WORK]; -- **commit marks the end of the current transaction**
In Oracle there is no BEGIN [WORK] equivalent. It’s just “understood” when the first executable statement makes some changes to the database. This includes DML and DDL statements. So, the above statement block in Oracle will be written as,
-- **implicit Transaction block begins?** --(1) INSERT.... UPDATE... DELETE... .. COMMIT [WORK]; -- **commit marks the end of the current transaction**
So, there you have it. In Oracle, an implicit transaction block is started when the first INSERT statement is executed and it is not started with a BEGIN or START.
BEGIN…END in Oracle
But then, you have surely seen a BEGIN (and END) in Oracle? Yes, you are correct about that statement being there, only it’s a statement in PL/SQL not plain SQL. PL/SQL is a separate procedural programming language in Oracle, just like C or Java, only PL/SQL is specialized to handle SQL more efficiently1.
This BEGIN statement in PL/SQL can be easily confused with BEGIN [WORK] in other databases, if you are new to Oracle. A BEGIN..END block in PL/SQL is merely a syntactical collection of statements (SQL, non-SQL). They don’t relate to transactions. However, since a bunch of SQL statements inside the BEGIN..END block mark a single PL/SQL statement, a COMMIT (or ROLLBACK) after a PL/SQL block could commit (or rollback) entire list of SQL statements within the PL/SQL block, thus implying BEGIN had to do with a new transaction. Not so! Suppose you ran the below block in SQL*Plus. Let’s say you updated a table. Try SELECTing the record you just updated, in another session. You won’t see the change yet, because we have not committed the below statement. That also shows END doesn’t have anything to do with Transaction.
-- **PLSQL BLock 1** BEGIN -- **SQL statements** END; / And as another example, if you had couple of PL/SQL block (BEGIN...END) before a COMMIT is issued, statements in both those blocks are committed. -- **PLSQL BLock 1** BEGIN --**SQL statements** END; / -- **PLSQL BLock 2** BEGIN --**SQL Statements** END; / COMMIT;
Here COMMIT applies to all the statements PLSQL blocks 1 and 2, showing us that PL/SQL block does not correspond to a transaction block.
[1] I think, differentiating between SQL and PL/SQL is the biggest challenge a newcomer to Oracle faces. PL/SQL is a procedural language with control structures like if, loops etc. You can build stored programs like Oracle Procedures, Triggers, Functions and Packages only in PL/SQL. It’s tightly coupled with Oracle SQL Engine causing some confusion. I will post more on this later.
Quick Tip: Oracle – Commit on Exit
Oracle by default does not automatically commit a transaction (Autocommit is typically off). Transaction Management (Commit or Rollback) is left to the client as this askTom post explains it nicely. The tool you use may provide this option for you. For e.g., SQL*Plus commits any open transaction upon exit. So, even if you don’t have COMMIT at the end of a script, when SQL*Plus will issue a COMMIT when the SQL is exited.
This was the default behavior for a long time and was generally accepted. Some developers wanted to have more control over it, and according to this stackoverflow post there was even a bug (633247) opened for it in 1998! Recently, in Oracle 11g this has finally been changed. Now, as of Oracle 11g R2, the user actually has an option (EXITCOMMIT) to tell SQL*Plus whether to COMMIT or ROLLBACK upon EXIT. Following statement makes SQL*Plus roll back a transaction upon exit.
set exitcommit off
See this Oracle Post for a good example.
Notes:
- One poster in the Stackoverflow post above, actually mentions about Autocommit option in SQL*Plus. I just want to clarify:
SQL*Plus does have an option to set Autocommit on or off. This is actually meant for every statement that you issue, not just the last statement before exit. Prior to Oracle 11gR2, this did not have an impact on the Commit on Exit!
- Also, this change is actually in client tool (SQL*Plus) not in the database. So, if you are using an older client (like I am), you will be disappointed not to find this option!
References
http://stackoverflow.com/questions/1368092/why-does-sqlplus-commit-on-exit
http://www.oracle.com/technetwork/articles/sql/11g-misc-091388.html
http://www.acehints.com/2011/07/oracle-11g-r2-sqlplus-set-exitcommit.html
http://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:314816776423
Oracle: Commit and Forward Slash
Further to my post earlier about DDL and Forward Slash, I would like to explain Forward slash’s role (or lack thereof) in other scenarios. One such scenario people seem to get confused with is COMMIT. They seem to want to find some type of connection between the two. Let me say it upfront, there is no connection between COMMIT and “/”.
Forward Slash is simply a SQL Executor and it happens to show up in various situations. This combined with SQL*Plus’s own quirks seem to imply certain other functions for this character. Let me repeat: it’s just an executor – short for RUN command in SQL*Plus.
One reason it could be construed as connected with COMMIT is that it may often be the last character in a file. When you run SQL*Plus in batch mode (running a script in a file), it typically exits after the last line (on *nix. It doesn’t DoS/Windows). And the SQL*Plus’s own behavior is to COMMIT a transaction when exiting. (Why? See here for a nice discussion about this.) So, it’s the exit that committed not the “/”. But. you can see why some newcomer to Oracle who just inherited some scripts, may think that “/” (being the last statement) actually committed the transaction!!!
The other reason could be DDL. DDLs are implicitly committed. (Actually it commits before and after the DDL statement itself, so beware if you are mixing DMLs and DDLs).
Now, typically a DDL could be ended with “/”. For e.g.,
INSERT INTO department(100, 'HR');
CREATE TABLE employee(
emp_id NUMBER,
emp_name VARCHAR2(40)
/
In the above case, department table will have the entry, even if the CREATE TABLE below failed. In generla, any DML (like INSERT/UPDATE/DELETE) before a DDL, DML would have been committed, even if the DDL itself failed. Since “/” is the last statement here, one could think, “/” did it!!! Another strike against “/”.
So, trust me folks! “/” doesn’t do anything else, except sending a SQL to Oracle in SQL*Plus. It’s just a shortcut for Run Command. But, depending on the situation, it may look like it’s doing something more. In such cases, analyze the SQL and try to rearrange them. Try to keep DMLs separate from DDLs to avoid surprises. And of course, set some standards for punctuations and stick to it to avoid any undesired effects.
Oracle: DDL and Forward Slash
In this blog, I write about various technologies I come across. Only a few topics seem to get a lot of attention. One of them happens to be Forward slash in (Oracle) SQL. I’ve already posted about this here and here. When I look at the search terms that lead to my site, I sense a lack of understanding of it’s usage. A search through the web quickly reveals that forward slash has been a source of confusion even among some of the experienced Oracle SQL developers 1. Based on the search terms, I can see the newcomers are often getting confused by the SQLs left for them by their predecessors and are looking for a clear explanation. “/” is often mixed in various statements, such as DDL, COMMIT etc and this leads to confusion about the statement itself. I will try to explain the role of “/” (or lack thereof) in each different scenario in separate posts.
One of the search term that I get a lot is “DDL and Forward slash” or some variation of it. Forward slash has nothing to do with DDL per se. If you saw my other posts, it is just a statement executor (RUN command for both SQL and PL/SQL). Unfortunately, semi-colon doubles as statement terminator and executor for SQLs (poor choice of shortcut) in SQL*Plus. There lies the confusion. Thus, the CREATE TABLE or DROP VIEW statements can be terminated by a semi-colon as well as “/”.
For e.g.,
CREATE TABLE employee(
emp_id NUMBER,
emp_name VARCHAR2(40));
and
CREATE TABLE employee(
emp_id NUMBER,
emp_name VARCHAR2(40))
/
are identical as for SQL*Plus is concerned. But, as a matter of preference, people tend to use “/” in DDL.
But this is not the case in other DDL statements that involve PL/SQL:
CREATE OR REPLACE FUNCTION f_get_employee(a_emp_id NUMBER)
RETURN VARCHAR2
AS
v_emp_name VARCHAR2(40);
BEGIN
SELECT emp_name
INTO v_emp_name
FROM employee
WHERE emp_id = a_emp_id;
END;
/
Here “/” is a must, as the above statement is one PL/SQL statement (from CREATE to END;). Remember, PL/SQL block ends with END; and it has to be followed by “/” to be executed 2 (see notes below). Thus some DDL statements (those that create programming constructs like packages, procedures, triggers) require “/” at the end. This, I think, led the newcomers to believe there is a link between “/” and DDL!!! There is no connection! You cannot generalize the use of “/” for all DDLs. For e.g., if you tried to do this (semi-colon followed by “/”) in a CREATE TABLE statement, you will have unexpected behavior:
2 emp_id NUMBER,
3 emp_name VARCHAR2(40));
Table created.SQL> /
CREATE TABLE employee(
*
ERROR at line 1:
ORA-00955: name is already used by an existing objectSQL>
In this case, the semi-colon followed by slash is a bad idea.
In a nutshell, use only semi-colon or slash at the end of a SQL (DML or DDL) and semi-colon followed by slash at the end of a PL/SQL. See here for the difference between SQL and PL/SQL).
As a personal choice, I normally use “/” for all DDLs (SQL or PL/SQL) and semi-colon for all DMLs. You can have one DDL per file in which case, the “/” is the last character in the file. You can also have multiple DDLs in a file, each separated by “/”. Try not to mix DDLs and DMLs in the same file! This may lead to more confusion and errors!
References
http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands
http://docs.oracle.com/html/A90108_01/sqcmd.htm
Notes:
1. See here for e.g., https://forums.oracle.com/thread/1020117
2. When I say “executed”, I mean the PL/SQL block that creates the function/procedure is executed – meaning the program is compiled and stored in the database. (Hence the name stored programs.) The actual program execution is done by
SELECT <function name> FROM dual; OR EXEC <procedure name>
Oracle SQL – UNION ALL
Here is another TechTarget article about Oracle SQL. This time about using UNION ALL to combine results of 2 SQLs to sort numbers.
Quick Tip: tnsping
Tnsping is a nice little utility that comes with Oracle. Lot of developers don’t know about this. If you have SQL*Plus available on your machine, chances are you also have tnsping. You can use this tool to troubleshoot Oracle connectivity issues, sort of like ping for TCP/IP. To use it, just type
tnsping <Service Alias> [count]
Where Service Alias is defined in tnsnames.ora.
Typically, if tnsping returns an error, chances are the alias doesn’t exist in tnsnames.ora file or a typo. Just add/correct that in the file, you will be able to ping the Oracle instance.
On a Windows system, this is where an introduction to tnsnames.ora would end. But not on Unix.
Gotcha on Unix
When we had connectivity issue with Oracle 11g database this morning, we kept getting errors while trying to tnsping.I logged in with the oracle id and checked the contents of tnsping.ora file. It looked OK. Also, with this id, I was able to login to Oracle without any problem. This was a puzzle.
Then I remembered the file level permission on *nix systems. I looked at the permissions attribute of the file, tnsnames.ora (If you do a ls -l on Unix, it shows you that). Bingo! The file had didn’t have “read” access for Other (public).
-rw-rw-r-- tnsping.ora
In this case, even though the file was there, it wasn’t “visible” to other id’s, because of missing read permission. When I added that (r in bold), the other user was able to tnsping and connect to Oracle finally.
Note on connectivity
If tnsping is successful, it merely tells us that the SQL*Net listener is running correctly on the server side. This doesn’t guarantee that the database itself is running. You need to login to find that out.
References
- http://edstevensdba.wordpress.com/2011/02/27/tnsping-101/
- http://www.orafaq.com/wiki/Tnsnames.ora
- http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/TDT2/Default.aspx
- http://docs.oracle.com/cd/B19306_01/network.102/b14212/connect.htm#sthref1535
- http://www.dartmouth.edu/~rc/help/faq/permissions.html
Gotcha: Oracle11g Password Case Sensitivity
We are in the process of upgrading to Oracle 11g. Today, a friend at work asked about an issue he had with connecting to Oracle 11g from Unix. He was able to connect fine in SQL*Tools, SQL*Plus etc, but not when connecting from a script. The script kept failing with invalid user name or password error. The difference was that the script actually used an encrypted password and decrypted it using ccrypt utility.
We tried the decrypt option on command line, and it turns out the password was all upper case. Same script used to work in 10g. Next, we tried SQL*Plus with upper case password. It failed while the same thing worked in Oracle 10g. There we realized that Oracle 11g passwords may be case sensitive.
A beautiful post about the same, confirmed this. Apparently, passwords are case sensitive in Oracle 11g by default. This can be overridden as mentioned in the above link. Just to be complete, I am including that here:
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;
You can also look at Oracle page about authentication for more details. AskTom talks about this change.
It was a real gotcha for us today.
References
http://www.oracle-base.com/articles/11g/case-sensitive-passwords-11gr1.php
http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams211.htm
http://docs.oracle.com/cd/B28359_01/network.111/b28531/authentication.htm#DBSEG3225
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:708040800346734217
Power to Build 