Я разрабатываю приложение в Oracle APEX. У меня есть строка с идентификатором пользователя, который разделен запятой, который выглядит следующим образом:
45,4932,20,19
Эта строка сохраняется как
:P5_USER_ID_LIST
Мне нужен запрос, который найдет всех пользователей, входящих в этот список, мой запрос выглядит так:
SELECT * FROM users u WHERE u.user_id IN (:P5_USER_ID_LIST);
Я продолжаю получать ошибку Oracle: недопустимый номер. Если я, тем не менее, скопирую строку в запрос, она работает. Вот так:
SELECT * FROM users u WHERE u.user_id IN (45,4932,20,19);
Кто-нибудь знает, почему это может быть проблемой?
Переменная привязки связывает значение a, в этом случае строка ’45, 4932,20,19 ‘. Вы можете использовать динамический SQL и конкатенацию, как было предложено Рэнди, но вам нужно быть очень осторожным, чтобы пользователь не смог изменить это значение, иначе у вас возникнет проблема с внедрением SQL.
Более безопасный маршрут заключается в том, чтобы поместить идентификаторы в коллекцию Apex в процессе PL/SQL:
declare
array apex_application_global.vc_arr2;
begin
array := apex_util.string_to_table (:P5_USER_ID_LIST, ',');
apex_collection.create_or_truncate_collection ('P5_ID_COLL');
apex_collection.add_members ('P5_ID_COLL', array);
end;
Затем измените свой запрос на:
SELECT * FROM users u WHERE u.user_id IN
(SELECT c001 FROM apex_collections
WHERE collection_name = 'P5_ID_COLL')
Более простым решением является использование instr:
SELECT * FROM users u
WHERE instr(',' || :P5_USER_ID_LIST ||',' ,',' || u.user_id|| ',', 1) !=0;
фокусы:
',' || :P5_USER_ID_LIST ||','
чтобы сделать вашу строку ,45,4932,20,19,
',' || u.user_id|| ','
т.е. ,32, и избегать выбора 32 в ,4932,
Я столкнулся с этой ситуацией несколько раз, и вот что я использовал:
SELECT *
FROM users u
WHERE ','||to_char(:P5_USER_ID_LIST)||',' like '%,'||to_char(u.user_id)||',%'
ive использовал подобный оператор, но вы должны быть немного осторожны с одним аспектом здесь: ваш элемент P5_USER_ID_LIST должен быть “, 45,4932,20,19”, так что, как это будет сравнивать с точным числом “, 45,” “.
При использовании этого, выбор не будет ошибочным, скажем: 5 с 15, 155, 55.
Попробуйте и дайте мне знать, как это происходит;)
Приветствия,
Alex
Создайте собственный запрос, а не используйте “createQuery/createNamedQuery”
Причина этого в том, что вы не можете просто привязать список в списке так, как хотите, и почти каждый делает эту ошибку хотя бы один раз, когда они изучают Oracle (и, вероятно, SQL!).
Когда вы связываете строку ’32, 64,128 ‘, она становится таким же, как:
select ...
from t
where t.c1 in ('32,64,128')
В Oracle это совершенно другое:
select ...
from t
where t.c1 in (32,64,128)
Первый пример имеет одно строковое значение в списке, а второе имеет 3 числа в списке. Причина, по которой вы получаете недопустимую ошибку числа, заключается в том, что Oracle пытается отличить строку ’32, 64,128 ‘от числа, которое она не может сделать из-за запятых в строке.
Вариант этого вопроса “как связать список” появился здесь несколько раз недавно.
В общем, и не прибегая к PLSQL, беспокоясь о SQL Injection или не привязывая запрос правильно, вы можете использовать этот трюк:
with bound_inlist
as
(
select
substr(txt,
instr (txt, ',', 1, level ) + 1,
instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 )
as token
from (select ','||:txt||',' txt from dual)
connect by level <= length(:txt)-length(replace(:txt,',',''))+1
)
select *
from bound_inlist a, users u
where a.token = u.id;
Если возможно, лучшей идеей может быть не сохранение ваших идентификаторов пользователей в csv! Поместите их в таблицу или не получив массив и т.д. Вы не можете привязать поле csv как число.
Пожалуйста, не используйте: WHERE ‘,’ || to_char (: P5_USER_ID_LIST) || ‘,’ like ‘%,’ || to_char (u.user_id) || ‘,%’, потому что вы заставите полную таблицу сканирование, хотя в таблице пользователей у вас может быть не так много, поэтому влияние будет низким, но с другими таблицами в корпоративной среде это проблема.
EDIT: я собрал script, чтобы продемонстрировать различия между методом регулярных выражений и подобным методом. Не только регулярное выражение, но и гораздо более надежное.
-- Create table
create table CSV_TEST
(
NUM NUMBER not null,
STR VARCHAR2(20)
);
create sequence csv_test_seq;
begin
for j in 1..10 loop
for i in 1..500000 loop
insert into csv_test( num, str ) values ( csv_test_seq.nextval, to_char( csv_test_seq.nextval ));
end loop;
commit;
end loop;
end;
/
-- Create/Recreate primary, unique and foreign key constraints
alter table CSV_TEST
add constraint CSV_TEST_PK primary key (NUM)
using index ;
alter table CSV_TEST
add constraint CSV_TEST_FK unique (STR)
using index;
select sysdate from dual;
select *
from csv_test t
where t.num in ( Select Regexp_Substr('100001, 100002, 100003 , 100004, 100005','[^,]+', 1, Level) From Dual
Connect By Regexp_Substr('100001, 100002,100003, 100004, 100005', '[^,]+', 1, Level) Is Not Null);
select sysdate from dual;
select *
from csv_test t
where ('%,' || '100001,100002, 100003, 100004 ,100005' || ',%') like '%,' || num || ',%';
select sysdate from dual;
select *
from csv_test t
where t.num in ( Select Regexp_Substr('100001, 100002, 100003 , 100004, 100005','[^,]+', 1, Level) From Dual
Connect By Regexp_Substr('100001, 100002,100003, 100004, 100005', '[^,]+', 1, Level) Is Not Null);
select sysdate from dual;
select *
from csv_test t
where ('%,' || '100001,100002, 100003, 100004 ,100005' || ',%') like '%,' || num || ',%';
select sysdate from dual;
drop table csv_test;
drop sequence csv_test_seq;
Решение от Тони Эндрюса работает для меня. Процесс должен быть добавлен в “Обработка страницы” → “После отправки” → “Процессы”.
Как вы Запоминание Идентификаторы пользователя, как String, так что вы можете легко соответствовать строки Использование Like как ниже
SELECT * FROM users u WHERE u.user_id LIKE '%'||(:P5_USER_ID_LIST)||'%'
Например
:P5_USER_ID_LIST = 45,4932,20,19
Ваш запрос обязательно вернет любой из 1 идентификатора пользователя, который соответствует таблице пользователей
Это, безусловно, решит вашу проблему, наслаждайтесь
вам нужно будет запустить это как динамический SQL.
создайте всю строку, затем запустите ее динамически.