How to write Stored Procedure call

[Former Member]

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

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

Akshay,

Thanks for the info, but could you please tell em how to create a SP for the inserting the data that I gave above. I want to siple SP to insert data into database using JDBC adapter...

Xier

[manuel_bassani · Accepted solution]

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 <a href="http://help.sap.com/saphelp_nw04/helpdata/en/14/80243b4a66ae0ce10000000a11402f/frameset.htm">Document Formats for the Receiver JDBC Adapter</a>)

Regards,

Manuel