create database link и ошибка SQL Error: ORA-00933: SQL command not properly ended

Понадобилось создать линк к удалённой БД.
Смотрю пример в документации:

CREATE DATABASE LINK local 
   CONNECT TO hr IDENTIFIED BY hr
   USING 'local';

и выполняю
create database link mylink connect to schema_name identified by 123456 using 'orcl'

В результате получаю сообщение об ошибке:

SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"

Разницы от того, выполняется ли запрос из SQL Developer или из sqlplus, нет никакой.

Как оказалось, значение пароля (123456) нужно указать в двойных кавычках - да и другие параметры тоже лучше в них, т. е. вот так:

create database link "mylink" connect to "schema_name" identified by "123456" using 'orcl'


При этом orcl - именно в одинарых.

Oracle, как выбрать подмножество строк

Выбрать строки 10..20 из HR.departments

select * 
  from (select d.*, rownum rnum
          from HR.departments d
         where rownum <= 20)
where rnum >= 10

Если при этом набор данных нужно отсортировать, как чаще всего и бывает, то это выражение можно записать в виде

select * 
  from (select d.*, rownum rnum
          from HR.departments d
         where rownum <= 20
         order by d.department_name)
where rnum >= 10


но работать будет не правильно - значения rownum определяются на этапе выборки данных и при выполнении order by порядок будет утерян. 

Поэтому в таком случае можно обернуть выборку и сортировку в ещё один select:

select * 
from (
  select data.*, rownum rnum 
  from ( 
    select d.*
    from HR.departments d
    order by d.department_name ) data
  where rownum <= 20 )
where rnum >= 10

Cursor и порядок

Как (внезапно) выяснилось, порядок, в котором столбцы выбираются в курсор, имеет важное значение.

Пусть есть таблица:
CREATE TABLE A( F1 NUMBER(1), F2 FLOAT);

с такими данными:
INSERT INTO A VALUES(1, 11.1);  

INSERT INTO A VALUES(2, 22.22);

INSERT INTO A VALUES(3, 33.333);

И пусть мне захотелось сделать курсор, который бы содержал данные из этой таблицы:
DECLARE
  TYPE a_cursor_type IS REF CURSOR RETURN A%ROWTYPE;
  a_cursor a_cursor_type;
  
  a_record A%ROWTYPE;
BEGIN
  OPEN a_cursor FOR SELECT f1, f2 FROM A;
  
  LOOP
    FETCH a_cursor INTO a_record;
    EXIT WHEN a_cursor%NOTFOUND;
    
    dbms_output.put_line('F1: ' || a_record.f1 || ', F2: ' || a_record.f2);
  END LOOP;
END;

В выражении SELECT f1, f2 FROM A важно не перепутать порядок выбираемых полей таблицы - хотя в обычной жизни на такие вещи не обращаешь внимания. А вот с курсором обращать приходится, в противном случае вместо ожидаемого результата
F1: 1, F2: 11.1
F1: 2, F2: 22.22
F1: 3, F2: 33.333

легко получить
Error report:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 10
06502. 00000 -  "PL/SQL: numeric or value error%s"

При этом повезёт, если типы столбцов или содержащиеся в них значения окажутся не совместимые и сразу получим сообщение об ошибке. Иначе можно совсем долго разбираться, отчего в нужных столбцах показываются неправильные данные.

Как подключиться из ERwin к Oracle

Предположим, я хочу выполнить Reverse Engineer для какой-то схемы базы. Для этого я вызываю Tools -> Reverse Engineer... Появляется диалог, в котором я указываю тип СУБД - Oracle

Нажимаю Next.

Изменяю или оставляю заданные значения опций импорта на появившемся окне:

и снова нажимаю Next.

Появляется окно, в котором нужно указать свойства подключения к базе данных.

Здесь я указываю тип аутентификации - у меня используется Database Authentification.

Указываю значения username и пароля. 

После этого необходимо указать значение строки соединения (connection string). Значение этой строки можно добыть из tnsnames.ora.

В tnsnames.ora выбираю определение

XE =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = desktop-1)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = XE)

    )

  )

Это выражение нужно привести к одной строке, убрав переводы строк, получив вот такое:

(DESCRIPTION =  (ADDRESS = (PROTOCOL = TCP)(HOST = desktop-1)(PORT = 1521)) (CONNECT_DATA =  (SERVER = DEDICATED) (SERVICE_NAME = XE)))

Копирую эту строку в поле ввода Connection String - и можно нажимать Connect. 

Поле для хранения GUID в Oracle, использование SYS_GUID

Тип данных для хранения значения - RAW(16).

Для автогенерации значения можно сделать триггер, использовать хранимую процедуру, а можно и вот так:

CREATE TABLE TEST(ID RAW(16) DEFAULT SYS_GUID(), ...);

С другой стороны, если хотим получить значение созданного идентификатора, как обычно и бывает, можно вот так

DECLARE guid RAW(16);

BEGIN

  guid := SYS_GUID();

  INSERT INTO TEST2(ID, NAME) VALUES(guid, 'ПЖиВ И медведи');

END;

Как получить курсор из хранимой процедуры Oracle - SimpleJdbcCall

Понадобилось получить набор данных из хранимой процедуры Oracle. Данные получить эти надо в java-приложение.
Посмотрел несколько вариантов, наиболее симпатичным (что, впрочем, предсказуемо) оказался вариант и использованием спрингового SimpleJdbcCall.

Итак, есть такая табличка - разумеется, что здесь всё упрощено насколько это можно - и даже чуть более

CREATE TABLE BOOK (
    ID INTEGER NOT NULL PRIMARY KEY, 
    NAME VARCHAR2(20))

Есть такая вот хранимая процедура

PROCEDURE GET_BOOKS_BY_NAME (book_cursor OUT SYS_REFCURSOR, name_template IN VARCHAR2)
IS
BEGIN
  OPEN book_cursor FOR
    SELECT b.ID as ID, b.NAME as NAME 
      FROM BOOK b
     WHERE b.NAME LIKE name_template
     ORDER BY ID;    
  
END GET_BOOKS_BY_NAME;

Осталась самая малость - получить от этой процедуры содержимое курсора book_cursor.

Для начала, понадобится получить экземпляр SimpleJdbcCall. Его можно создать руками, можно прописать в контексте спринга - по вкусу.

Для представления данных удобно создать отдельный DTO


public class BookItem {
    private Integer id;
    private String name;
    public BookItem() {}
    // getters и setters опущены 
}


На экземпляры этого класса будут отображены данные из получаемого курсора.

А теперь собираем всё вместе и получаем данные


    public static void main(String[] args) {
        ApplicationContext context = new ClassPathXmlApplicationContext("app-config.xml");


        SimpleJdbcCall simpleJdbcCall = context.getBean("simpleJdbcCall", SimpleJdbcCall.class);


        // Этот экземпляр отвечает за отображение набора данных, полученных из
        // базы данных в курсоре, на список объектов
        RowMapper rowMapper = ParameterizedBeanPropertyRowMapper.newInstance(BookItem.class);
        // Указывается имя вызываемой хранимой процедуры
        simpleJdbcCall.withProcedureName("GET_BOOKS_BY_NAME");
        // Связывается курсор, получаемый из хранимой процедуры, с отображателем
        // данных
        simpleJdbcCall.returningResultSet("BOOK_CURSOR", rowMapper);


        // Задаются входные параметры для хранимой процедуры
        SqlParameterSource params = new MapSqlParameterSource().addValue("NAME_TEMPLATE", "NEW%");


        // Теперь производится непосредственный вызов хранимой процедуры.
        // В возвращаемый в результате вызова map помещается имя курсора
        // (параметр типа String) и сами полученные данные в виде ArrayList.
        // Для того, чтобы эти данные из ArrayList разобрать, и используется
        // rowMapper
        Map resultMap = simpleJdbcCall.execute(params);


        // При этом вызове производится отображение полученных данных на тип,
        // указанный в параметре rowMapper
        @SuppressWarnings("unchecked")
        List resultList = (List) resultMap.get("BOOK_CURSOR");


        // А теперь просто работаем с тем, что получили от БД
        System.out.println("Получено записей: " + resultList.size());
        for (BookItem item : resultList) {
            System.out.println(item);
        }
    }

Вот и всё.