Oracle DBMS
Reference
Oracle Database XE [https://www.oracle.com/database/technologies/appdev/xe.html]
Free Oracle Database for Everyone
SQL Fiddle [http://sqlfiddle.com/]
A tool for easy online testing and sharing of database problems and their solutions
Oracle Database XE
After installation, connect to SID 'XE' w/ user 'system' and the chosen pass (eg: 'sa').
Then create a new user, eg:
alter session set "_ORACLE_SCRIPT"=true;
create user MYUSER identified by MYUSER;
grant all privileges to MYUSER;
And finally connect w/ the new one.
Windows services
Started by default after installation:
OracleOraDB21Home1MTSRecoveryService
OracleOraDB21Home1TNSListener
OracleServiceXE
PL/SQL Developer
Asking for variable values at runtime
SELECT
'&myparam1' AS "myString"
, &myparam2 AS "myNumber"
FROM DUAL
Does current SQL session have uncommited changes?
SELECT dbms_transaction.local_transaction_id FROM dual;
Stored procedure creation
Procedure w/ a custom type as input parameter
CREATE OR REPLACE TYPE external_person AS OBJECT (
name VARCHAR2(30),
phone VARCHAR2(20)
);
create or replace procedure proc1 (pEP IN external_person) AS
begin
dbms_output.put_line(pEP.name);
end;
Procedure w/ a collection of a custom type as input parameter
CREATE OR REPLACE PROCEDURE proc2col (pEPCol IN external_person_col) AS
BEGIN
FOR i in 1 .. pEPCol.count LOOP
DBMS_OUTPUT.PUT_LINE( pEPCol(i).name || ' ' || pEPCol(i).phone);
END LOOP;
DBMS_OUTPUT.PUT_LINE( '--------');
END;
Stored procedure invocation
W/ input and output parameters
--Sample academic record: 1273913 (extinction curriculum) o 1241843 (web)
declare
pCertificateCode varchar2(6) :='unused';
pLang varchar2(3) := 'CAS';
pPosition varchar2(6) :='unused';
pParam1 varchar2(10) := ';1241843;';
pParam2 varchar2(6) :='unused';
pParam3 varchar2(6) :='unused';
pInOut varchar2(32767) := 'unused';
pRet number;
begin
pRet:=DOC_FUNCIONES.AssignaturesTransferides(pCertificateCode, pLang, pPosition
, pParam1, pParam2, pParam3, pInOut);
dbms_output.put_line('pInOut='||pInOut||'; Ret='||pRet);
end;
/
W/ a custom type input parameter
declare
pEP external_person := external_person ('Ewa', '+34111333777');
begin
proc1(pEP);
end;
W/ a collection of a custom type as input parameter
DECLARE
pEP1 external_person := external_person ('Ewa', '+34111333777');
pEP2 external_person := external_person ('Nadia', '+35211333772');
epCol external_person_col := external_person_col(pEP1, pEP2);
BEGIN
proc2col(epCol);
END;
Viewing All Tables
Tip: User cat
select * from dba_tables
select * from dba_tables where table_name = 'MY_TABLE'
select * from dba_tables where owner='MY_SCHEMA'
Viewing All Views
Tip: User cat
select * from dba_views
select * from dba_views where view_name = 'MY_VIEW'
Oracle SQL
Insert if it doesn't exist
insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(config_env,config_env_pk) */
into myschema.config_env (env, clau, valor)
values ('DEV', 'MyClau', 'c:\\archiv~1\\intern~1\\iexplore.exe');
Upsert
All credits of this idea to user MT0 at https://stackoverflow.com/a/50253033/1323562
Using "SELECT ... FROM DUAL" for generating a single row:
merge into student dst
using (
SELECT 123 AS sid,
'Rahul' AS name,
'Temp' AS major
FROM DUAL
) src
on (src.major=dst.major)
when matched then
update set name=src.name
when not matched then
insert(SID,name,major) values ( src.sid, src.name, src.major )
Custom type
CREATE TYPE external_person AS OBJECT (
name VARCHAR2(30),
phone VARCHAR2(20)
);
CREATE TABLE contacts (
person external_person,
day DATE
);
INSERT INTO contacts VALUES (
external_person (NULL, NULL),
'24 Jun 2022'
);
INSERT INTO contacts VALUES (
external_person ('John', '+48111333777'),
'25 Jul 2023'
);
select c.person.name, c.person.phone, c.day from contacts c
Collection of a custom type
CREATE OR REPLACE TYPE external_person_col AS TABLE OF external_person;
Oracle JDBC driver
DBMS version (jdbc)
Query:
SELECT * FROM v$version WHERE banner LIKE 'Oracle%';
Sample responses:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Driver (jdbc)
The driver to use depends on versions of JDK and Oracle database. See:
https://www.oracle.com/database/technologies/faq-jdbc.html [What are the Oracle JDBC releases Vs JDK versions?]
-----------------------------------------------------------------
Oracle Database version JDBC Jar files specific to the release
-----------------------------------------------------------------
23.x ojdbc11.jar with JDK11, JDK17 and JDK21
ojdbc8.jar with JDK8 and JDK11
-----------------------------------------------------------------
21.x ojdbc11.jar with JDK11 and JDK17
ojdbc8.jar with JDK8 and JDK11
-----------------------------------------------------------------
19.x ojdbc10.jar with JDK11 and JDK17
ojdbc8.jar with JDK8, JDK11 and JDK17
-----------------------------------------------------------------
18.x ojdbc8.jar with JDK8 and JDK11
-----------------------------------------------------------------
12.2 or 12cR2 ojdbc8.jar with JDK 8
-----------------------------------------------------------------
12.1 or 12cR1 ojdbc7.jar with JDK 7 and JDK 8
ojdbc6.jar with JDK 6
-----------------------------------------------------------------
11.2 or 11gR2 ojdbc6.jar with JDK 6, JDK 7, and JDK 8
(JDK7 and JDK8 are supported in 11.2.0.3 and 11.2.0.4 only)
ojdbc5.jar with JDK 5
-----------------------------------------------------------------
Connection string (jdbc)
The format of the JDBC connection string for Oracle Database (non-RAC) is:
jdbc:oracle:thin:@[host]:[port]:[sid]
Example:
jdbc:oracle:thin:@meow-test.cou.edu:1521:cat
Reference:
Spring Boot (jdbc)
Oracle Connection Pooling With Spring
https://www.baeldung.com/spring-oracle-connection-pooling
Note that the framework defines the version of the dependencies (may be replaced if needed), eg for Spring Boot 2.7.0
<oracle-database.version>21.5.0.0</oracle-database.version>
pom.xml
Note: In SpringBoot, the recommeded connection pool is hikari. Oracle connection pool dependencies (ucp & ons) aren't needed at all unless you need to use advanced configurations.
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!-- oracledb >= 11.2: jdbc driver compatible with jdk 11-15 -->
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc11</artifactId>
</dependency>
<!-- oracledb >= 11.2: Oracle Universal Connection Pool (UCP). Requires 'ons' -->
<!--
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ucp</artifactId>
</dependency>
-->
<!-- oracledb >= 11.2: Oracle Notification System (ONS) library for FAN events. Required by 'ucp' -->
<!--
<dependency>
<groupId>com.oracle.database.ha</groupId>
<artifactId>ons</artifactId>
</dependency>
-->
application.properties
Normally you do not need to specify the dialect, but if the application does not start due to connectivity problems with the database, the following can be configured:
# As of Hibernate 6.0, use OracleDialect and ignore deprecated subclasses
spring.jpa.database-platform=org.hibernate.dialect.OracleDialect
# W/ Hibernate <6.0, use a dialect suitable for the DBMS version
# spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect
Reference > https://docs.jboss.org/hibernate/orm/6.0/migration-guide/migration-guide.html
Spring
Consume stored procedure (Spring)
academic-applicationContext-dao.xml
<!-- You can use Hibernate for 90% to 95% or so of database access.
The JdbcTemplate is for when you can't use Hibernate.
Don't use JDBC directly! Use the JdbcTemplate as it handles JDBC cleanup.-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="myDataSource" />
</bean>
<!-- Dao beans -->
<bean id="academicDao" class="edu.cou.services.cat.academic.dao.hibernate.AcademicDAOImpl">
<property name="sessionFactory" ref="sessionFactory"/>
<property name="jdbcTemplate" ref="jdbcTemplate"/>
</bean>
AcademicDAOImpl.java
public class AcademicDAOImpl extends HibernateDaoSupport implements ExpedientDAO {
/**
* Bean jdbcTemplate.
*/
private JdbcTemplate jdbcTemplate;
/**
* It gets el bean de la jdbc template.
*
* @return the jdbcTemplate.
*/
public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
/**
* Assigna el bean de la jdbc template.
*
* @param jdbcTemplate the jdbcTemplate to set.
*/
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
/*-Override*/
public List<ElemTransferidoVO> getAssignaturesTransferidesByExpedient(Long academicRecordCode,
String langUocCode) throws IOException {
final String UNUSED = "unused";
final String pInOut = "pInOut";
class AssignaturesTransferidesSP extends StoredProcedure {
public AssignaturesTransferidesSP() {
setJdbcTemplate(jdbcTemplate);
setFunction(true);
setSql("DOC_FUNCIONES.AssignaturesTransferides");
// Procedure return (must be declared before parameters)
declareParameter(new SqlOutParameter("pRet", Types.INTEGER));
// Procedure parameters, in the same order as in the DB stored procedure
declareParameter(new SqlParameter("pCertificateCode", Types.VARCHAR));
declareParameter(new SqlParameter("pLang", Types.VARCHAR));
declareParameter(new SqlParameter("pPositionCode", Types.VARCHAR));
declareParameter(new SqlParameter("pParam1", Types.VARCHAR));
declareParameter(new SqlParameter("pParam2", Types.VARCHAR));
declareParameter(new SqlParameter("pParam3", Types.VARCHAR));
declareParameter(new SqlInOutParameter(pInOut, Types.VARCHAR));
//
compile();
}
}
AssignaturesTransferidesSP stored = new AssignaturesTransferidesSP();
HashMap<String, Object> mapIn = new HashMap<String, Object>();
mapIn.put("pCertificateCode", UNUSED);
mapIn.put("pLang", langUocCode);
mapIn.put("pPositionCode", UNUSED);
mapIn.put("pParam1", String.format(";%d;", academicRecordCode));
mapIn.put("pParam2", UNUSED);
mapIn.put("pParam3", UNUSED);
mapIn.put(pInOut, UNUSED);
if (log.isLoggable(Level.INFO)) {
log.info("INI AssignaturesTransferidesSP mapIn:\n" + mapIn.toString());
}
@SuppressWarnings("unchecked")
Map<String, Object> mapOut = stored.execute(mapIn);
if (log.isLoggable(Level.INFO)) {
log.info("END AssignaturesTransferidesSP mapOut:\n" + mapOut.toString());
}
Integer result = ((Integer) mapOut.get("pRet"));
if (result != 0) {
throw new IllegalStateException(
"Error invoking sotored procedure: DOC_FUNCIONES.AssignaturesTransferides. Returned: "
+ result + ". Parameters: " + mapIn.toString());
}
// Let's build result (stored procedure executed fine)
List<ElemTransferidoVO> ret = new ArrayList<ElemTransferidoVO>();
BufferedReader bufReader = new BufferedReader(new StringReader((String) mapOut.get(pInOut)));
String line;
while ((line = bufReader.readLine()) != null) {
ret.add(ElemTransferidoVO.of(line));
}
//
return ret;
} // getAssignaturesTranferidesByExpedient
}
Troubleshooting
Pagination w/ Spring Boot 3.2 and DBMS Oracle 11.2
https://stackoverflow.com/a/77851632