Home » SQL & PL/SQL » SQL & PL/SQL » Pass cursor to Procedure (12.1)
Pass cursor to Procedure [message #682085] Sun, 04 October 2020 14:19 Go to next message
ora9a
Messages: 42
Registered: June 2010
Member
Hi,

I have a requirement to take in a list of values passed in from a logic app in Azure, and use that list in a where clause, and return the results.
The values passed in will be codes such as 'EP-L200-20044-21', 'EL-B910-20077-21'


CREATE TABLE IA_TEST (COL1 VARCHAR2(20));

CREATE OR REPLACE PROCEDURE IA_TEST_PROC (P_LIST IN SYS_REFCURSOR, P_OUTPUT OUT SYS_REFCURSOR ) AS 

BEGIN

      OPEN 
      
      p_output

  FOR   
   
  SELECT COL1
  FROM IA_TEST
  WHERE COL1 IN (P_LIST);

END;


This proc will not work, but what is the best way to achieve this?

thanks guys.
Re: Pass cursor to Procedure [message #682086 is a reply to message #682085] Sun, 04 October 2020 14:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
The values passed in will be codes such as 'EP-L200-20044-21', 'EL-B910-20077-21'
But your P_LIST parameter is not a list it is a ref cursor.
So what is the actual type for the input parameter? A string? A collection? A ref cursor? Something else?

Re: Pass cursor to Procedure [message #682087 is a reply to message #682086] Sun, 04 October 2020 15:02 Go to previous messageGo to next message
ora9a
Messages: 42
Registered: June 2010
Member
Not sure how Azure sends it, assuming it is a list.
Re: Pass cursor to Procedure [message #682089 is a reply to message #682087] Mon, 05 October 2020 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So first ask Azure guys.

Re: Pass cursor to Procedure [message #682095 is a reply to message #682089] Mon, 05 October 2020 04:37 Go to previous messageGo to next message
ora9a
Messages: 42
Registered: June 2010
Member
It's coming as associate arrays.
Re: Pass cursor to Procedure [message #682096 is a reply to message #682095] Mon, 05 October 2020 05:06 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post an example of how the procedure is called with the definition of the used types.

[Updated on: Mon, 05 October 2020 05:07]

Report message to a moderator

Previous Topic: Group pivot column
Next Topic: extract string and compare
Goto Forum:
  


Current Time: Fri Mar 29 06:00:48 CDT 2024