oracle sql in集合,SQL語句中不允許的本地集合類型——Oracle。

SET SERVEROUTPUT ON;

DECLARE

type r05_id_type is TABLE of r01_table.R01_ID%TYPE;

r01_ids r05_id_type;

BEGIN

SELECT r1.r01_ID as R01_ID

BULK COLLECT INTO r01_ids

FROM r01_table r1, r05_table r5

WHERE r1.r01_ID= r5.r05_R01_ID_FK

AND r5.r05_status='D' AND r5.r05_date_time_captured <= TRUNC(SYSDATE) - 1095

AND r1.r01_id NOT IN(select r01.r01_ID

FROM r01_table r01, r05_table r05

WHERE r05.r05_status !='D'

AND r01.r01_ID= r05.r05_R01_ID_FK);

dbms_output.put_line(r01_ids.COUNT);

FOR indx IN 1 .. r01_ids.COUNT

LOOP

dbms_output.put_line(r01_ids(indx));

insert all into r01_table_archived (R01_ID_TYPE,R01_IDENTITY_NUMBER,R01_PASSPORT_COUNTRY,R01_DATE_TIME_CAPTURED)

values(rr1_R01_ID_TYPE,rr1_R01_IDENTITY_NUMBER,rr1_R01_PASSPORT_COUNTRY,rr1_R01_DATE_TIME_CAPTURED)

select rr1_R01_ID_TYPE,rr1_R01_IDENTITY_NUMBER,rr1_R01_PASSPORT_COUNTRY,rr1_R01_DATE_TIME_CAPTURED

FROM (SELECT R01_ID_TYPE,R01_IDENTITY_NUMBER,R01_PASSPORT_COUNTRY,R01_DATE_TIME_CAPTURED

FROM r01_table

WHERE r01_id IN (r01_ids));

END LOOP;

END;

I am writing the above procedure in order to check some records between two tables and storing the matching ID (r01_ID) in an inner table call r01_ids. dbms_output.put_line(r01_ids(indx)) prints the correct values which indicates that the values are correctly inserting to r01_ids. but the issue happens when I try to insert the values to the r01_table_archived table within the LOOP.

我正在編寫上面的過程，以便檢查兩個表之間的一些記錄，並將匹配的ID (r01_ID)存儲在內部表中的r01_ids中。put_line(r01_ids(indx))打印出正確的值，這表明這些值正正確地插入到r01_ids中。但是當我試圖將值插入到循環中的r01_table_archived表中時，就會發生問題。

I am getting the below error

我得到了下面的錯誤

PLS-00642: local collection types not allowed in SQL statements

PLS-00642: SQL語句中不允許使用本地集合類型

Can't I get the values of r01_ids from the insert query like this? If I am wrong, someone please help me to find what's the issue and how to perform it in the right way.

難道我不能從這樣的insert查詢中獲取r01_id的值嗎?如果我是錯的，請有人幫助我找出問題所在，以及如何正確地執行。

1 个解决方案

#1

6

Until Oracle 11g, if you declare type under the scope of a PLSQL block, it cannot be called in SQL statement within the block and this is the reason you are getting this error:

在Oracle 11g之前，如果您在PLSQL塊的范圍內聲明類型，則不能在塊內的SQL語句中調用它，這是您得到這個錯誤的原因:

PLS-00642: local collection types not allowed in SQL statements

You can create a type outside the SCOPE of PLSQL block and then use it in SQL. See below:

您可以在PLSQL塊范圍之外創建一個類型，然后在SQL中使用它。見下文:

--Created a type of Number assuming ID is number

CREATE OR REPLACE TYPE R05_ID_TYPE IS TABLE OF NUMBER;

/

--Block

DECLARE

--TYPE R05_ID_TYPE IS TABLE OF R01_TABLE.R01_ID%TYPE;

R01_IDS R05_ID_TYPE;

BEGIN

SELECT R1.R01_ID AS R01_ID

BULK COLLECT INTO R01_IDS

FROM R01_TABLE R1,

R05_TABLE R5

WHERE R1.R01_ID = R5.R05_R01_ID_FK

AND R5.R05_STATUS = 'D'

AND R5.R05_DATE_TIME_CAPTURED <= TRUNC (SYSDATE) - 1095

AND R1.R01_ID NOT IN (

SELECT R01.R01_ID

FROM R01_TABLE R01,

R05_TABLE R05

WHERE R05.R05_STATUS != 'D'

AND R01.R01_ID = R05.R05_R01_ID_FK);

DBMS_OUTPUT.PUT_LINE (R01_IDS.COUNT);

FOR INDX IN 1 .. R01_IDS.COUNT

LOOP

DBMS_OUTPUT.PUT_LINE (R01_IDS (INDX) );

INSERT

INTO R01_TABLE_ARCHIVED

(R01_ID_TYPE,

R01_IDENTITY_NUMBER,

R01_PASSPORT_COUNTRY,

R01_DATE_TIME_CAPTURED)

SELECT RR1_R01_ID_TYPE,

RR1_R01_IDENTITY_NUMBER,

RR1_R01_PASSPORT_COUNTRY,

RR1_R01_DATE_TIME_CAPTURED

FROM (SELECT R01_ID_TYPE,

R01_IDENTITY_NUMBER,

R01_PASSPORT_COUNTRY,

R01_DATE_TIME_CAPTURED

FROM R01_TABLE

--Using the collection in SQL statement

WHERE R01_ID IN (Select column_value from table(R01_IDS) );

END LOOP;

COMMIT;

END;