Htykuut

I have a table call OUTGOING which has many fields but the ones to be populated in this situation is:

• FILENUMBER


• OUTGOINGDATE


• DEPARTMENT

now i have a report which whas an sql

 SELECT APEX_ITEM.CHECKBOX2(1,registry.filenumber) "Select",



  INCOMINGREQUESTNOTIFICATION.REQUESTEDFILE as REQUESTEDFILE,

    INCOMINGREQUESTNOTIFICATION.FILENUMBER as FILENUMBER,

    INCOMINGREQUESTNOTIFICATION.REQUESTEDDEPARTMENT as REQUESTEDDEPARTMENT,

    INCOMINGREQUESTNOTIFICATION.REQUESTDATE as REQUESTDATE,



    REGISTRY.STATUS as STATUS 

 from REGISTRY REGISTRY,

    INCOMINGREQUESTNOTIFICATION INCOMINGREQUESTNOTIFICATION 

 where REGISTRY.FILENUMBER(+) =INCOMINGREQUESTNOTIFICATION .FILENUMBER

and INCOMINGREQUESTNOTIFICATION.STATUS ='PENDING'

which is fine .. what i need is for

• INCOMINGREQUESTNOTIFICATION.FILENUMBER as FILENUMBER


• INCOMINGREQUESTNOTIFICATION.REQUESTEDDEPARTMENT as REQUESTEDDEPARTMENT


• and sysdate

to be inserted in the outgoing table under the relevant names of course.

I have a pl/sql

 DECLARE

  L_FILENUMBER WWV_FLOW_GLOBAL.VC_ARR2;

BEGIN

  L_FILENUMBER := APEX_APPLICATION.G_F01;

  FOR IDX IN 1 .. L_FILENUMBER.COUNT

  LOOP

    IF L_FILENUMBER(IDX) IS NOT NULL THEN

      INSERT INTO OUTGOING

     (FILENUMBER,OUTGOINGDATE,DEPARTMENT)



VALUES

((to_number(APEX_APPLICATION.G_F01(1)))     

   ,SYSDATE

,to_char(APEX_APPLICATION.G_F02(2))  )



     ;



    END IF;

  END LOOP;

END;

which is not working.. However if i leave only filenumber

DECLARE

  L_FILENUMBER WWV_FLOW_GLOBAL.VC_ARR2;

BEGIN

  L_FILENUMBER := APEX_APPLICATION.G_F01;

  FOR IDX IN 1 .. L_FILENUMBER.COUNT

  LOOP

    IF L_FILENUMBER(IDX) IS NOT NULL THEN

      INSERT INTO OUTGOING

     (FILENUMBER)



VALUES

((to_number(APEX_APPLICATION.G_F01(1)))     



     ;



    END IF;

  END LOOP;

END;

its inserting only the file number fine . This is all being done via a submit button.

NB: i also tried putting outgoing date and department in the declare statement but it still doesnt work

I'd suggest you to learn how to use table aliases. SELECT you wrote is difficult to read due to VERY long table & column names; alias would certainly help.

As of your question: saying that "it is not working" doesn't help at all. What exactly doesn't work? Is there any error? If so, which one? Did you run the page in debug mode and check what's going on? If not, do that.

Apart from that, code you wrote doesn't make much sense - you're trying to insert the same values all over again. E.g. shouldn't APEX_APPLICATION.G_F01(1) be APEX_APPLICATION.G_F01(IDX)? Something like this:

begin

  for idx in 1 .. apex_application.g_f01.count

  loop

    if apex_application.g_f01(idx) is not null then

       insert into outgoing 

         (filenumber, 

          outgoingdate, 

          department

         )

         values

         (apex_application.g_f01(idx),

          sysdate,

          apex_application.g_f02(idx)

         );

    end if;

  end loop;

end;

Check (by inspecting the page) whether (tabular form?) items really are those that you've used (G_F01 and G_F02). If not, you'll have to fix that.

I didn't use any TO_NUMBER nor TO_CHAR functions; I don't know whether your really need them. Even if you don't have them, Oracle will perform implicit datatype conversion when possible, but it'll fail if you try to put e.g. 'abc123' into a NUMBER datatype column. You didn't share that information so - I left those out. Apply them if necessary.