CODEWITHSUNDEEP
oracle-databasestored-procedures
Fab
Fab

Reputation: 1215

ORACLE stored procedure - Store query result

I have the following stored procedure:

CREATE OR REPLACE PROCEDURE SP 
(
   query IN VARCHAR2(200),
   CURSOR_ OUT SYS_REFCURSOR
)
AS
  row_ PROCESSED_DATA_OBJECT; 
  processed PROCESSED_DATA_TABLE;
BEGIN

       .....

END;

with

CREATE TYPE processed_data_obj AS OBJECT(
  id   INTEGER,
  value FLOAT
);
/

CREATE OR REPLACE TYPE processed_data_table AS TABLE OF processed_data_obj;
/

I call the stored procedure passing the query to be executed as input parameter. The query is something like that:

SELECT A,B FROM TABLE WHERE

where A,B and TABLE are not fixed (defined at runtime during java program execution), so I don't know their values in advance.

How could I fetch/store each returned row in my structure?

processed PROCESSED_DATA_TABLE;

Thanks

Upvotes: 0

Views: 278

Answers (1)

Jair Hernandez
Jair Hernandez

Reputation: 494

This is one way you can process a dynamically generated query into a user defined type. Note that, in order for this to work, the structure of your query (columns) must match the data type structure of your type (attributes) otherwise you're in for trouble.

CREATE TYPE processed_data_obj AS OBJECT(
  ID   INTEGER,
  VALUE FLOAT,

  constructor FUNCTION processed_data_obj RETURN self AS result
);
/
CREATE OR REPLACE TYPE BODY processed_data_obj IS
    constructor FUNCTION processed_data_obj RETURN self AS result IS
    BEGIN
        RETURN;
    END;
END;
/
CREATE OR REPLACE TYPE processed_data_table AS TABLE OF processed_data_obj;
/
CREATE OR REPLACE PROCEDURE sp (
   p_query  IN VARCHAR2
) AS
  cursor_       sys_refcursor;
  processed     processed_data_table := processed_data_table();
BEGIN
    OPEN cursor_ FOR p_query;
    loop
        processed.EXTEND;
        processed(processed.count) := processed_data_obj();
        fetch cursor_ INTO processed(processed.count).ID, processed(processed.count).VALUE;
        exit WHEN cursor_%notfound;
        dbms_output.put_line(processed(processed.count).ID||' '||processed(processed.count).VALUE);-- at this point do as you please with your data.
    END loop;
    CLOSE cursor_; -- always close cursor ;)
    processed.TRIM; -- or processed.DELETE(processed.count);
END sp;

I noticed that, originally, you did put CURSOR_ as an output parameter in your stored procedure, if that is still your goal, you can create your procedure as:

CREATE OR REPLACE PROCEDURE sp (
    p_query  IN VARCHAR2,
    cursor_  out sys_refcursor
) AS
    processed     processed_data_table := processed_data_table();
BEGIN
    OPEN cursor_ FOR p_query;
    loop
        processed.EXTEND;
        processed(processed.count) := processed_data_obj();
        fetch cursor_ INTO processed(processed.count).ID, processed(processed.count).VALUE;
        exit WHEN cursor_%notfound;
        dbms_output.put_line(processed(processed.count).ID||' '||processed(processed.count).VALUE);-- at this point do as you please with your data.
    END loop;
    -- cursor remains open
    processed.TRIM; -- or processed.DELETE(processed.count);
END sp;

In this case just be conscious about handling your cursor properly and always close it when you're done with it.

Upvotes: 1

Related Questions