Home » SQL & PL/SQL » SQL & PL/SQL » Generate SQL via SQL
|
|
| Re: Generate SQL via SQL [message #683816 is a reply to message #683815] |
Tue, 23 February 2021 01:58   |
 |
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
The test case must be representative of your data and different cases you have to handle.
[Updated on: Tue, 23 February 2021 02:00] Report message to a moderator |
|
|
|
|
|
| Re: Generate SQL via SQL [message #683825 is a reply to message #683822] |
Tue, 23 February 2021 04:13 |
|
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
A test case is something you can write, there is no more need than CREATE TABLE and INSERT statements.
For instance, you say: "the 7 refers to the seventh column in tab_data" so post a CREATE TABLE for this table.
Or, you have string like "({7@72} * 1) + ({7@73} * -1)", where are these strings? In a table? So post CREATE TABLE and INSERT statements for some possible data (formulas).
Then we can work with this test case to try to achieve your goal and show you our tries.
Otherwise, the only thing we can say is: "yes, it is possible in SQL".
Now you will say "how?" and the answer will be "we need a test case to build the SQL and show it".
It is not to bother people we ask some things, it is because they are needed to help in an accurate way.
|
|
|
|
| Re: Generate SQL via SQL [message #683827 is a reply to message #683825] |
Tue, 23 February 2021 06:18 |
 |
mikewazowski
Messages: 15
Registered: February 2021
|
Junior Member |
|
|
OK, new try 
So I have two tables, called tab_data and tab_rules:
create table tab_data (
id number constraint tab_data_pk primary key,
val varchar2(200),
dim_1 number,
dim_2 number,
dim_3 number,
dim_4 number,
dim_5 number,
dim_6 number,
dim_7 number
)
create table tab_rules (
id number constraint tab_rules_pk primary key,
dim_id number,
name varchar2(200),
type number,
rule varchar2(100)
)
INSERT INTO tab_data
VALUES (1, NULL, 11, 22, 33, 44, 55, 66, 71);
INSERT INTO tab_data
VALUES (2, 5000, 11, 22, 33, 44, 55, 66, 72);
INSERT INTO tab_data
VALUES (3, 2000, 11, 22, 33, 44, 55, 66, 73);
INSERT INTO tab_rules
VALUES (71, 7, 'Value A+B', 4, '({7@72} * 1) + ({7@73} * -1)');
INSERT INTO tab_rules
VALUES (72, 7, 'Value A', 1, NULL);
INSERT INTO tab_rules
VALUES (73, 7, 'Value B', 1, NULL);
|
|
|
|
|
|
| Re: Generate SQL via SQL [message #683831 is a reply to message #683827] |
Tue, 23 February 2021 08:23 |
Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
mikewazowski wrote on Tue, 23 February 2021 07:18
Now I'm looking for a SELECT statement to get the value of 7000 (addition of 5000 and 2000) for tab_data-ID 1.
Well, you maybe looking to get 7000 but it isn't what your formula says: ({7@72} * 1) + ({7@73} * -1) so result would be 5000 - 2000 = 3000.
Anyway, something like:
SELECT RULE,
XMLCAST(
XMLQUERY(
'/ROWSET/ROW/VAL'
PASSING DBMS_XMLGEN.GETXMLTYPE(
'SELECT ' ||
REPLACE(
REPLACE(
REPLACE(
RULE,
'{',
'(SELECT VAL FROM TAB_DATA WHERE DIM_'
),
'@',
' = '
),
'}',
')'
) ||
' VAL FROM DUAL'
)
RETURNING CONTENT
)
AS NUMBER
) RULE_RESULT
FROM TAB_RULES
WHERE RULE IS NOT NULL
/
RULE RULE_RESULT
------------------------------ -----------
({7@72} * 1) + ({7@73} * -1) 3000
SQL>
|
|
|
|
|
|
|
|
| Re: Generate SQL via SQL [message #683835 is a reply to message #683828] |
Tue, 23 February 2021 08:39 |
|
mikewazowski
Messages: 15
Registered: February 2021
|
Junior Member |
|
|
I'll try to elaborate a little. We have an application in which the user can define fields that are calculated from other fields. In my example, the element with tab_rules.id = 71 is calculated by adding the IDs 72 and 73 together. That's why the formula '({7@72} * 1) + ({7@73} * -1)' is in tab_rules.rule.
When accessing this calculated field, I now want to determine tab_data.val. To do this, I have to process all dim_*-columns, but for our example I will limit myself to dim_7. There, tab_data.dim_7 contains the value 71 (for tab_data.id = 1).
With this ID 71, I go to the table tab_rules and select the data set with tab_rules.id = 7. In the column tab_rules.rule I then find the string '({7@72} * 1) + ({7@73} * -1)'. (Short note on this: This 'syntax' can still be adapted so that the SELECT might be easier to implement).
Now it's time to analyse the string. Between { and }, the individual formula components are defined, in my example two (for the addition of these). The 7 (in front of the @) defines that I have to look in tab_data into the column dim_7 (and not dim_1 or dim_2 etc.) and search there for the ID 72 (which is behind the @) in order to select the value from tab_data.val. Thus I get the values 5000 and 2000 for the example. These are added and calculated with each other: 5000 * 1 + 2000 * -1 = 3000.
And I would like to determine this with a SELECT, if this is possible (e.g. to carry out the calculation again via trigger when the values change).
Is this written in an understandable way?
Best regards, Michael
|
|
|
|
|
|
| Re: Generate SQL via SQL [message #683840 is a reply to message #683837] |
Tue, 23 February 2021 09:02 |
Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
mikewazowski wrote on Tue, 23 February 2021 09:42
Additional question: Do you know if this SQL would also work in MS SQL and MySQL (maybe with slightly different syntax)?
You will have to find replacemenmt for DBMS_XMLGEN logic.
SY.
|
|
|
|
| Re: Generate SQL via SQL [message #683842 is a reply to message #683835] |
Tue, 23 February 2021 09:13 |
|
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
So no more tab_rule.dim_id.
Here's another way (I modified a little bit your tables and data to avoid confusion):
SQL> select * from tab_data order by id;
ID VAL DIM_1 DIM_2 DIM_3 DIM_4 DIM_5 DIM_6 DIM_7
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 1000 11 21 31 41 51 61 71
2 5000 12 22 32 42 52 62 72
3 2000 13 23 33 43 53 63 73
3 rows selected.
SQL> select * from tab_rules order by id;
ID DIM_ID NAME TYPE RULE
---------- ---------- -------------------- ---------- --------------------------------------------------
101 Value A+B 4 ({7@72} * 1) + ({7@73} * -1)
102 Value A+B/C 4 ({1@11} * 1) + ({2@22} * 2) / ({3@33} * 3)
103 Value (A+B)/C 4 (({1@11} * 1) + ({2@22} * 2)) / ({3@33} * 3)
104 Value A 1
105 Value B 1
5 rows selected.
SQL> with
2 elems as (
3 select id, rule, column_value fnb,
4 regexp_substr(rule, '\(+[^)]+\)+', 1, column_value) f,
5 regexp_substr(rule, '\) *([^) ]+)', 1, column_value, null, 1) ope
6 from tab_rules,
7 table(cast(multiset(select level column_value from dual
8 connect by level <= regexp_count(rule,'@'))
9 as sys.odciNumberList))
10 where type = 4
11 ),
12 bits as (
13 select id fid, rule, fnb, f,
14 regexp_substr(f,'^\(+') deb,
15 regexp_substr(f,'{(\d+)', 1, 1, null, 1) colnb,
16 regexp_substr(f,'@(\d+)', 1, 1, null, 1) did,
17 regexp_substr(f,'}(.*$)', 1, 1, null, 1) fin,
18 ope
19 from elems
20 )
21 select fid, rule,
22 to_number(
23 extractvalue(
24 dbms_xmlgen.getXMLtype(
25 'select '||
26 listagg(deb||'(select val from tab_data where dim_'||colnb||'='||did||')'
27 ||fin||ope)
28 within group (order by fnb)||
29 ' val from dual'),
30 '/ROWSET/ROW/VAL')) val
31 from bits
32 group by fid, rule
33 order by fid
34 /
FID RULE VAL
---------- -------------------------------------------------- ----------
101 ({7@72} * 1) + ({7@73} * -1) 3000
102 ({1@11} * 1) + ({2@22} * 2) / ({3@33} * 3) 1001.66667
103 (({1@11} * 1) + ({2@22} * 2)) / ({3@33} * 3) 1.83333333
3 rows selected.
SQL> SELECT RULE,
2 XMLCAST(
3 XMLQUERY(
4 '/ROWSET/ROW/VAL'
5 PASSING DBMS_XMLGEN.GETXMLTYPE(
6 'SELECT ' ||
7 REPLACE(
8 REPLACE(
9 REPLACE(
10 RULE,
11 '{',
12 '(SELECT VAL FROM TAB_DATA WHERE DIM_'
13 ),
14 '@',
15 ' = '
16 ),
17 '}',
18 ')'
19 ) ||
20 ' VAL FROM DUAL'
21 )
22 RETURNING CONTENT
23 )
24 AS NUMBER
25 ) RULE_RESULT
26 FROM TAB_RULES
27 WHERE RULE IS NOT NULL
28 /
RULE RULE_RESULT
-------------------------------------------------- -----------
({7@72} * 1) + ({7@73} * -1) 3000
(({1@11} * 1) + ({2@22} * 2)) / ({3@33} * 3) 1.83333333
({1@11} * 1) + ({2@22} * 2) / ({3@33} * 3) 1001.66667
3 rows selected.[Updated on: Tue, 23 February 2021 09:23] Report message to a moderator |
|
|
|
| Re: Generate SQL via SQL [message #683845 is a reply to message #683837] |
Tue, 23 February 2021 09:36 |
|
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
mikewazowski wrote on Tue, 23 February 2021 15:42Oh, you already answered while I was writing so much 
I will test this in the next few days and get back to you.
Additional question: Do you know if this SQL would also work in MS SQL and MySQL (maybe with slightly different syntax)?
You can use Solomon inner expressions to generate the queries and then execute them:
SQL> select 'SELECT ' ||
2 REPLACE(
3 REPLACE(
4 REPLACE(
5 RULE,
6 '{',
7 '(SELECT VAL FROM TAB_DATA WHERE DIM_'
8 ),
9 '@',
10 ' = '
11 ),
12 '}',
13 ')'
14 ) ||
15 ' VAL FROM DUAL'
16 sqls
17 from tab_rules
18 where type = 4
19 order by id
20 /
SQLS
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT ((SELECT VAL FROM TAB_DATA WHERE DIM_7 = 72) * 1) + ((SELECT VAL FROM TAB_DATA WHERE DIM_7 = 73) * -1) VAL FROM DUAL
SELECT ((SELECT VAL FROM TAB_DATA WHERE DIM_1 = 11) * 1) + ((SELECT VAL FROM TAB_DATA WHERE DIM_2 = 22) * 2) / ((SELECT VAL FROM TAB_DATA WHERE DIM_3 = 33) * 3) VAL FROM DUAL
SELECT (((SELECT VAL FROM TAB_DATA WHERE DIM_1 = 11) * 1) + ((SELECT VAL FROM TAB_DATA WHERE DIM_2 = 22) * 2)) / ((SELECT VAL FROM TAB_DATA WHERE DIM_3 = 33) * 3) VAL FROM DUAL
3 rows selected. |
|
|
|
|
|
| Re: Generate SQL via SQL [message #683847 is a reply to message #683845] |
Tue, 23 February 2021 09:50 |
|
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
This makes me think that with 19c (in fact starting from 12c), you can avoid the use of dbms_xmlgen:
SQL> with
2 function exec (str varchar2) return number
3 is
4 ret number;
5 begin
6 execute immediate str into ret;
7 return ret;
8 end;
9 select id, rule,
10 exec ('SELECT '||
11 REPLACE(
12 REPLACE(
13 REPLACE(RULE, '{', '(SELECT VAL FROM TAB_DATA WHERE DIM_'),
14 '@', ' = '),
15 '}', ')') ||
16 ' VAL FROM DUAL')
17 val
18 from tab_rules
19 where type = 4
20 order by id
21 /
ID RULE VAL
---------- -------------------------------------------------- ----------
101 ({7@72} * 1) + ({7@73} * -1) 3000
102 ({1@11} * 1) + ({2@22} * 2) / ({3@33} * 3) 1001.66667
103 (({1@11} * 1) + ({2@22} * 2)) / ({3@33} * 3) 1.83333333
3 rows selected.[Updated on: Tue, 23 February 2021 10:05] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Generate SQL via SQL [message #683889 is a reply to message #683888] |
Tue, 02 March 2021 12:36 |
|
mikewazowski
Messages: 15
Registered: February 2021
|
Junior Member |
|
|
With pleasure
INSERT INTO tab_data
VALUES (1, NULL, 11, 21, 31, 41, 51, 61, 71);
INSERT INTO tab_data
VALUES (2, 5000, 11, 21, 31, 41, 51, 61, 72);
INSERT INTO tab_data
VALUES (3, 2000, 11, 21, 31, 41, 51, 61, 73);
INSERT INTO tab_data
VALUES (4, NULL, 12, 22, 32, 42, 52, 62, 71);
INSERT INTO tab_data
VALUES (5, 50000, 12, 22, 32, 42, 52, 62, 72);
INSERT INTO tab_data
VALUES (6, 20000, 12, 22, 32, 42, 52, 62, 73);
INSERT INTO tab_data
VALUES (7, NULL, 13, 23, 33, 43, 53, 61, 70);
INSERT INTO tab_data
VALUES (8, 10000, 13, 23, 33, 43, 53, 62, 70);
INSERT INTO tab_data
VALUES (9, 2000, 13, 23, 33, 43, 53, 63, 70);
INSERT INTO tab_rules
VALUES (61, 6, 'Value X/Y', 4, '({6@62} * 1) / ({6@63} * 1)');
INSERT INTO tab_rules
VALUES (71, 7, 'Value A+B', 4, '({7@72} * 1) + ({7@73} * -1)');
|
|
|
|
| Re: Generate SQL via SQL [message #683890 is a reply to message #683889] |
Tue, 02 March 2021 12:49 |
|
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
For this, I moved all the code in the inline function:
SQL> select * from tab_data order by id;
ID VAL DIM_1 DIM_2 DIM_3 DIM_4 DIM_5 DIM_6 DIM_7
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 11 21 31 41 51 61 71
2 5000 11 21 31 41 51 61 72
3 2000 11 21 31 41 51 61 73
4 12 22 32 42 52 62 71
5 50000 12 22 32 42 52 62 72
6 20000 12 22 32 42 52 62 73
7 13 23 33 43 53 61 70
8 10000 13 23 33 43 53 62 70
9 2000 13 23 33 43 53 63 70
9 rows selected.
SQL> select * from tab_rules order by id;
ID DIM_ID NAME TYPE RULE
---------- ---------- -------------------- ---------- ------------------------------
101 Value A+B 4 ({7@72} * 1) + ({7@73} * -1)
110 Value A/B 4 ({6@62} * 1) / ({6@63} * 1)
2 rows selected.
SQL> with
2 function exec (rule varchar2, dims sys.odcinumberlist)
3 return number
4 is
5 col pls_integer;
6 que varchar2(32760);
7 ret number;
8 begin
9 que := regexp_replace(rule,
10 '{(\d+)@(\d+)}',
11 '(SELECT val FROM tab_data d2 WHERE dim_\1 = \2 AND val IS NOT NULL #\1#)');
12 col := to_number (regexp_substr (que, '#(\d+)#', 1, 1, null, 1));
13 que := replace (que, '#'||col||'#', '#');
14 for i in 1..7 loop
15 if i != col then
16 que := replace (que, '#', ' and dim_'||i||' = '||dims(i)||'#');
17 end if;
18 end loop;
19 que := 'SELECT '||replace(que, '#', '')||' val FROM DUAL';
20 execute immediate que into ret;
21 return ret;
22 end;
23 compute as (
24 select r.id rule_id, r.rule, d.id data_id,
25 exec (rule,
26 sys.odcinumberlist(dim_1, dim_2, dim_3, dim_4, dim_5, dim_6, dim_7))
27 val
28 from tab_rules r, tab_data d
29 where r.type = 4
30 and d.val is null
31 )
32 select rule_id, rule, data_id, val
33 from compute
34 where val is not null
35 order by rule_id, data_id
36 /
RULE_ID RULE DATA_ID VAL
---------- ------------------------------ ---------- ----------
101 ({7@72} * 1) + ({7@73} * -1) 1 3000
101 ({7@72} * 1) + ({7@73} * -1) 4 30000
110 ({6@62} * 1) / ({6@63} * 1) 7 5
3 rows selected. |
|
|
|
| Re: Generate SQL via SQL [message #683891 is a reply to message #683890] |
Tue, 02 March 2021 23:48 |
|
mikewazowski
Messages: 15
Registered: February 2021
|
Junior Member |
|
|
Oh wow, this looks much more complicated at first sight than the first idea But many, many thanks for that.
At first I couldn't get it to work for me. In lines 23 to 31 you define compute. There you join the two tables tab_data and tab_rule, but in my eyes the connection between both tables is missing, isn't it? I get "no data found".
To understand exec, I have to learn about regular expressions, but I can do that. Other databases should be able to handle this (SQLServer, MySQL), the only question is whether they can work with FUNCTION within the WITH statement.
Best regards, Michael
|
|
|
|
| Re: Generate SQL via SQL [message #683892 is a reply to message #683891] |
Wed, 03 March 2021 00:36 |
|
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote: but in my eyes the connection between both tables is missing, isn't it?
There is none.
From your examples, I inferred you want to check every rule of type 4 with every data with null val.
Some of these data rows won't match with some rules and so they gets a NULL result, this is why there is an intermediate "compute" inline view which computes all results and NULL are filtered out in the main part.
You can replace the "compute" in the last part by its definition in "compute" part without any change:
select <blabla> from (<something>)
is equivalent to
with compute as (<something>) select <blabla> from compute
You can replace the lines 9 to 13 with expressions containing only REPLACE, SUBSTR and INSTR functions.
[Updated on: Wed, 03 March 2021 14:54] Report message to a moderator |
|
|
|
|
|
| Re: Generate SQL via SQL [message #683895 is a reply to message #683893] |
Wed, 03 March 2021 02:29 |
|
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Here's the version without regular expressions and "compute":
SQL> with
2 function exec (rule varchar2, dims sys.odcinumberlist)
3 return number
4 is
5 col pls_integer;
6 que varchar2(32760);
7 ret number;
8 begin
9 -- Retrieve the dim column in rule (first one is taken as all are same)
10 col := to_number (substr (rule,
11 instr (rule, '{') + 1,
12 instr (rule, '@') - instr (rule, '{') - 1));
13 -- Convert the rule to the query expression to execute
14 que := replace (
15 replace (
16 replace (rule, '{', '(SELECT VAL FROM TAB_DATA WHERE DIM_'),
17 '@', ' = '),
18 '}', ' AND val IS NOT NULL #)');
19 -- Add conditions on the non-rule dim columns
20 for i in 1..7 loop
21 if i != col then
22 que := replace (que, '#', ' AND dim_'||i||' = '||dims(i)||'#');
23 end if;
24 end loop;
25 -- Complete the query
26 que := 'SELECT '||replace(que, '#', '')||' val FROM DUAL';
27 -- Execute the query
28 execute immediate que into ret;
29 return ret;
30 end;
31 select rule_id, rule, data_id, val
32 from ( select r.id rule_id, r.rule, d.id data_id,
33 exec (rule,
34 sys.odcinumberlist(dim_1, dim_2, dim_3, dim_4, dim_5, dim_6, dim_7))
35 val
36 from tab_rules r, tab_data d
37 where r.type = 4
38 and d.val is null )
39 where val is not null
40 order by rule_id, data_id
41 /
RULE_ID RULE DATA_ID VAL
---------- ------------------------------ ---------- ----------
101 ({7@72} * 1) + ({7@73} * -1) 1 3000
101 ({7@72} * 1) + ({7@73} * -1) 4 30000
110 ({6@62} * 1) / ({6@63} * 1) 7 5
3 rows selected.[Updated on: Wed, 03 March 2021 02:32] Report message to a moderator |
|
|
|
| Re: Generate SQL via SQL [message #683988 is a reply to message #683895] |
Mon, 15 March 2021 03:17 |
|
mikewazowski
Messages: 15
Registered: February 2021
|
Junior Member |
|
|
Hello, it has now taken a little longer
Now that I have managed the selection - thanks to your help - I would like to create a trigger based on the same principle, which adjusts the data of other records in this table when a value in tab_data is changed.
So if a value is changed, which has influence on another data set due to a rule (in which VAL is calculated from the changed data set), this other data set should also be updated.
Unfortunately, I seem to have a problem understanding between BEFORE and AFTER UPDATE triggers. Theoretically it can happen that several data sets are changed with one update, therefore I need (according to my understanding) a trigger with the statement FOR EACH ROW.
With the AFTER UPDATE TRIGGER I would have expected that after the update is done everything is possible on the same table. But it comes the ORA-04091, which probably means that I am not allowed to work on tab_data, because the trigger reacts on this table. In my example, I have not even performed an update, but only selected so far.
So which trigger do I have to use to adjust by changing a value (tab_data.val) all dependent entries in tab_data which have a connection via tab_rules. It must be ensured that several values are updated with one UPDATE. In addition it can be naturally that the data record connected by tab_rules leads then also again to an update, because this value is also again part of a rule in a third data record.
My example:
CREATE OR REPLACE TRIGGER trg_tab_data_upd_val
AFTER UPDATE OF val ON tab_data
FOR EACH ROW
DECLARE
que CLOB;--VARCHAR2(32767);
ret NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('ID: ' || :new.id || ', Value: ' || :new.val);
FOR rec IN (SELECT r.id AS rule_id, r.rule rule_txt, r.dimension_id,
sys.odcinumberlist(d.dim_1, d.dim_2, d.dim_3, d.dim_4, d.dim_5, d.dim_6, d.dim_7) dim_lst
FROM tab_rules r, tab_data d
WHERE r.type = 4)
LOOP
que := REPLACE(REPLACE(REPLACE(rec.rule_txt, '{', '(SELECT NVL(val, 0) FROM tab_data WHERE dim_'), '@', ' = '), '}', ' #)');
FOR i IN 1..7 LOOP
IF i != rec.dimension_id THEN
que := REPLACE(que, '#', ' AND dim_'||i||' = '||rec.dim_lst(i)||'#');
END IF;
END LOOP;
que := 'SELECT '||REPLACE(que, '#', '')||' val FROM dual';
--EXECUTE IMMEDIATE que INTO ret;
DBMS_OUTPUT.PUT_LINE( rec.rule_id || ': ' || rec.rule_txt || ' = ' || ret || ' >> ' || que);
END LOOP;
END;
|
|
|
|
| Re: Generate SQL via SQL [message #683995 is a reply to message #683988] |
Tue, 16 March 2021 15:42 |
|
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:But it comes the ORA-04091, which probably means that I am not allowed to work on tab_data, because the trigger reacts on this table
This is correct, you cannot read the table you are currently modifying inside the trigger which handles this modification.
Oracle does not allow this and the reason is clear: the result could be non-deterministic which means that if you execute twice the same UPDATE on the same table data you could have a different result inside the table depending on the order the rows are processed.
And you tell it yourself with "In addition it can be naturally that the data record connected by tab_rules leads then also again to an update, because this value is also again part of a rule in a third data record."
Needing this also means your data model is not correct.
[Updated on: Tue, 16 March 2021 15:42] Report message to a moderator |
|
|
|
|
|
| Re: Generate SQL via SQL [message #684006 is a reply to message #684005] |
Thu, 18 March 2021 03:25 |
|
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Yes, the trigger runs AFTER the update has processed (that is after rows are updated) but still inside the UPDATE statement.
The execution plan of an UPDATE is:
- Start of UPDATE statement
- BEFORE STATEMENT triggers
- BEFORE EACH ROW to update triggers
- Update rows
- AFTER EACH ROW to update triggers
- AFTER STATEMENT triggers
- End of UPDATE statement
Triggers are part of the DML statements.
Note that you can SELECT the table you are modifying in a STATEMENT trigger but not in a EACH ROW one:
SQL> desc t
Name Null? Type
-------------------------------- -------- ----------------------
VAL NUMBER(38)
SQL> create or replace trigger t_after after update on t for each row
2 declare
3 s pls_integer;
4 begin
5 select sum(val) into s from t;
6 dbms_output.put_line('Sum is '||s);
7 end;
8 /
Trigger created.
SQL> update t set val=2*val;
update t set val=2*val
*
ERROR at line 1:
ORA-04091: table MICHEL.T is mutating, trigger/function may not see it
ORA-06512: at "MICHEL.T_AFTER", line 4
ORA-04088: error during execution of trigger 'MICHEL.T_AFTER'
SQL> create or replace trigger t_before before update on t -- for each row
2 declare
3 s pls_integer;
4 begin
5 select sum(val) into s from t;
6 dbms_output.put_line('Before sum is '||s);
7 end;
8 /
Trigger created.
SQL> create or replace trigger t_after after update on t -- for each row
2 declare
3 s pls_integer;
4 begin
5 select sum(val) into s from t;
6 dbms_output.put_line('After sum is '||s);
7 end;
8 /
Trigger created.
SQL> update t set val=2*val;
Before sum is 30
After sum is 60
20 rows updated.
|
|
|
|
Goto Forum:
Current Time: Sat Mar 25 14:39:43 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
