SQL and It's Environment Setup : Setting up Oracle Database Server with SQL Developer

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. Oracle PL/SQL is designed to extend and integrate with Oracle SQL. While SQL itself is powerful and can accomplish many tasks, it is a set-oriented language designed to obtain and manipulate data in a relational database system (RDBMS). Even though Oracle SQL includes additional functionality unavailable in ANSI standard SQL, it lacks the features available in procedural languages. A procedural language provides the ability to assign values to variables, execute statements in sequence, iterate, and conditionally process commands. Hence, PL/SQL (or Procedural Language extensions to the Structured Query Language) was designed by Oracle to provide such features within the database. PL/SQL was specifically designed for to interact closely with SQL within the Oracle database.

SQL Developer and SQL*Plus are client programs provided by Oracle.  SQL*Plus is an interactive tool that allows you to type SQL and PL/SQL statements at the command prompt. These commands are then sent to the database for processing. Once the statements are processed, the results are sent back and displayed on screen. To run PL/SQL programs, we should have the Oracle RDBMS Server installed in our machine. For practicing the SQL queries we are going to use the free Oracle Express Edition database. The most recent version of Oracle RDBMS is 11g XE. The Oracle 11g XE is available for downlaod at Oracle website, but before download, you need to register yourself on their website. For registration goto the https://www.oracle.com/index.html and click on the 'Sign in' option then 'Create an Account' option.


Than fill the details and submit it. After Registration it will send you a confirmation mail to your provided email, now go to your email address, open up the mail, click on the given link, then sign in with email address as your name and the password you are provided during the registration. After 'sign in' Click on ' Menu > Downloads and Trials > Database > Oracle Database '



 or just go to the this link : http://www.oracle.com/technetwork......loads/index.html . Now at this page scroll down and find 'Oracle Database 11g Express Edition'



Now click on the appropriate link depends on your systems weather it is 32 bit or 64 bit. After that in next page click on the 'Accept License Agreement's button and click on the below given links to start the download



 and when the download is completed then click on the 'Oracle SQL Developer' link



 Then download the SQL Developer, I downloaded 'Windows 64-bit with JDK 8 included' version.



Note :  If you are downloading the 'Windows 32-bit/64bit' version, the second one then first you need to download and install the JDK (Java Development Kit). To download it just click on then 'JDK 8' link



Then on the next page click on the 'Accept License Agreement's button and download the 'Windows x86' version.



Now we have downloaded the Oracle 11g XE database server and sql developer.  Now first extract Oracle 11g XE zip file.



After extraction open the extracted file and click on DISK1 folder then click on the 'setup.exe' file.



Click on 'yes' on the popup message then wait for sometime to finish the process. After that click on 'next'



Click on accept the license agreement and click next.



Click next again



Then put your database password for oracle database. in my case i put : 1234 . then click next



Then click install and wait for the installation process to be finished, it will take some time.



And at the end of the installation process just click on the 'Finish' button



Now the Oracle 11g XE database is successfully installed on the system. After that go to search box and type 'start database' , then click on it. This will start the database.



A command prompt will appear when you click on it. This will start the database.



Close the command prompt. Now lets create a new Database user to run queries, for this click on the 'Get Started With Oracle Database 11g Ex. Ed.' icon on the Desktop.


Or simply open this url on your browser : http://127.0.0.1:8080/apex/f?p=4950 and you can see a webpage will open, then click on the 'Application Express' tab




Now at the login tab, put username as 'SYSTEM' and at the password field put the password which you provide in database installation process. which is in my case 1234



And when you logged in then at next page fill the information to create a new user account. In my case i put

username bill
Database username bill joy
Password 1234
Confirm Password 1234




After that click on 'Create Workspace' button, and when account is created then go to 'Application tab' and 'Log in' button.



Now at the 'login' page put username at Workspace, and at username put Database Username, put password and click on to 'login'.








Now click on 'SQL Workshop', and then 'SQL Commands', Now at the input box type
 SELECT 'hello world, This is my first sql query.!!' FROM dual;
And click on run button.



And at the bottom windows you can see your query will be successfully executed.



Now this is the method to run sql queries through Oracle db web control panel, we can also run queries through command line with SQL Command Line tool. For using this type 'SQL Command Line' on search box and click on the program.




 Now at the 'SQL Command Line' prompt type connect and then give your username and password of account which you have created at above steps, and press enter, Note : when you type your password at password field, then the password is not visible for security concerns.





Now you are connected with the database. We can run the above query on it.



And our query will be successfully executed. But when we type the larger queries in the command prompt then it will little bit intimidating. So, for this and IDE called SQL Developer, which we have downloaded in previous steps. Oracle SQL Developer is an Integrated development environment for working with SQL in Oracle databases. Oracle Corporation provides this product free of cost. To install SQL Developer extract the 'Sql Developer' zip file



After that open up the extracted folder and run the sqldeveloper application file.



 Now at the Left side of the window, click on the green '+' sign.



At the 'Database Connection' window put the 'Connection name' which could be anything, then username and password for your account. After that click on Connect button.



And now its connected to the database. We can test it by running the query.



To run the query, press the button in circle at above picture, or you can run it by pressing F5 button. So that's the process of setting up SQL and its Development environment.


Next Topic :

No comments:

Post a Comment