0

I have a table with ID's and I want to pass these ID's as an input to an API to fetch the details. The API URL looks something similar to this "https://xxx/yyy/id". Currently, I am able to pass only one ID from Table1 to API and get the out but I want it to be a recurring process or pass all the ID from TABLE1 to API and I don't know how to do it.

If it was a 2 TABLES then I can use IN function and get all the ID from TABLE1 and pass it to TABLE2, since it is an API, I am having trouble

NOTE:- I am working on TIBCO.

PROCEDURE Test(
IN rd1 VARCHAR(255), 
OUT result CURSOR(
    Id VARCHAR(255), 
StartTime VARCHAR(255), 
EndTime VARCHAR(255)))
BEGIN
    DECLARE prog_id VARCHAR(255);
    SET prog_id = (Select Id FROM TABLE1(rd1));
    OPEN result FOR SELECT 
            API.Id,
            API.StartTime,
            API.EndTime
                FROM TABLE1(rd1) TAB1 INNER JOIN 
                 API(prog_id) API  ON TAB1.Id = API.Id;
END;

Actual Result: Only one ID is passed.

Expected Result: A recurring process where it passes all the ID's from TABLE1 to TABLE2

Nik
  • 155
  • 1
  • 10
  • 1
    You don't seem to be passing anything, and I'm not sure why you have `prog_id` at all. I think you've simplified this too much; can you include a more complete [mre]? I think I might know what you mean, but you need a schema-level collection type, if that gives you somewhere to start. [This sort of thing maybe?](https://stackoverflow.com/a/43179008/266304) – Alex Poole Jul 26 '19 at 15:47
  • @AlexPoole Just modified the description hope that helps, please let me know if you need more info. – Nik Jul 26 '19 at 16:17
  • Your `DECLARE` and `SET` statements look to be invalid syntax-- it looks like SQL Server syntax and your procedure declaration doesn't look to be valid. Are you sure your stored procedure compiles? – Justin Cave Jul 26 '19 at 18:43
  • @JustinCave Yes it is working but I get only one row. I am using a Data Virtualization tool called TIBCO. But in my took I dont have "Create" I just added it here but the set and everything is working fine. My only problem is I am not able to put it in a loop. I dont know how to do it on oracle – Nik Jul 26 '19 at 19:15
  • So is the procedure you posted a TIBCO procedure? Because it's not a syntactically valid PL/SQL procedure. – Justin Cave Jul 26 '19 at 19:18
  • @JustinCave Yes its a TIBCO procedure – Nik Jul 26 '19 at 19:51
  • 2
    OK. So then the problem is how to write a TIBCO procedure that does what you want not how to write an Oracle PL/SQL procedure that does what you want? That's a substantially different question. – Justin Cave Jul 26 '19 at 19:54
  • @JustinCave My bad will change the tags and title – Nik Jul 26 '19 at 19:58

0 Answers0