I am using a type record
and a table of that record
And when I add values to that table, I am doing it like this, for example, a drop operation input into table:
and similar to that every other operation, but how to get it all in dbms_output,
For table_indx in ? and what now, table_indx is a string value, how to get it all in string output, sorted out by tab_res(table_indx).table_ asc?
I am trying to make crud matrix and store results into PL/SQL table instead of physical oracle table, kind of a collection.
So my idea is to make a table_indx column a varchar2 field, and bind all other fields to a table_indx.
I have made it also with a counter defined as a integer value, but it seems to me that this way is a lot easier when inserting a new values into table, or I am wrong?
It's a mistype, I'm constantly working on the code, so it's a copy paste mistake.
I have read that, but FIRST NEXT gives me an error
character to number conversion error.
Well, tab_res.FIRST returns the first index of tab_res which is a VARCHAR2 whereas a loop index is expected to be a NUMBER.
So you cannot use FOR...LOOP construct you must use WHILE..LOOP, e.g.
SQL> SET SERVEROUTPUT ON;
2 TYPE results_rec IS RECORD(
3 table_ VARCHAR2(40),
4 create_ VARCHAR2(1) := '-',
5 read_ VARCHAR2(1) := '-',
6 update_ VARCHAR2(1) := '-',
7 delete_ VARCHAR2(1) := '-');
9 TYPE results_tab IS TABLE OF results_rec
10 INDEX BY VARCHAR2(40);
12 results results_tab;
14 variable_name VARCHAR2 (40);
16 results ('name_a').table_ := 'name_a';
17 results ('name_a').create_ := 'x';
19 results ('name_b').table_ := 'name_b';
20 results ('name_b').update_ := 'x';
22 results ('name_c').table_ := 'name_c';
23 results ('name_c').read_ := 'x';
24 results ('name_c').delete_ := 'x';
26 variable_name := results.FIRST;
28 WHILE variable_name IS NOT NULL LOOP
30 DBMS_OUTPUT.PUT_LINE (
31 results (variable_name).table_ || ' ' ||
32 results (variable_name).create_ ||
33 results (variable_name).read_ ||
34 results (variable_name).update_ ||
35 results (variable_name).delete_);
37 variable_name := results.NEXT (variable_name);
38 END LOOP;
PL/SQL procedure successfully completed.
Is it possible to this:
instead of separate block for every operation to make single block for the cases when commands are like this 'DROP TABLE', 'INSERT INTO', 'DELETE FROM'.
Make a varray of commands := ( 'DROP TABLE ', 'INSERT INTO ', 'DELETE FROM ');
And after that something like this:
Basically I'm looking for a way to simplify my version of code and make it smaller and more optimized.