7 Replies Latest reply: Oct 10, 2013 5:53 AM by bluvulture RSS

    Using Type Record and Table Of and sorting out output

    bluvulture

      I am using a type record

       

      • TYPE results_rec is RECORD(
      •     table_  varchar2(40),
      •     create_ char(1) := '-',
      •     read_   char(1) := '-',
      •     update_ char(1) := '-',
      •     delete_ char(1) := '-');

       

      and a table of that record

       

      • TYPE results_tab IS TABLE of results_rec INDEX BY VARCHAR2(40);
      •   tab_res results_tab;
      •   table_indx varchar2(40);

       

      And when I add values to that table, I am doing it like this, for example, a drop operation input into table:

       

       

      • v_check := instr2(v_string_fnc, 'DROP ');
      •   if v_check > 0 then
      •     counter      := counter + 1;
      •     delete_flag  := 'D';
      •     v_check      := instr2(v_string_fnc, 'TABLE ', v_check);
      •     v_check      := v_check + 6;
      •     result_table := substr(v_string_fnc, v_check);
      •     rest_string  := result_table;
      •     result_table := substr(result_table, 0, instr(result_table, ' '));
      •     result_table := rtrim(result_table);
      •     table_indx := result_table;
      •     tab_res(table_indx).table_ := result_table;
      •     tab_res(table_indx).delete_ := delete_flag;

       

      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?

        • 1. Re: Using Type Record and Table Of and sorting out output
          Karthick_Arp

          What are you trying to do? Why is table_index defined as varchar2 column?

          • 2. Re: Using Type Record and Table Of and sorting out output
            bluvulture

            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?

            • 3. Re: Using Type Record and Table Of and sorting out output
              padders

              > INDEX BY BINARY_INTEGER(40)

               

              This doesn't even compile. Make an effort to something decent if you want a decent answer.

               

              Are you asking how to loop through a PL./SQL table indexed by VARCHAR2? If so you will need to look at FIRST/ NEXT operators.

              • 4. Re: Using Type Record and Table Of and sorting out output
                bluvulture

                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

                 

                • For table_indx in tab_res.first..tab_res.last loop
                •     dbms_output.put_line(rpad(tab_res(table_indx).table_, 21) ||
                •                          lpad(tab_res(table_indx)
                •                               .create_ || ' ' || tab_res(table_indx).read_ || ' ' || ' ' || tab_res(table_indx)
                •                               .update_ || ' ' || tab_res(table_indx).delete_,
                •                               10));

                 

                character to number conversion error.

                • 5. Re: Using Type Record and Table Of and sorting out output
                  padders

                  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;

                  SQL> DECLARE

                    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) := '-');

                    8

                    9     TYPE results_tab IS TABLE OF results_rec

                  10        INDEX BY VARCHAR2(40);

                  11

                  12     results results_tab;

                  13

                  14     variable_name VARCHAR2 (40);

                  15  BEGIN

                  16     results ('name_a').table_ := 'name_a';

                  17     results ('name_a').create_ := 'x';

                  18

                  19     results ('name_b').table_ := 'name_b';

                  20     results ('name_b').update_ := 'x';

                  21

                  22     results ('name_c').table_ := 'name_c';

                  23     results ('name_c').read_ := 'x';

                  24     results ('name_c').delete_ := 'x';

                  25

                  26     variable_name := results.FIRST;

                  27

                  28     WHILE variable_name IS NOT NULL LOOP

                  29

                  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_);

                  36

                  37        variable_name := results.NEXT (variable_name);

                  38     END LOOP;

                  39  END;

                  40  /

                  name_a x---

                  name_b --x-

                  name_c -x-x

                   

                  PL/SQL procedure successfully completed.

                   

                  SQL>

                  • 6. Re: Using Type Record and Table Of and sorting out output
                    bluvulture

                    That is exactly a thing what I was looking for, it works perfectly

                    I was pretty sure that this is possible but I just didn't know how.

                    Thank you.

                    • 7. Re: Using Type Record and Table Of and sorting out output
                      bluvulture

                      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:

                       

                      • FOR i in 1..commands.length() loop
                      • v_check := instr2(string_to_parse, commands(i));
                      • v_check := INSTR2(string_to_parse, ' ', v_check) + 2;
                      •       result_table := SUBSTR(string_to_parse, v_check);
                      •       string_to_parse := result_table;
                      •       result_table := RTRIM(SUBSTR(result_table,
                      •                                    0,
                      •                                    INSTR(result_table, ' ')));
                      •       table_indx := result_table;
                      •       tab_res(table_indx).table_ := result_table;

                       

                      Basically I'm looking for a way to simplify my version of code and make it smaller and more optimized.