Solved

Concatenate sys ref cursor

Posted on 2013-10-23
12
1,047 Views
Last Modified: 2013-10-23
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 :

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;
/

Open in new window


How can achieve this ?

Thanks,
Sloba
0
Comment
Question by:Swadhin Ray
  • 6
  • 5
12 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 39594809
they don't have the same number of columns, concatenating them wouldn't make sense

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
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39594859
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;
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39594931
I am trying to convert all the individual functions to JSON format as like below:

SQL> select MS_SRA_GET_JSON_FNC(my_data.get_data_1) from dual;

OUTPUT:
[
  {
    "FACILITY_ID":"FC-115",
    "FACILITY_NAME":"ABC"
  }
]

Open in new window


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"
  }
]

Open in new window



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"
  }
]

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
ID: 39594954
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.
0
 
LVL 32

Expert Comment

by:awking00
ID: 39594985
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.
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39595002
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.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39595003
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.
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39595196
I am able to get the expected result by :

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"
  }
]

Open in new window


But was thinking if there is any better solution for this.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39595212
don't select from dual each time, just once

SELECT    REPLACE(ms_sra_get_json_fnc(my_data.get_data_2), ']', ',')
       || REPLACE(ms_sra_get_json_fnc(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.
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39596164
Can you please let me know how to do that ?
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39596222
I don't have "done" versions already to give you; I'm envisioning three variations.

At the simplest level, generate your json text and use collect with a string writer or, if small enough, listagg to pull them together.

On slightly more complex level, use something like concat_agg in my article but alter it to construct a clob instead of a varchar2, unless you're positive you'll never have more than 4000 characters in your json.   Then use that aggregate on the json output from the functions you've already got.

For the most complex version, put the json construction inside the aggregate itself with a record type based on the columns you need to group.  This may not be feasible if you need to aggregate lots of different structures.

In general, the greater the complexity in up-front effort, the easier the usage will be in final implementation, at least, that would be the goal anyway.

More than that is a whole project in itself and a different direction and scope from this question, plus the rules of exactly what and how to aggregate aren't well defined to even begin trying to detail any of them.
0
 
LVL 16

Author Closing Comment

by:Swadhin Ray
ID: 39596337
Thanks a lot for help me to solve my issue..
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle -- identify blocking session 24 51
Extract the first word (before the , ) 2 48
Read only access to a Procedure in oracle? 4 65
Query Records that don't match 8 42
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question