Мой блог
[angor@omega admin]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/client_1
[angor@omega admin]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
[angor@omega admin]$ export PATH=$ORACLE_HOME/bin:$PATH
3. Устанавливаем cx_oracle
[angor@omega admin]$ sudo easy_install cx_oracle
4. Проверяем
[angor@omega ~]$ python
Python 3.4.1 (default, May 19 2014, 17:23:49)
[GCC 4.9.0 20140507 (prerelease)] on linux
Type «help», «copyright», «credits» or «license» for more information.
>>> import cx_Oracle
>>>
import cx_Oracle
ip = ‘192.168.0.1’
port = 1521
SID = ‘YOURSIDHERE’
dsn_tns = cx_Oracle.makedsn(ip, port, SID)
db = cx_Oracle.connect(‘username’, ‘password’, dsn_tns)
import cx_Oracle
connstr = ‘scott/tiger@server:1521/orcl’
conn = cx_Oracle.connect(connstr)
import cx_Oracle
CONN_INFO = ‘host’: ‘192.168.0.1’,
‘port’: 1521,
‘user’: ‘user_name’,
‘psw’: ‘your_password’,
‘service’: ‘my_service’,
>
CONN_STR = ‘/@:/’.format(**CONN_INFO)
connection = cx_Oracle.connect(CONN_STR)
import cx_Oracle
ip = ‘192.168.0.1’
port = 1521
service_name = ‘my_service’
dsn = cx_Oracle.makedsn(ip, port, service_name=service_name)
db = cx_Oracle.connect(‘user’, ‘password’, dsn)
import cx_Oracle
dsn = cx_Oracle.makedsn(host=’127.0.0.1′, port=1521, sid=’your_sid’)
conn = cx_Oracle.connect(user=’angor’, password=’password’, dsn=dsn)
conn.close()
import cx_Oracle
ip = ‘192.168.0.1’
port = 1524
SID = ‘dev3’
dsn_tns = cx_Oracle.makedsn(ip, port, SID)
conn = cx_Oracle.connect(‘angor’, ‘pass’, dsn_tns)
print conn.version
conn.close()
import sys
import getpass
import platform
import cx_Oracle
# Версии Python и модулей
print («Python version: » + platform.python_version())
print («cx_Oracle version: » + cx_Oracle.version)
print («Oracle client: » + str(cx_Oracle.clientversion()).replace(‘, ‘,’.’))
print (‘-‘ * 90)
# Приконнектимся к Oracle
username = ‘scott’
pwd = ‘tiger’
database = ‘testdb’
connection = cx_Oracle.connect(username, pwd, database)
# Или так:
#connection = cx_Oracle.connect(‘scott’, ‘tiger’, ‘testdb’)
#connection = cx_Oracle.connect(‘scott/tiger@testdb’)
# Некоторые атрибуты объекта connection:
print («Oracle DB version: » + connection.version)
print («Oracle client encoding: » + connection.encoding)
print (‘-‘ * 90)
# Создадим курсор и выполним запрос к БД:
cursor = connection.cursor()
query = «select * from v$version»
cursor.execute(query)
rows = cursor.fetchall()
for row in rows:
print(row)
C:\project\py>test_ora.py
Python version: 3.5.1 cx_Oracle version: 5.2 Oracle client: (12.1.0.2.0) ------------------------------------------------------------------------------------------ Oracle DB version: 12.1.0.2.0 Oracle client encoding: WINDOWS-1252 ------------------------------------------------------------------------------------------ ('Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production', 0) ('PL/SQL Release 12.1.0.2.0 - Production', 0) ('CORE\t12.1.0.2.0\tProduction', 0) ('TNS for 64-bit Windows: Version 12.1.0.2.0 - Production', 0) ('NLSRTL Version 12.1.0.2.0 - Production', 0)
Ещё способы создания объектов соединений с БД Oracle:
#lsnrctl servises
service = ‘testdb.localdomain’
connection = cx_Oracle.connect(‘scott’, ‘tiger’, ‘localhost:1521/’ + service)
connection = cx_Oracle.connect(‘scott/tiger@localhost:1521/’ + service)
dsn_tns = cx_Oracle.makedsn(‘localhost’, 1521, service).replace(‘SID’,’SERVICE_NAME’)
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=testdb.localdomain)))
connection = cx_Oracle.connect(‘scott’, ‘tiger’, dsn_tns)
Пример создания таблицы:
CREATE TABLE EMP(
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
);
alter session set nls_date_format=’DD-MON-YYYY’;
alter session set nls_language=AMERICAN;
INSERT INTO EMP VALUES(7369, ‘SMITH’, ‘CLERK’, 7902,TO_DATE(’17-DEC-1980′, ‘DD-MON-YYYY’), 800, NULL, 20);
INSERT INTO EMP VALUES(7499, ‘ALLEN’, ‘SALESMAN’, 7698,TO_DATE(’20-FEB-1981′, ‘DD-MON-YYYY’), 1600, 300, 30);
INSERT INTO EMP VALUES(7521, ‘WARD’, ‘SALESMAN’, 7698,TO_DATE(’22-FEB-1981′, ‘DD-MON-YYYY’), 1250, 500, 30);
INSERT INTO EMP VALUES(7566, ‘JONES’, ‘MANAGER’, 7839,TO_DATE(‘2-APR-1981’, ‘DD-MON-YYYY’), 2975, NULL, 20);
INSERT INTO EMP VALUES(7654, ‘MARTIN’, ‘SALESMAN’, 7698,TO_DATE(’28-SEP-1981′, ‘DD-MON-YYYY’), 1250, 1400, 30);
INSERT INTO EMP VALUES(7698, ‘BLAKE’, ‘MANAGER’, 7839,TO_DATE(‘1-MAY-1981’, ‘DD-MON-YYYY’), 2850, NULL, 30);
INSERT INTO EMP VALUES(7782, ‘CLARK’, ‘MANAGER’, 7839,TO_DATE(‘9-JUN-1981’, ‘DD-MON-YYYY’), 2450, NULL, 10);
INSERT INTO EMP VALUES(7788, ‘SCOTT’, ‘ANALYST’, 7566,TO_DATE(’09-DEC-1982′, ‘DD-MON-YYYY’), 3000, NULL, 20);
INSERT INTO EMP VALUES(7839, ‘KING’, ‘PRESIDENT’, NULL,TO_DATE(’17-NOV-1981′, ‘DD-MON-YYYY’), 5000, NULL, 10);
INSERT INTO EMP VALUES(7844, ‘TURNER’, ‘SALESMAN’, 7698,TO_DATE(‘8-SEP-1981’, ‘DD-MON-YYYY’), 1500, 0, 30);
INSERT INTO EMP VALUES(7876, ‘ADAMS’, ‘CLERK’, 7788,TO_DATE(’12-JAN-1983′, ‘DD-MON-YYYY’), 1100, NULL, 20);
INSERT INTO EMP VALUES(7900, ‘JAMES’, ‘CLERK’, 7698,TO_DATE(‘3-DEC-1981’, ‘DD-MON-YYYY’), 950, NULL, 30);
INSERT INTO EMP VALUES(7902, ‘FORD’, ‘ANALYST’, 7566,TO_DATE(‘3-DEC-1981’, ‘DD-MON-YYYY’), 3000, NULL, 20);
INSERT INTO EMP VALUES(7934, ‘MILLER’, ‘CLERK’, 7782,TO_DATE(’23-JAN-1982′, ‘DD-MON-YYYY’), 1300, NULL, 10);
С использованием cx_Oracle:
try:
cursor.execute(«DROP TABLE EMP»)
except:
print(‘Таблица не существует’)
create_table = «»»
CREATE TABLE EMP(
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2))
«»»
cursor.execute(«alter session set nls_date_format=’DD-MON-YYYY'»)
cursor.execute(«alter session set nls_language=AMERICAN»)
cursor.execute(«INSERT INTO EMP VALUES(7369, ‘SMITH’, ‘CLERK’, 7902,TO_DATE(’17-DEC-1980′, ‘DD-MON-YYYY’), 800, NULL, 20)»)
cursor.execute(«INSERT INTO EMP VALUES(7499, ‘ALLEN’, ‘SALESMAN’, 7698,TO_DATE(’20-FEB-1981′, ‘DD-MON-YYYY’), 1600, 300, 30)»)
cursor.execute(«INSERT INTO EMP VALUES(7521, ‘WARD’, ‘SALESMAN’, 7698,TO_DATE(’22-FEB-1981′, ‘DD-MON-YYYY’), 1250, 500, 30)»)
cursor.execute(«INSERT INTO EMP VALUES(7566, ‘JONES’, ‘MANAGER’, 7839,TO_DATE(‘2-APR-1981’, ‘DD-MON-YYYY’), 2975, NULL, 20)»)
cursor.execute(«INSERT INTO EMP VALUES(7654, ‘MARTIN’, ‘SALESMAN’, 7698,TO_DATE(’28-SEP-1981′, ‘DD-MON-YYYY’), 1250, 1400, 30)»)
cursor.execute(«INSERT INTO EMP VALUES(7698, ‘BLAKE’, ‘MANAGER’, 7839,TO_DATE(‘1-MAY-1981’, ‘DD-MON-YYYY’), 2850, NULL, 30)»)
cursor.execute(«INSERT INTO EMP VALUES(7782, ‘CLARK’, ‘MANAGER’, 7839,TO_DATE(‘9-JUN-1981’, ‘DD-MON-YYYY’), 2450, NULL, 10)»)
cursor.execute(«INSERT INTO EMP VALUES(7788, ‘SCOTT’, ‘ANALYST’, 7566,TO_DATE(’09-DEC-1982′, ‘DD-MON-YYYY’), 3000, NULL, 20)»)
cursor.execute(«INSERT INTO EMP VALUES(7839, ‘KING’, ‘PRESIDENT’, NULL,TO_DATE(’17-NOV-1981′, ‘DD-MON-YYYY’), 5000, NULL, 10)»)
cursor.execute(«INSERT INTO EMP VALUES(7844, ‘TURNER’, ‘SALESMAN’, 7698,TO_DATE(‘8-SEP-1981’, ‘DD-MON-YYYY’), 1500, 0, 30)»)
cursor.execute(«INSERT INTO EMP VALUES(7876, ‘ADAMS’, ‘CLERK’, 7788,TO_DATE(’12-JAN-1983′, ‘DD-MON-YYYY’), 1100, NULL, 20)»)
cursor.execute(«INSERT INTO EMP VALUES(7900, ‘JAMES’, ‘CLERK’, 7698,TO_DATE(‘3-DEC-1981’, ‘DD-MON-YYYY’), 950, NULL, 30)»)
cursor.execute(«INSERT INTO EMP VALUES(7902, ‘FORD’, ‘ANALYST’, 7566,TO_DATE(‘3-DEC-1981’, ‘DD-MON-YYYY’), 3000, NULL, 20)»)
cursor.execute(«INSERT INTO EMP VALUES(7934, ‘MILLER’, ‘CLERK’, 7782,TO_DATE(’23-JAN-1982′, ‘DD-MON-YYYY’), 1300, NULL, 10)»)
query = «select * from emp»
cursor.execute(query)
rows = cursor.fetchall()
for row in rows:
print(row)
query1 = «select empno, ename, to_char(hiredate,’dd.mm.yyyy hh24:mi:ss’) from emp»
cursor.execute(query1)
rows = cursor.fetchall()
for row in rows:
print(row)
drop_table = «delete from emp»
cursor.execute(drop_table)
connection.commit()
cursor.execute(query)
rows = cursor.fetchall()
for row in rows:
print(row)
(7369, 'SMITH', 'CLERK', 7902, datetime.datetime(1980, 12, 17, 0, 0), 800.0, None, 20) (7499, 'ALLEN', 'SALESMAN', 7698, datetime.datetime(1981, 2, 20, 0, 0), 1600.0, 300.0, 30) (7521, 'WARD', 'SALESMAN', 7698, datetime.datetime(1981, 2, 22, 0, 0), 1250.0, 500.0, 30) (7566, 'JONES', 'MANAGER', 7839, datetime.datetime(1981, 4, 2, 0, 0), 2975.0, None, 20) (7654, 'MARTIN', 'SALESMAN', 7698, datetime.datetime(1981, 9, 28, 0, 0), 1250.0, 1400.0, 30) (7698, 'BLAKE', 'MANAGER', 7839, datetime.datetime(1981, 5, 1, 0, 0), 2850.0, None, 30) (7782, 'CLARK', 'MANAGER', 7839, datetime.datetime(1981, 6, 9, 0, 0), 2450.0, None, 10) (7788, 'SCOTT', 'ANALYST', 7566, datetime.datetime(1982, 12, 9, 0, 0), 3000.0, None, 20) (7839, 'KING', 'PRESIDENT', None, datetime.datetime(1981, 11, 17, 0, 0), 5000.0, None, 10) (7844, 'TURNER', 'SALESMAN', 7698, datetime.datetime(1981, 9, 8, 0, 0), 1500.0, 0.0, 30) (7876, 'ADAMS', 'CLERK', 7788, datetime.datetime(1983, 1, 12, 0, 0), 1100.0, None, 20) (7900, 'JAMES', 'CLERK', 7698, datetime.datetime(1981, 12, 3, 0, 0), 950.0, None, 30) (7902, 'FORD', 'ANALYST', 7566, datetime.datetime(1981, 12, 3, 0, 0), 3000.0, None, 20) (7934, 'MILLER', 'CLERK', 7782, datetime.datetime(1982, 1, 23, 0, 0), 1300.0, None, 10) ------------------------------------------------------------------------------------------ (7369, 'SMITH', '17.12.1980 00:00:00') (7499, 'ALLEN', '20.02.1981 00:00:00') (7521, 'WARD', '22.02.1981 00:00:00') (7566, 'JONES', '02.04.1981 00:00:00') (7654, 'MARTIN', '28.09.1981 00:00:00') (7698, 'BLAKE', '01.05.1981 00:00:00') (7782, 'CLARK', '09.06.1981 00:00:00') (7788, 'SCOTT', '09.12.1982 00:00:00') (7839, 'KING', '17.11.1981 00:00:00') (7844, 'TURNER', '08.09.1981 00:00:00') (7876, 'ADAMS', '12.01.1983 00:00:00') (7900, 'JAMES', '03.12.1981 00:00:00') (7902, 'FORD', '03.12.1981 00:00:00') (7934, 'MILLER', '23.01.1982 00:00:00')
Подключение к базам в цикле:
database.csv sid;pwdsys;uconn;host TESTDB;oracle;(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=omega)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TESTDB_OMEGA)));omega TESTDB;oracle;(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=omega)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TESTDB_OMEGA)));omega TESTDB;oracle;(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=omega)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TESTDB_OMEGA)));omega TESTDB;oracle;DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=omega)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TESTDB_OMEGA)));omega TESTDB;oracle;(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=omega)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TESTDB_OMEGA)));omega import cx_Oracle import pandas as pd import numpy as np data = pd.read_csv('database.csv', sep=';') data_rows, data_columns = data.shape for i in range(data_rows): sid = data.loc[i,'sid'] pwd = data.loc[i,'pwdsys'] conn = data.loc[i,'uconn'] s = 'sys/'+pwd+'@'+conn #print(s) try: ora_conn = cx_Oracle.connect(s, mode = cx_Oracle.SYSDBA) cursor = ora_conn.cursor() query = "select * from dual" cursor.execute(query) rows = cursor.fetchall() for row in rows: print(row) except cx_Oracle.DatabaseError as e: error, = e.args if error.code == 1017: print('Please check your credentials. <>'.format(e)) else: print('Database connection error: <>'.format(e)) finally: try: cursor.close() ora_conn.close() except: print('cx_Oracle.connect or db.cursor') ('X',) ('X',) ('X',) Database connection error: ORA-12154: TNS:could not resolve the connect identifier specified cx_Oracle.connect or db.cursor ('X',)
username = 'angor' password = '' dsn = 'localhost/pdborcl' port = 1512 encoding = 'UTF-8' import cx_Oracle import config sql = 'select customer_id, name ' \ 'from customers ' \ 'order by name' try: with cx_Oracle.connect( config.username, config.password, config.dsn, encoding=config.encoding) as connection: #fetchone with connection.cursor() as cursor: cursor.execute(sql) while True: row = cursor.fetchone() if row is None: break print(row) except cx_Oracle.Error as error: print(error) #fetchall with connection.cursor() as cursor: # execute the SQL statement cursor.execute(sql) # fetch all rows rows = cursor.fetchall() if rows: for row in rows: print(row) except cx_Oracle.Error as error: print(error) batch_size = 20 #fetchmany with connection.cursor() as cursor: # execute the SQL statement cursor.execute(sql) while True: # fetch rows rows = cursor.fetchmany(batch_size) if not rows: break # display rows for row in rows: print(row) except cx_Oracle.Error as error: print(error)
Существует четыре типа больших объектов:
BLOB - Большой двоичный объект, используемый для хранения двоичных данных. cx_Oracle использует тип cx_Oracle.BLOB. CLOB - Большой символьный объект, используемый для символьных строк в формате набора символов базы данных. cx_Oracle использует тип cx_Oracle.CLOB. NCLOB - большой объект национального символа, используемый для символьных строк в формате набора национальных символов. cx_Oracle использует тип cx_Oracle.NCLOB. BFILE - внешний двоичный файл, используемый для ссылки на файл, хранящийся в операционной системе хоста за пределами базы данных. cx_Oracle использует тип cx_Oracle.BFILE. Большие объекты могут передаваться в Oracle Database и из нее. Большие объекты длиной до 1 ГБ также могут обрабатываться непосредственно как строки или байты в cx_Oracle. Это облегчает работу с большими объектами и дает значительные преимущества в производительности по сравнению с потоковой передачей. Однако для этого требуется, чтобы все данные больших объектов присутствовали в памяти Python, что может быть невозможно. Смотрите GitHub для примеров LOB. Простая вставка больших объектов Рассмотрим таблицу со столбцами CLOB и BLOB: CREATE TABLE lob_tbl ( id NUMBER, c CLOB, b BLOB ); С помощью cx_Oracle данные больших объектов могут быть вставлены в таблицу путем привязки строк или байтов по мере необходимости: with open('example.txt', 'r') as f: textdata = f.read() with open('image.png', 'rb') as f: imgdata = f.read() cursor.execute(""" insert into lob_tbl (id, c, b) values (:lobid, :clobdata, :blobdata)""", lobid=10, clobdata=textdata, blobdata=imgdata) Обратите внимание, что при таком подходе размер больших данных ограничен 1 ГБ. Извлечение больших объектов в виде строк и байтов CLOB и BLOB размером менее 1 ГБ могут запрашиваться из базы данных напрямую в виде строк и байтов. Это может быть намного быстрее, чем потоковое. Необходимо использовать Connection.outputtypehandler или Cursor.outputtypehandler, как показано в этом примере: def OutputTypeHandler(cursor, name, defaultType, size, precision, scale): if defaultType == cx_Oracle.CLOB: return cursor.var(cx_Oracle.LONG_STRING, arraysize=cursor.arraysize) if defaultType == cx_Oracle.BLOB: return cursor.var(cx_Oracle.LONG_BINARY, arraysize=cursor.arraysize) id_v = 1 textData = "Текстовые данные" bytesData = b"Бинарные данные" cursor.execute("insert into lob_tbl (id, c, b) values (:1, :2, :3)", [id_v, textData, bytesData]) connection.outputtypehandler = OutputTypeHandler cursor.execute("select c, b from lob_tbl where [id_v]) clobData, blobData = cursor.fetchone() print("CLOB length:", len(clobData)) print("CLOB data:", clobData) print("BLOB length:", len(blobData)) print("BLOB data:", blobData) Без обработчика типа вывода значения CLOB и BLOB извлекаются как объекты LOB. Размер объекта LOB можно получить, вызвав LOB.size (), а данные можно прочитать, вызвав LOB.read (): id_v = 1 textData = "Текстовые данные" bytesData = b"Бинарные данные" cursor.execute("insert into lob_tbl (id, c, b) values (:1, :2, :3)", [id_v, textData, bytesData]) cursor.execute("select b, c from lob_tbl where [id_v]) b, c = cursor.fetchone() print("CLOB length:", c.size()) print("CLOB data:", c.read()) print("BLOB length:", b.size()) print("BLOB data:", b.read()) Этот подход дает те же результаты, что и в предыдущем примере, но он будет работать медленнее потому что это требует большего количества обращений к базе данных Oracle и имеет более высокие издержки.
Читать большие BLOB объекты можно с помощью метода LOB.read(). Метод LOB.read() может вызываться повторно до тех пор, пока не будут прочитаны все данные cursor.execute("select b from lob_t where [10]) blob, = cursor.fetchone() offset = 1 numBytesInChunk = 65536 with open("image.png", "wb") as f: while True: data = blob.read(offset, numBytesInChunk) if data: f.write(data) if len(data) < numBytesInChunk: break offset += len(data)
Записывать большие BLOB объекты можно с помощью метода BLOB.write(): id_v = 9 lob_v = cursor.var(cx_Oracle.BLOB) cursor.execute(""" insert into lob_tbl (id, b) values (:1, empty_blob()) returning b into :2""", [id_v, lob_v]) blob, = lob_v.getvalue() offset = 1 numBytesInChunk = 65536 with open("image.png", "rb") as f: while True: data = f.read(numBytesInChunk) if data: blob.write(data, offset) if len(data) < numBytesInChunk: break offset += len(data) connection.commit()
Артём Санников
Данная книга является руководством для начинающих специалистов в области анализа и обработки данных. В книге рассматривается язык SQL и его процедурное расширение PL/SQL от компании Oracle.
Главная › Базы данных › Oracle PL/SQL › Строки › Тип данных VARCHAR2 в PL/SQL Oracle
Тип данных VARCHAR2 в PL/SQL Oracle
В переменных типа VARCHAR2 хранятся символьные строки переменной длины. При объявлении такой строки для нее определяется максимальная длина в диапазоне от 1 до 32 767 байт. Максимальная длина может задаваться в байтах или символах, но в любом случае компилятор определяет ее в байтах.
Синтаксис объявления VARCHAR2
имя_переменной VARCHAR2 (макс_длина [CHAR | BYTE]);
Здесь имя_переменной — имя объявляемой переменной, макс_длина — ее максимальная длина, а CHAR и BYTE — аргументы, указывающие, что максимальная длина выражается в символах или в байтах соответственно.
Если максимальная длина строковой переменной VARCHAR2 задается в символах (спецификатор CHAR ), то ее реальная длина в байтах вычисляется на основе максимального количества байтов, используемых для представления одного символа.
Спецификатор длины CHAR используется в основном при работе с многобайтовыми наборами символов — такими, как UTF-8.
Если в объявлении переменной VARCHAR2 опустить спецификатор CHAR или BYTE , тогда заданное значение длины будет интерпретировано в байтах или символах в зависимости от параметра инициализации NLS_LENGTH_SEMANTICS . Текущее значение можно узнать, обратившись с запросом к NLS_SESSION_PARAMETERS .
Несколько примеров объявления строк типа VARCHAR2 :
DECLARE small_string VARCHAR2(4); line_of_text VARCHAR2(2000); feature_name VARCHAR2(100 BYTE); -- Строка длиной 100 байт emp_name VARCHAR2(30 CHAR); ------- Строка длиной 30 символов
Итак, максимальная длина переменной типа VARCHAR2 в PL/SQL составляет 32 767 байт. Это ограничение действует независимо от того, определяется ли длина строки в байтах или символах. Однако следует учитывать, что SQL поддерживает этот максимум только в том случае, если параметру инициализации MAX_SQL_STRING_SIZE задано значение EXTENDED ; по умолчанию используется значение STANDARD .
Если вам понадобится работать со строками длиной более 4000 байт, рассмотрите возможность их хранения в столбцах типа CLOB .
Записи по теме
Размер строк и таблицы для таблиц, оптимизированных для памяти
До SQL Server 2016 (13.x) размер данных в строке оптимизированной для памяти таблицы не может превышать 8 060 байт. Однако начиная с SQL Server 2016 (13.x) и в Базе данных SQL Azure теперь можно создать оптимизированную для памяти таблицу с несколькими большими столбцами (например, несколькими столбцами varbinary(8000) и бизнес-столбцами (т. е. varbinary(max), varchar(max) и nvarchar(max)) и выполнять операции с ними с помощью встроенных скомпилированных модулей T-SQL и типов таблиц.
Столбцы, превышающие максимальный размер строки в 8060 байт, размещаются вне строки в специальной внутренней таблице. У каждого такого столбца имеется соответствующая внутренняя таблица, которая, в свою очередь, имеет один некластеризованный индекс. Дополнительные сведения об этих внутренних таблицах, используемых для столбцов вне строк, см. в sys.memory_optimized_tables_internal_attributes (Transact-SQL).
Существуют определенные сценарии, в которых удобно вычислять размер строки и таблицы.
- Какой объем памяти используется таблицей?
- Объем используемой таблицей памяти невозможно подсчитать точно. На объем используемой памяти влияет множество факторов. Это такие факторы, как постраничное выделение места в памяти, размещение, кэширование и заполнение. Кроме того, несколько версий строк, которые имеют активные связанные транзакции либо ожидают сборку мусора.
- Минимальный размер, необходимый для данных и индексов в таблице, определяется вычислением для [размера таблицы], о котором рассказывается ниже.
- Вычисление используемой памяти в самом лучшем случае может быть выполнено лишь приближенно, поэтому рекомендуется включить планирование вместимости в планы разработки.
Таблица, оптимизированная для памяти, представляет собой набор строк, а также индексов, которые содержат указатели на строки. На следующей схеме показана таблица с индексами и строками, которые в свою очередь содержат заголовки и текст:
Таблица, оптимизированная для памяти, состоящая из индексов и строк.
Размер таблицы вычислений
Размер, занимаемый таблицей в памяти (в байтах) вычисляется следующим образом.
[table size] = [size of index 1] + . + [size of index n] + ([row size] * [row count])Размер хэш-индекса фиксируется на момент создания таблицы и зависит от фактического числа контейнеров. Значение bucket_count , указанное спецификацией индекса, округляется в сторону увеличения до ближайшей степени числа 2 для получения фактического числа контейнеров. Например, если заданное число bucket_count равно 100 000, то фактическое число контейнеров для индекса составляет 131 072.
[hash index size] = 8 * [actual bucket count]Размер некластеризованного индекса определяется в [row count] * [index key size] .
Размер строки вычисляется путем сложения значений для заголовка и текста:
[row size] = [row header size] + [actual row body size] [row header size] = 24 + 8 * [number of indexes]Размер текста строки вычислений
Структура строк. Строки в таблице, оптимизированной для памяти, включают следующие компоненты.
- Заголовок строки содержит метку времени, необходимую для управления версиями строки. Заголовок строки также содержит указатель индекса, который позволяет реализовать цепочку строк в хэш-контейнере (описано выше).
- Текст строки содержит фактические данные столбцов, которые включают некоторые вспомогательные сведения, такие как массив значений NULL для столбцов, допускающих значение NULL, и массив смещений для типов данных с переменной длиной.
На следующем рисунке показана структура строк для таблицы с двумя индексами.
Метки времени начала и конца показывают период, в котором определенная версия строки является допустимой. Транзакции, запускаемые в данном интервале, могут видеть эту версию строки. Дополнительные сведения см. в разделе Транзакции с таблицами, оптимизированными для памяти.
Указатели индекса указывают на следующую строку в цепочке, принадлежащей хэш-контейнеру. На следующем рисунке показана структура таблицы с двумя столбцами (имя, город) и двумя индексами, один для столбца name и второй для столбца city.
На этом рисунке имена Джон и Джейн хэшированы на первый контейнер. Сьюзан хэширована на втором контейнере. Города Пекин и Богота хэшированы на первом контейнере. Париж и Прага хэшированы на втором контейнере.
Таким образом, цепочки для хэш-индекса по именам выглядят следующим образом.
- Первый контейнер: (Джон, Пекин); (Джон, Париж); (Джейн, Прага)
- Второй контейнер: (Сьюзан, Богота)
Цепочки для индекса по городам выглядят следующим образом:
- Первый контейнер: (Джон Пекин), (Сьюзан, Богота)
- Второй контейнер: (Джон, Пекин); (Джон, Париж); (Джейн, Прага)
Конечная метка времени ∞ (бесконечность) указывает, что это действительная на данный момент версия строки. Строка была обновлена или удалена с того момента, как была записана эта версия.
Для времени больше 200 таблица содержит следующие строки.
Имя City Джон Пекин Джейн Prague Однако любая активная транзакция с начальным временем 100 увидит следующую версию таблицы.
Имя City Джон Париж Джейн Prague Сьюзан Богота Вычисление [размера текста строки] демонстрируется в следующей таблице.
Размер текста строки вычисляется двумя способами: вычисляемый размер и фактический размер.
- Вычисляемый размер (далее — вычисляемый размер строки) используется для того, чтобы определить, не превышает ли размер строки ограничение в 8060 байт.
- Фактический размер (далее — фактический размер строки) представляет собой фактический размер строки в памяти и в файлах контрольных точек.
Оба показателя, вычисляемый размер строки и фактический размер строки, вычисляются схожим образом. Отличается только вычисление размера столбцов (n)varchar(I) и столбцов varbinary (I), как показано в нижней части таблицы. Вычисляемый размер строки использует в качестве размера столбца декларируемый размер i , тогда как фактический размер строки использует фактический размер данных.
В следующей таблице описано вычисление размера текста строки как фактический размер текста строки = SUM(размер мелких типов) + 2 + 2 * число столбцов глубокого типа.
Раздел Размер Комментарии Столбцы поверхностных типов SUM [размер поверхностных типов] Размер отдельных типов в байтах: Bit: 1
Tinyint: 1
Smallint: 2
Int: 4
Real: 4
Smalldatetime: 4
Smallmoney: 4
Bigint: 8
Datetime: 8
Datetime2: 8
Float: 8
Money: 8
Числовой (точность <=18): 8
Time: 8
Числовой (точность>18): 16
1, если в таблице присутствуют столбцы глубоких типов, а общий размер данных в столбцах поверхностного типа является нечетным числом.
0, если в таблице нет столбцов глубоких типов
1, если в таблице имеются столбцы глубоких типов данных и размер массива значений NULL равен нечетному числу байтов.
Размер каждого столбца составляет:
i для типов char(i) и binary(i).
вычисляемый размер каждого столбца составляет:
i для типов varchar(i) и varbinary(i)
Фактический размер каждого столбца составляет:
n, где n — количество символов, хранящихся в столбце, для типа varchar(i).
2 * n, где n — количество символов, хранящихся в столбце, для типа nvarchar(i).
Пример: вычисление размера строки и таблицы
Для хэш-индекса фактическое число контейнеров округляется в сторону увеличения до ближайшей степени числа 2. Например, если заданное число bucket_count равно 100 000, то фактическое число контейнеров для индекса составляет 131 072.
Рассмотрим таблицу Orders со следующим определением:
CREATE TABLE dbo.Orders ( OrderID int NOT NULL PRIMARY KEY NONCLUSTERED, CustomerID int NOT NULL INDEX IX_CustomerID HASH WITH (BUCKET_COUNT=10000), OrderDate datetime NOT NULL, OrderDescription nvarchar(1000) ) WITH (MEMORY_OPTIMIZED=ON) GOОбратите внимание, что эта таблица содержит один хэш-индекс и некластеризованный индекс (первичный ключ). Кроме того, она содержит три столбца фиксированной длины и один столбец переменной длины, при этом один из столбцов допускает значения NULL ( OrderDescription ). Допустим, таблица Orders содержит 8379 строк, а средняя длина значений в столбце OrderDescription составляет 78 символов.
Чтобы определить размер таблицы, сначала необходимо определить размер индексов. Для обоих индексов указан показатель bucket_count, равный 10 000. Эта величина округляется в сторону увеличения до ближайшей степени числа 2: 16 384. Поэтому общий размер индексов для таблицы Orders составляет:
8 * 16384 = 131072 bytesОстается найти размер данных таблицы, который равен
[row size] * [row count] = [row size] * 8379(Пример таблицы содержит 8379 строк.) Теперь у нас есть:
[row size] = [row header size] + [actual row body size] [row header size] = 24 + 8 * [number of indices] = 24 + 8 * 1 = 32 bytesТеперь давайте рассчитаем [фактический размер текста строки].
-
Столбцы поверхностных типов:
SUM([size of shallow types]) = 4 [int] + 4 [int] + 8 [datetime] = 162 + 2 * [number of deep type columns] = 2 + 2 * 1 = 4- 8 — наибольшее требования выравнивания.
- Размер на данный момент равен 16 + 0 + 4 + 1 + 1 = 22.
- Ближайшее число, кратное 8, — это 24.
- В итоге заполнение составляет 24 – 22 = 2 байта.
[actual row body size] = 24 + 156 = 180 bytesДля завершения вычисления:
[row size] = 32 + 180 = 212 bytes [table size] = 8 * 16384 + 212 * 8379 = 131072 + 1776348 = 1907420Таким образом, общий размер, занимаемый таблицей в памяти, составляет около 2 мегабайт. Это значение не учитывает потенциальные издержки при выделении памяти, а также управление версиями строк, необходимое для доступа транзакций к этой таблице.
Фактический размер памяти, выделяемый для данной таблицы и используемый ею и ее индексами, можно получить при помощи следующего запроса:
select * from sys.dm_db_xtp_table_memory_stats where object_id = object_id('dbo.Orders')Ограничения столбцов вне строки
Ниже перечислены некоторые ограничения и пояснения, касающиеся использования столбцов "вне строки" в таблице, оптимизированной для памяти.
- Если имеется индекс columnstore для таблицы, оптимизированной для памяти, то все столбцы должны умещаться "в строке".
- Но все ключевые столбцы индекса должны хранится "в строке". Если ключевой столбец индекса не помещается "в строке", добавление индекса завершается ошибкой.
- Пояснения по изменению таблицы, оптимизированной для памяти, со столбцами "вне строки".
- Для больших объектов (LOB) ограничение размера соответствует аналогичному ограничению для таблиц на диске (лимит 2 ГБ на значения LOB).
- Для обеспечения оптимальной производительности рекомендуется проследить, чтобы большинство столбцов умещалось в 8060 байт.
Как изменить возвращаемый из `outputtypehandler` тип данных datetime на строку?
Возможно ли изменить тип данных datetime , возвращаемых из outputtypehandler на строку формата yyyymmdd ?
def OutputTypeHandler(cursor, name, defaultType, size, precision, scale): if defaultType in (cx_Oracle.DB_TYPE_TIMESTAMP, cx_Oracle.DB_TYPE_DATE): return cursor.var(datetime.date,cursor.arraysize)Свободный перевод вопроса Change datetime.date type in outputtypehandler от участника @Babar
Отслеживать
задан 22 дек 2020 в 23:43
51.6k 201 201 золотой знак 63 63 серебряных знака 245 245 бронзовых знаков
ассоциация:stackoverflow.com/q/65376907
23 дек 2020 в 0:021 ответ 1
Сортировка: Сброс на вариант по умолчанию
import cx_Oracle connection = cx_Oracle.connect ('connection_string') def OutputTypeHandler (cursor, name, defaultType, size, precision, scale): if defaultType in (cx_Oracle.DB_TYPE_TIMESTAMP_TZ, cx_Oracle.DB_TYPE_DATE): return cursor.var (cx_Oracle.STRING, arraysize=cursor.arraysize) connection.outputtypehandler = OutputTypeHandler with connection.cursor () as cursor: sql = """ alter session set nls_date_format = 'YYYYMMDD' nls_timestamp_tz_format = 'YYYYMMDD' """ cursor.execute (sql) sql = """select systimestamp, sysdate from dual""" for r in cursor.execute(sql): print(r)('20201223', '20201223')Если пользоваться NLS настройками формата даты на стороне клиента, как показано выше, не стоит забывать использовать session callback для выполнения ALTER с пулом сессий. Но возможно, было бы лучше явное преобраование с маской формата YYYYMMDD в самом SQL запросе.
Если интересуют другие типы дат с часовыми поясами, то нужно добавить их в условие if defaultType и ALTER .