• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 598
  • Last Modified:

using a function that returns a recordset

I have a table called status_table and i have about 15 columns

Activity              Flag              Name          Ean_number     periode     ........
-------------         --------         ---------        --------------       --------     ----------------
varchar2           varchar2      varchar2     number             number    ........


i need a function or a stored procedure with 4 parameters that findes/returns  activities and Flags :

example :
 select a_function(20112008, 'somthing', 'somthing, 'somthing'') from dual;

20112008 could for example be a periode or a Ean_number.
and 'somthing' could be any of other varchar2 columns.

that should return some rows in a Data Grid for me.

there are only varchar2 and number types in the table.
could u pls help me with that?
0
delmandiyar
Asked:
delmandiyar
  • 15
  • 11
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you want to implement a pipeline function:
http://www.akadia.com/services/ora_pipe_functions.html
0
 
delmandiyarAuthor Commented:
maybe u are right, but the problem is that these 4 parameters could be any type, så i need a select query with a where clause and the where clause should be a dynamic sql since the parameters can be any types.

Could you maybe give me a tip to how i should implemented, or could u give me an example?
i oploaded a snapshot of my table desc.

thanx
Unavngivet.JPG
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
how should the function know what to do with the parameters?
can you clarify?
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
delmandiyarAuthor Commented:
yes i have made an example with 2 parameters i can send you the code, so you can get an idea of what i want!
i'm using these parameters in dynamic sql.

CREATE OR REPLACE PACKAGE findactivities_pkg
IS
   TYPE csGetResultSet IS REF CURSOR;
 
   FUNCTION findactivities (P1 varchar2, P2 varchar2)
      RETURN csGetResultSet;
END findactivities_pkg;
/
**************************
 
CREATE OR REPLACE package body findactivities_pkg is
 
 
function findactivities
( P1 varchar2, P2 varchar2)
return csGetResultSet is
 
csGetActivities csGetResultSet;
text varchar2(4000);
 
begin
 
text := 'select AKTIVITET, FLAG from statustabel where 1=1';
   IF P1 IS NOT NULL then
       text := text || ' AND (  LAGER_ID = '''||P1||''' OR KAVERNE_ID = '''||P1||''' OR TO_CHAR(PERIODE) = '''||P1||''' 
       OR MASSETYPE_ID = '''||P1||''' OR AKTIVITET = '''||P1||''' OR KUNDE_ID = '''||P1||''' 
       OR TO_CHAR(MR_ID) = '''||P1||''' OR MAALESTED_ID = '''||P1||''' OR MAALESTED_STRENG = '''||P1||''' 
       OR ANLAEG_ID = '''||P1||''' OR LEDNINGS_ID = '''||P1||''' OR LEVERANDQR_ID = '''||P1||''' OR BALANCE_TYPE = '''||P1||''' 
       OR ELEMENTID = '''||P1||''' OR FUELGAS_MAALER_ID = '''||P1||''' OR TO_CHAR(DATO) = '''||P1||''' OR FLAG = '''||P1||''' 
       OR UDFQRT_AF = '''||P1||''' OR TO_CHAR(FLG_NR) = '''||P1||''' OR TO_CHAR(VERSION) = '''||P1||''' OR MEDTAGES = '''||P1||''' 
       OR GODKEND_NAVN = '''||P1||''' OR TO_CHAR(GODKEND_DATO) = '''||P1||''' OR TO_CHAR(GODKEND_NR) = '''||P1||''' 
       OR TO_CHAR(MAALER_ID) = '''||P1||''' OR NAVN = '''||P1||''' OR KUNDE_EANNR = '''||P1||''' OR PORTEFQLGE_EANNR = '''||P1||''' 
       OR GRAENSESTATIONSNAVN = '''||P1||''' OR CSC_FLG_NR = '''||P1||''' OR TO_CHAR(KORREKTIONSPERIODE) = '''||P1||''' 
       OR TO_CHAR(KORREKTIONSTYPENR) = '''||P1||''' OR LEVERANCEID = '''||P1||''' OR SHIPPERCODE = '''||P1||'''  )';
   END IF;
   IF P2 IS NOT NULL THEN
       text := text || ' AND (  LAGER_ID = '''||P2||''' OR KAVERNE_ID = '''||P2||''' OR TO_CHAR(PERIODE) = '''||P2||''' 
       OR MASSETYPE_ID = '''||P2||''' OR AKTIVITET = '''||P2||''' OR KUNDE_ID = '''||P2||''' 
       OR TO_CHAR(MR_ID) = '''||P2||''' OR MAALESTED_ID = '''||P2||''' OR MAALESTED_STRENG = '''||P2||''' 
       OR ANLAEG_ID = '''||P2||''' OR LEDNINGS_ID = '''||P2||''' OR LEVERANDQR_ID = '''||P2||''' OR BALANCE_TYPE = '''||P2||''' 
       OR ELEMENTID = '''||P2||''' OR FUELGAS_MAALER_ID = '''||P2||''' OR TO_CHAR(DATO) = '''||P2||''' OR FLAG = '''||P2||''' 
       OR UDFQRT_AF = '''||P2||''' OR TO_CHAR(FLG_NR) = '''||P2||''' OR TO_CHAR(VERSION) = '''||P2||''' OR MEDTAGES = '''||P2||''' 
       OR GODKEND_NAVN = '''||P2||''' OR TO_CHAR(GODKEND_DATO) = '''||P2||''' OR TO_CHAR(GODKEND_NR) = '''||P2||''' 
       OR TO_CHAR(MAALER_ID) = '''||P2||''' OR NAVN = '''||P2||''' OR KUNDE_EANNR = '''||P2||''' OR PORTEFQLGE_EANNR = '''||P2||''' 
       OR GRAENSESTATIONSNAVN = '''||P2||''' OR CSC_FLG_NR = '''||P2||''' OR TO_CHAR(KORREKTIONSPERIODE) = '''||P2||''' 
       OR TO_CHAR(KORREKTIONSTYPENR) = '''||P2||''' OR LEVERANCEID = '''||P2||''' OR SHIPPERCODE = '''||P2||'''  )';
   END IF;
 
 
open csGetActivities for text;
 
return csGetActivities;
 
end findactivities;
 
end findactivities_pkg;
 
/

Open in new window

0
 
delmandiyarAuthor Commented:
with this package, i'm not able to do this query:
select GetRefCursors.sfGetAccountInterval('200403','somthing') from dual;

but i can get results like this:
VARIABLE g_ref REFCURSOR;
EXEC :g_ref := findactivities_pkg.findactivities('200403','somthing');
print g_ref;
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if the function is pipelined, the query would read like this:
select * FROM TABLE(GetRefCursors.sfGetAccountInterval('200403','somthing'));

Open in new window

0
 
delmandiyarAuthor Commented:
thank you, i will try pipelined, i'm not sure i can get it to work, but we will se.
0
 
delmandiyarAuthor Commented:
Hi angelIII
I try to use pipelined, but i don't know how to use dynamic sql with PIPE ROW.
I attach my new code so u can see it.

i'm getting this error:
PLS-00306: wrong number or types of arguments in call to 'STATUSTABEL_TYPE'
CREATE OR REPLACE PACKAGE FIND_AKTIVITET_PKG1
AS
FUNCTION FIND_AKTIVITET(P1 IN VARCHAR2, P2 IN VARCHAR2) RETURN
      STATUSTABEL_TYPE PIPELINED;
END;
/
****************************
CREATE OR REPLACE PACKAGE BODY FIND_AKTIVITET_PKG1
AS
   FUNCTION FIND_AKTIVITET (P1 IN VARCHAR2, P2 IN VARCHAR2)
      RETURN STATUSTABEL_TYPE
      PIPELINED
   IS
      SQL_TEXT   VARCHAR2 (4000);
   BEGIN
      SQL_TEXT := 'SELECT AKTIVITET, FLAG FROM STATUSTABEL WHERE 1=1';
         IF P1 IS NOT NULL then
       SQL_TEXT := SQL_TEXT || ' AND (  LAGER_ID = '''||P1||''' OR KAVERNE_ID = '''||P1||''' OR TO_CHAR(PERIODE) = '''||P1||''' 
       OR MASSETYPE_ID = '''||P1||''' OR AKTIVITET = '''||P1||''' OR KUNDE_ID = '''||P1||''' 
       OR TO_CHAR(MR_ID) = '''||P1||''' OR MAALESTED_ID = '''||P1||''' OR MAALESTED_STRENG = '''||P1||''' 
       OR ANLAEG_ID = '''||P1||''' OR LEDNINGS_ID = '''||P1||''' OR LEVERANDQR_ID = '''||P1||''' OR BALANCE_TYPE = '''||P1||''' 
       OR ELEMENTID = '''||P1||''' OR FUELGAS_MAALER_ID = '''||P1||''' OR TO_CHAR(DATO) = '''||P1||''' OR FLAG = '''||P1||''' 
       OR UDFQRT_AF = '''||P1||''' OR TO_CHAR(FLG_NR) = '''||P1||''' OR TO_CHAR(VERSION) = '''||P1||''' OR MEDTAGES = '''||P1||''' 
       OR GODKEND_NAVN = '''||P1||''' OR TO_CHAR(GODKEND_DATO) = '''||P1||''' OR TO_CHAR(GODKEND_NR) = '''||P1||''' 
       OR TO_CHAR(MAALER_ID) = '''||P1||''' OR NAVN = '''||P1||''' OR KUNDE_EANNR = '''||P1||''' OR PORTEFQLGE_EANNR = '''||P1||''' 
       OR GRAENSESTATIONSNAVN = '''||P1||''' OR CSC_FLG_NR = '''||P1||''' OR TO_CHAR(KORREKTIONSPERIODE) = '''||P1||''' 
       OR TO_CHAR(KORREKTIONSTYPENR) = '''||P1||''' OR LEVERANCEID = '''||P1||''' OR SHIPPERCODE = '''||P1||'''  )';
   END IF;
   IF P2 IS NOT NULL THEN
       SQL_TEXT := SQL_TEXT || ' AND (  LAGER_ID = '''||P2||''' OR KAVERNE_ID = '''||P2||''' OR TO_CHAR(PERIODE) = '''||P2||''' 
       OR MASSETYPE_ID = '''||P2||''' OR AKTIVITET = '''||P2||''' OR KUNDE_ID = '''||P2||''' 
       OR TO_CHAR(MR_ID) = '''||P2||''' OR MAALESTED_ID = '''||P2||''' OR MAALESTED_STRENG = '''||P2||''' 
       OR ANLAEG_ID = '''||P2||''' OR LEDNINGS_ID = '''||P2||''' OR LEVERANDQR_ID = '''||P2||''' OR BALANCE_TYPE = '''||P2||''' 
       OR ELEMENTID = '''||P2||''' OR FUELGAS_MAALER_ID = '''||P2||''' OR TO_CHAR(DATO) = '''||P2||''' OR FLAG = '''||P2||''' 
       OR UDFQRT_AF = '''||P2||''' OR TO_CHAR(FLG_NR) = '''||P2||''' OR TO_CHAR(VERSION) = '''||P2||''' OR MEDTAGES = '''||P2||''' 
       OR GODKEND_NAVN = '''||P2||''' OR TO_CHAR(GODKEND_DATO) = '''||P2||''' OR TO_CHAR(GODKEND_NR) = '''||P2||''' 
       OR TO_CHAR(MAALER_ID) = '''||P2||''' OR NAVN = '''||P2||''' OR KUNDE_EANNR = '''||P2||''' OR PORTEFQLGE_EANNR = '''||P2||''' 
       OR GRAENSESTATIONSNAVN = '''||P2||''' OR CSC_FLG_NR = '''||P2||''' OR TO_CHAR(KORREKTIONSPERIODE) = '''||P2||''' 
       OR TO_CHAR(KORREKTIONSTYPENR) = '''||P2||''' OR LEVERANCEID = '''||P2||''' OR SHIPPERCODE = '''||P2||'''  )';
   END IF;
 
 
 
      PIPE ROW (STATUSTABEL_TYPE (SQL_TEXT));
 
      RETURN;
   END;
END;
/

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
actually, you won't use "dynamic" sql.
instead, you create (2) cursors, and depending on the IF statement, open, fetch, and pipe row from the ones you want.

note that in your case, it looks like you don't want to run the query 2 times, but instead make it larger OR.
as the query will do a full table scan, it will even improve performance...
0
 
delmandiyarAuthor Commented:
Hey angelIII
I was ill all the week, so i could not try what you told me. but today i tried to create these 2 cursors, but no luck. I just can't find out to how to create these to cursors! :(  could u pls giv me a tip to how to do it with my example. in my example i use only 2 parameters, but actually i need 4 parameters. so if i get it to work with 2 parameters, then it is easy.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you need to declare and open the cursor, for example something alone these lines.

there are 2 types that you must declare, one for the cursor record return, and one for the cursor reference...
CREATE OR REPLACE PACKAGE BODY FIND_AKTIVITET_PKG1
AS
   FUNCTION FIND_AKTIVITET (P1 IN VARCHAR2, P2 IN VARCHAR2)
      RETURN STATUSTABEL_TYPE
      PIPELINED
   IS
      c refcur_pkg.refcur_t;
      in_rec record_type_for_the_select;
   BEGIN
      IF P1 IS NOT NULL
      OPEN P1 FOR CURSOR( 
       SELECT AKTIVITET, FLAG 
         FROM STATUSTABEL 
        WHERE LAGER_ID IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR KAVERNE_ID IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR TO_CHAR(PERIODE) IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR MASSETYPE_ID  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR AKTIVITET  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR KUNDE_ID  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR TO_CHAR(MR_ID)  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR MAALESTED_ID  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR MAALESTED_STRENG  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR ANLAEG_ID  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR LEDNINGS_ID  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR LEVERANDQR_ID  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR BALANCE_TYPE  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR ELEMENTID  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR FUELGAS_MAALER_ID  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR TO_CHAR(DATO)  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR FLAG  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR UDFQRT_AF  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR TO_CHAR(FLG_NR)  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR TO_CHAR(VERSION)  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR MEDTAGES  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR GODKEND_NAVN  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR TO_CHAR(GODKEND_DATO)  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR TO_CHAR(GODKEND_NR)  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR TO_CHAR(MAALER_ID)  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR NAVN  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR KUNDE_EANNR  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR PORTEFQLGE_EANNR IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR GRAENSESTATIONSNAVN  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR CSC_FLG_NR  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR TO_CHAR(KORREKTIONSPERIODE) IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR TO_CHAR(KORREKTIONSTYPENR)  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR LEVERANCEID  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            OR SHIPPERCODE  IN (NVL(P1,'xxxxx'), nvl(P2,'xxxxx'))
            );
 
    LOOP
      FETCH c INTO in_rec; 
      EXIT WHEN p%NOTFOUND;
 
      PIPE ROW (STATUSTABEL_TYPE (in_rec.AKTIVITET));
 
      RETURN;
    END LOOP;
    CLOSE c;
END;
/

Open in new window

0
 
delmandiyarAuthor Commented:
Thank u :). i will give it a try.
0
 
delmandiyarAuthor Commented:
what are these 'xxxxx' for? i'm not sure, this will work for me!
0
 
delmandiyarAuthor Commented:
i know that 'xxxxx' is to replace it when it's null, but what should i replace it with?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
with a value that cannot occur in the relevant field(s)
0
 
delmandiyarAuthor Commented:
Hey angelIII
i'm sorry that i'm so tiresome, but i tried to just make a query like this:
SELECT   AKTIVITET, FLAG
  FROM   statustabel
 WHERE   periode = 200403 AND aktivitet = 'MANUEL MÅNEDSFORBRUG';

Then i get only 25 rows as shown in the screenshot query1.

But if i try yours like this:
SELECT   AKTIVITET, FLAG
  FROM   STATUSTABEL
 WHERE   TO_CHAR (PERIODE) IN
               (NVL ('200403', 'xxxxx'),
                NVL ('MANUEL MÅNEDSFORBRUG', 'xxxxx'))
         OR AKTIVITET IN
                 (NVL ('200403', 'xxxxx'),
                  NVL ('MANUEL MÅNEDSFORBRUG', 'xxxxx'));
Then i get all rows as shown in the screenshot query2.
Maybe i'm not understanding to use it like that, could u pls help me?
Best Regards
query1.JPG
query2.JPG
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
could you please include the field PERIODE in the output?
0
 
delmandiyarAuthor Commented:
Yes, there are 274 rows with periode 200403, efter 200403 is 200402 and so on.

QUERY1.JPG
QUERY2.JPG
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
wells, it's normal in q1 you have AND, in q2 you have OR





q1 : WHERE periode = 200403
 AND aktivitet = 'MANUEL MÅNEDSFORBRUG';
 
 
q2: WHERE TO_CHAR (PERIODE) IN
(NVL ('200403', 'xxxxx'),
NVL ('MANUEL MÅNEDSFORBRUG', 'xxxxx'))
OR AKTIVITET IN
(NVL ('200403', 'xxxxx'),
NVL ('MANUEL MÅNEDSFORBRUG', 'xxxxx'));

Open in new window

0
 
delmandiyarAuthor Commented:
yes, i can also use AND in q2, but when i have i have one more column then it will not return any rows!
like this:

SELECT   PERIODE, AKTIVITET, FLAG
  FROM   STATUSTABEL
 WHERE   TO_CHAR (PERIODE) IN
               (NVL ('200403', 'xxxxx'),
                NVL ('MANUEL MÅNEDSFORBRUG', 'xxxxx'))
         AND AKTIVITET IN
                 (NVL ('200403', 'xxxxx'),
                  NVL ('MANUEL MÅNEDSFORBRUG', 'xxxxx'))
         AND MR_ID IN
                 (NVL ('200403', 'xxxxx'),
                  NVL ('MANUEL MÅNEDSFORBRUG', 'xxxxx'));

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will have to clarify what the q has to do: AND or OR.
resp, change the AND into OR in q1, then you will get the same amounts.
0
 
delmandiyarAuthor Commented:
it's just not working, i can not change some OR's with AND's or the opposite. If i change one, then i need to change all of them, because these input parameters i get, they could be any value from any columns in the table.
Maybe i asked my question not very well, what i need is somthing like this:

select a_function_or_procedure(p1, p2, p3, p4) from dual;
p1,p2,p3 and p4 could be value of any column in the table, could be number or varchar2.
and the result should be returned like Data Grid. just like if u select from a table.

sorry, but i'm just getting crazy, that i can't make this.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see what you mean, but the problem is that the p1,p2,p3,p4 are not specified which column to look at.
so, you have to look at all of them...
so, you have to use OR, so any match of any column with any of the parameters(p1..p4) will make the row returns.
if you want that the row matches all 4 parameters, you will have to add another condition (monster condition!!), which matches the match count with the passed values count, aka
SELECT   PERIODE, AKTIVITET, FLAG
  FROM   STATUSTABEL
 WHERE (  TO_CHAR (PERIODE) IN
               (NVL (p1, 'xxxxx'), NVL (p2, 'xxxxx'))
         OR AKTIVITET IN
                 (NVL (p1, 'xxxxx'), NVL (p2, 'xxxxx'))
         OR MR_ID IN
                 (NVL (p1, 'xxxxx'), NVL (p2, 'xxxxx'))
        )
   AND  CASE WHEN TO_CHAR (PERIODE) IN
               (NVL (p1, 'xxxxx'), NVL (p2, 'xxxxx'))
             THEN 1 ELSE 0 END 
      + CASE WHEN AKTIVITET IN
                 (NVL (p1, 'xxxxx'), NVL (p2, 'xxxxx'))
             THEN 1 ELSE 0 END 
      + CASE WHEN MR_ID IN
                 (NVL (p1, 'xxxxx'), NVL (p2, 'xxxxx'))
             THEN 1 ELSE 0 END 
      >= CASE WHEN p1 IS NOT NULL THEN 1 ELSE 0 END
       + CASE WHEN p2 IS NOT NULL THEN 1 ELSE 0 END
 
          

Open in new window

0
 
delmandiyarAuthor Commented:
let us say that we only have 4 columnes, how should i do that?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you add to the OR clause as needed, and to the + CASE ... END  syntax parts...
for the parameters, you and to the 2nd  + CASE ... END  syntax parts...
0
 
delmandiyarAuthor Commented:
Hey again, i just can't get it to work! :(
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 15
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now