I am trying to find this on internet but I can find it anywhere. I want to have Oracle stored procedure something like :
CREATE OR REPLACE PROCEDURE Read()
IS
BEGIN
SELECT * FROM Books;
END;
/
that will get me as result and array. So I want to retrieve in PHP array result of this oracle stored procedure. Can you please modify it? So when I get array in php I can easily fetch through the data? Thank you in advance!
I suspect you don't want to return an array. Instead, I suspect that you want your stored procedure to return a REF CURSOR.
CREATE OR REPLACE PROCEDURE Read( p_rc OUT SYS_REFCURSOR )
AS
BEGIN
OPEN p_rc
FOR SELECT *
FROM books;
END;
If you really want to return an array from PL/SQL (which is going to use vastly more PGA space on the server among other resources), you could do
CREATE TYPE book_typ
AS OBJECT (
<<list of columns in BOOKS>>
);
CREATE TYPE book_tbl
AS TABLE OF book_typ;
CREATE OR REPLACE PROCEDURE Read( p_arr OUT book_tbl )
AS
BEGIN
SELECT <<list of columns>>
BULK COLLECT INTO p_arr
FROM books;
END;
It would almost never make sense to structure code this way rather than returning a REF CURSOR. It will be much less efficient, use vastly more server resources making it much less scalable, etc. Additionally, there are a variety of ways to generate XML directly in PL/SQL.
OPEN p_rc AS to be more specific arount AS statement it says it's expecting .( or something like that and then transform cursor into array of data? So I don't waste a lot of resources? And this <<list of columns in BOOKS>> you mean I just put all columns separated. with ,? Thank youFOR not an AS in the OPEN. I corrected that.