PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Friday, October 7, 2022

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

 October 07, 2022     function, method-call, oracle, sql, statistics     No comments   

Issue

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;



Answered By - DarkBlade
Answer Checked By - Clifford M. (PHPFixing Volunteer)
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing