How to supply values to a PL/SQL loop from a select query

Question

Hi friends I have a perl script which interacts with oracle Db through sqlplus. Here I am running two queries. first query fetches the serial numbers for a specific date from view and writes to a file.

SELECT DISTINCT serialnumber FROM VOUCHERHISTORYCLIENTV WHERE at LIKE '&1';    (where &1 = $date)

Then perl loop reads this file, takes serial number line by line & runs the below query, writes the data to another file which then processed by the following perl code. Now these serial numbers are too huge so every iteration of loop connects to sqlplus runs the query & generates the output & then disconnects. That is why it is taking too much time.

SELECT * FROM VSOWNERUPE07.VOUCHERHISTORYCLIENTV WHERE serialnumber = '&1';    (where &1 = $serialnumber)

Is there any way where I don't need to connect & disconnect sqlplus session again & again? what I want in the end is result of the second query for each iteration appended on an unix file..so that my perl script could format them. I guess pl/sql loop can do that...but I have never worked on pl/sql...could you please help me here?

PS: I can't use DBD::oracle here as I am facing too many issues while installing this module on solaris mahine & as this server is 3rd party server so I can't make any chnages on this

Update: 1

I tried below two procedures & run for specific date for which table has 6 million records. but query kept running & didn't produce any output even after 2 days...

Procedure# 1:

  DECLARE

    CURSOR CUR1 IS
    SELECT DISTINCT serialnumber FROM VSOWNERUPE07.VOUCHERHISTORYCLIENTV WHERE at LIKE '&1';

    CURSOR CUR2(p_ser_num NUMBER) IS
    SELECT serialnumber, state, at as time1, operatorid  FROM VSOWNERUPE07.VOUCHERHISTORYCLIENTV WHERE serialnumber = p_ser_num;

    BEGIN

    FOR l_cur1 IN CUR1
    LOOP
      NULL;
      FOR l_cur2 IN CUR2(l_cur1.serialnumber)
      LOOP

       DBMS_OUTPUT.PUT_LINE(l_cur2.serialnumber||' '||l_cur2.state ||' '||l_cur2.time1 ||' '||l_cur2.operatorid );

      END LOOP;
    END LOOP;
    END;

    /

    quit;

Procedure# 2:

DECLARE

CURSOR CUR1 IS
SELECT DISTINCT serialnumber FROM VSOWNERUPE07.VOUCHERHISTORYCLIENTV WHERE at LIKE '&1';

TYPE t_tab1 IS TABLE OF CUR1%ROWTYPE;

l_tab1 t_tab1;

CURSOR CUR2(p_ser_num NUMBER) IS
SELECT serialnumber, state, at as time1, operatorid  FROM VSOWNERUPE07.VOUCHERHISTORYCLIENTV WHERE serialnumber = p_ser_num;

TYPE t_tab2 IS TABLE OF CUR2%ROWTYPE;

l_tab2 t_tab2;


BEGIN

  OPEN CUR1;
  LOOP
   FETCH CUR1 BULK COLLECT INTO l_tab1;
   EXIT WHEN CUR1%NOTFOUND;
  END LOOP;
  CLOSE CUR1;
 FOR i in 1..l_tab1.COUNT
   LOOP
    OPEN CUR2(l_tab1(i).serialnumber);
       LOOP
          FETCH CUR2 BULK COLLECT INTO l_tab2;
          EXIT WHEN CUR2%NOTFOUND;
       END LOOP;
    CLOSE CUR2;
       for j in 1..l_tab2.COUNT
            LOOP
              DBMS_OUTPUT.PUT_LINE(l_tab2(j).serialnumber||' '||l_tab2(j).state ||' '||l_tab2(j).time1 ||' '||l_tab2(j).operatorid );
            END LOOP;
   END LOOP;

END;

/

quit;

Can I improve the above procedures or there is any other way which can do the job for us? Please help.

Answer 1

For once, you can just replace the 2 queries with this single query:

SELECT * FROM VSOWNERUPE07.VOUCHERHISTORYCLIENTV 
   WHERE serialnumber IN (SELECT DISTINCT serialnumber FROM  
                           VOUCHERHISTORYCLIENTV WHERE at LIKE '&1');

And write to output to your final file.

You see any issues with this?

UPDATE1

First, breaking it into 2 different queries is almost never a better solution.SQL context switch will kill you.

Build index on serialnumber and at columns of the two tables respectively and try with the single query.
If even then its slow you can try UPDATE2.

UPDATE2

If too many records is the problem,, then use Bulk Processing. The process is a bit tedious, but is designed to handle these kind of situations.

Some examples how to do Bulk Processing are here, here and here.

For this to work, you will probably have to write a PLSQL procedure.
Inside the procedure, bulk collect all the data into a , say, varray from your first query. now run the second select query using this varray and collect your data.

Next
You can put these serial numbers into, say another v array and write it to a file
or
create a new single column table and write it to this new table. then write it to a file/query this new table from perl separately.

Note1: If you are using a separate table, truncate it after each run; and build index on it
Note2: If you are clueless of what I am talking about, check out those links first.