Локальные типы коллекций не допускаются в инструкциях sql - Instruktiv.top - инструкции по применению и эксплуатации на

I am new to programming in ORACLE and I am trying to compare a table column value to a passed in array and I am having a rather frustrating time in doing so.

Here is the Type Declaration from the package head.

TYPE T_STRING_ARRAY IS TABLE OF VARCHAR2(5);

and here is the the function that is using it.

create or replace PACKAGE BODY TEST_PACK IS 

  FUNCTION TEST_LOG_FN 
  (
        PI_START_DATE IN VARCHAR2,
        PI_END_DATE IN VARCHAR2, 
        PI_LOG_TYPE IN T_STRING_ARRAY
  )
   RETURN T_REF_CURSOR
    AS  
    PO_RESULT T_REF_CURSOR;

  BEGIN   


  OPEN PO_RESULT FOR
  SELECT
        EL.ENTRY_BASE_LOG_ID,
          EL.APP_NAME,
          EL.APP_MODULE,
          EL.CREATION_DATE,
          EL.APP_STATUS,
          EL.LOG_TYPE
    FROM 
          LG_ENTRY_BASE_LOG EL
  WHERE     
          CREATION_DATE > PI_START_DATE AND
          CREATION_DATE  < PI_END_DATE AND              
         (EL.LOG_TYPE IN PI_LOG_TYPE OR PI_LOG_TYPE = NULL);

  RETURN 
      PO_RESULT;

EXCEPTION
      WHEN NO_DATA_FOUND THEN 
           RETURN NULL;
       END TEST_LOG_FN;
END;

The error I am getting is PLS-00642: local collection types not allowed in SQL statements. I have read online

«To avoid the PLS-00642, the collection will need to be defined at the schema level; therefore, you would need to define the varray table as a real table, using Oracle DDL with the CREATE TYPE syntax. »

http://www.dba-oracle.com/t_pls_00642_local_collection_types_not_allowed_in_sql_statement.htm

I am not sure how to do that nor have I found any references online that I could use. Can someone help me with that? If someone knows an easier way to see if a string exists in an array, that is a perfectly acceptable answer as well.

Источник

Форумы

/ Oracle

[игнор отключен]

[закрыт для гостей]

Не получается выбрать значеия из коллекции через from table(…)

12 сообщений из 12, страница 1 из 1

Не получается выбрать значеия из коллекции через from table(…)

#40137424

Приветствую, ребята! На простом примере пытаюсь загрузить данные из таблички в коллекцию, потом сделать с неё select, но ругается на тип: PLS-00642 local collection types not allowed in sql statement.

Вот что, делаю:

1) Создаю табличку

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

CREATE TABLE TEST
(
  ISN    NUMBER                                 NOT NULL,
  CID    NUMBER                                 NOT NULL,
  RID    NUMBER                                 NOT NULL,
  VALUE  NUMBER,
  TYPE   VARCHAR2(1 BYTE),
  DATER  DATE
);


insert into TEST (isn, cid, rid, value, type, dater)
values ('234', '309', '359', '79', 'D', to_date('31.12.2021', 'dd.mm.yyyy'));

insert into TEST (isn, cid, rid, value, type, dater)
values ('274', '309', '359', '47', 'D', to_date('31.12.2021', 'dd.mm.yyyy'));

insert into TEST (isn, cid, rid, value, type, dater)
values ('294', '309', '333', '22', 'D', to_date('31.12.2021', 'dd.mm.yyyy'));
commit;

2) Затем пытаюсь выполнить такой скрипт:

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

DECLARE

 TYPE test_values_set IS TABLE OF test%ROWTYPE;
 underpaid test_values_set;

BEGIN

SELECT * BULK COLLECT 
  INTO underpaid 
  FROM test
 WHERE dater = to_date('31.12.2021', 'dd.mm.yyyy');

select * from table(underpaid);

 end;

Подскажите, что не так делаю?

…

Рейтинг:

0
/ 0

03.03.2022, 19:10

| Ответить

| Цитировать

| Написать

Не получается выбрать значеия из коллекции через from table(…)

#40137426

Leonid Kudryavtsev

Участник

Сообщения: 9 300

Рейтинг:
0
/ 0

PLS-00642 local collection types not allowed in sql statement.
…
Подскажите, что не так делаю?

Нельзя использоваться PL/SQL тип, тип нужно объявлять на уровне базы CREATE TYPE.

AFAIK

…

Рейтинг:

0
/ 0

03.03.2022, 20:13

| Ответить

| Цитировать

| Написать

Не получается выбрать значеия из коллекции через from table(…)

#40137431

mnbvcx

Гость

PLS-00642 local collection types not allowed in sql statement.
…
Подскажите, что не так делаю?

Нельзя использоваться PL/SQL тип, тип нужно объявлять на уровне базы CREATE TYPE.

AFAIK

А если на уровне пакета?

…

Рейтинг:

0
/ 0

03.03.2022, 20:44

| Ответить

| Цитировать

| Написать

Не получается выбрать значеия из коллекции через from table(…)

#40137445

ASNexus

Гость

пропущено…

Нельзя использоваться PL/SQL тип, тип нужно объявлять на уровне базы CREATE TYPE.

AFAIK

А если на уровне пакета?

Тип, объявленный в спецификации пакета, можно использовать в SQL-запросах SELECT, в запросах DML — нельзя (возможно, в каких-то новых версиях это изменилось, не знаю).

Объявленный в теле пакета — нельзя ни в каких в SQL-запросах.

…

Рейтинг:

0
/ 0

03.03.2022, 22:19

| Ответить

| Цитировать

| Написать

Не получается выбрать значеия из коллекции через from table(…)

#40137452

mnbvcx

Гость

ASNexus, ну я это и имел в виду, что не обязательно на уровне схемы тип объявлять.

…

Рейтинг:

0
/ 0

03.03.2022, 22:41

| Ответить

| Цитировать

| Написать

Не получается выбрать значеия из коллекции через from table(…)

#40137461

oragraf

Участник

Откуда: Moscow

Сообщения: 1 206

Рейтинг:
0
/ 0

select * from table(underpaid);

Подскажите, что не так делаю?

Конкретно тут ты пытаешься выбрать данные «в никуда». Если ты их хочешь просто посмотреть в целях тестирования, то напиши цикл по массиву и выведи в консоль через dbms_output

…

Рейтинг:

0
/ 0

03.03.2022, 23:43

| Ответить

| Цитировать

| Написать

Не получается выбрать значеия из коллекции через from table(…)

#40137509

zorlo

Гость

select * from table(underpaid);

Подскажите, что не так делаю?

Да, это понятно. Я этот пример просто написал, чтобы разобраться почему не работает обращение коллекции через table().
Идея была в том, чтобы использовать в коллекцию в имеющем запросе в джойне (вместо истенной таблицы), который собирается динамически, записывается в переменную, а потом выполняется.

Но суть в том, что, хотелось бы, чтобы выполнился подобный кусок в процедуре.

…

Рейтинг:

0
/ 0

04.03.2022, 10:12

| Ответить

| Цитировать

| Написать

Не получается выбрать значеия из коллекции через from table(…)

#40137515

zorlo

Гость

zorlo,

Дополню, чего хотел бы пока, чтобы отрабатывало без ошибок.

Создаём ещё табличку

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

CREATE TABLE TEST1
(
  id    NUMBER                                 NOT NULL,
  TYPER   VARCHAR2(1 BYTE)
);

insert into TEST1 (id, TYPER)
values('333', 'T');
insert into TEST1 (id, TYPER)
values('359', 'R');
commit;

И вот, что хотел бы, чтобы заработало:

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.

DECLARE

 TYPE test_values_set IS TABLE OF test%ROWTYPE;
 underpaid test_values_set;

BEGIN

SELECT * BULK COLLECT 
  INTO underpaid 
  FROM test
 WHERE dater = to_date('31.12.2021', 'dd.mm.yyyy');

SELECT value, type from (
  select value, type FROM table(underpaid) v1 inner join test v2
  on v1.id = v2.rid
 );

 end;

…

Рейтинг:

0
/ 0

04.03.2022, 10:22

| Ответить

| Цитировать

| Написать

Не получается выбрать значеия из коллекции через from table(…)

#40137518

zorlo

Гость

zorlo,

Тип можно объявить только на уровне схемы? На уровне пакета нельзя?
Как правильно это сделать?
Вот так не срабатывает

CREATE OR REPLACE TYPE test_values_set IS TABLE OF test%ROWTYPE;

…

Рейтинг:

0
/ 0

04.03.2022, 10:29

| Ответить

| Цитировать

| Написать

Не получается выбрать значеия из коллекции через from table(…)

#40137543

oragraf

Участник

Откуда: Moscow

Сообщения: 1 206

Рейтинг:
0
/ 0

zorlo, не тупи

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

declare
    type tt is table of dual%rowtype;
    t tt;
    i pls_integer;
begin
    select * bulk collect into t from (select * from dual union all select * from dual);

    i := t.first;
    while (i is not null)
    loop
        dbms_output.put_line(t(i).dummy);
        i := t.next(i);
    end loop;

end;

…

Рейтинг:

0
/ 0

04.03.2022, 12:44

| Ответить

| Цитировать

| Написать

Не получается выбрать значеия из коллекции через from table(…)

#40137550

zorlo

Гость

oragraf,

Да это понятно, как вывести элементы.
Но мне нужно обращение к коллекции через select

…

Рейтинг:

0
/ 0

04.03.2022, 13:32

| Ответить

| Цитировать

| Написать

Не получается выбрать значеия из коллекции через from table(…)

#40137617

Кроик Семён

Участник

Откуда: СПб —> Dortmund

Сообщения: 7 039

Рейтинг:
0
/ 0

zorlo,

попробуйте с CAST:

select * from TABLE(CAST(underpaid as test_values_set))

…

Рейтинг:

0
/ 0

04.03.2022, 18:07

| Ответить

| Цитировать

| Написать

12 сообщений из 12, страница 1 из 1

Форумы

/ Oracle

[игнор отключен]

[закрыт для гостей]

Не получается выбрать значеия из коллекции через from table(…)

Источник

Для примера определим пакет:

create or replace package sample_pkg is
type myRec is record(
id number,
name varchar2(50));
type myRecTbl is table of myRec;
type crs is ref cursor;
procedure test;
end sample_pkg;

Если в коде захочется сделать например так

function test_crs return crs is
myTab myRecTbl := myRecTbl();
p_id number;
p_name varchar2(50);
i number := 1;
res crs;

begin

for cr in (select * from sample_table) loop
myTab.extend();
myTab(i).id := cr.id;
myTab(i).name := cr.name;
i := i + 1;
end loop;

for x in (select * from table(myTab)) loop
dbms_output.put_line(x.name);
end loop;

return crs;

end test_crs;

будет ошибка Error: PLS-00642: local collection types not allowed in SQL statements, так как для использования в SQL тип доложен быть определен на уровне БД (схемы) а не в пакете.

Источник

AND error_code = ‘PLS-00642’

База данных: 12c Выпуск 1
Код ошибки: PLS-00642
Описание: local collection types not allowed in SQL statements
Причина: В выражении SQL был использован локально определенный (т.е. не на уровне схемы) тип коллекции. Тип должен быть определен в схеме, чтобы быть принятым в операторе SQL.
Действие: Определите тип коллекции в вашей схеме, а не внутри подпрограммы PL / SQL.

База данных: 12c Выпуск 1
Код ошибки: PLS-00642
Описание: local collection types not allowed in SQL statements
Причина: A locally-defined (i.e. not schema level) collection type was used in a SQL statement. The type must be defined in a schema to be accepted in a SQL statement.
Действие: Define the collection type in your schema, not inside a PL/SQL subprogram.

База данных: 10g Выпуск 1
Код ошибки: PLS-00642
Описание: local collection types not allowed in SQL statements
Причина: В выражении SQL был использован локально определенный (т.е. не на уровне схемы) тип коллекции. Тип должен быть определен в схеме, чтобы быть принятым в операторе SQL.
Действие: Определите тип коллекции в вашей схеме, а не внутри подпрограммы PL / SQL.

База данных: 10g Выпуск 2
Код ошибки: PLS-00642
Описание: local collection types not allowed in SQL statements
Причина: В выражении SQL был использован локально определенный (т.е. не на уровне схемы) тип коллекции. Тип должен быть определен в схеме, чтобы быть принятым в операторе SQL.
Действие: Определите тип коллекции в вашей схеме, а не внутри подпрограммы PL / SQL.

База данных: 11g Выпуск 1
Код ошибки: PLS-00642
Описание: local collection types not allowed in SQL statements
Причина: В выражении SQL был использован локально определенный (т.е. не на уровне схемы) тип коллекции. Тип должен быть определен в схеме, чтобы быть принятым в операторе SQL.
Действие: Определите тип коллекции в вашей схеме, а не внутри подпрограммы PL / SQL.

База данных: 11g Выпуск 2
Код ошибки: PLS-00642
Описание: local collection types not allowed in SQL statements
Причина: В выражении SQL был использован локально определенный (т.е. не на уровне схемы) тип коллекции. Тип должен быть определен в схеме, чтобы быть принятым в операторе SQL.
Действие: Определите тип коллекции в вашей схеме, а не внутри подпрограммы PL / SQL.

База данных: 12c Выпуск 2
Код ошибки: PLS-00642
Описание: local collection types not allowed in SQL statements
Причина: В выражении SQL был использован локально определенный (т.е. не на уровне схемы) тип коллекции. Тип должен быть определен в схеме, чтобы быть принятым в операторе SQL.
Действие: Определите тип коллекции в вашей схеме, а не внутри подпрограммы PL / SQL.

База данных: 18c Выпуск 1
Код ошибки: PLS-00642
Описание: local collection types not allowed in SQL statements
Причина: В выражении SQL был использован локально определенный (т.е. не на уровне схемы) тип коллекции. Тип должен быть определен в схеме, чтобы быть принятым в операторе SQL.
Действие: Определите тип коллекции в вашей схеме, а не внутри подпрограммы PL / SQL.

База данных: 19c Выпуск 1
Код ошибки: PLS-00642
Описание: local collection types not allowed in SQL statements
Причина: В выражении SQL был использован локально определенный (т.е. не на уровне схемы) тип коллекции. Тип должен быть определен в схеме, чтобы быть принятым в операторе SQL.
Действие: Определите тип коллекции в вашей схеме, а не внутри подпрограммы PL / SQL.

Источник