Skip to Content
avatar image
Former Member

Inserting multiple rows into DB via SQL insert or stored procedure?

I have successfully created an application where I select a row in an output table view from a Microsoft Access DB SQL data source, and get an Oracle stored procedure to save the row in a new table in our Oracle DB.

This works like a charm when selecting one single row in the table view. What I really need though is for the procedure to save multiple rows at once.

My table is configured with selection mode = 'multiple', and the data mapping line between the table and my procedure has mapping scope = 'selected data rows'.

So, I am able to select multiple rows but still my procedure only stores one of the values sent. Does anyone know if this is related to my procedure not handling multiple parameter entries or if it's related to how VC outputs data to a procedure?

Here is output from runtime flex log where you see the three values: 3, 11 and 9 that is sent to the procedure's IN parameter 'P_ID'.

<Row OWNAPPS_TESTSQL_HYTTER_P_ID="3"/><Row OWNAPPS_TESTSQL_HYTTER_P_ID="11"/><Row OWNAPPS_TESTSQL_HYTTER_P_ID="9"/>

Please help

Henning Strand

I am still very, very interested in hearing if anyone has been successful at passing multiple rows to a stored procedure in one submit action.

Edited by: Henning Strand on Apr 11, 2008 1:58 PM

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    May 13, 2008 at 01:36 PM

    Update for all you happy people using Oracle stored procedures with Visual Composer:

    When trying to run a simple procedure that accepts arrays as input parameters, the JDBC connector returns an error message saying: 'PLS-00306: wrong number or types of arguments in call ...'

    I registered this as a customer message with sap support and got an answer back saying:

    Unfotunately our JDBC connector is still not able to work with SP that contain arrays, this is the reason for the behaviour that you're seeing.

    This are the bad news, the good news is that with WebServices we don't have this limitation, so you can achieve the same results by using a WS instead of the Store Procedure.

    I have asked if and when using stored procedures with arrays will be supported - waiting for answer.

    Henning Strand

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      My questions:

      1. Do you have any idea if/when this will be supported?

      2. Wouldn't it be possible to use another JDBC driver for creating jdbc_systems in portal? After all I am uploading Oracle's latest driver and successfully creating bi_jdbc systems (for doing SQL's in VC). I have never though been successful at creating jdbc portal systems, which are required for running stored procedures.

      Answers from SAP:

      1-. We're just analysing how to improve it, probably will be included in a Support Package for the Enhacement Package 1 Version (7.0.1) but we don't have an estimated time for this.

      2-. The problem is more related with the connector framework than with the driver, for this reason we recommend you to use a WebService and discharge this alternative, this will be for sure the best and safe alternative.