This discussion is archived
7 Replies Latest reply: Oct 10, 2013 3:53 AM by bluvulture RSS

Using Type Record and Table Of and sorting out output

bluvulture Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    > 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 Newbie
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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.  

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points