Получить максимально допустимую длину в столбце | Оракул

Как получить максимальную и минимальную длину, разрешенную в столбце varchar2. Мне нужно проверить входящие данные из временной таблицы, которая поступает из какой-то удаленной базы данных. И каждое значение строки должно быть проверено для определенных столбцов, чтобы оно имело максимальное или минимальное значение, которое может быть установлено в столбце.

Поэтому я должен был получить спецификации столбца, используя детали его схемы. Я сделал proc для этого:

PROCEDURE CHK_COL_LEN(VAL IN VARCHAR2,             
                       MAX_LEN IN NUMBER :=4000,
                       MIN_LEN IN NUMBER :=0,
                       LEN_OUT OUT VARCHAR2)
        IS
            BEGIN
               IF LENGTH(VAL)<MIN_LEN THEN
                    LEN_OUT := 'ERROR';
                    RETURN;
               ELSIF LENGTH(VAL)>MAX_LEN THEN
                    LEN_OUT := 'ERROR';
                    RETURN;
               ELSE
                    LEN_OUT := 'SUCCESS';
                    RETURN;
               END IF;
            END;
END CHK_COL_LEN;

Но проблема в том, что его нельзя использовать повторно, и он немного жестко запрограммирован. Я должен явно отправить значение MAX и MIN для каждого значения вместе с данными для проверки.

Итак, при вызове proc это что-то вроде:

CHK_COL_LEN(EMP_CURSOR.EMP_ID, 5, 1, LEN_ERROR_MSG);

Вместо этого я хочу что-то вроде: (Если что-то подобное существует!)

CHK_COL_LEN(EMP_CURSOR.EMP_ID, 
               EMP.COLUMN_NAME%MAX_LENGTH, 
               EMP.COLUMN_NAME%MIN_LENGTH, 
               LEN_ERROR_MSG)

Заранее спасибо.

ИЗМЕНИТЬ

select max(length(col)) from table;

Это решение, но мне снова придется запускать этот запрос каждый раз, чтобы установить две переменные для значений MAX и MIN. И выполнение дополнительных двух запросов для каждого значения, а затем установка 2 переменных будет стоить значительного снижения производительности при наличии около 32 таблиц, каждая с 5-8 столбцами varchar2 и средними строками около 40k-50k в каждой таблице.


sql oracle maxlength
person Sachin    schedule 15.12.2014    source источник
comment
Но max(length(col)) даст вам максимальную существующую длину, это не обязательно MAX_LENGTH вашего столбца. Я думаю, что @road242 прав, единственный способ получить размер столбца - использовать словарь.   -  person Aramillo    schedule 15.12.2014
comment
Почему вы пытаетесь написать проверку, используя словарь данных, вместо того, чтобы позволить определению таблицы сделать проверку за вас?   -  person Allan    schedule 15.12.2014
comment
Потому что я хочу поймать это заранее. Поскольку после создания исключения (даже если оно обрабатывается вручную) оно затем пропускает операции, которые будут запланированы позже (возьмите сценарий, когда произошло всего несколько циклов, а затем были созданы исключения), затем цикл завершается при первом возникновении исключения.   -  person Sachin    schedule 16.12.2014

Ответы (3)


arrow_upward
4
arrow_downward

Вы можете запросить таблицу «user_tab_columns table», чтобы получить информацию о метаданных конкретной таблицы:

SELECT 
   COLUMN_NAME, DATA_LENGTH, DATA_PRECISION 
FROM 
   user_tab_columns 
WHERE 
   t.table_name IN ('<YOURTABLE>');

с этой информацией вы можете запросить метаданные непосредственно в вашей хранимой процедуре:

...
SELECT 
   CHAR_LENGTH INTO max_length 
FROM 
   user_tab_columns 
WHERE 
   table_name = '<YOURTABLE>' AND COLUMN_NAME = '<YOURCOLUMN>';

...

Пример процедуры для получения максимальной длины таблицы/столбца:

create or replace PROCEDURE GET_MAX_LENGTH_OF_COLUMN(    
                       tableName IN VARCHAR2,
                       columnName IN VARCHAR2,
                       MAX_LENGTH OUT VARCHAR2)
        IS
            BEGIN

            SELECT CHAR_LENGTH INTO MAX_LENGTH 
            FROM user_tab_columns 
            WHERE table_name = tableName AND COLUMN_NAME = columnName;            

END GET_MAX_LENGTH_OF_COLUMN;
person road242    schedule 15.12.2014
comment
Да, с этим я могу получить длину, конечно. Но это не так уж и полезно, так как для каждого столбца мне придется написать такой большой код и получить значение для установки в переменную, а затем передать переменную в proc. Также я не могу написать ИМЯ ТАБЛИЦЫ в proc, потому что proc reuable и использовал мои около 32 таблиц. Так что сделал общий прок для всех столов. - person Sachin; 15.12.2014
comment
Почему? вы можете использовать имя таблицы + имя столбца в качестве входного параметра, поэтому вам придется написать эту процедуру только один раз. - person road242; 15.12.2014
comment
Вы правы, но, к сожалению, в Oracle имя таблицы не может быть динамическим. Ни одно имя схемы не может быть динамическим в Oracle и должно быть жестко запрограммировано. Ограничение оракула. - person Sachin; 15.12.2014
comment
работает на меня. См. отредактированный ответ с процедурой отображения максимальной длины столбца (работает как вставленный) - person road242; 15.12.2014
comment
имя таблицы или имя схемы здесь не является динамическим, оператор просто извлекает метаданные, используя существующие таблицы oracle-system-tables - person road242; 15.12.2014
comment
Но на самом деле, когда я передаю имя таблицы в качестве параметра, запрос должен выполняться с использованием ЭТОГО параметра вместо имени таблицы. А этого Oracle не позволяет. - person Sachin; 15.12.2014
comment
Я думаю, что @road242 имеет в виду динамическое расположение максимальной длины (обратите внимание, вам может потребоваться учитывать длину BYTE/CHAR в зависимости от набора символов), тогда как @Sachin теперь говорит о динамическом запросе данных из переменной таблицы для сравнения до максимальной длины... для этого вам нужно будет использовать EXECUTE IMMEDIATE, чтобы вы могли выполнять динамические запросы внутри процедуры. - person Captain; 15.12.2014
comment
Как сказал @Captain: я говорю о решении, упомянутом мной. Процедура GET_MAX_LENGTH_OF_COLUMN не имеет в себе имен переменных таблиц/столбцов... Она компилируется и работает так, как написано выше (поверьте мне, а если нет, то скомпилируйте и запустите ее). - person road242; 15.12.2014
comment
Готово .. @Captain прав, я неправильно истолковал это с EXECUTE IMMEDIATE. Спасибо всем. - person Sachin; 16.12.2014

arrow_upward
1
arrow_downward

Попробуйте создать свою процедуру следующим образом:

create or replace procedure Checking_size(column_name varchar2,columnvalue varchar2,state out varchar2) is

begin
execute immediate 'declare
z '||column_name||'%type;
begin
z:=:param2;
end;' using columnvalue;

state:='OK';
exception when value_error then
state:='NOT OK';

end;

Как видите, я имитирую присваивание ошибок. Если длина columnvalue больше, чем столбец, который я передаю как column_name, он выдаст исключение value_error и вернет NOT OK, иначе вернет OK. Например, если your_table.your_column ссылается на столбец длиной (3), то вернуть NOT OK.

declare 
state varchar2(10);
begin

Checking_size('your_table.your_column','12345',state);
dbms_output.put_line(state);
end;
person Aramillo    schedule 15.12.2014

arrow_upward
0
arrow_downward

Если список таблиц невелик, вы можете указать МИН. значение, используя ПРОВЕРЬТЕ ограничение на столе.

Любой DML в таблице автоматически завершится ошибкой, если он превысит длину, назначенную этому столбцу.

CREATE TABLE suppliers
(
 supplier_id numeric(4),
 supplier_name varchar2(50),
 CONSTRAINT check_supplier_id
 CHECK (length(supplier_name) > 5 )
 );
person psaraj12    schedule 15.12.2014
comment
Да, но проверка должна быть сделана до DML. Поэтому изначально данные хранятся в таблице Temp, а затем после проверки выполняется DML. Результат должен быть пойман заранее, любое исключение! Потому что для клиента исключение должно срабатывать по очень серьезным проблемам, а не по таким. - person Sachin; 15.12.2014
comment
как создается курсор EMP_CURSOR.EMP_ID - person psaraj12; 15.12.2014