5 Replies Latest reply: Jun 3, 2014 7:00 AM by Billy~Verreynne RSS

    want to insert records into table by using API. Is APIs are availables in APEX

    Nagesh.Patil

      Hi All,

       

      I want to know that, is there any API available to insert records in table ?

       

      If it available then please share some documents or any other related information ASAP.

       

      APEX Version - 4.2

      Database - Oracle 11g

       

      Regards,

      Nagesh Patil

        • 1. Re: want to insert records into table by using API. Is APIs are availables in APEX
          Sunil Bhatia

          Hi,

           

          Where will those records be? CSV on server? Or you wish to create a screen and upload a CSV using browse etc? Please write exact requirements/

           

          -- Sunil Bhatia

          • 2. Re: want to insert records into table by using API. Is APIs are availables in APEX
            Nagesh.Patil

            hi sunil,

             

            Thanks for the reply.

             

            I'll tell you my full requirement  :

             

            There is on another application developed in .net with SQL Server database.

            we need information from that application to our APEX application.

            The Admin of that .net application told me that he will insert all records in one of our staging table every midnight.

            Then from our side we will create one DBMS Job which will trigger API (if available) , and that API will took all records from staging table and insert into our master table after doing all data validations or any other validations.

             

            I want to know that , the work APIs doing in Oracle  APPS, can same be done in Oracle APEX. Does it possible in apex.

             

            Regards,

            Nagesh Patil

            • 3. Re: want to insert records into table by using API. Is APIs are availables in APEX
              Sunil Bhatia

              Hi,

               

              Understood your requirements except that why you wish to use APEX for it? If data is already present in staging tables, and you are creating Oracle Jobs to validate and populate master tables, why you need APEX API's further?

               

              The Oracle jobs will populate your master tables after validation. APEX is the front end tool which can display/manipulate/validate your data as per front end needs, but here everything is done using backend jobs. Oracle PL SQL is sufficient to complete your requirement. I dont feel APEX is needed here, unless you are recreating complete .net structure as front end.

               

              -- Sunil Bhatia

              • 4. Re: want to insert records into table by using API. Is APIs are availables in APEX
                Nagesh.Patil

                Thanks sunil.

                 

                Is there any pre-defined APIs to do it.

                 

                I just want to clear my thoughts regarding APIs, like how it is using in oracle Apps same can be use in this case.

                 

                can you plz clear me how can I complete my task.(any workarounds)

                 

                -- Nagesh Patil

                • 5. Re: want to insert records into table by using API. Is APIs are availables in APEX
                  Billy~Verreynne

                  Nagesh.Patil wrote:

                   

                  I want to know that , the work APIs doing in Oracle  APPS, can same be done in Oracle APEX. Does it possible in apex.

                   

                  Apex does not come with Oracle Apps APIs. Oracle Apps is a separate product.

                   

                  Apex itself is a database product consisting out of PL/SQL code and database objects (tables, views, synonyms, etc).

                   

                  Apex is a free product bundled with the database. Part of the database is the PL/SQL engine - and with it, a fairly extensive set of PL/SQL APIs supplied by Oracle for PL/SQL application development. These APIs range from a LOB API, Mail API, TCP client API, HTTP API, to Advance Queue API, parallel processing API and others.

                   

                  See Oracle® Database PL/SQL Packages and Types Reference for details of many of these APIs.

                   

                  Apex supports APIs itself for dealing with dynamic page rendering for example, or using collections, dealing with security, and so on. See Oracle® Application Express API Reference for details.

                   

                  PL/SQL also enables you to create your own APIs for your business processes and logical databases. In fact, this is highly recommended. It provides numerous advantages, and make client application development significantly easier and less dependent on database implementation and technical details.

                   

                  So if you want to create an API for adding an employee for example (this procedure should be part of a package that encapsulates the API):

                  create or replace procedure AddEmployee( .. paramaters go here.. ) is

                  begin

                    .. apply validation rules ..

                    .. apply business rules ..

                    insert into employees(

                      ..columns..

                    ) values(

                      ..parameters/variables..

                    );

                  end;

                   

                  And from an Apex dynamic or post page process, you would call this API with Apex page items as parameters:

                  AddEmployee(

                     param1 => :P1_ITEM1,

                     ..

                     paramn => :P1_ITEMn

                  );