PL/SQL: Execute procedure in another procedure

Question

I'm executing a procedure in another procedure.

Procedure 1:

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;
/

Procedure 2:

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 VARCHAR2(1000);
    l_test_status VARCHAR2(100);
  begin
    l_message := coalesce(a_test.description, a_test.name)||' ['||round(a_test.execution_time,3)||' sec]';
    Dbms_Output.Put_Line(a_test.result||'test_result');
    --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';
      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';
      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';
      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;

It doesn't store the message and description in test_status_table table, but when I print them it gets displayed.

Am I doing something wrong?

You've already got the answer (posted by user7294900). However, why do you use dynamic SQL in proc_test_status_table procedure? There's nothing *dynamic* in it. — Littlefoot, Oct 22 '18 at 05:16

APC · Accepted Answer · 2018-11-14T14:56:42.573

Probably you just need to commit your logged message in the procedure.

Logging is one of the few cases where an autonomous transaction is valid: generally we want to log our messages without interfering with the transaction we're logging.

Also, you don't need to use dynamic SQL here. Just reference the parameters in the VALUES clause.

CREATE OR REPLACE PROCEDURE proc_test_status_table(
     p_test_description IN VARCHAR2,
     p_test_status IN varchar2)
AS
    l_sql VARCHAR2(4000);
    PRAGMA autonomous_transaction;
BEGIN
    insert into test_status_table(test_description, test_status)
    values ( p_test_description, p_test_status);
    commit;
END;
/

What is the value of AUTONOMOUS_TRANSACTION here? The OP appears to be building a testing framework. With an autonomous transaction we can persist a log message without affecting the wider transaction i.e. the test. Committing log messages without the AUTONOMOUS_TRANSACTION pragma can have side-effects which might break other tests (such as ORA-01022, ORA-1555) or just might make teardown more complicated.

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

1

You are missing commit; statement

After direct path insert: the table cannot be read until the insert is committed.

Add commit at the end of procedure to view inserted records

edited Jun 20 '20 at 09:12

Community

1
1

answered Oct 22 '18 at 05:14

Ori Marko

56,308
23
131
233

score -1 · Answer 3 · answered Nov 14 '18 at 04:37

-1

You forgot to commit. After each insert statement there must be commit if you want to store it into the table.

answered Nov 14 '18 at 04:37

sp324

285
2
20

@SuccessShrestha - of course you are entitled to choose whichever answer you accept . But I think you should explain why you unaccepted my answer in favour of an answer which is just a subset of my answer. – APC Nov 14 '18 at 14:59

PL/SQL: Execute procedure in another procedure

3 Answers3