I have a problem related with Oracle DB function. I don;t know how to pass column_name as parameter. Below I present current function without parameters and it's working fine:
At the beginning i'm creating two types:
CREATE OR REPLACE NONEDITIONABLE TYPE metric_record AS OBJECT (
period_start VARCHAR2(255),
data_01_avg NUMBER,
data_02_avg NUMBER,
data_03_avg NUMBER,
data_04_avg NUMBER,
group_name VARCHAR2(255)
);
CREATE OR REPLACE NONEDITIONABLE TYPE metric_table AS TABLE OF metric_record;
And the function:
create or replace NONEDITIONABLE FUNCTION get_metrics_summary RETURN metric_table IS
s_metrics metric_table;
BEGIN
SELECT metric_record(
TO_CHAR(period_start, 'YYYY-MM-DD HH24:MI'),
AVG(data_01_avg),
AVG(data_02_avg),
AVG(data_03_avg),
AVG(data_04_avg),
group_name)
BULK COLLECT INTO s_metrics
FROM (
SELECT
TO_DATE(FLOOR(TO_NUMBER(TO_CHAR(t.TIMESTAMP, 'SSSSS'))/900)*900, 'SSSSS') AS period_start,
TO_CHAR(TRUNC(t.TIMESTAMP) + FLOOR(TO_NUMBER(TO_CHAR(t.TIMESTAMP, 'SSSSS'))/900)/96, 'YYYY-MM-DD HH24:MI:SS') AS time_start,
ROUND(AVG(t.data_01),9) AS data_01_avg,
ROUND(AVG(t.data_02),2) AS data_02_avg,
ROUND(AVG(t.data_03),9) AS data_03_avg,
ROUND(AVG(t.data_04),2) AS data_04_avg,
m.HARDCODED_COLUMN_NAME AS group_name
FROM METRICS t
INNER JOIN METADATA m ON t.metadata_id = m.id
WHERE m.HARDCODED_COLUMN_NAME = 2
GROUP BY TO_DATE(FLOOR(TO_NUMBER(TO_CHAR(t.TIMESTAMP, 'SSSSS'))/900)*900, 'SSSSS'),
TRUNC(t.TIMESTAMP) + FLOOR(TO_NUMBER(TO_CHAR(t.TIMESTAMP, 'SSSSS'))/900)/96,
m.HARDCODED_COLUMN_NAME
)
GROUP BY period_start, group_name
ORDER BY period_start;
RETURN s_metrics;
END get_metrics_summary;
above function is working fine, but as You can see I have hardcoded column. I wan't to pass 'HARDCODED_COLUMN_NAME' as parameter, like this:
...get_metrics_summary(COL_NAME)...
and then use it in my query in each place where I need it (3 places):
...m.COL_NAME AS group_name...
...m.COL_NAME = 2...
...m.COL_NAME...
Beside of that as You can see I have hardcoded value for my column, but as column name it should be passed as parameter:
...m.COL_NAME = VALUE...
...get_metrics_summary(COL_NAME, VALUE)...
I was trying for some time how to figure it out but I don't know how, please help :)
I'm expecting updated, fixed, working, rebuilded query or different solution how can I retrieve my data. Additional info: i'll make a call to trigger this function and retrieve data from my app.
I was trying to find on stack overflow and use EXECUTE IMMEDIATE but was not working.
I have implemented EXECUTE IMMEDIATE:
CREATE OR REPLACE NONEDITIONABLE FUNCTION function1 RETURN metric_table IS
v_sql VARCHAR2(8000);
s_metrics metric_table;
BEGIN
v_sql := 'SELECT metric_record(
TO_CHAR(period_start, ''YYYY-MM-DD HH24:MI''),
AVG(data_01_avg),
AVG(data_02_avg),
AVG(data_03_avg),
AVG(data_04_avg),
group_name)
BULK COLLECT INTO s_metrics
FROM (
SELECT
TO_DATE(FLOOR(TO_NUMBER(TO_CHAR(t.TIMESTAMP, ''SSSSS''))/900)*900, ''SSSSS'') AS period_start,
TO_CHAR(TRUNC(t.TIMESTAMP) + FLOOR(TO_NUMBER(TO_CHAR(t.TIMESTAMP, ''SSSSS''))/900)/96, ''YYYY-MM-DD HH24:MI:SS'') AS time_start,
ROUND(AVG(t.data_01),9) AS data_01_avg,
ROUND(AVG(t.data_02),2) AS data_02_avg,
ROUND(AVG(t.data_03),9) AS data_03_avg,
ROUND(AVG(t.data_04),2) AS data_04_avg,
t.METADATA_ID AS group_name
FROM METRICS t
GROUP BY TO_DATE(FLOOR(TO_NUMBER(TO_CHAR(t.TIMESTAMP, ''SSSSS''))/900)*900, ''SSSSS''),
TRUNC(t.TIMESTAMP) + FLOOR(TO_NUMBER(TO_CHAR(t.TIMESTAMP, ''SSSSS''))/900)/96,
t.METADATA_ID
)
GROUP BY period_start, group_name
ORDER BY period_start';
EXECUTE IMMEDIATE v_sql INTO s_metrics;
RETURN s_metrics;
END function1;
but now I have an error
select * from table(function1());
select function1() from dual;
ORA-03001: niezaimplementowana funkcja (unimplemented function)
ORA-06512: przy "SYSTEM.FUNCTION1", linia 30 (by "SYSTEM.FUNCTION1", line 30)
03001. 00000 - "unimplemented feature"
*Cause: This feature is not implemented.
*Action: None.
Do not use EXECUTE IMMEDIATE, instead whitelist the possible values in a CASE expression:
CREATE NONEDITIONABLE FUNCTION get_metrics_summary(
i_col_name IN VARCHAR2
)
RETURN metric_table
IS
s_metrics metric_table;
BEGIN
SELECT metric_record(
TO_CHAR(period_start, 'YYYY-MM-DD HH24:MI'),
AVG(data_01_avg),
AVG(data_02_avg),
AVG(data_03_avg),
AVG(data_04_avg),
group_name
)
BULK COLLECT INTO s_metrics
FROM (
SELECT TO_DATE(FLOOR(TO_NUMBER(TO_CHAR(t.TIMESTAMP, 'SSSSS'))/900)*900, 'SSSSS') AS period_start,
TO_CHAR(TRUNC(t.TIMESTAMP) + FLOOR(TO_NUMBER(TO_CHAR(t.TIMESTAMP, 'SSSSS'))/900)/96, 'YYYY-MM-DD HH24:MI:SS') AS time_start,
ROUND(AVG(t.data_01),9) AS data_01_avg,
ROUND(AVG(t.data_02),2) AS data_02_avg,
ROUND(AVG(t.data_03),9) AS data_03_avg,
ROUND(AVG(t.data_04),2) AS data_04_avg,
CASE i_col_name
WHEN 'COLUMN1' THEN m.column1
WHEN 'COLUMN2' THEN m.column2
WHEN 'COLUMN3' THEN m.column3
WHEN 'COLUMN4' THEN m.column4
END AS group_name
FROM METRICS t
INNER JOIN METADATA m
ON t.metadata_id = m.id
WHERE CASE i_col_name
WHEN 'COLUMN1' THEN m.column1
WHEN 'COLUMN2' THEN m.column2
WHEN 'COLUMN3' THEN m.column3
WHEN 'COLUMN4' THEN m.column4
END = 2
GROUP BY
TO_DATE(FLOOR(TO_NUMBER(TO_CHAR(t.TIMESTAMP, 'SSSSS'))/900)*900, 'SSSSS'),
TRUNC(t.TIMESTAMP) + FLOOR(TO_NUMBER(TO_CHAR(t.TIMESTAMP, 'SSSSS'))/900)/96,
CASE i_col_name
WHEN 'COLUMN1' THEN m.column1
WHEN 'COLUMN2' THEN m.column2
WHEN 'COLUMN3' THEN m.column3
WHEN 'COLUMN4' THEN m.column4
END
)
GROUP BY period_start, group_name
ORDER BY period_start;
RETURN s_metrics;
END get_metrics_summary;
For passing multiple key-value pairs to the function, something like:
CREATE NONEDITIONABLE FUNCTION get_metrics_summary(
i_col_name1 IN VARCHAR2,
i_value1 IN VARCAHR2,
i_col_name2 IN VARCHAR2 DEFAULT NULL,
i_value2 IN VARCAHR2 DEFAULT NULL,
i_col_name3 IN VARCHAR2 DEFAULT NULL,
i_value3 IN VARCAHR2 DEFAULT NULL
)
RETURN metric_table
IS
s_metrics metric_table;
BEGIN
SELECT metric_record(
TO_CHAR(period_start, 'YYYY-MM-DD HH24:MI'),
AVG(data_01_avg),
AVG(data_02_avg),
AVG(data_03_avg),
AVG(data_04_avg),
group_name
)
BULK COLLECT INTO s_metrics
FROM (
SELECT TO_DATE(FLOOR(TO_NUMBER(TO_CHAR(t.TIMESTAMP, 'SSSSS'))/900)*900, 'SSSSS') AS period_start,
TO_CHAR(TRUNC(t.TIMESTAMP) + FLOOR(TO_NUMBER(TO_CHAR(t.TIMESTAMP, 'SSSSS'))/900)/96, 'YYYY-MM-DD HH24:MI:SS') AS time_start,
ROUND(AVG(t.data_01),9) AS data_01_avg,
ROUND(AVG(t.data_02),2) AS data_02_avg,
ROUND(AVG(t.data_03),9) AS data_03_avg,
ROUND(AVG(t.data_04),2) AS data_04_avg,
CASE i_col_name1
WHEN 'COLUMN1' THEN m.column1
WHEN 'COLUMN2' THEN m.column2
WHEN 'COLUMN3' THEN m.column3
WHEN 'COLUMN4' THEN m.column4
END AS group_name
FROM METRICS t
INNER JOIN METADATA m
ON t.metadata_id = m.id
WHERE CASE
WHEN i_col_name1 = 'COLUMN1' AND m.column1 = i_value1 THEN 1
WHEN i_col_name1 = 'COLUMN2' AND m.column2 = i_value1 THEN 1
WHEN i_col_name1 = 'COLUMN3' AND m.column3 = i_value1 THEN 1
WHEN i_col_name1 = 'COLUMN4' AND m.column4 = i_value1 THEN 1
-- No default, expect the 1st key-value pair to always be present
END = 1
AND CASE
WHEN i_col_name2 = 'COLUMN1' AND m.column1 = i_value2 THEN 1
WHEN i_col_name2 = 'COLUMN2' AND m.column2 = i_value2 THEN 1
WHEN i_col_name2 = 'COLUMN3' AND m.column3 = i_value2 THEN 1
WHEN i_col_name2 = 'COLUMN4' AND m.column4 = i_value2 THEN 1
WHEN i_col_name2 IS NULL THEN 1 -- Always match if not set
END = 1
AND CASE
WHEN i_col_name3 = 'COLUMN1' AND m.column1 = i_value3 THEN 1
WHEN i_col_name3 = 'COLUMN2' AND m.column2 = i_value3 THEN 1
WHEN i_col_name3 = 'COLUMN3' AND m.column3 = i_value3 THEN 1
WHEN i_col_name3 = 'COLUMN4' AND m.column4 = i_value3 THEN 1
WHEN i_col_name3 IS NULL THEN 1 -- Always match if not set
END = 1
GROUP BY
TO_DATE(FLOOR(TO_NUMBER(TO_CHAR(t.TIMESTAMP, 'SSSSS'))/900)*900, 'SSSSS'),
TRUNC(t.TIMESTAMP) + FLOOR(TO_NUMBER(TO_CHAR(t.TIMESTAMP, 'SSSSS'))/900)/96,
CASE i_col_name1
WHEN 'COLUMN1' THEN m.column1
WHEN 'COLUMN2' THEN m.column2
WHEN 'COLUMN3' THEN m.column3
WHEN 'COLUMN4' THEN m.column4
END,
CASE i_col_name2
WHEN 'COLUMN1' THEN m.column1
WHEN 'COLUMN2' THEN m.column2
WHEN 'COLUMN3' THEN m.column3
WHEN 'COLUMN4' THEN m.column4
END,
CASE i_col_name3
WHEN 'COLUMN1' THEN m.column1
WHEN 'COLUMN2' THEN m.column2
WHEN 'COLUMN3' THEN m.column3
WHEN 'COLUMN4' THEN m.column4
END
)
GROUP BY period_start, group_name
ORDER BY period_start;
RETURN s_metrics;
END get_metrics_summary;