utPLSQL - Passing argument(l_message) into a procedure. Not passing the value.

Question

I have a procedure which insert values into a table.

CREATE OR REPLACE PROCEDURE proc_test_status_table(
     p_test_description IN VARCHAR2,
     p_test_status IN varchar2)                                                
 AS    
  l_sql VARCHAR2(4000);
  BEGIN
  l_sql := 'insert into test_status_table(test_description, test_status)
            values
            ( '''||p_test_description||''',
              '''||p_test_status||''')';

  EXECUTE IMMEDIATE (l_sql);

END;
/

on ut_documentation_reporter i have modified procedure after_calling_test the code to:

overriding member procedure after_calling_test(self in out nocopy ut_documentation_reporter, a_test ut_test) as
l_message varchar2(4000);
l_test_description VARCHAR(1000);
l_test_status VARCHAR(100);                                             
begin
l_message := coalesce(a_test.description, a_test.name)||' ['||round(a_test.execution_time,3)||' sec]';
--if test failed, then add it to the failures list, print failure with number
if a_test.result = ut_utils.gc_disabled then
  self.print_yellow_text(l_message || ' (DISABLED)');
  l_test_description := 'DISABLED';

  --calling procedure
  proc_test_status_table(l_message, l_test_description);

elsif a_test.result = ut_utils.gc_success then
  self.print_green_text(l_message);                            
  l_test_description := 'PASS';

  --calling procedure
  proc_test_status_table(l_message, l_test_description);

elsif a_test.result > ut_utils.gc_success then
  failed_test_running_count := failed_test_running_count + 1;
  self.print_red_text(l_message || ' (FAILED - ' || failed_test_running_count || ')');     
  l_test_description := 'FAIL';

  --calling procedure
  proc_test_status_table(l_message, l_test_description);

end if;

-- reproduce the output from before/after procedures and the test
self.print_clob(a_test.get_serveroutputs); end;

the arguments are not being passed. neither can i print the value inside the procedure. I want to add the message the test status/description into an existing table.

I suggest that you add a DBMS_OUTPUT.PUT_LINE before the line which reads `if a_test.result = ut_utils.gc_disabled then` to print out the value of `a_test.result`. — Bob Jarvis - Reinstate Monica, Oct 14 '18 at 20:54
Why are you using SQL Dynamic to do a direct insert? Sentence will parse every time is executed. — alvalongo, Jan 17 '19 at 17:04

score 0 · Accepted Answer · answered Nov 30 '18 at 05:08

0

You might have missed COMMIT in the procedure. Insert statement requires commit to insert data into the table.

answered Nov 30 '18 at 05:08

sp324

275
1
20

jgebal · Answer 2 · 2019-01-21T08:26:20.820

You're missing a commit.
Make sure to make this an autonomous transaction otherwise you're committing everything including changes done by a test.
Create a custom reporter (for example my_reporter) to make this insert. That way you separate responsibilities and avoid loosing your changes whenever you re-install/upgrade utPLSQL.
run your tests with multiple reporters using utplsql-cli.
When using dynamic sql, use bind variables - dont concatenate - your SQL will be much faster and also much safer (resistant to SQL-injection)

Example:

create or replace procedure proc_test_status_table(
  p_test_description in varchar2,
  p_test_status in varchar2
) as    
  pragma auotonomous_transaction;
  l_sql varchar2(4000);
begin

  execute immediate 
    'insert into test_status_table(test_description, test_status)
     values( :desc, :stataus )'
     using p_test_description, p_test_status;
  commit;
end;
/

create or replace type my_reporter under ut_reporter_base(
  constructor function my_reporter(self in out nocopy my_reporter) return self as result,
  overriding member procedure before_calling_test(self in out nocopy my_reporter, a_test ut_test),
  overriding member procedure after_calling_test(self in out nocopy my_reporter, a_test ut_test),
  overriding member function get_description return varchar2

)
/

create or replace type body my_reporter as

  constructor function my_reporter(self in out nocopy my_reporter) return self as result,
  begin
    self.init($$plsql_unit);
    return;
  end;
  overriding member procedure before_calling_test(self in out nocopy my_reporter, a_test ut_test) is 
  begin 
    proc_test_status_table(
      coalesce(a_test.description, a_test.name),
      'Starting'
    );  
  end;
  overriding member procedure after_calling_test(self in out nocopy my_reporter, a_test ut_test) is 
  begin 
    proc_test_status_table(
      coalesce(a_test.description, a_test.name)||' ['||round(a_test.execution_time,3)||' sec]',
      ut_utils.test_result_to_char(a_test.result)
    );  
  end;
  overriding member function get_description return varchar2 is
  begin
    return 'My custom reporter to insert test status data into test_status_table';
  end;

end;
/

Also note: It would be great, if you could post such issues on the utPLSQL project directly. https://github.com/utPLSQL/utPLSQL/issues — jgebal, Jan 21 '19 at 08:27

utPLSQL - Passing argument(l_message) into a procedure. Not passing the value.

2 Answers2