What is wrong with this PL/SQL code, and how to debug it in Apex?

DannyS-Oracle

Hi guys, I am fairly new in Apex and still struggling on some basic things like debugging this PL/SQL procedure. Hopefully someone can help me on this:

I made this PL/SQL procedure to do some insert operations by looping through another table's records:

DECLARE    l_test_id number := :P5_ID;    l_test_type varchar2(50) := :P5_TEST_TYPE;    CURSOR c_criteria is      SELECT ID FROM DR_APP_TABLETOP_CRITERIA;BEGIN     IF l_test_type = 'Tabletop' THEN        FOR r_criteria IN c_criteria         LOOP            INSERT INTO DR_APP_TABLETOP_WORKBOOK(ID_TEST, ID_CRITERIA)            VALUES (l_test_id, r_criteria.ID);        END LOOP;        COMMIT;    END IF;END;

There is no error on the Apex debugger, but the INSERT operations never happened. Does anyone notice error in this code? Or, can someone teach me on how to print simple log message on the debugger? Something like "System.out.println()" in Java - so I can debug the PL/SQL easier. Surprisingly this thing is not easy to find on the Internet: I tried DBMS_OUTPUT and APEX_DEBUG functions but got no luck - probably I was doing it wrong .... Any help will be appreciated!

In case you wonder, I used Apex v5.0.4.

Thank you!

Bharat G

Hi ,

Here three things we need to make sure:

1. SELECT ID FROM DR_APP_TABLETOP_CRITERIA;
2. IF l_test_type = 'Tabletop' THEN
3. Does APEX is running on which schema and does this schema having these two tables permissions ?

In the first one, are we having records ?

In the second one, are we having the same value (Tabletop) in that variable? I mean we may get TABLETOP or TableTop or something like that, So in this case also it will not enter if statement

In the third one, which schema we are on in apex, and does this schema have access to those tables with read, Insert permissions ?

Regards,

Bharat

Sunil Bhatia

Hi,

Did you log into custom table that actually it is going into the loop or not. Also, if the process is binded to correct button click?

If it is going into loop:

You can have exception handling:

DECLARE      l_test_id number := :P5_ID;      l_test_type varchar2(50) := :P5_TEST_TYPE;      CURSOR c_criteria is        SELECT ID FROM DR_APP_TABLETOP_CRITERIA;  BEGIN   insert into temp_table('Procedure Called');    IF l_test_type = 'Tabletop' THEN          FOR r_criteria IN c_criteria           LOOP  insert into temp_table('Inside Loop With ID: ' || r_criteria.ID);            INSERT INTO DR_APP_TABLETOP_WORKBOOK(ID_TEST, ID_CRITERIA)              VALUES (l_test_id, r_criteria.ID);          END LOOP;          COMMIT;      END IF;  EXCEPTION WHEN OTHERS THEN     raise_application_error(-20001,SQLERRM);END;  

TexasApexDeveloper

Please re-post to the pl/sql /sql support forum, since this question has NOTHING to do with APEX..

Thank you,

Tony Miller
LuvMuffin Software
Los Alamos, NM

DannyS-Oracle

"Did you log into custom table that actually it is going into the loop or not"

Hi Sunil, thanks for replying, sorry I could not understand that part of your question ... what do you mean with custom table?

The procedure is done right after "Process Row" procedure when a user is submitting a form, and before "reset page" procedure called. I already set the "Return Key to Item" attribute to P5_ID in the "Process Row" procedure, btw.

I just tried the EXCEPTIONS that you suggested, but it did not throw anything in the debugger console ...

DannyS-Oracle

Hi Tony, before you triumphantly write NOTHING in capslock, please notice that I was asking in the subject and body about how to debug and print log from the PL/SQL procedure to Apex debugger console - thus it is relevant in this forum.

Scott Wesley

No need for custom table, there is apex_debug.message() procedure that you can include within your PL/SQL, eg:

apex_debug.message('l_test_type:'||l_test_type);

Then run your page in debug mode, and submit the page to test your process.

Open the relevant debug log and look for your output. Your process name should at least be mentioned, where you might have a condition that skips actually processing it.

Bharat G

Hi ,

Here three things we need to make sure:

1. SELECT ID FROM DR_APP_TABLETOP_CRITERIA;
2. IF l_test_type = 'Tabletop' THEN
3. Does APEX is running on which schema and does this schema having these two tables permissions ?

In the first one, are we having records ?

In the second one, are we having the same value (Tabletop) in that variable? I mean we may get TABLETOP or TableTop or something like that, So in this case also it will not enter if statement

In the third one, which schema we are on in apex, and does this schema have access to those tables with read, Insert permissions ?

Regards,

Bharat

fac586

https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_debug.htm#AEAPI29182https://docs.oracle.com/cd/E59726_01/doc.50/e39147/debug_mode.htm#HTMDB10003https://docs.oracle.com/cd/E59726_01/doc.50/e39147/debug_mode.htm#HTMDB10003Danny Sindra-Oracle wrote:Hi guys, I am fairly new in Apex and still struggling on some basic things like debugging this PL/SQL procedure. Hopefully someone can help me on this:I made this PL/SQL procedure to do some insert operations by looping through another table's records:

1. DECLARE
2. l_test_idnumber:=:P5_ID;
3. l_test_typevarchar2(50):=:P5_TEST_TYPE;
4. CURSORc_criteriais
5. SELECTIDFROMDR_APP_TABLETOP_CRITERIA;
6. BEGIN
7. IFl_test_type='Tabletop'THEN
8. FORr_criteriaINc_criteria
9. LOOP
10. INSERTINTODR_APP_TABLETOP_WORKBOOK(ID_TEST,ID_CRITERIA)
11. VALUES(l_test_id,r_criteria.ID);
12. ENDLOOP;
13. COMMIT;
14. ENDIF;
15. END;

DECLARE  l_test_id number := :P5_ID;  l_test_type varchar2(50) := :P5_TEST_TYPE;  CURSOR c_criteria is  SELECT ID FROM DR_APP_TABLETOP_CRITERIA; BEGIN   IF l_test_type = 'Tabletop' THEN  FOR r_criteria IN c_criteria   LOOP  INSERT INTO DR_APP_TABLETOP_WORKBOOK(ID_TEST, ID_CRITERIA)  VALUES (l_test_id, r_criteria.ID);  END LOOP;  COMMIT;  END IF; END;

There is no error on the Apex debugger, but the INSERT operations never happened. Does anyone notice error in this code? Or, can someone teach me on how to print simple log message on the debugger? Something like "System.out.println()" in Java - so I can debug the PL/SQL easier. Surprisingly this thing is not easy to find on the Internet: I tried DBMS_OUTPUT and APEX_DEBUG functions but got no luck - probably I was doing it wrong .... 

There aren't any syntax errors in the code. We can't comment on the existence of runtime or logical errors as we are not party to your database, data, the requirements of your application, or the assumptions you are making. (For example in line 7: Will P5_TEST_TYPE actually contain the value "Tabletop"? Should this expression use a case-insensitive comparison?)

Debug mode and the debug API are well documented, and adding instrumentation using apex_debug.message to the process block should produce output in the debug trace. If you are not seeing this then:

• You may not be interpreting the debug trace correctly.
• You may be looking at the wrong debug trace.
• The process may not have been executed due to other page logic, such as a condition on the process, or a branch at an earlier processing point.

Note that by following recommended APEX practice to use declarative features wherever possible, the internal logic in the process code can be moved to the process condition and the process code simplified to:

begin  insert into dr_app_tabletop_workbook    (   id_test      , id_criteria)  select        to_number(:p5_id)      , id  from        dr_app_tabletop_criteria;end; 

This approach means there is less necessity to add custom instrumentation code within processes as information about the evaluation of conditions is automatically included in the debug trace.

As someone said earlier, a key aspect of utilizing APEX effectively is developing an "APEX mindset", a core principle of which is "write less code".

(Note that I'm not saying that process code shouldn't contain instrumentation, but that it shouldn't contain unnecessary or redundant instrumentation, as APEX will produce a lot of what is required by default.)

Mike Kutz

For the OP

You should remove the COMMIT in all APEX Processes.

APEX automatically calls COMMIT/ROLLBACK based on success/failure of all Processes.

APEX also has the right to ROLLBACK your process and "try again".

This means, you will (eventually) get double inserts for (what appears to be) unknown reasons.

In addition to what fac586 said, use APEX_DEBUG to instrument your code.  (I believe that is what you are seeking)

fac586 wrote:

1. begin
2. APEX_DEBUG.MESSAGE( 'Updating with ID %s.', :p5_id );
4. insert into dr_app_tabletop_workbook
5. (id_test
6. ,id_criteria)
7. select
8. to_number(:p5_id)
9. ,id
10. from
11. dr_app_tabletop_criteria;
13. APEX_DEBUG.MESSAGE( 'Number of rows added = %s', SQL%ROWCOUNT );
14. end;

begin  insert into dr_app_tabletop_workbook  ( id_test  , id_criteria)  select  to_number(:p5_id)  , id  from  dr_app_tabletop_criteria; end; 

MK

DannyS-Oracle

Thanks Bharat for the sharp suggestions. Your first one is correct, I forgot to insert any records on the DR_APP_TABLETOP_CRITERIA table. Sorry for all the trouble, I must be really tired after a long day that I forgot to put the data there.

@fac586 and Mike, thanks for the helpful links and insight - it will be very helpful for me to start learning Apex. I just noticed I should start my debug "with APEX_DEBUG.ENABLE (p_level => 4);". My previous log messages were not appearing in the console because I only enabled the level up to 2. Now it came out properly in the debugger console.

Thanks again for all the patience for this newbie developer, guys (except TexasApexDeveloper). Really appreciate it. Hopefully I will be able to help others as well once I learned more about Apex.

TexasApexDeveloper

I am sorry if I somehow offended you..  I did NOT see th eportion of your message where you did indicate this was APEX related..

Thank you,

Tony Miller

Los Alamos, NM