How do I store values from a single column into a single variable in oracle?

Question

The below is my code:

SELECT
      /*+ parallel (sales 8) */     
      COUNT(1)
    INTO v_datacount_actualtable
    FROM sales
    WHERE processingunitseq=38
    AND (compensationdate BETWEEN TO_DATE(v_startdate,'DD-MON-YYYY') AND TO_DATE(v_enddate,'DD-MON-YYYY')
    OR eventtypeseq IN
      (SELECT EVENTTYPESEQ FROM EPS_FRS_PAYMENTS
      ));

SELECT EVENTTYPESEQ FROM EPS_FRS_PAYMENTS will return 4 values. So I cannot do a simple SELECT INTO.

I want to store the result of SELECT EVENTTYPESEQ FROM EPS_FRS_PAYMENTS into a variable and make the code something like below, so that the subquery is not executed each time.

SELECT
      /*+ parallel (sales 8) */     
      COUNT(1)
    INTO v_datacount_actualtable
    FROM sales
    WHERE processingunitseq=38
    AND (compensationdate BETWEEN TO_DATE(v_startdate,'DD-MON-YYYY') AND TO_DATE(v_enddate,'DD-MON-YYYY')
    OR eventtypeseq = v_frseventpeseqs );

SELECT EVENTTYPESEQ FROM EPS_FRS_PAYMENTS returns four values:

1,
2,
3,
4,

I do not want to hardcode these values in the code as it is a bad practice. Need it in a variable like v_frseventypeseqs.

How can I do that - array/ collection/records/bulk collect into?

Yes a collection with `bulk select into` is the way to go – Oct 17 '19 at 07:33 — , Oct 17 '19 at 07:33

Rustam Pulatov · Answer 1 · 2019-10-17T07:49:04.250

Create type if it isn't exists

CREATE OR REPLACE 
TYPE t_number1 AS TABLE OF NUMBER(8,0)

it's array of number

and using in PL/SQL

declare
v_frseventpeseqs t_number1;
begin
    SELECT EVENTTYPESEQ bulk collect 
    into v_frseventpeseqs 
    FROM EPS_FRS_PAYMENTS;

    for i in (select t.column_value id from table(v_frseventpeseqs) t)
    loop
        dbms_output.put_line(i.id );
    end loop;

end;

only using statement in (select t.column_value from table(v_frseventpeseqs) t)

score 0 · Answer 2 · answered Oct 17 '19 at 08:13

You can use the WITH clause. The advantage is that repeated references to a subquery may be more efficient as the data is easily retrieved from a temporary table, rather than being requeried by each reference.

WITH eventtypeseq_query AS 
     (SELECT EVENTTYPESEQ 
      FROM EPS_FRS_PAYMENTS)
SELECT
       /*+ parallel (sales 8) */   
       COUNT(1)
INTO v_datacount_actualtable
FROM sales
WHERE processingunitseq=38
  AND (compensationdate BETWEEN TO_DATE(v_startdate,'DD-MON-YYYY') AND TO_DATE(v_enddate,'DD-MON-YYYY')
        OR eventtypeseq IN eventtypeseq_query);

You can find more information here: https://oracle-base.com/articles/misc/with-clause

score 0 · Answer 3 · answered Oct 17 '19 at 09:55

0

Since

SELECT EVENTTYPESEQ FROM EPS_FRS_PAYMENTS

is not correlated you don't need any "optimization" here - Oracle will run this subquery only once.

answered Oct 17 '19 at 09:55

Leonid

108
1
9

score 0 · Answer 4 · edited Nov 18 '19 at 22:27

Using EXISTS is always better in terms of performance as compared to using IN. Also, if you are writing this query to run regularly in LIVE environment, it is suggested not to use HINTs like /*+ parallel */. HINTs should be used only for ad-hoc queries.

I have re-written your query below using EXISTS:

SELECT
    /*+ parallel (sales 8) */     
    COUNT(1)
    INTO v_datacount_actualtable
    FROM sales sl
    WHERE processingunitseq=38
    AND (compensationdate BETWEEN TO_DATE(v_startdate,'DD-MON-YYYY') AND TO_DATE(v_enddate,'DD-MON-YYYY')
    OR EXISTS ( SELECT 1 from EPS_FRS_PAYMENTS efp where s.eventtypeseq = efp.eventtypeseq);

How do I store values from a single column into a single variable in oracle?

4 Answers4