oracle 10g
TRANSCRIPT
![Page 1: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/1.jpg)
Oracle Databasefor Developers
Introduction to Oracle, SQL Developer, SQL, PL/SQL, …
Svetlin NakovTechnical Trainerwww.nakov.comSoftware Universityhttp://softuni.bg
OracleDatabase
![Page 2: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/2.jpg)
2
This lesson is NOT for absolute beginners
Target audience: Familiar with databases (MySQL / SQL Server / other) Know what is table / column / relationship Know what is SQL and simple SQL commands Have basic coding skills (C# / Java / JS / other)
Target Audience
![Page 3: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/3.jpg)
Table of Contents
1. Oracle Database Overview2. Installing Oracle Database 12c3. Installing SQL Developer4. Creating DB Schemas and Tables5. SQL Language Basics
SELECT, INSERT, UPDATE, DELETE, …
6. PL/SQL: Procedures, Functions, Triggers7. Accessing Oracle from C# and Java
3
![Page 4: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/4.jpg)
Oracle DatabaseOverview
![Page 5: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/5.jpg)
5
Oracle Database World's leader in enterprise database systems Powers big organizations, e.g. the financial sector Designed for very large databases (exabytes of data) Supports everything from the DB world
Transactions, stored procedures, big data, cloud, …
Very expensive, for big players only Has a free Expression Edition – Oracle 11g XE
What is Oracle Database?
![Page 6: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/6.jpg)
Installing Oracle 12c / 11g XE
![Page 7: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/7.jpg)
7
Installing Oracle 12c
Typically, Oracle 12c DB Enterprise takes~ 10GB HDD space
![Page 8: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/8.jpg)
8
Installing Oracle 12c – Use a Built-In User
Developers may use "Windows Built-in Account"
for simplified setup
![Page 9: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/9.jpg)
9
Installing Oracle 12c – Location & Password
Select a directory without spaces,
e.g. C:\oracle
Don't select "Container database". It is too complex!
![Page 10: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/10.jpg)
10
Installing Oracle 12c – Be Patient
Usually it takes 20-30 minutes
![Page 11: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/11.jpg)
11
Oracle DB Express Edition (XE) Free, limited, lightweight version 1 CPU, 1 GB RAM, 11 GB storage Only 11g (no 12c version)
Installing Oracle Database XE Download Oracle Database XE 11g Install it in a folder without spaces, e.g. C:\Progra~1\Oracle Remember the admin password (for the users SYS and SYSTEM)
Alternative: Oracle Express Edition (XE)
Use Oracle XE for slower computers
![Page 12: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/12.jpg)
12
OracleServiceORCL / OracleServiceXE The Oracle database engine for the "ORCL" / "XE" instance (SID) The core of the Oracle database
OracleOraDB12Home1TNSListener / OracleXETNSListener Connects Oracle database with client applications (TNS service)
Listens on TCP port 1521 (TNS listener)
Enterprise Manager (EM) console – https://localhost:5500/em Application Express Web interface (APEX) – http://localhost:8080
Oracle Database Services and Ports
![Page 13: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/13.jpg)
Install Oracle 12c DatabaseLive Exercise in Class (Lab)
![Page 14: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/14.jpg)
Oracle SQL DeveloperInstalling and Using SQL Developer
![Page 15: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/15.jpg)
15
Oracle SQL Developer is free GUI tool for managing Oracle databases: SQL queries, edit table data, edit DB schema, write code, debug, …
Oracle SQL Developer
Oracle 12c Enterprise comes with pre-installed SQL Developer 3.2(old version, not recommended)
![Page 16: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/16.jpg)
16
1. Download Oracle SQL Developer 4.1 from http://oracle.com/technetwork/developer-tools/sql-developer
2. Extract the ZIP archive in some folder, e.g. C:\oracle\sqldeveloper
3. Run sqldeveloper.exe4. Choose your JDK location
5. Create a start menu shortcut
6. Enjoy
Installing Oracle SQL Developer
![Page 17: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/17.jpg)
17
Connecting to Oracle with SQL Developer
Use "ORCL" or "XE" as SID (System
ID)
![Page 18: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/18.jpg)
18
Executing SQL Commands
![Page 19: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/19.jpg)
19
User SYS Holds the system schema SYS and data dictionary (DB metadata) Has a DBA role
Includes most database system privileges, e.g. "create user" Has a SYSDBA privilege – can start / stop / create / recover DB
User SYSTEM Has a DBA role – can administer the DB, e.g. create / drop users No SYSDBA privilege
Users SYS and SYSTEM in Oracle DB
![Page 20: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/20.jpg)
20
Unlocking the "HR" User (Schema)
![Page 21: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/21.jpg)
21
Connecting with the "HR" User
![Page 22: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/22.jpg)
22
View / Edit Data in the "HR" Shema
![Page 23: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/23.jpg)
Install Oracle SQL Developerand Access the "HR" Schema
Live Exercise in Class (Lab)
![Page 24: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/24.jpg)
Creating DB Schemas and TablesCreating Users, Tables, Relationships, Etc.
![Page 25: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/25.jpg)
25
Oracle runs single database with multiple users MS SQL Server and MySQL have many databases "User (schema) in Oracle" == "Database" in MSSQL and MySQL
Creating a new user (schema) and give typical privileges:
Creating a New User (Schema) in Oracle
CREATE USER maria IDENTIFIED BY "password123";
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TRIGGER, UNLIMITED TABLESPACE TO maria;
![Page 26: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/26.jpg)
26
Users in Oracle may have certain privileges CREATE SESSION – allows the users to connect to DB CREATE TABLE / CREATE VIEW / CREATE PROCEDURE / … UNLIMITED TABLESPACE – unlimited storage quota (0 by default) SYSDBA – start / stop / edit / backup the entire database
Users in Oracle may have certain roles DBA – database administrator (full DB access) CONNECT + RESOURCE – login + create / edit tables and DB objects
User Privileges and Roles in Oracle
![Page 27: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/27.jpg)
27
Creating a New User in SQL Developer
Granting "DBA" role is easy but might be
a security risk
![Page 28: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/28.jpg)
28
Creating Tables in SQL Developer: COUNTRIES
![Page 29: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/29.jpg)
29
NUMBER(precision, scale) – a number, e.g. 12345 precision – total number of digits scale – number of digits to the right of the decimal point
VARCHAR2(length) – sequence of characters (up to 4000) NVARCHAR2(length) – sequence of Unicode characters DATE – date and time, e.g. "18-June-2015 20:30:07" BLOB – binary large object (e.g. PDF document or JPEG image) CLOB – character large object (e.g. HTML document)
Oracle Data Types
![Page 30: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/30.jpg)
30
Oracle database has some specifics One database with many users (schemas)
Each user has its own schema (tables and other DB objects) Use UPPERCASE for all identifiers
Otherwise you should use the quoted syntax, e.g. "My Table" No auto-increment columns until version 12c
Use a SEQUENCE + TRIGGER for auto-increment in Oracle 11g / 10g In Oracle NULL is the same like "" (empty string)
This causes many problems!
Beware: Oracle has Specifics!
![Page 31: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/31.jpg)
31
Editing Table Data in SQL Developer
![Page 32: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/32.jpg)
32
Creating Tables in SQL Developer: TOWNS
![Page 33: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/33.jpg)
33
Creating Relationships in SQL Developer
First save the "TOWNS" table, then edit it to add
the foreign key constraint
![Page 34: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/34.jpg)
34
View E/R Diagram
![Page 35: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/35.jpg)
35
Foreign Key Constraint Violation
![Page 36: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/36.jpg)
36
Creating E/R Diagram with Data Modeler
![Page 37: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/37.jpg)
Creating DB Schema "Maria" Holding Countries and Towns (One to Many)
Live Exercise in Class (Lab)
![Page 38: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/38.jpg)
SQL Language – BasicsBasics SQL Commands: SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP
SELECT *FROM COUNTRIESWHERE COUNTRY_ID = 'UK'
SELECT c.COUNTRY_NAME AS COUNTRY, r.REGION_NAME AS REGIONFROM COUNTRIES c JOIN REGIONS r ON c.REGION_ID = r.REGION_ID
![Page 39: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/39.jpg)
39
SELECT WHERE (filtering) JOIN (joining tables) GROUP BY (grouping)
INSERT UPDATE DELETE
SQL Language
SELECT d.DEPARTMENT_NAME AS DEPT, COUNT(e.EMPLOYEE_ID) AS EMP_COUNTFROM DEPARTMENTS d JOIN EMPLOYEES e ON e.DEPARTMENT_ID = d.DEPARTMENT_IDGROUP BY d.DEPARTMENT_ID, d.DEPARTMENT_NAMEHAVING COUNT(e.EMPLOYEE_ID) >= 5ORDER BY EMP_COUNT DESC
![Page 40: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/40.jpg)
40
In Oracle, we have the "HR" schema, coming as example The "HR" schema holds:
Employees Jobs Departments Locations (addresses) Countries
To use it, unlock the "HR" user and change its password
The HR Sample Schema
![Page 41: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/41.jpg)
41
SELECT
WHERE
ORDER BY
SQL: SELECT, WHERE, ORDER BY
SELECT * FROM COUNTRIES
SELECT COUNTRY_ID, REGION_IDFROM COUNTRIES
SELECT * FROM COUNTRIESWHERE COUNTRY_ID = 'UK'
SELECT * FROM COUNTRIESWHERE COUNTRY_ID LIKE 'C%' OR COUNTRY_NAME LIKE 'Un%'
SELECT * FROM COUNTRIESORDER BY COUNTRY_NAME
SELECT * FROM COUNTRIESORDER BY COUNTRY_ID DESCFETCH NEXT 3 ROWS ONLY
![Page 42: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/42.jpg)
42
Join COUNTRIES with REGIONS tables in SQL SELECT
SQL: Join Tables
SELECT *FROM COUNTRIES JOIN REGIONS ON COUNTRIES.REGION_ID = REGIONS.REGION_ID
SELECT c.COUNTRY_NAME AS COUNTRY, r.REGION_NAME AS REGIONFROM COUNTRIES c JOIN REGIONS r ON c.REGION_ID = r.REGION_ID
![Page 43: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/43.jpg)
43
Insert a new department
SQL: INSERT
INSERT INTO DEPARTMENTS( DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)VALUES ( DEPARTMENTS_SEQ.nextval, 'Brazil Sales', 2800 /* Sao Paulo, Brazil */)
Primary key is populated by a
SEQUENCE
![Page 44: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/44.jpg)
44
Update existing department change name + commit
Update existing employee change hire date + rollback
SQL: UPDATE
UPDATE EMPLOYEES SET HIRE_DATE = '2-Jan-2001'WHERE EXTRACT(YEAR FROM HIRE_DATE) = 2001;ROLLBACK; -- Discard (cancel) pending changes
UPDATE DEPARTMENTSSET DEPARTMENT_NAME = 'Brazil Sales and Marketing'WHERE DEPARTMENT_NAME = 'Brazil Sales'COMMIT; -- Save pending changes
![Page 45: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/45.jpg)
45
Delete existing department + commit
Delete all locations in Italy + rollback
SQL: DELETE
DELETE FROM DEPARTMENTSWHERE DEPARTMENT_ID = 320;COMMIT; -- Save pending changes
DELETE FROM LOCATIONSWHERE COUNTRY_ID = (SELECT COUNTRY_ID FROM COUNTRIES WHERE COUNTRY_NAME = 'Italy');ROLLBACK; -- Discard (cancel) pending changes
![Page 46: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/46.jpg)
SQL CommandsLive Exercise (Lab)
1. Write SQL SELECT to find all locations in towns starting with 'S'.2. Write SQL INSERT to create a new job (id 'SW_DEV', title 'Software
Developer', salary range 10000 … 50000).3. Write SQL UPDATE to change the job title 'Software Developer' to
'Software Engineer' and max salary to 75000.4. Write SQL DELETE to remove the job title 'Software Engineer'.5. Write a SQL SELECT to find all job titles and the number of
employees for each job title. Use GROUP BY JOB_ID.
More exercises: http://www.srikanthtechnologies.com/oracle/dec9/hrqueries.html
![Page 47: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/47.jpg)
The PL/SQL LanguageProgramming Logic in the Database
![Page 48: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/48.jpg)
48
PL/SQL extends the SQL language in Oracle DB Blocks (declare / begin / exception / end) Variables, types, assignments Conditional statements (if-then-else) Loops (for, while, do…while) Cursors (loops over query results) Exceptions handling Stored procedures, functions, triggers, packages
PL/SQL Overview
![Page 49: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/49.jpg)
49
PL/SQL – ExampleSET SERVEROUTPUT ONDECLARE e_id EMPLOYEES.EMPLOYEE_ID%TYPE; e_fname EMPLOYEES.FIRST_NAME%TYPE; e_lname EMPLOYEES.LAST_NAME%TYPE; CURSOR e_employees IS SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME FROM EMPLOYEES; result CLOB;BEGIN result := NULL; OPEN e_employees;
Goal of this PL/SQL program: Collect the employee data into a single string (comma separated)
![Page 50: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/50.jpg)
50
PL/SQL – Example (2) LOOP FETCH e_employees INTO e_id, e_fname, e_lname; EXIT WHEN e_employees%NOTFOUND; IF result IS NOT NULL THEN result := result || ', '; END IF; result := result || e_id || ' (' || e_fname || ' ' || e_lname || ')'; END LOOP; CLOSE e_employees; DBMS_OUTPUT.PUT_LINE(result);END;
![Page 51: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/51.jpg)
51
PL/SQL: Interchange Salaries – ExampleCREATE OR REPLACE PROCEDURE exchange_salaries( emp1_id NUMBER, emp2_id NUMBER)IS old_emp1_salary EMPLOYEES.SALARY%TYPE;BEGIN SELECT SALARY INTO old_emp1_salary FROM EMPLOYEES WHERE EMPLOYEE_ID = emp1_id; UPDATE EMPLOYEES SET SALARY = (SELECT SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID = emp2_id) WHERE employee_id = emp1_id; UPDATE EMPLOYEES SET SALARY = old_emp1_salary WHERE EMPLOYEE_ID = emp2_id; COMMIT;END; CALL exchange_salaries(204,
206);
![Page 52: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/52.jpg)
Accessing Oracle DB from JavaUsing the Oracle JDBC Driver
Connection
Statement
ResultSet
Oracle DB
setInt(…)
setDate(…)setString(…)
![Page 53: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/53.jpg)
53
Setup a Maven-Based Java Project
<project …> <build> <plugins> <plugin> <artifactId>maven-compiler-plugin</artifactId> <configuration> <source>1.8</source> <target>1.8</target> </configuration> </plugin> </plugins> </build></project>
pom.xml
Create a Maven project and specify Java 8 compilation mode(by default Maven uses Java 5)
![Page 54: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/54.jpg)
54
Reference the Oracle JDBC Drivers Library
<repositories> <!-- Repository for ORACLE ojdbc7 (unofficial) --> <repository> <id>codelds</id> <url>https://code.lds.org/nexus/content/groups/main-repo</url> </repository></repositories><dependencies> <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc7</artifactId> <version>12.1.0.2</version> </dependency></dependencies>
pom.xml
Add an unofficial Maven repository holding the Oracle JDBC drivers
Reference the latest OJDBC library (12.1 for Java 7+)
![Page 55: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/55.jpg)
55
List All Employees from the HR Schemapublic static void main(String[] args) throws Exception { Class.forName("oracle.jdbc.driver.OracleDriver"); try (Connection dbConnection = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:orcl", "hr", "hr")) { Statement stmp = dbConnection.createStatement(); ResultSet rs = stmp.executeQuery("SELECT * FROM EMPLOYEES"); while (rs.next()) { int id = rs.getInt("EMPLOYEE_ID"); String firstName = rs.getString("FIRST_NAME"); String lastName = rs.getString("LAST_NAME"); System.out.println(id + ": " + firstName + " " + lastName); } }}
![Page 56: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/56.jpg)
Accessing Oracle DB from C#Using the Oracle Data Provider for .NET
OracleConnection
OracleCommand
OracleDataReader
Oracle DB
OracleParameter
OracleParameterOracleParameter
![Page 57: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/57.jpg)
57
Reference the Oracle Data Provider from NuGet
![Page 58: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/58.jpg)
58
Configure the Data Source in App.config
<oracle.manageddataaccess.client> <version number="*"> <dataSource alias="orcl" descriptor= "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=localhost)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=ORCL))) "/> </version></oracle.manageddataaccess.client>
App.config
![Page 59: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/59.jpg)
59
List All Employees from the HR Schemausing (var dbCon = new OracleConnection( "Data Source=orcl; User Id=hr; Password=hr")){ dbCon.Open(); var cmd = new OracleCommand("SELECT * FROM EMPLOYEES", dbCon); var reader = cmd.ExecuteReader(); while (reader.Read()) { int id = (int) reader["EMPLOYEE_ID"]; string firstName = (string) reader["FIRST_NAME"]; string lastName = (string) reader["LAST_NAME"]; Console.WriteLine(id + ": " + firstName + " " + lastName); }}
![Page 60: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/60.jpg)
Accessing Oracle from Java and C#Live Exercise (Lab)
![Page 61: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/61.jpg)
61
Oracle is world's leading RDBMS Powerful, but complex
Oracle SQL Developer DB GUI tool for developers
SQL language SELECT, INSERT, UPDATE, DELETE
PL/SQL – variables, conditions, loops, cursors, … Accessing from Java – use the Oracle JDBC drivers Accessing from C# – use the .NET Data Provider for Oracle
Summary
![Page 62: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/62.jpg)
?
??
?
?
??
?
?
Questions?
Oracle Database for Developers
https://softuni.bg/courses/software-technologies
![Page 63: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/63.jpg)
License This course (slides, examples, demos, videos, homework, etc.)
is licensed under the "Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International" license
63
![Page 64: Oracle 10g](https://reader033.vdocuments.us/reader033/viewer/2022051007/586fdddb1a28ab18428b69c1/html5/thumbnails/64.jpg)
Free Trainings @ Software University Software University Foundation – softuni.org Software University – High-Quality Education,
Profession and Job for Software Developers softuni.bg
Software University @ Facebook facebook.com/SoftwareUniversity
Software University @ YouTube youtube.com/SoftwareUniversity
Software University Forums – forum.softuni.bg