How to include select * into Oracle procedure?
May be stupid topic, but... I have a select contents a function and would like to compile it as stored procedure:
CREATE OR REPLACE PROCEDURE PDATA.FILE_PARALLEL_DUMP IS BEGIN SELECT * FROM TABLE (parallel_dump ( CURSOR( SELECT to_clob(B1)||to_clob(B2)||to_clob(B3)||to_clob(B4) AS cvs FROM (select (A1...A839) as B4 from PDATA.FILE_TAB) s), 'PR', 'TEST_FOLDER' ) ) nt; END EXT_FILE_PARALLEL_DUMP; /
But during compilation I am reciving a mistake: PLS-00428: an INTO clause is expected in this SELECT statement.
I know than the structure of select in the stored procedure should be like "select A1, A2 into p_a1, a_A2 from...", but in case below (with using function) i have no idea how to write a correct code. May you help me? Thank you.
In PL/SQL, the result of a SELECT needs to go somewhere. Either your create a loop:
FOR r IN (SELECT ... your query ) LOOP -- do something with the row r END LOOP;
Or you use use a memory table:
SELECT * INTO mem_tab_instance FROM ....
Or then you need to approach your task in a different way.
Do you have prior experience with another database system such as SQL Server? In this aspect, these systems are very different.