CODEWITHSUNDEEP
sqloracle-databasefunctionstored-procedures
May12
May12

Reputation: 2520

Convert sql statement with function into Oracle stored procedure

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.

Upvotes: 0

Views: 1588

Answers (1)

schurik
schurik

Reputation: 7928

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;
/

Upvotes: 2

Related Questions