Setting Up a Development Environment to Learn Pl/Sql

Setting up a development environment to learn PL/SQL

If you want to sandbox the whole environment, Oracle provide pre-built VM images which contain an 11g database, SQL Developer and other tools; more info here, and if you just want PL/SQL the first version listed should have everything you need.

As well as being very quick and easy to set up - no DBA knowledge required - it's completely isolated from any real environments you want to avoid interfering with, can run on any machine and O/S that runs VirtualBox, and best of all, if (or when) you really mess something up you can trash it and start again.

how to set up a databse test and development environment with data from production

There are a number of options here. But before going into them a but of background, it is bad to put production data into a test or dev environment. Typically based on the nature of test and dev environments there are looser restrictions on who can access them. Therefore the security of your production environment becomes meaningless when you have the same data in a dev / test environment without the same level of security.

1. Replicate the structure not the data

To best achieve this you can follow the same setup / migration steps that you perform on the production system. Essentially this will give you a system with the same schema as your production database. From there you can add your own test data.

2. Replicate your production database using data masking

Some companies e.g. Oracle offer Data Masking technologies. This allows you to replicate your production system while masking the true values in the production database. The best Data Masking solutions will preserve the integrity of the data, for example a phone number will still look like a phone number.

3. Restore Backup of Production Database

This is the last in my list based on the security issues I outlined earlier. But this is essentially following your Disaster Recovery plan to restore the latest copy of your database (including data) into a development / test environment.

IDE for Pl/SQL development

I use SQL Developer every day to develop packages. Whilst it's not perfect, it's got some useful features:

• Syntax highlighting;
• Autocompletion;
• Debugging (although not of live requests as far as I can tell);
• Simple connection configuration (JDBC-based as well as TNSNAMES);
• ...

It's also free, unlike the (admittedly better) Toad mentioned previously.

What's a quality development environment for writing Oracle SQL?

All IDE:s for Oracle DB are more or less bad, TOAD is the best but still nothing comapared to real IDE like Visual Studio or Eclipse.

Main problem with TOAD is the default texteditor. Find is nightmare and you can't change shortcuts and auto alignment uses spaces even if you configure it to use tabs and other annoying features. Also cancelling queries or making query to disconnected session might crash it. Also the intellisense is kinda slow imo but it works which is better than what others offer.

pl/sql developer IDE

Tools -> Preferences -> user interface section -> key configuration -> scroll down to Editor : scroll up is where you would normally do it, however I see your problem, CTRL+UP is a control key in the preferences window that takes you back up to the top of that window.

If you go to the defaults.ini file on your pl/sql developer install ( probably in something like C:\Program Files\PLSQL Developer\Preferences\your username ), you can change the entry manually, I'd recommend setting the scroll up entry to someother value, then you will see an entry, you can then change it to 16422, which is the value on my install for CTRL+UP.

Quickest way to setup a local Oracle Database in local development

An easy way out - from my point of view - would be to install Virtual Box to all those computers, download one of pre-built developer virtual machines, copy them to those computers and that's it ... you have everything you wanted.

As there are "several time consuming scripts", I guess that you'd want to run them first on one VM copy; create everything you need (users, tables, load data, whatever) and THEN let other developers use it.

Benefit? Keep one copy for you (the one where everything is set up as you wanted); when someone needs a fresh start, replace their VM with the "original" one.

Such an approach (virtual machine) solves your 2nd question; everyone's their own administrator there, and it won't affect the host computer (no need for them to be administrators on the host).

By the way, answer to your 1st question is "No", that won't work.

P.S. Ah, yes - you tagged the question with "11g". I don't think that it is available on pre-built VMs. If it has to be that version, no problem - approach is the same: create your own virtual machine (for example, I use one under MS Windows XP, with Developer suite 10g on it), install anything you need (database 11g (perhaps XE version?), SQL Developer, any other tool ... ) and call it your own pre-built VM.

How to configure Oralce locale to be the same between development and integration

The environment difference between development and the integration server is the first is a Swiss(FR) setup OS. And... the server is a Linux with the default English's.

So you start to see where I have to go.

Tell Java you run your thing with 'fr' locale:

I had to complete the java command line with the below:

 -Duser.country=fr -Duser.language=fr

Here is where I found inspiration: NLS_LANG setting for JDBC thin driver? (most upvoted answer).

Create an oracle pl/sql package using a package on a different remote database

Yes, it's possible, you could created a process on your target system which uses the DBMS_METADATA package on the remote system to fetch the DDL for the desired package spec and body, and then use dynamic SQL on local system to compile the fetched code.

Alternatively, you could use tools such as Oracle's SQL Developer for migrating code. Using either the database diff functionality to detect differences and prepare the appropriate DDL scripts, or the Cart functionality to pick and choose what get's migrated. However, I'm not sure how well the SQL Developer method can be automated.

---

Related Topics