1 2 3 Previous Next 40 Replies Latest reply: Oct 3, 2013 11:54 AM by Mark D Powell RSS

    PL/SQL CRUD matrix from a source code?

    bluvulture

      I am trying to create a CRUD matrix for my function from a source code of that function, so I created a procedure that will read a source code.

       

      create or replace procedure test_

      IS

      CURSOR c_text is

           SELECT USER_SOURCE.TEXT

           FROM USER_SOURCE

           WHERE USER_SOURCE.name='TEST_FUNCTION'

           AND USER_SOURCE.type='FUNCTION';

           order by line; 

           v_single_text varchar2(4000);

           v_tmp_text varchar2(10000) := ' ';


      begin


      open c_text;

           loop

           fetch c_text into v_single_text;

           exit when c_text%notfound;

           v_tmp_text := v_tmp_text|| chr(10) || rtrim(v_single_text);

           dbms_output.put_line(v_single_text);

           end loop;


      close c_text;


      end test_;

      And that works very good for me, I get the source code of my desired function. It's a very simple function and I use this to learn PL/SQL. Output of that procedure look's like this.

       

      function test_funkction Return varchar2

      IS

      kpp_value varchar2(20);


      begin


      select KPP into kpp_value from CUSTOMER where CUSTOMER_ID = 200713; 


      dbms_output.put_line (kpp_value);


      Return kpp_value;


      end test_function;

      Now, how to parse the string I've got in the output to get a desired result, my result should be like this

      ==TABLE_NAME==========OPERATIONS== 

      CUSTOMER - R - - 

      ==================================

        • 1. Re: PL/SQL CRUD matrix from a source code?
          _Karthick_

          To get source code you could simply use DBMS_METADATA. And to know the dependent objects in a procedure or function you can just query ALL_DEPENDENCIES.

           

          But its certain that I am not clear on what's your objective is!!.

          • 2. Re: PL/SQL CRUD matrix from a source code?
            bluvulture

            I can't use dependencies. It's an assigment for me, in a way to learn PL/SQL, but I'm stuck at parsing the code, -

            • 3. Re: PL/SQL CRUD matrix from a source code?
              bencol

              I think you'll have difficulty, how do you parse

              select col1

                    ,(select col1

                      from   tab2

                     )

              --from   tabl1

              from   tab4

              where  col2 in (select col2

                              from   tab3

                             );

                            

              update

                (select t1.col1 t1_col1

                       ,t2.col1 t2_col2

                 from   t1

                       ,t2

                 where  t1.col2 = t2.col2

                )

              set t1_col1 = t2_col1;

              ?

              What are you hoping the achieve? What practical purpose will you CRUD matrix have?

              • 4. Re: PL/SQL CRUD matrix from a source code?
                bluvulture

                I don't have idea how to parse it, I'am a first time user of a PL/SQL and trough this task I hope to learn PL/SQL basic stuffs.

                 

                 

                 

                The procedure should do following:

                 

                Read source code of PL/SQL function function_name source code should be taken from Oracle system tables)

                Identify what tables are accessed in the function by SQL queries. What kind of operations are used?

                Print the result DBMS_OUTPUT as CRUD (Create, Read, Update, Delete) matrix, like:

                 

                ==TABLE_NAME==========OPERATIONS==

                CUSTOMER                            C R U –

                ======================================

                • 5. Re: PL/SQL CRUD matrix from a source code?
                  _Karthick_

                  There are lot of different way to learn and test your coding skills. I don't feel this is one of them. Ask your assigner to give a better problem that would actually help in real life!!

                   

                  This is just that you are trying to reinvent the wheel.

                  • 6. Re: PL/SQL CRUD matrix from a source code?
                    bluvulture

                    I am aware of that, but for now I am stuck with this task, and every help for me is a like a blessing

                    • 7. Re: PL/SQL CRUD matrix from a source code?
                      _Karthick_

                      If you want to build a parser, you need to have rules defined that will determine what need to be parsed. If you have such a rule already in place please share it with us. We can convert that rule into technical solution.

                       

                      For example if the rule says "A table is one which is after the keyword FROM and ends with space" then this can easily be converted into a technical solution using a regular expression or using conventional substr and instr. But will it logically return the table name that is valid is still a question.

                      • 8. Re: PL/SQL CRUD matrix from a source code?
                        bluvulture

                        I was thinking the same thing wright now. In my example when a parser get's to the keyword FROM and after that is a space and a word, that word is a name of my table that is being accessed with the operation SELECT.

                         

                        select KPP into kpp_value from CUSTOMER where CUSTOMER_ID = 200713;

                        • 9. Re: PL/SQL CRUD matrix from a source code?
                          _Karthick_

                          Keeping whatever i have already said, this could be a start

                           

                          SQL> with t
                             2  as
                             3  (
                             4  select 'select KPP into kpp_value from CUSTOMER where CUSTOMER_ID = 200713;' str
                             5    from dual
                             6  )
                             7  select regexp_replace
                             8         (
                             9               regexp_substr
                            10               (
                            11                    str
                            12                  , 'FROM [^ ]+'
                            13                  , 1
                            14                  , 1
                            15                  , 'i'
                            16               )
                            17             , 'FROM '
                            18             , ''
                            19             , 1
                            20             , 1
                            21             , 'i'
                            22         ) table_name
                            23    from t;

                           

                          TABLE_NA
                          --------
                          CUSTOMER

                           

                          SQL>

                          • 10. Re: PL/SQL CRUD matrix from a source code?
                            bluvulture

                            Thank you, I will try it.

                            • 11. Re: PL/SQL CRUD matrix from a source code?
                              bluvulture

                              I have managed to do it, but it will only work with my simple function, now I want to make a procedure that will work with any function.

                              Source code below.

                               

                              create or replace procedure test_

                               

                               

                              IS

                               

                               

                                v_string_fnc varchar2(10000) := UPPER('function test_function

                               

                               

                               

                                Return varchar2

                               

                                IS

                               

                                  kpp_value varchar2(20);

                               

                                 

                               

                                    begin

                               

                                 

                               

                                    select KPP into kpp_value from CUSTOMER where CUSTOMER_ID = 200713;

                               

                               

                               

                                    dbms_output.put_line (kpp_value);

                               

                                  

                               

                                Return kpp_value;

                               

                               

                               

                                end test_function;');

                               

                               

                                v_check PLS_INTEGER;

                               

                               

                                CURSOR c_text is

                                  SELECT USER_SOURCE.TEXT

                                    FROM USER_SOURCE

                                   WHERE USER_SOURCE.name = 'TEST_FUNCTION'

                                     AND USER_SOURCE.type = 'FUNCTION'

                                   order by line;

                               

                               

                                v_single_text varchar2(4000);

                                v_tmp_text    varchar2(10000) := ' ';

                               

                               

                                /*v_string      varchar2(10000);*/

                               

                               

                                insert_flag char := '-';

                                read_flag   char := '-';

                                update_flag char := '-';

                                delete_flag char := '-';

                                empty_space char(34) := '                             ';

                                underline   char(42) := '==========================================';

                               

                               

                                /*v_txt         varchar2(10000) := ' ';*/

                               

                               

                                result_table varchar2(1000) := '/';

                               

                               

                              begin

                               

                               

                                /*execute immediate 'create table crud_table_matrix(tables_used varchar2(20), operations_used varchar2(20))';

                                insert into crud_table_matrix(tables_used, operations_used) values ('empty', insert_char || read_char || update_char || delete_char);  */

                               

                               

                                open c_text;

                               

                               

                                loop

                                  fetch c_text

                                    into v_single_text;

                                  exit when c_text%notfound;

                                  v_tmp_text := v_tmp_text || chr(10) || rtrim(v_single_text);

                               

                                  /* print source code*/

                                  /*dbms_output.put_line(v_single_text);*/

                                

                                end loop;

                               

                               

                                close c_text;

                               

                               

                                /*DELETE SEARCH*/

                               

                               

                                v_check := instr(v_string_fnc, 'DELETE ');

                               

                               

                                if v_check < 1 then

                                  dbms_output.put_line('THERE IS NO DELETE COMMAND');

                                else

                                  dbms_output.put_line('THERE IS A DELETE COMMAND');

                                  delete_flag  := 'D';

                                  v_check      := instr(v_string_fnc, 'FROM ');

                                  v_check      := v_check + 5;

                                  result_table := substr(v_string_fnc, v_check);

                                  result_table := substr(result_table, 0, instr(result_table, ' '));

                                  dbms_output.put_line('TABLE AFFECTED BY DELETE: ' || result_table);

                                end if;

                               

                               

                                /*SELECT SEARCH*/

                               

                               

                                v_check := instr(v_string_fnc, 'SELECT ');

                                if v_check < 1 then

                                  dbms_output.put_line('THERE IS NO READ COMMAND');

                                else

                                  dbms_output.put_line('THERE IS A READ COMMAND');

                                  read_flag    := 'R';

                                  v_check      := instr(v_string_fnc, 'FROM ');

                                  v_check      := v_check + 5;

                                  result_table := substr(v_string_fnc, v_check);

                                  result_table := substr(result_table, 0, instr(result_table, ' '));

                                  dbms_output.put_line('TABLE AFFECTED BY READ: ' || result_table);

                               

                                end if;

                               

                               

                                /*UPDATE SEARCH*/

                                v_check := instr(v_string_fnc, 'UPDATE ');

                                if v_check < 1 then

                                  dbms_output.put_line('THERE IS NO UPDATE COMMAND');

                                else

                                  dbms_output.put_line('THERE IS A UPDATE COMMAND');

                                  update_flag  := 'U';

                                  v_check      := instr(v_string_fnc, 'FROM ');

                                  v_check      := v_check + 5;

                                  result_table := substr(v_string_fnc, v_check);

                                  result_table := substr(result_table, 0, instr(result_table, ' '));

                                  dbms_output.put_line('TABLE AFFECTED BY UPDATE: ' || result_table);

                               

                                end if;

                               

                               

                                /*INSERT SEARCH*/

                                v_check := instr(v_string_fnc, 'INSERT ');

                                if v_check < 1 then

                                  dbms_output.put_line('THERE IS NO CREATE COMMAND');

                                else

                                  dbms_output.put_line('THERE IS A CREATE COMMAND');

                                  insert_flag  := 'C';

                                  v_check      := instr(v_string_fnc, 'FROM ');

                                  v_check      := v_check + 5;

                                  result_table := substr(v_string_fnc, v_check);

                                  result_table := substr(result_table, 0, instr(result_table, ' '));

                                  dbms_output.put_line('TABLE AFFECTED BY CREATE: ' || result_table);

                                end if;

                                dbms_output.put_line(' ');

                                dbms_output.put_line('==========' || 'TABLE_NAME' || '==========' ||

                                                     'OPERATIONS' || '==');

                                dbms_output.put_line(empty_space || insert_flag || read_flag ||

                                                     update_flag || delete_flag);

                                dbms_output.put_line(underline);

                               

                               

                              end test_;

                               

                              With that procedure I can extract and output my code, dbms needs a bit clean up but it will give the result I need.

                              Now a few questions, how to put a source code of my function to a variable that is not predefined, here is v_string_fnc but it needs to be predefined to work.

                              And how to link a certain operation with the table, here in my example is easy, one SELECT and keyword FROM that gives me a name of table.

                              Struggling continues

                              • 12. Re: PL/SQL CRUD matrix from a source code?
                                ranit B

                                Karthick_Arp wrote:

                                 

                                There are lot of different way to learn and test your coding skills. I don't feel this is one of them. Ask your assigner to give a better problem that would actually help in real life!!

                                 

                                This is just that you are trying to reinvent the wheel.

                                 

                                Hi Karthick,

                                 

                                I don't exactly agree to this point. Even I faced a similar requirement some time back - https://forums.oracle.com/thread/2560827

                                and https://forums.oracle.com/thread/2564061

                                • 13. Re: PL/SQL CRUD matrix from a source code?
                                  _Karthick_

                                  I don't exactly understand what you are disagreeing on

                                  • 14. Re: PL/SQL CRUD matrix from a source code?
                                    ranit B

                                    I was disgreeing on the point that - "Ask your assigner to give a better problem that would actually help in real life!!"

                                     

                                    I found this problem to be very trivial... and a tool or utility (if developed) to get a CRUD output of PL/SQL code will be helpful.

                                     

                                    FYI - A 3rd party tool called "Clear SQL" does this work but also has few limitations.


                                    1 2 3 Previous Next