This discussion is archived
6 Replies Latest reply: Nov 15, 2013 1:26 PM by Mike Kutz RSS

DML  FORM VIEW SQL OF INSERT STATEMENT

LudwigLustig Newbie
Currently Being Moderated

Hi Guys,

I am getting the following Error: ORA-44003: invalid SQL name...

And yes I red the posts regarding this topic. I am not asking what caused this error but I would like to now if somebody can tell me if it is possible to view the INSERT Statement generated by an DML FORM.

By the way I am using Apex 4.0.2.00.07

Thanks in advance...

  • 1. Re: DML  FORM VIEW SQL OF INSERT STATEMENT
    vincent_deelen Pro
    Currently Being Moderated

    Hi,

     

    To my knowledge the insert statement itself is not directly visible. What you can try is create an new application with only one page and one form, export the application and look at the code of your export file. There you should be able to see what procedures and functions are used for your form. Mostly these functions are part of the apex_util package, you can have a look at the package specifaction  and see if you can get any info out of it. The body of the package is wrapped, so yuo need to use an unwrapper to view that.

     

    Regards,

    Vincent

  • 2. Re: DML  FORM VIEW SQL OF INSERT STATEMENT
    Mike Kutz Expert
    Currently Being Moderated

    DEBUG

     

    Result for "Form on Table" of the default DEPT table:

     

    ..Execute Statement: begin begin insert into "MYWORKSPACE"."DEPT" ( "DEPTNO","DNAME","LOC") values (:DML_BV0001,:DML_BV0002,:DML_BV0003) returning rowid into wwv_flow.g_dml_rowid; end; end;

  • 3. Re: DML  FORM VIEW SQL OF INSERT STATEMENT
    LudwigLustig Newbie
    Currently Being Moderated

    Hi Mike,

    thanks for the answer first.

    Can you please tell me where exactly I can find an executed statement if I puted on the Debug-Mode?

    cheers

  • 4. Re: DML  FORM VIEW SQL OF INSERT STATEMENT
    Mike Kutz Expert
    Currently Being Moderated

    You put the page in DEBUG mode. (ie click the DEBUG button on the bottom)

    Run the page

    Then you click the "View DEBUG".

     

    That will pop-up a window with a Report of recent page runs (that were ran in DEBUG mode) for your session.  (filters are automatically added)

    Within the details, you will see A LOT of information.

    This is where you view everything about the back-end processing of an APEX page.

    This is also where you see all the output from your custom PL/SQL code when you use the APEX_DEBUG utility.

     

    I suggest you look at that page very closely so you know what type of information shows up there.

    At some point in time, you'll need to know what the server is thinking to figure out why something didn't do what you thought it should do.

     

    To find the INSERT statement, you'll have to look through that large report.

    The easiest way is just to search for the word INSERT

     

    MK

  • 5. Re: DML  FORM VIEW SQL OF INSERT STATEMENT
    LudwigLustig Newbie
    Currently Being Moderated

    Hi Mike,

    thank you, your answer was really helpful. I found out that the value for my primary key wasn't stored in the corresponding item, i fixed that problem by changing the item from hidden to read only( it seems a known problem that you cant store values in hidden items ).

    The problem with the information shown on the "View Debug" page is, that it is not complete, so the important parts needed for my analysis is not shown. But if i am querying the "apex_debug_messages" view I obtain the session id in which the sql statement was executed.

    Now I am searching for a view or table inside the oracle db, where executed sql statements are stored (temporarly) and try to find the statement  over the session id... I let you know when i have some results.

     

    Cheers and thanks again

  • 6. Re: DML  FORM VIEW SQL OF INSERT STATEMENT
    Mike Kutz Expert
    Currently Being Moderated

    You're better off asking that question in the General forum.

    Where can I find the SQL statement that was ran???

     

    Make sure you add a link to this thread.

    (and make sure you google for the answer...)

     

    Most likely, you are looking for the view:  v$sql

     

    MK

Legend

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