delmandiyar
asked on
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?
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?
ASKER
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
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
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
how should the function know what to do with the parameters?
can you clarify?
can you clarify?
ASKER
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.
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;
/ASKER
with this package, i'm not able to do this query:
select GetRefCursors.sfGetAccount
but i can get results like this:
VARIABLE g_ref REFCURSOR;
EXEC :g_ref := findactivities_pkg.findact
print g_ref;
select GetRefCursors.sfGetAccount
but i can get results like this:
VARIABLE g_ref REFCURSOR;
EXEC :g_ref := findactivities_pkg.findact
print g_ref;
if the function is pipelined, the query would read like this:
select * FROM TABLE(GetRefCursors.sfGetAccountInterval('200403','somthing'));
ASKER
thank you, i will try pipelined, i'm not sure i can get it to work, but we will se.
ASKER
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'
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;
/
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...
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...
ASKER
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.
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.
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...
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;
/ASKER
Thank u :). i will give it a try.
ASKER
what are these 'xxxxx' for? i'm not sure, this will work for me!
ASKER
i know that 'xxxxx' is to replace it when it's null, but what should i replace it with?
with a value that cannot occur in the relevant field(s)
ASKER
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
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
could you please include the field PERIODE in the output?
ASKER
Yes, there are 274 rows with periode 200403, efter 200403 is 200402 and so on.
QUERY1.JPG
QUERY2.JPG
QUERY1.JPG
QUERY2.JPG
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'));
ASKER
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:
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'));
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.
resp, change the AND into OR in q1, then you will get the same amounts.
ASKER
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
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.
Maybe i asked my question not very well, what i need is somthing like this:
select a_function_or_procedure(p1
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.
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
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
ASKER
let us say that we only have 4 columnes, how should i 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
Hey again, i just can't get it to work! :(
http://www.akadia.com/services/ora_pipe_functions.html