sqloraclefunctionstatisticsmethod-call

How to create auto incrementing table based on a column name?

The title might be a little confusing but I will try explaining here better.

What I want to achieve is:

  1. Create a table with 3 columns (so far so good):

    CREATE TABLE StatisticCounter
(
    statisticID NUMBER GENERATED ALWAYS as IDENTITY (START with 1000 INCREMENT by 1), 
    statistikname varchar(255),
    counter integer 
);

  2. Create a way to call (function, procedure, trigger or something. I will call it function for now) that will increment counter with 1 based on statistikname or statisticid.

The restriction here being: said statistic is an SQL script ran through a cockpit file with a little bit of a different syntax than regular sql (in: out: select, WHERE with variables). I want to put a function or something in this cockpit file, that will run each time the script is run. Which will auto increment the number of times the statistic has been used.

I have no idea what I need (function, procedure, trigger or anything else) and this is why I am writing it a bit vague.

EDIT: I tried with merge but I always get the WHEN NOT MATCHED result executed. Without CAST its the same.

merge into StatisticCounter stc using 
CAST((select 1000 id from dual)AS INTEGER) val on (stc.statisticid=val.id) 
when matched then 
UPDATE StatisticCounter SET counter = counter + 1; 
when not matched then 
select * from dual;
Solution

  • I found an answer on my own after going through a lot of functions and procedures.

    1. Create table;
    2. Create function;
    3. Call function in file.

    The important thing here is that the outside _cockpit file that executes the SQL goes through all OUT parameters for each line the statistic returns as result. This makes the counter go wild - no way to catch END of statistic or beginning. So I made a second _cockpit with only 1 result and attach it to the statistic I want to count.

    CREATE TABLE UDX_Table_StatisticCounter(statisticID INTEGER, StatistikName varchar2(256 char), counter integer);

    CREATE OR REPLACE FUNCTION UDX_FUNC_StatisticCounter(datainput IN VARCHAR2) RETURN VARCHAR2 IS PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN UPDATE UDX_Table_StatisticCounter SET counter = counter +1 WHERE statisticID=datainput; COMMIT; RETURN 'done'; END UDX_FUNC_StatisticCounter;