+ Reply to Thread
Results 1 to 2 of 2

Thread: getting array using orcale

  1. #1
    Junior Member
    Join Date
    Jan 2012
    Posts
    31

    getting array using orcale

    I want to have Oracle stored procedure something like :
    Code:
    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?

  2. #2
    I suspect you don't want to return an array. Instead, I suspect that you want your stored procedure to return a REF CURSOR.
    Code:
    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
    Code:
    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.

+ Reply to Thread

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts