I have a table A(a1,a2,a3);
I want to write a procedure like this :
CREATE OR REPLACE PROCEDURE B
AS
BEGIN
.........
..........
SELECT * FROM A;
END
so that when I do EXECUTE B;
It should output table A
as happens in select * from A;
See I tried with the other answers but it was not working for me that is why i posted this question
SQL Server (which I believe you indicated you were coming from in a different question) is different than Oracle. You can't just have a procedure that executes a query.
You could define your procedure to have an OUT parameter of type SYS_REFCURSOR.
CREATE OR REPLACE PROCEDURE b( p_rc OUT SYS_REFCURSOR )
AS
BEGIN
OPEN p_rc
FOR SELECT *
FROM a;
END;
The caller of your procedure would then need to know how to fetch from that ref cursor and do something meaningful with the results. If you are using SQL*Plus
SQL> variable rc refcursor;
SQL> exec b( :rc );
SQL> print rc
If you are using something other than SQL*Plus, the code would likely be different.
Alternately, your procedure could return a PL/SQL collection. Or it could be a pipelined table function rather than a procedure in which case you could use it in the FROM clause of a query.
I was searching for posts to see a way to output the data from oracle function for my ssis package, so thought i will share the info on working with pipeline in oracle to output like table
--create table type object to define format of the rows
CREATE OR REPLACE TYPE OUTPUT_TABLE_TYPE
IS OBJECT (
COL1 VARCHAR(100),
COL2 NUMBER
);
--create table type based on above object
CREATE OR REPLACE TYPE OUTPUT_TABLE
AS TABLE OF OUTPUT_TABLE_TYPE
CREATE OR REPLACE FUNCTION FN_OUT_TABLE (INPARAM1 IN NUMBER)
RETURN OUTPUT_TABLE PIPELINED
IS
BEGIN
FOR RECORD_OUTPUT IN (
SELECT * FROM (
SELECT CAST('OUTPUT SAM1' AS VARCHAR(100)) AS COL1,CAST( 1 AS NUMBER) AS COL2 FROM DUAL
UNION ALL
SELECT CAST('OUTPUT SAM2' AS VARCHAR(100)) AS COL1,CAST( 2 AS NUMBER) AS COL2 FROM DUAL
) SAM_TEMP -- replace this sub query with your query
WHERE COL2=INPARAM1
)
LOOP
PIPE ROW (OUTPUT_TABLE_TYPE(RECORD_OUTPUT.COL1,RECORD_OUTPUT.COL2));
END LOOP;
END;
To get the output we need to run below select statement
SELECT * FROM TABLE(FN_OUT_TABLE(2));
find more info @ http://sqlbisam.blogspot.com/2013/12/output-table-or-multiple-rows-from-stored-procedure-function.html
