Swadhin Ray
asked on
Concatenate sys ref cursor
Hello Experts,
I have a package which have two functions which returns sys_refcursor.
I want to concatenate both function datas.
here is my package :
How can achieve this ?
Thanks,
Sloba
I have a package which have two functions which returns sys_refcursor.
I want to concatenate both function datas.
here is my package :
CREATE OR REPLACE PACKAGE my_data IS
FUNCTION get_data_1 RETURN SYS_REFCURSOR;
FUNCTION get_data_2 RETURN SYS_REFCURSOR;
END my_data;
/
CREATE OR REPLACE PACKAGE BODY my_data IS
FUNCTION get_data_1 RETURN SYS_REFCURSOR AS
l_cursor SYS_REFCURSOR;
BEGIN
OPEN l_cursor FOR
SELECT facility_id
,facility_name
FROM sample_hash
WHERE auditors = 'Auditor1';
RETURN l_cursor;
END;
FUNCTION get_data_2 RETURN SYS_REFCURSOR AS
l_cursor SYS_REFCURSOR;
BEGIN
OPEN l_cursor FOR
SELECT protocol_type
,facility_id
,facility_name
FROM sample_hash
WHERE auditors = 'Auditor2';
RETURN l_cursor;
END;
END my_data;
/
How can achieve this ?
Thanks,
Sloba
in this particular example, the queries are simple enough, I'd just put the queries together and NULL the Auditor1 portion since it doesn't have a protocol_type column
something like this...
FUNCTION get_data_3
RETURN SYS_REFCURSOR
AS
l_cursor SYS_REFCURSOR;
BEGIN
OPEN l_cursor FOR
SELECT CASE WHEN auditors = 'Auditor2' THEN protocol_type ELSE NULL END protocol_type,
facility_id,
facility_name
FROM sample_hash
WHERE auditors IN ('Auditor1', 'Auditor2');
RETURN l_cursor;
END;
something like this...
FUNCTION get_data_3
RETURN SYS_REFCURSOR
AS
l_cursor SYS_REFCURSOR;
BEGIN
OPEN l_cursor FOR
SELECT CASE WHEN auditors = 'Auditor2' THEN protocol_type ELSE NULL END protocol_type,
facility_id,
facility_name
FROM sample_hash
WHERE auditors IN ('Auditor1', 'Auditor2');
RETURN l_cursor;
END;
ASKER
I am trying to convert all the individual functions to JSON format as like below:
Then if I run it individually then the 2nd function would return as like below:
Similarly I have 7 different function which returns sys ref cursors ....
But the final output should be like as below:
SQL> select MS_SRA_GET_JSON_FNC(my_data.get_data_1) from dual;
OUTPUT:
[
{
"FACILITY_ID":"FC-115",
"FACILITY_NAME":"ABC"
}
]
Then if I run it individually then the 2nd function would return as like below:
SQL> select MS_SRA_GET_JSON_FNC(my_data.get_data_2) from dual;
[
{
"PROTOCOL_TYPE":"FULL PROTOCOL",
"FACILITY_ID":"FC-115",
"FACILITY_NAME":"ABC"
}
]
Similarly I have 7 different function which returns sys ref cursors ....
But the final output should be like as below:
[
{
"FACILITY_ID":"FC-115",
"FACILITY_NAME":"ABC"
}
,
{
"PROTOCOL_TYPE":"FULL PROTOCOL",
"FACILITY_ID":"FC-115",
"FACILITY_NAME":"ABC"
}
]
if you don't really want a "unioned" cursor but want text output then just process each cursor independently and concatenate the text output.
especially since you're going to embed a comma between them. So you're not really trying to put them together anyway; but keep them separated.
So, leave them separate and put the text together how you want it
remove the [] bracketing from each individual piece, only group the entire concatenation.
especially since you're going to embed a comma between them. So you're not really trying to put them together anyway; but keep them separated.
So, leave them separate and put the text together how you want it
remove the [] bracketing from each individual piece, only group the entire concatenation.
You may just want to include the protocol_type where auditors = 'Auditor1' even if you don't need it rather than make it null. Just a thought, no points please.
ASKER
The problem is all the cursors are not having similar columns and not having the same condition.
In some cases the where condition is been checked with STATUS and in other cursor the data and structure was entirely different.
Where is the only option is to concatenate the result.
It would be great if I can get any solution for concatenating the results.
In some cases the where condition is been checked with STATUS and in other cursor the data and structure was entirely different.
Where is the only option is to concatenate the result.
It would be great if I can get any solution for concatenating the results.
if you "REALLY" want to union them together, then again, you must have the same number and types of columns
FUNCTION get_data_4
RETURN SYS_REFCURSOR
AS
l_cursor SYS_REFCURSOR;
BEGIN
OPEN l_cursor FOR
SELECT protocol_type, facility_id, facility_name
FROM sample_hash
WHERE auditors = 'Auditor2'
UNION ALL
SELECT NULL protocol_type, facility_id, facility_name
FROM sample_hash
WHERE auditors = 'Auditor1';
RETURN l_cursor;
END;
It will be up to your cursor processing code to remove the null value for the Auditor1 records, and also determine which rows are in which block.
This basically means, by putting the cursors together, you're forcing the next step to figure out how to take them apart. Since you really want them to be kept apart, just leave them apart.
So, again, I'd just process them individually and then assemble the text output however you want. The fact that your text is json is largely irrelevant in this usage.
FUNCTION get_data_4
RETURN SYS_REFCURSOR
AS
l_cursor SYS_REFCURSOR;
BEGIN
OPEN l_cursor FOR
SELECT protocol_type, facility_id, facility_name
FROM sample_hash
WHERE auditors = 'Auditor2'
UNION ALL
SELECT NULL protocol_type, facility_id, facility_name
FROM sample_hash
WHERE auditors = 'Auditor1';
RETURN l_cursor;
END;
It will be up to your cursor processing code to remove the null value for the Auditor1 records, and also determine which rows are in which block.
This basically means, by putting the cursors together, you're forcing the next step to figure out how to take them apart. Since you really want them to be kept apart, just leave them apart.
So, again, I'd just process them individually and then assemble the text output however you want. The fact that your text is json is largely irrelevant in this usage.
ASKER
I am able to get the expected result by :
But was thinking if there is any better solution for this.
SQL> SELECT (SELECT replace(ms_sra_get_json_fnc(my_data.get_data_2),']',',') FROM dual) ||
(SELECT replace(ms_sra_get_json_fnc(my_data.get_data_1),'[',' ') FROM dual)
FROM dual;
OUTPUT:
[
{
"PROTOCOL_TYPE":"FULL PROTOCOL",
"FACILITY_ID":"FC-115",
"FACILITY_NAME":"ABC"
}
,
{
"FACILITY_ID":"FC-115",
"FACILITY_NAME":"ABC"
}
]
But was thinking if there is any better solution for this.
don't select from dual each time, just once
SELECT REPLACE(ms_sra_get_json_fn c(my_data. get_data_2 ), ']', ',')
|| REPLACE(ms_sra_get_json_fn c(my_data. get_data_1 ), '[', ' ')
FROM DUAL;
if you're going to do a bunch of these, I'd write a json aggregator that will group the pieces within [] and add commas between them, with each piece NOT putting its own [] around them.
SELECT REPLACE(ms_sra_get_json_fn
|| REPLACE(ms_sra_get_json_fn
FROM DUAL;
if you're going to do a bunch of these, I'd write a json aggregator that will group the pieces within [] and add commas between them, with each piece NOT putting its own [] around them.
ASKER
Can you please let me know how to do that ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot for help me to solve my issue..
Good conversation
what you're asking for is basically a UNION ALL and would have the same restrictions:
i.e. same number of columns and same datatypes