on 08-20-2007 10:29 AM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
87 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.