CALLING ORACLE SELECT PROCEDURE
I am new to oracle and its syntax for creating stored procedures.
I understand you have to declare an out variable if you want to return a collection of rows to the user. I have a procedure that has compiled succesfully, even though I am not entirely sure it works properly, as I am not able to call the procedure.
How do you do that? How do you pass in a variable that is supposed to hold all of the values that will be returned? I am very confused about this, it doesn´t seem as intuitive as creating procedures with SQL Server.
Here is my store proc:
PROCEDURE SP_SELECT_CHAT_DEV (
OPPID IN NUMBER,
Result_Cursor OUT Result_Set_Pkg.Result_Cursor
INSERT INTO TEMP_CHAT
(SELECT a.rtcm_company,c.ownerorgid,a.rtcm_message,a.rtcm_ address FROM tn_rtc_chat_message@dblink_dadb_dev a
LEFT JOIN userlist@dblink_dadb_dev b ON b.usernum=a.rtcm_recipient
LEFT JOIN tbl_alternativeid@dblink_tpd_dev c ON c.altid=b.userid
group by a.rtcm_time,a.rtcm_message,a.rtcm_company,c.ownero rgid,a.rtcm_address);
WHERE ownerorgid IS NULL;
OPEN Result_Cursor FOR
SELECT * FROM TEMP_CHAT;
How can I call this store procedure from inside sqlnavigator?