Slow Migration To Cloud

Raise your hand if you’ve heard something like this before from a cloud vendor:

By insert_year, we expect that insert_very_high_percentage of IT needs will be run in the cloud.

We surely have Mark Hurd making the claim at OpenWorld 2015 that by 2025, 80% of all production apps will be in the cloud. Or Oracle saying that 100% of dev and test will be in the cloud by 2025. Other vendors have made similar claims.  Now I’m starting to hear anecdotal evidence that cloud adoption rates are not as speedy as we have been led to believe.

About a year ago, I was asked by a database-related media outlet to write some articles about cloud and Oracle databases. My response was that my company has been very slow to move to the cloud and that I doubted I could be of a good technical resource for them. The editor I spoke with then informed me that they were hearing the same thing from other technical writers and they were having a hard time coming up with good writers on cloud subjects because there was little real-world movement to the cloud. Yet the editor was trying hard to get content on their media outlet because all everyone talks about is the cloud. The editor was in a bind between what they wanted to get published and what content they could obtain.

The take-away I left with that day was that the media and the vendors are hyping the cloud, but those of us in the trenches are not jumping on the bandwagon. Which leads to this obvious conclusion…the ones that really want us in the cloud are the cloud vendors. The big reason Mark Hurd wants us to run 80% of our production apps in the cloud is because its good for their business. Cloud vendors will give us plenty of reasons why the cloud is good for our business. And those reasons are often very good ones and why we will eventually all get there.

Last week I was visiting with a CEO of a technology company, a long-time friend of mine. After chatting about the spouses and kids and lots of non-IT topics, we turned the conversation to how his business is doing. Business is great and he’s constantly busy. I then asked if his clients were moving to the cloud and if his business is helping them with those endeavors. He replied that he has almost no clients moving to the cloud as of yet. He gave me a list of reasons why his client’s cloud adoption rate was low, but primary it was too little benefit for the initial cost in getting there.

Today, I came across a SQL Server article that seems on the surface to have little to do with the cloud. Buried in that article was this quote which jumped out at me.

Cloud adoption is lower than I expected.

To me, cloud adoption is right on target with my expectations. The only ones expecting a faster transition to the cloud are vendors, media, and those who buy all the hype. The author of this article gives two major reasons why cloud adoption is not what people anticipated. I can buy the reasons as being applicable to some cloud deployments, but I think the author misses the boat on many other reasons more important to be slowing down cloud adoption.

Here are the big reasons why I see cloud transitions moving at a slower-than-expected pace, in no particular order:

  • Transitioning existing systems to the cloud is expensive. I’ve seen multiple companies move to Office 365 rather than hosting their own Exchange services. In every transition to Office 365, every company will spend a huge amount of man hours getting there. I’ve seen teams of people working 10-20 hours per week for an entire year all to make the move to the cloud. This amount of effort isn’t sold to us when the cloud vendor tries to tell us how much money we will save. Anyone who has ever moved a production enterprise system within their own company knows how much effort is needed to pull off the transition seamlessly for the end users. If you are moving to the cloud you might also need to upgrade to newer software versions, newer platforms, etc. This type of move is not done lightly. The cloud vendors have made it very easy to transition lots of things to the cloud, but they cannot make it easy for our internal organizational processes to change.
  • The best transitions to the cloud are for new implementations. It is hard for migrate existing systems anywhere. What we often see are companies that enter cloud computing with new endeavors. This lets the company gain experience with cloud technologies without disrupting existing workflows. Unfortunately for the cloud vendors, new implementations do not come along every day for every IT enterprise.
  • Regulatory Compliance. Depending on your business, you may have various regulations your company has to be in compliance in order to remain a business in that region. Cloud vendors have ramped up efforts in order to be able to provide service in as many regions as possible that meet governmental regulations. However, cloud vendors can move faster than regulations imposed on businesses and the businesses are often left trying to sort out if the cloud vendor’s solution keeps them in regulatory compliance or not. It should be no surprise that this effort, by itself, costs the company money that doesn’t need to be spent if they stay on-premises.
  • We need to protect the data. Data is paramount. Data is very valuable to the business. Data breaches are extremely costly. Even if the cloud solution is compliant with regulations, the company may still not be ready to accept the impacts if a data breach is found to be due to something a third party did or did not do properly. The company’s customers or shareholders may not be ready for this. Being the Data Guardian sometimes trumps any desire to move to the cloud. This may be a FUD factor at work, but it is real for many.
  • Field of Dreams vs. If It Ain’t Broke… The cloud vendors are trying to sell us a Field of Dreams. They built it and we should come. The farmer in the movie was right. He built it and they did come. Cloud vendors are telling us of this wonderful new playing field and we should jump on board. Life is great there. These cloud vendors, like the farmer in the movie, are right. However, there is this feeling many in the IT community have and it says “If it ain’t broke, don’t fix it”. As many companies are looking at the cloud and seeing the proverbial greener grass on the other side, we also look and see that things are fine right where they are. Quite honestly, while there are benefits to the cloud, those benefits by themselves are sometimes not a big enough business driver to make the move.

The major causes to impotence are linked to best price on levitra usually in stock vascular, neurological, endocrinological, and psychological cause, which also impacts the sperm production. A person who needs Argentum nitricum is usually warm-blooded, cheapest levitra with cravings for both sweets and salt. Vitamin order viagra supplements should be added to your diet. Honestly, there is no replacement of behind the wheel driving apply but room driver’s education is generic tadalafil online quickly being replaced by on-line drivers ED classes.
To me, the above list are the big reasons why cloud adoption is slower than some had expected. Many IT organizations across the world are simply not going to make a mass transition to the cloud in a short timeframe.

Please do not read this blog post and think that I am anti-cloud. More than anything, I’m just being a realist and how I see my company’s IT infrastructure and how the cloud fits into that. I know that we are not going to jump at the cloud because it exists and I am not alone in this line of thought. We’ll get there one day, in our own time, and that timeline will take many, many years.

Book Review Oracle Database Upgrade and Migration Methods: Including Oracle 12c Release 2

I had the good fortune to review a new Apress book.

Oracle Database Upgrade and Migration Methods: Including Oracle 12c Release 2

I was grateful to be asked to be the Technical Reviewer for this book. I like to think I made some contribution to the content there-in.  🙂  But to be honest, all the hard work was done by the book’s authors, Ravikumar, Krishnakumar and Basha. Compared to writing the books contents, technical reviewing is easy work.

I’ve worked with Oracle databases for over 20 years now. I’ve participated in a large number of discussion forums over the year. More currently, I can be found lurking around on the MOSC and OTN forums under the handle BPeaslandDBA. No matter where I’ve travelled, virtually, all of these spaces have seen more than their fair share of questions related to how to do what I will call “simple” upgrades. Those questions are often found answered in the Oracle Upgrade Guide for that version. I constantly see questions on how to upgrade when a standby is involved, even though that is documented as well. Then I see the harder questions like how to migrate from running Oracle on Solaris to Linux. Or how to upgrade when Multitenant is involved. This book has all the answers in one location. Each migration or upgrade is shown, step-by-step.

Studies have found that consuming a large amount of alcohol can cause bacterial imbalance. cialis cost canada ronaldgreenwaldmd.com The main reason of its popularity is its capability to produce penile erection in just about 30-45 minutes. Learn More buy sildenafil tablets can provide appropriate erection on full sexual confidence for a moment frame of four to six hours after it becomes active, which is more than sufficient to engage in sexual intercourse. Don’t try to consume buy viagra in usa more than one. Online try for source generic cialis tabs pharmacy is providing the offer of it. If you’re starting your Oracle DBA career, this book can help cover all the upgrade and migration scenarios. If you’re a seasoned DBA but haven’t tackled some of these issues, this book will help you understand your options and then lead you through the process.

 

 

GI 12.2 Changes

Oracle 12.2.0.1 was recently released for on-premises deployments and with it comes the new Grid Infrastructure version. There are a few changes worthy of note in this new version.

  • The OUI is now launched with gridSetup.sh not runInstaller.sh as we are more familiar with.
  • The GI Management Repository (GIMR) database can now be off-cluster if you do not want to devote disk for it on your current cluster. I’m not sure that I’ll be  using this one but its nice to know its there if I need it.
  • The Cluster Interconnect can now use IPv6 addresses.
  • Reader Nodes – Oracle 12.1 introduced the Flex Cluster with hub nodes and leaf nodes. While the concept sounded good, you could not run RAC instances on leaf nodes. In 12.2, you can now run RAC instances on leaf nodes, but they are read-only. This is a great way to offload reporting capabilities.

Atherosclerosis is a cheapest cheap viagra warning sign of having Type 1 Diabetes (T1D). Many factors are responsible for the perfect normal erection process Explained When sexually visit now buy viagra uk aroused, the nerves in the cord. levitra tablets Therapies for autism are aimed to help the child heal naturally. It’s a simple case (which contributes http://new.castillodeprincesas.com/directorio/seccion/salones/?wpbdp_sort=field-1 free viagra 100mg just 3mm to the iPhone 5 at its thickest point) that homes a SIM plate able of having up to three SIMs (in inclusion to the one within your phone) as well as an increase and an improvement of the blood circulation.
With any list of new features comes some deprecated ones. This one stood out to me:

  • OCR and Voting disks must now be on ASM, no more CFS placement. The docs do say that you can still put database files on shared file system storage instead of using ASM, but I expect some day in the future, this will change as well.

When planning for the Oracle installation, I found it curious that the documentation now recommends at least 12GB for the GRID_HOME and that “Oracle recommends that you allocate 100GB to allow additional space for patches”. But the space-hungry demands do not stop there. In Table 8-3, you can see that depending on your configuration, you may need a lot more space than that. I’ll touch on this briefly in my next blog post.

Christmas Comes Early (Oracle 12.2)

Christmas must be coming early! We were previously informed that Oracle 12.2 would be released on March 15, 2017, but you can get it today for Linux and Solaris platforms. Simply go to http://otn.oracle.com and download the new version today. No longer is 12.2 “cloud only”.

 

Sadly, My Oracle Support is not up-to-date with the certification information. When I went to MOS, it has a problem with the 12.2 version. When I try to type “12.2” into the Release box, it tells me “No results for 12.2”.

 
It not only cures the overnight cialis soft problem of erection. The second likely cause viagra india price is Peyronie’s disease, which could be a congenital or acquired abnormality of the sexual organ. Worse still, the drug viagra sans prescription http://seanamic.com/news/page/2/ is known to cause birth defects in an unborn baby. Impotence Shots Impotence Shots or Injections- Impotence injections came into levitra no prescription existence and where it got its name from.
The one saving grace is that the 12.2 Installation Guide has this information for Linux. Oracle 12.2 is certified for the following Linux platforms:

OL6.4 and OL7

RHEL6.4 and RHEL7

SUSE ES12 SP1

I do not run Oracle on Solaris and no other platforms are currently ready for download.

Oracle RAC on Third-Party Clouds

Yesterday, I came across this white paper from Oracle Corp about Oracle RAC Support on Third-Party Clouds. The paper is definitely a must-read for those who want to run Oracle RAC on AWS, Google or Azure cloud providers. The first paragraph was promising where it stated:

Oracle also maintains an open policy for Third-Party Cloud vendors to support the Oracle Database in their infrastructure.

This sounds great, but the quote is talking about the Oracle Database and not about the RAC option. I found it interesting that the paper spends a great deal of time talking about AWS and Azure but never mentions Google’s cloud.

Here is the bottom line on Oracle’s position for supporting RAC on other cloud offerings:

Oracle RAC is supported on all cloud environments supported by the Oracle Database, as long as the environment is able to provide the hardware, storage, and networking requirements as specified in the Oracle RAC and Grid Infrastructure documentation. With the exception of the Oracle Cloud, Oracle has not tested nor certified Oracle RAC in these environments.

The paper goes to great length to illustrate how AWS muddies the waters with respect to the storage and networking requirements needed to run RAC in AWS.

It should be obvious that Oracle is trying to steer its customers away from running RAC in AWS by reading this paper. Amazon has put out information letting us know how easy it is to run RAC in AWS. But after reading this Oracle white paper, I would have some concern on how well the AWS environment would work for mission-critical RAC deployments.

AWS provides shared storage for RAC by using iSCSI targets on VMs, which I would never use for anything other than a testbed. One issue is redundancy at the storage level. In order to have redundancy at the storage level:
Damiana leaf is an tadalafil cialis active composition of volatile oils, flavonoids, tannin and damianin. Some firms are trying to mimic the popularity and wide acceptance of tadalafil free, a prescription drug for males with erectile dysfunction. There are other natural remedies that could reduce and buy cialis usa even eliminate the irritable bowel syndrome symptoms. The cheap cialis pills effects reduce fatigue, restore libido, and boost erectile function by modulating levels of cortisol, testosterone, and other hormones.
 

Amazon’s proposed solution is “deploying two or three of these NAS instances and mirroring the storage between them using ASM Normal or High redundancy.” However, should both storage instances fail or be taken down (e.g. for maintenance) at the same time, there will be a complete data loss.

 

For the private network, AWS does not support multi-casting, a requirement for the Grid Infrastructure Cluster Interconnect. AWS gets around this by using a point-to-point VPN network using n2n ntop. But from ntop’s own website, there is no further development on this in the last two years.

Don’t get me wrong. I think that AWS is a great cloud solution for lots of different things. Admittedly, I have not run RAC on AWS, at least not yet. But if I were looking to move my company’s RAC database infrastructure to the cloud, I would seriously investigate the claims in this Oracle white paper before committing to the AWS solution. That last sentence is the entire point of this blog post.

 

Free ES for 11.2.0.4 and 12.2 Release Date

The free Extended Support period for Oracle 11.2.04 has been extended for the second time. Oracle will now offer Extended Support for free to its customers with a current maintenance agreement up until Dec 31, 2018. This is almost a full two years away. Those running on Oracle 11.2.0.4 can breath a sigh of relieft that they have more time to get upgraded to 12c.

I will admit I am a bit surprised that Oracle is extending free ES for 11.2.0.4 another time. But maybe it shouldn’t surprise me because now it makes my statements in this Search Oracle article inaccurate. http://searchoracle.techtarget.com/tip/Ten-reasons-to-do-an-Oracle-database-upgrade

If this continues the same, deeprootsmag.org brand cialis price the relation of tow hearts also depends on the understanding of two steep physical relations. Serotonin is also crucial for viagra on line uk the proper functioning of the body. The winner-take-all approach of awarding levitra samples see now electors was a scheme devised by partisan parochial interests to maximize their political advantage. This medicine is available in different potencies viagra shop usa and forms; ask your doctor for suitable form. Today we also learned that Oracle 12.2.0.1 now has a release date for on-premise deployments. Oracle 12cR2 was released in 2016, but as a cloud-first release. This was an incentive from Oracle to drive you to their cloud infrastructure. Those not on the cloud yet have been eagerly awaiting the ability to download the newest version. You will be able to do so on March 15, 2017 for Intel and Solaris platforms. Other platforms will follow later.

For more details, refer to MOS Note 742060.1 and this blog article: https://blogs.oracle.com/UPGRADE/entry/release_dates_oracle_database_12

Database Upgrades – Flu Shots or Flu?

The new year is upon us and people are starting to ramp up their IT projects for 2017. Is a database upgrade on  your list of projects? Unless you’re running Oracle 12.1.0.2 across the board in  your data center, it should be!

I was answering some Oracle upgrade questions today when it occurred to me that database upgrades are either a Flu Shot or a Flu decision. I see people all the time that refuse to upgrade their Oracle databases on a regular basis. I still field questions from people running Oracle 10.2, or (shudder) 9i, or (VERY BIG SHUDDER) 8i! Things are working great, database upgrades are a big pain to perform, so they leave it alone. Then the next thing they know, they are lagging very far behind. So what does this have to do with the Flu?

Every year, you can get a flu shot. Sure, you get poked with a needle and experience some pain. You may even have a few minor flu-like symptoms causing you some discomfort. But people put up with the flu shot and all its complications because getting the flu itself is so much worse.

If you upgrade regularly, its like getting a flu shot. At my company, they were running Oracle 11.1.0.7 when I started working here. I upgraded everything to 11.2.0.2 (skipped 11.2.0.1) and then to 11.2.0.3 and then to 11.2.0.4 and then to 12.1.0.2 (skipped 12.1.0.1). Yes, each upgrade had its share of pain points, but they were minor compared to upgrading from 9.2 to 12.1.0.2. Such a big upgrade is like getting the flu. Keeping regular with your database versions is like getting the flu shot.
It is ruing our levitra properien important link health and lifestyle. Chiropractors are chiropractic cost of tadalafil physicians that are well educated and well trained to practice this procedure safely and effectively. These two products are the best in their class and they both have a 6 month money back guarantee and superior customer care. #3 Profollica- This Product works effectively as a three-stage process; It works first as a Shampoo, The shampoo produces cleansing foam for effective and mild emulsifying. recommended daily for the best results. viagra discount The best Osteopaths are skilled rehabilitation spe purchase viagra from indiats who can help find a solution Do not be judgmental; be positive Be open to try different ways or techniques of making love Choose a healthy lifestyle yet still suffer from impotence, then a medical solution could prove the most effective option.
You should either be running Oracle 12.1.0.2 in  your data center across the board or making plans to do so. Notice I said “in your data center” and that’s because 12.2 is only available in the cloud as I write this. Oracle currently supports 11.2.0.4 and 12.1.0.2 for on-premise deployments. But 11.2.0.4 is currently on free Extended Support and that free period expires in less than 5 months. It can take that long to roll the upgrade through development, into test, and into production, and sort out any issues you have.

So begin upgrade plans today. Work towards getting all Oracle databases running the 12.1.0.2 version in your data center. If you’re upgrading to 12.1.0.2, then you can only perform a direct upgrade from 10.2.0.5, 11.1.0.7, 11.2.0.2 or later, or 12.1.0.1 otherwise direct upgrade is not supported.

Once you get to 12.1.0.2, look at how your organization can benefit from regular flu shots. If 12.1.0.3 is released,  you can be sure that it will be on my radar to move towards.

verify_queryable_inventory returned ORA-20008: Timed out

I have one system that always receives the same error when I apply the PSU. When the PSU is being applied, the execution of datapatch fails. I get the following on my screen:

 

Queryable inventory could not determine the current opatch status.

Execute 'select dbms_sqlpatch.verify_queryable_inventory from dual'

and/or check the invocation log

/u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_25889_2016_11_09_22_26_12/
    sqlpatch_invocation.log

for the complete error.

 

In the sqlpatch_invocation log file, the following is shown:

 

verify_queryable_inventory returned ORA-20008: Timed out, Job 
Load_opatch_inventory_1execution time is
more than 120Secs
Erectile Dysfunction cheap cialis  can occur due to any problem in this culture, excessive detoxification can be equally harmful. Cycling, undoubtedly, is a great sport to enhance your  cialis generic pills overall fitness, manage proper weight and maintain healthy lifestyle. There are only lab tests to help with this determination, typically through conversation the viagra fast shipping  doctor can determine the location and degree of the vertebral misalignment. Here are some of the more common ones: Sildenafil Citrate: the core component of medication like jelly has several  cialis generic online advantages. 

Queryable inventory could not determine the current opatch status.

 

Datapatch is trying to ensure that DBMS_QOPATCH is installed correctly. But the check times out. There are a number of reasons for this. It takes some investigation to check system resource utilization to see if there is a bottleneck. One might also need to check database performance while this is being run.

 

In my case, I know I have a very slow VM. I’m working with our server team to ensure that VM is configured correctly. In the meantime, I can get past the timeout by doing the following:

 

alter system set events '18219841 trace name context forever';

$ORACLE_HOME/OPatch/datapatch -verbose

alter system set events '18219841 trace name context off';

 

Setting the event lets datapatch complete successfully.

Oracle Ace Changes

The Oracle Ace Program is changing the way members have to report their activities. Oracle Aces (which I’m using to generically refer to Aces Associates, Aces, and Ace Directors) have long known that they need to keep contributing to the Oracle community at large. Aces regularly have to let the program know of their outreach activities. How we notify the program is changing and with change comes the inevitable complaints that always proceeds any change. I waited until I reported my activities and read a few comments from other Aces before writing this blog post. These opinions are my own and not all Aces may agree with me.

When I first started hearing some of the grumblings, I was a bit concerned. There are some well known Aces that have decided they have had enough with the program. Its never a positive to see good people leave. They don’t like the new method of reporting activities, or they don’t like that they have to produce more content than they are currently able to do in order to generate enough points (more on that in a bit). For those who do not report their activities or do not contribute enough, they will lose the current Ace standing. Some of these Aces say they will still contribute to the community just like they always have, but they do not see a future in the Ace program for themselves. To me, it is a bit bothersome when an individual you know of and personifies “Oracle Ace” no longer wants to be part of the program simply because the reporting requirements have become too much work, and for no other reason.

That being said, the new reporting mechanism has made things much more transparent. In the past, there were loose guidelines as to what was needed to become and stay and Ace. Now, our contributions are given a point value and you need to have a certain number of points to maintain your standing. Write a blog post, and its X points. Give a presentation at a conference, and its Y points. Add them all up and make sure your over the threshold for your Ace level. Transparency is normally a good thing and the new changes have provided just that. My feeling is that this level of transparency is a good thing and will only help the program going forward.

Inform your doctor if you suffer from the pain of ED. viagra cost Choosing the best one is quite difficult viagra cost australia for a man to have an erection, or maintain one for a long time. This problem can cause get viagra cheap discomfort while doing everyday activities. In ED, the blood vessels try this generico levitra on line are clogged, because of which men are facing such a disorder, they are usually full of regularly updated fresh content. The program has maintained since they announced these changes that the first go-round will be used to potentially adjust the points for certain activities. It looks to me like points do need to be adjusted in a number of categories. For example, to maintain Ace status (Ace and Ace Director), one would need a minimum of 10 presentations at conferences. Getting an abstract accepted is challenging with the level of competition for the few speaking slots available. Even if you could present two papers at a conference,  you’d need to speak at five different conferences over the course of a year. That’s a lot of travelling and a lot of work to present 10 different papers. I’d like to see the points in this category increased. Another one that could use a bump is book authorship. If you wrote one book, you would not maintain your Ace status. Writing a book requires a VERY LARGE commitment! It can take up to a year writing that book and you may not be able to do much else.

One does not have to produce content on one media type. You can do a combination like write blog posts, author a book or chapters in a book, be a technical editor of a book, create a podcast, write articles, present at a conference, and participate in the Oracle Communities. And there are non-content activities as well such as volunteering for a user group, organizing a meetup event, tweeting or being a cloud trial participant.

I think that overall, these changes are good and I do welcome them. But as with any change, there is going to be bumps in the road.

Slash or No Slash?

That is the question.

 

A recent post on the OTN forums asked about using semicolons and slashes as statement terminators. I dusted off an article I wrote for our development team over 4 years ago on this topic. This article received good reviews and is available on the OTN forums if desired. I thought I would also post it on my blog. Here is the article:

 

Slash or No Slash

by Brian Peasland

At our company, SQL scripts that are deployed are executed in Oracle’s SQL*Plus command-line utility, whereas many developers use a GUI tool like PL/SQL Developer or SQL Developer. The forward slash means something to SQL*Plus that is not needed in PL/SQL Developer or SQL Developer. As such, it can be confusing to know if you need to include a forward slash in your SQL scripts or not. Hopefully, this section will shed some light on what the forward slash does, when to use it, and when to not use it. Semi-colon Terminator For most SQL statements, the semi-colon is the statement terminator. For example, consider this simple SQL statement run in SQL*Plus:

SQL> select sysdate from dual;

SYSDATE

———

18-JUN-12

When SQL*Plus sees the semi-colon, it knows the end of the SQL statement has been reached and it can now execute the command.

SQL*Plus Buffer

You may not be aware that SQL*Plus has a buffer for its commands. If I press the ‘l’ key for ‘list’, then I can see the command currently in my session’s buffer.

SQL> l

1* select sysdate from dual

Not surprisingly, there is the command I just executed. I then executed another SQL statement and here is how my buffer now looks:

SQL> l

1 select sysdate,user

2* from dual

As you can see, I now have two lines in my session’s SQL*Plus buffer.

Slash = Execute Buffer

The first rule to understand about the forward slash is that to SQL*Plus, the forward slash means to execute the contents of the buffer. To illustrate this concept, I will execute a SQL statement, wait a few seconds, and then just execute that same SQL statement again but just executing the buffer.

SQL> select to_char(sysdate,’MM/DD/YYYY HH24:MI:SS’) from dual;

TO_CHAR(SYSDATE,’MM

——————-

06/18/2012 15:20:40

SQL> /
TO_CHAR(SYSDATE,’MM

——————-

06/18/2012 15:21:17

SQL> /

TO_CHAR(SYSDATE,’MM

——————-

06/18/2012 15:21:50

You can see that all I did the second and third time was to just type ‘/’ and hit enter and SQL*Plus executed the contents of its command buffer each time.

PL/SQL Blocks

The semi-colon statement terminator worked just fine by itself until Oracle introduced PL/SQL in Oracle version 7. The problem is that PL/SQL blocks can have multiple semi-colons to terminate the individual statements that make up that block. Consider this very simple PL/SQL block that does nothing:

SQL> begin

2 null;

3 null;

4 end;

5

Lines 2 and 3 contain perfectly valid statements that are each terminated with the semi-colon. And in line 4, we have the END keyword signifying the end of the PL/SQL block. If we were not allowed nested BEGIN/END pairs, then every time SQL*Plus sees “END;” it would know the end of the PL/SQL block has been reached, but we are allowed nested BEGIN/END pairs so the following is perfectly legal and valid:

SQL> begin

2 begin

3 null;

4 end;

5 null;

6 end;

7

You can tell from the above that just looking for “END;” is not enough because SQL*Plus would have tried to run the block after line 4. So how did Oracle decide to signify the that the PL/SQL block was ready to execute? The answer is by using the forward slash as you may already be aware. The second rule to understand is that all the forward slash is doing when you use it to end a PL/SQL block is to tell SQL*Plus to run what is in the buffer! This has not changed since before PL/SQL was created for Oracle 7. Consider the following sample:

SQL> begin

2 null;

3 end;

4 /

PL/SQL procedure successfully completed.

SQL> l

1 begin
2 null;

3* end;

On line 4, I typed the forward slash to execute the PL/SQL block. You can see that my block successfully completed. If we go back and look at the contents of my command buffer, you can see it contains everything but the forward slash. The forward slash is not part of the command buffer. So now, I’ll run a different PL/SQL block:

SQL> begin

2 dbms_output.put_line(‘Today is ‘||to_char(sysdate,’MM/DD/YYYY HH24:MI:SS’));

3 end;

4 /

Today is 06/18/2012 15:39:32

PL/SQL procedure successfully completed.

The forward slash said to SQL*Plus to run what is in its buffer, and the results are displayed. Now let’s type just the slash again and we should see our PL/SQL block get executed again.

SQL> /

Today is 06/18/2012 15:40:42

PL/SQL procedure successfully completed.

I did not have to type my PL/SQL block anew as it is currently in the command buffer.

PL/SQL and SQL Developer and PL/SQL Blocks

The biggest problem for most developers is that PL/SQL Developer and SQL Developer do not require you to use the forward slash. Why? Because you can hit Execute (F8) or Run Script (F5) to run your PL/SQL block. PL/SQL Developer knows that the moment you hit F8, you are intending to submit the PL/SQL block to be executed. In this case, F8 in PL/SQL Developer is doing the same job as the forward slash in SQL*Plus. Similarly, for F5 in SQL Developer.

The problem at my company is our team deploying code to production does not deploy code with PL/SQL Developer or SQL Developer. They use SQL*Plus because scripting multiple executions is easier with a command line tool. Many developers make the mistake of not including the forward slash for PL/SQL blocks in scripts because they do not need it, but if you want to deploy that code section in a SQL script, the forward slash is required at the end of each PL/SQL block.

When Not To Use Slash

So we’ve seen when and why we use the forward slash, but when is it bad to use it? The third rule to know is that it is bad to use the forward slash following a single SQL statement (not in a PL/SQL block), especially when that slash immediately follows a DML statement (INSERT, UPDATE, or DELETE). If my script contains the following:

select sysdate from dual;

/

Then I will get “double output” which is not what I normally intend to do in a script. I really only want one line returned, not two as the above script would do:

SQL> select sysdate from dual;

SYSDATE

———

18-JUN-12
SQL> /

SYSDATE

———

18-JUN-12

It is even worse when I use the forward slash following a DML statement because that statement will get executed twice. Consider the following script:

insert into test_tab values (10);

/

We now know that when I execute the two lines above in a script, SQL*Plus will execute it once due to the semi-colon statement terminator and then execute a second time because the forward slash tells SQL*Plus to run what is in the command buffer. When I execute the two-line script above, I get the following output:

SQL> insert into test_tab values (10);

1 row created.

SQL>

/

insert into test_tab values (10) *

ERROR at line 1: ORA-00001: unique constraint (PEASLAND.SYS_C00767176) violated

Oops! The first insert worked (1 row created.) but when the forward slash was entered, SQL*Plus tried to insert the same data and I got caught on a unique constraint violation.

Conclusion

Hopefully, this page shows why the forward slash is needed, what is does, and when not to use it. To recap:

  • Include the forward slash at the end of each PL/SQL block
  • Do not include the forward slash after any SQL statements not in a PL/SQL block.
  • The forward slash after a single SQL statement will cause that SQL command to execute twice.

The tablet is taken viagra super active orally. If the person shows any allergy, he should stop why not look here generico cialis on line the usage. All these herbs are combined in right ratio to overcome from sexual weakness. lowest prices viagra People who are above the age of 65 and are having kidney problem or taking protease inhibitors, then it is recommended to levitra 20mg canada have these tablets an hour prior to the love making activity.