I have problem with Oracle Apex 21 he give me error Ora 01400. I can't find when this code obtain null value. In sqldeveloper without 'insert' all work fine. But in Oracle Apex he wan't work. Probably I wrote something wrong. Thaks for understanding and helping.

desc pipiki 
id NUMBER  no NULL,
tp1 VARCHAR2(512) not NULL,
col3 date,
col4 date,
col5 number,
col6 number

desc cc
id number not NULL
col1 varchar2 not NULL
col2 varchar2 not NULL
col3 varchar2 
col4 varchar2 not NULL
col5 DATE
col6 DATE
col7 number
col8 number
col9 number
col10 number

declare
     l_cur_1 varchar2(255);
     l_cur_2 varchar2(255) := :val2;
     l_np1 varchar2(255) := :val3;
     l_np2 varchar2(255) := :val4;
     l_ai NUMBER;
     l_ui NUMBER;
     tp1 VARCHAR2(512);
     tp2 NUMBER;
BEGIN
    l_ai := gui /* <- function */;
    select uu into l_cur_1 
    from cc 
    where upper(uu) = upper(v('APP_USER'))
    group by uu;
    l_ui := uuu /* <- function */;
    if(l_ui <= '0') THEN
        tp1 := 0;
    else 
        tp1 := rpipi(l_cur_1,l_cur_2);
        UPDATE pipiki 
        set col = '0'
        where pic = tp1;
        commit;
        tp1 := 0;
        tp1 := rpipi(l_cur_1,l_np1);
        if(tp1 <= '0')THEN
            tp1 := 0;
        ELSE
            insert into pipiki(col1,col2,col3,col4,col5)
            values(tp1,SYSDATE,sysdate,l_ui,'0');
            commit;
        end if;
    end if;
    select ii into tp2 from pipiki where rsa = tp1;
    update cc
    set pig10 = tp2
    where upper(rsos) = upper(l_cur_1);
    commit;
end;

CodePudding user response：

You didn't post tables' descriptions, so we can't know which columns are declared as NOT NULL.

Candidates are:

• table pipiki:
  • col1 (you're inserting tp1 into it)
    • tp1 gets its value from rpipi function - check what it returns for passed parameters
  • col4 (you're inserting l_ui)
    • l_ui gets its value from uuu function (it doesn't accept any parameters so I presume it is OK)
• table cc
  • pig10 (you're updating it with tp2)
    • tp2 is returned from the pig table's ii column; as that query didn't fail with no_data_found, I presume it got its value

As you're running it in Apex, enable debug mode, run the page and - once it fails - check debug info.

CodePudding user response：

To check the values of variables in a pl/sql process in the apex session, use the API apex_debug. Add a call to apex_debug in pl/sql block, enable debug and look for the results of the call in the the debug data. Example:

declare
     l_cur_1 varchar2(255);
< rest of your code>
        ELSE
          apex_debug.info(
            p_message => 'Before pipiki insert: col1: %0, l_ui: %1',
            p0 => tp1,
            p1 => l_ui);
          insert into pipiki(col1,col2,col3,col4,col5)
            values(tp1,SYSDATE,sysdate,l_ui,'0');
< rest of your code>

Run the page process in debug and look for the string "Before pipiki insert". That will show you the values of tp1 and l_ui.

Add more calls to apex_debug to do more debugging if needed.