Convert sql statement with function into Oracle stored procedure

Question

Colls, hello. Could anybody tell me how convert the next “unconventional” select into stored procedure.

SELECT * FROM TABLE
                (parallel_dump –- this is a pipelined function which helps to create a huge file using UTL
                    (
                    CURSOR(
                    SELECT /*+ PARALLEL(s,4)*/
                    to_clob(B1)||to_clob(B2)||to_clob(B3)||to_clob(B4) AS cvs  
FROM 
(select 
(A1||…||A200) as B1,
(A201||…||A400) as B2, 
(A401||…||A600) as B3,
(A601||…||A839) as B4 from 
(
Select blabla from Dual

Union all 


select * from anytable


union all

select blablaba from DUAL  

union all

select blabla from DUAL  

)

) s),
                    'filename',
                    'DIRECTORY_NAME'
                    )
                ) nt;

I can’t understand where and how use “into”. I’ve tried some variants but proc can’t be compiled.

What do you want to do with the selected data? What will the procedure do apart from the select - are you just trying to make a wrapper that will call the pipelined function and discard the results? Or one that will do the UTL_FILE operations using the selected data? — Alex Poole
– Alex Poole, Commented Aug 14, 2012 at 13:56
selected data is uploaded into file. procedure send data to function which upload it into txt file. — May12
– May12, Commented Aug 14, 2012 at 14:02

schurik · Accepted Answer · 2012-08-14 14:24:30Z

create or replace procedure my_proc 
IS
begin
for rec in (
SELECT                     *
FROM TABLE (parallel_dump –- this IS a pipelined FUNCTION which helps TO CREATE a huge file USING UTL ( CURSOR
  (SELECT
    /*+ PARALLEL(s,4)*/
    to_clob(B1)
    ||to_clob(B2)
    ||to_clob(B3)
    ||to_clob(B4) AS cvs
  FROM
    (SELECT (A1
      ||…
      ||A200) AS B1,
      (A201
      ||…
      ||A400) AS B2,
      (A401
      ||…
      ||A600) AS B3,
      (A601
      ||…
      ||A839) AS B4
    FROM
      ( SELECT blabla FROM Dual
      UNION ALL
      SELECT * FROM anytable
      UNION ALL
      SELECT blablaba FROM DUAL
      UNION ALL
      SELECT blabla FROM DUAL
      )
    ) s
  ), 'filename', 'DIRECTORY_NAME' ) ) nt
  )
  LOOP
   -- do whatever you want with the data for example:
   dbms_output.put_line('value of col1 ' || rec.col1);
  END LOOP;
END;
/

Collectives™ on Stack Overflow

Convert sql statement with function into Oracle stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related