Skip to Content
avatar image
Former Member

How to write Stored Procedure call

Hi All,

Can someone tell me how to write a stored procedure call for inserting following data into a database:

RECTYPEIND CHAR,

JENUMBER CHAR,

COMPANY CHAR,

CONSTANT1 CHAR,

SYSTEMDATE CHAR,

DR_CR_ID CHAR,

AMOUNT CHAR,

MONTH_NUMBER CHAR,

COST_CENTER CHAR,

ORDERNO CHAR,

WBS CHAR,

ACCOUNTID CHAR,

BATCH_ID CHARR,

ASSIGNMENT CHAR,

GL_JOURNAL_CATEGORY CHAR,

PROFIT_CENTER CHAR,

REFDOCNUMBER CHAR

Regards,

XIer

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Aug 20, 2007 at 09:41 AM

    In some DBMSs, the following SQL statement creates a stored procedure:

    create procedure SHOW_SUPPLIERS

    as

    select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME

    from SUPPLIERS, COFFEES

    where SUPPLIERS.SUP_ID = COFFEES.SUP_ID

    order by SUP_NAME

    The following code puts the SQL statement into a string and assigns it to the variable createProcedure, which we will use later:

    String createProcedure = "create procedure SHOW_SUPPLIERS " +

    "as " +

    "select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " +

    "from SUPPLIERS, COFFEES " +

    "where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " +

    "order by SUP_NAME";

    The following code fragment uses the Connection object con to create a Statement object, which is used to send the SQL statement creating the stored procedure to the database:

    Statement stmt = con.createStatement();

    stmt.executeUpdate(createProcedure);

    The procedure SHOW_SUPPLIERS is compiled and stored in the database as a database object that can be called, similar to the way you would call a method.

    -


    Calling a Stored Procedure from JDBC

    CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");

    ResultSet rs = cs.executeQuery();

    Thanks & Regards,

    Akshay Jamgaonkar.

    • Dont Forget To give points for useful answer.

    Add comment
    10|10000 characters needed characters exceeded

    • Hi,

      I assume your scenario is JDBC receiver adapter...

      im using a stored procedure to insert values into an Oracle DB. I created the stored procedure in the DB system:

      create or replace procedure InsertSP(
      RECTYPEIND in CHAR,
      JENUMBER in CHAR,
      COMPANY in CHAR,
      CONSTANT1 in CHAR,
      SYSTEMDATE in CHAR,
      DR_CR_ID in CHAR,
      AMOUNT in CHAR,
      MONTH_NUMBER in CHAR,
      COST_CENTER in CHAR,
      ORDERNO in CHAR,
      WBS in CHAR,
      ACCOUNTID in CHAR,
      BATCH_ID in CHARR,
      ASSIGNMENT in CHAR,
      GL_JOURNAL_CATEGORY in CHAR,
      PROFIT_CENTER in CHAR,
      REFDOCNUMBER in CHAR
      
      ) is begin
      
      Begin
      
      insert into <TABLE NAME>
      values (RECTYPEIND, JENUMBER, COMPANY, CONSTANT1, SYSTEMDATE, DR_CR_ID,
              AMOUNT, MONTH_NUMBER, COST_CENTER, ORDERNO, WBS, ACCOUNTID, BATCH_ID,
              ASSIGNMENT, GL_JOURNAL_CATEGORY, PROFIT_CENTER, REFDOCNUMBER);
      end;
      
      end InsertSP;
      

      at this point you can call the SP from JDBC adapter. set action="EXECUTE" (see Document" target="_blank">http://help.sap.com/saphelp_nw04/helpdata/en/14/80243b4a66ae0ce10000000a11402f/frameset.htm">Document Formats for the Receiver JDBC Adapter)

      Regards,

      Manuel