Calling FND_REQUEST.SUBMIT_REQUEST from a JSP

Hello All.

I need to call the standard Oracle call FND_REQUEST.SUBMIT_REQUEST from a JSP passing it parameters and getting the return value.

So far, I have seen on the internet that I can use "ConcurrentRequest" which is part of the oracle.apps.fnd.cp.request package.

Can anyone help.

Cheers
LVL 3
ramiseesniblicAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sonicefuCommented:
Submit a concurrent program from PL/SQL program. Sample:
FND_REQUEST.submit_request(
                   application => 'PO',
                   program     => 'RVCTP',
                   description => NULL,
                   argument1   => 'BATCH',
                   argument2   => 123456);

Open in new window

0
sonicefuCommented:

create or replace procedure submitrequest_test is 
    RequestID number; 
    ReturnCode number;
    ErrorMessage varchar2(1000);
begin 
    begin
    RequestID := FND_REQUEST.SUBMIT_REQUEST ( 
        'APASZ02','APASZ02',null,null,FALSE,
        'test',CHR(0), '',   '',   '',  '',   '',   '',   '',   '', 
        '',   '',   '',   '',   '',   '',   '',   '',   '',   '', 
        '',   '',   '',   '',   '',   '',   '',   '',   '',   '', 
        '',   '',   '',   '',   '',   '',   '',   '',   '',   '', 
        '',   '',   '',   '',   '',   '',   '',   '',   '',   '', 
        '',   '',   '',   '',   '',   '',   '',   '',   '',   '', 
        '',   '',   '',   '',   '',   '',   '',   '',   '',   '', 
        '',   '',   '',   '',   '',   '',   '',   '',   '',   '', 
        '',   '',   '',   '',   '',   '',   '',   '',   '',   '', 
        '',   '',   '',   '',   '',   '',   '',   '',   '',   '');  
 
    exception
        when others then
            dbms_output.put_line(sqlerrm(sqlcode));
    end;
    If RequestID = 0 then 
        dbms_output.put_line('Submitting request error.'); 
        ReturnCode := 0; 
        ErrorMessage := 'Submitting request OK.'; 
        return; 
    else 
       dbms_output.put_line('Request id:'||to_char(RequestID)); 
       ReturnCode := 0; 
       ErrorMessage := 'Request id:'||to_char(RequestID); 
       Commit; 
    end If; 
end; 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ramiseesniblicAuthor Commented:
Does there need to be a stored procedure in the database, or can I call the FND_REQUEST.SUBMIT_REQUEST straight from the JSP? I do not control the database, so putting in a new store procedure may be an issue.

Cheers
0
sonicefuCommented:
Try the following code
Class.forName("oracle.jdbc.driver.OracleDriver");
 
// Step 2. Create a Connection object
Connection con = DriverManager.getConnection(
"jdbc:oracle:thin:@hdqofd:1541:ITPJ",
"apps", "ksl_apps");
System.out.println("got connection for item cost correction;" );
// Step 3. Create a Statement object and call its executeUpdate
// method to insert a record
Statement s = con.createStatement();
String sql = "";
sql =
"{ call FND_REQUEST.SUBMIT_REQUEST('PO','REQIMPORT',NULL, NULL,FALSE,'NOTES',NULL,
'BUYER', NULL, 'No', 'Yes')} ";
s.execute(sql);

Open in new window

0
ramiseesniblicAuthor Commented:
Cool.  I will give that a try.

One more thing..  In the sample SQL provided by the databse people, there is the following line:

"FND_GLOBAL.APPS_INITIALIZE(2483, 50810, 20043)".  
Now from what I understand, this sets the session up for the request to run.  Do I need to also do this from the SQL?

Cheers for all the help.
0
sonicefuCommented:
You can find your Question under this question,  in the following link
Question: Can you do fnd_request.submit_request from SQL Plus in Oracle?

http://oracle.forumcircle.com/viewtopic.php?p=17
0
ramiseesniblicAuthor Commented:
Hello.

Just tried the JSP with the code below (note that that some of the detail has been hidden), and it is return me the following SQL exception:

 Error: java.sql.SQLException: ORA-00900: invalid SQL statement

Any ideas what is wrong?

Cheers for the ongoing help.
<?xml version="1.0" encoding="ISO-8859-1"?>
<%@ page import="java.sql.*" %>
<%@ page contentType="text/xml;charset=ISO-8859-1" %>
 
<%
response.setHeader("Cache-Control","no-store"); //HTTP 1.1
response.setHeader("Pragma","no-cache"); //HTTP 1.0
response.setDateHeader ("Expires", 0); //prevents caching at the proxy server
%>
 
<result>
 
	<%
		
	try {
		DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
		Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@<server>:1551:<db>",<username>,<password>);
 
		String sql = "";
		try{
 
		String SQL_INITALIZE = "FND_GLOBAL.APPS_INITIALIZE(2483, 50810, 20043);";
				
	PreparedStatement stmt = conn.prepareStatement( SQL_INITALIZE );
	boolean ok = stmt.execute();
 
	if( ok ){
	  sql = "CALL apps.FND_REQUEST.SUBMIT_REQUEST(" +
		  "'<string>', " +
	          "'<string>', " +
		  "FALSE, " +
	          "18634, " +
	          "NULL, " +
		  "NULL, " +
		  "NULL, " +
	          "'01-JAN-2007', " +
	          "'10-JAN-2007' ) ";
		
		  stmt = conn.prepareStatement( sql );				
		  ResultSet rs = stmt.executeQuery();
			
	          while( rs.next() ){
		        %>
			 <request_id> <%=rs.getInt( 1 )%></request_id>
		        <%
 
		        }
		        
			}
			
		}finally{
			conn.close();			
		}
		
	} catch (Exception ex) {
	
		out.println("Error: " + ex.toString());
	}
%>
</result>

Open in new window

0
Jinesh KamdarCommented:
I guess this needs correction -
String SQL_INITALIZE = "FND_GLOBAL.APPS_INITIALIZE(2483, 50810, 20043);";
as this -
String SQL_INITALIZE = "CALL FND_GLOBAL.APPS_INITIALIZE(2483, 50810, 20043);";
0
ramiseesniblicAuthor Commented:
Also, if I just try calling the "FND_REQUEST.SUBMIT_REQUEST" it returns

      Error: java.sql.SQLException: ORA-06576: not a valid function or procedure name

Should I ask the DBA to create a stored procedure that does everything ( like suggested in your second post )
0
ramiseesniblicAuthor Commented:
Right, added the "Call" into the first SQL statement, and I think that it has executed..

The "apps.FND_REQUEST.SUBMIT_REQUEST" should return a request ID.  How can I get at that?

Cheers
0
ramiseesniblicAuthor Commented:
Sorry, my mistake.  I still get the Error: java.sql.SQLException: ORA-06576: not a valid function or procedure name calling FND_REQUEST.SUBMIT_REQUEST.

The reason why it didn't say that last time was that "stmt.execute();" returned false. (silly me)
0
Jinesh KamdarCommented:
How about using a CallableStatement?
try
{
 CallableStatement cs = conn.prepareCall("{call FND_GLOBAL.APPS_INITIALIZE(2483, 50810, 20043)}");
 cs.execute();
				
 String sqlStr = "{call apps.FND_REQUEST.SUBMIT_REQUEST("  +
		      "'<string>', "    + "'<string>', " +
		      "FALSE, "         +  "18634, "     +
	               "NULL, "          +  "NULL, "      +
		      "NULL, "          +
	               "'01-JAN-2007', " + "'10-JAN-2007')}";
		
    cs = conn.prepareCall(sqlStr);
    ResultSet rs = cs.executeQuery();
    ...

Open in new window

0
ramiseesniblicAuthor Commented:
I still get the same error as above: "ORA-06576: not a valid function or procedure name calling FND_REQUEST.SUBMIT_REQUEST"

Sorry
0
Jinesh KamdarCommented:
Ok, now i see what's wrong. You are not passing the arguments in the right sequence. Use the below function description as a reference to pass ur parameters in the right order. Note that the BOOLEAN parameter is 5th in the order and in ur call, its 3rd, so u gotta fix the call parameters first.
function submit_request
(
			  application IN varchar2 default NULL,
			  program     IN varchar2 default NULL,
			  description IN varchar2 default NULL,
			  start_time  IN varchar2 default NULL,
			  sub_request IN boolean  default FALSE,
			  argument1   IN varchar2 default CHR(0),
			  argument2   IN varchar2 default CHR(0),
  			  argument3   IN varchar2 default CHR(0),
			  argument4   IN varchar2 default CHR(0),
			  argument5   IN varchar2 default CHR(0),
			  argument6   IN varchar2 default CHR(0),
			  argument7   IN varchar2 default CHR(0),
			  argument8   IN varchar2 default CHR(0),
			  argument9   IN varchar2 default CHR(0),
			  argument10  IN varchar2 default CHR(0),
			  argument11  IN varchar2 default CHR(0),
			  argument12  IN varchar2 default CHR(0),
  			  argument13  IN varchar2 default CHR(0),
			  argument14  IN varchar2 default CHR(0),
			  argument15  IN varchar2 default CHR(0),
			  argument16  IN varchar2 default CHR(0),
			  argument17  IN varchar2 default CHR(0),
			  argument18  IN varchar2 default CHR(0),
			  argument19  IN varchar2 default CHR(0),
			  argument20  IN varchar2 default CHR(0),
			  argument21  IN varchar2 default CHR(0),
			  argument22  IN varchar2 default CHR(0),
  			  argument23  IN varchar2 default CHR(0),
			  argument24  IN varchar2 default CHR(0),
			  argument25  IN varchar2 default CHR(0),
			  argument26  IN varchar2 default CHR(0),
			  argument27  IN varchar2 default CHR(0),
			  argument28  IN varchar2 default CHR(0),
			  argument29  IN varchar2 default CHR(0),
			  argument30  IN varchar2 default CHR(0),
			  argument31  IN varchar2 default CHR(0),
			  argument32  IN varchar2 default CHR(0),
  			  argument33  IN varchar2 default CHR(0),
			  argument34  IN varchar2 default CHR(0),
			  argument35  IN varchar2 default CHR(0),
			  argument36  IN varchar2 default CHR(0),
			  argument37  IN varchar2 default CHR(0),
  			  argument38  IN varchar2 default CHR(0),
			  argument39  IN varchar2 default CHR(0),
			  argument40  IN varchar2 default CHR(0),
			  argument41  IN varchar2 default CHR(0),
  			  argument42  IN varchar2 default CHR(0),
			  argument43  IN varchar2 default CHR(0),
			  argument44  IN varchar2 default CHR(0),
			  argument45  IN varchar2 default CHR(0),
			  argument46  IN varchar2 default CHR(0),
			  argument47  IN varchar2 default CHR(0),
  			  argument48  IN varchar2 default CHR(0),
			  argument49  IN varchar2 default CHR(0),
			  argument50  IN varchar2 default CHR(0),
			  argument51  IN varchar2 default CHR(0),
  			  argument52  IN varchar2 default CHR(0),
			  argument53  IN varchar2 default CHR(0),
			  argument54  IN varchar2 default CHR(0),
			  argument55  IN varchar2 default CHR(0),
			  argument56  IN varchar2 default CHR(0),
			  argument57  IN varchar2 default CHR(0),
			  argument58  IN varchar2 default CHR(0),
			  argument59  IN varchar2 default CHR(0),
			  argument60  IN varchar2 default CHR(0),
			  argument61  IN varchar2 default CHR(0),
			  argument62  IN varchar2 default CHR(0),
  			  argument63  IN varchar2 default CHR(0),
			  argument64  IN varchar2 default CHR(0),
			  argument65  IN varchar2 default CHR(0),
			  argument66  IN varchar2 default CHR(0),
			  argument67  IN varchar2 default CHR(0),
			  argument68  IN varchar2 default CHR(0),
			  argument69  IN varchar2 default CHR(0),
			  argument70  IN varchar2 default CHR(0),
			  argument71  IN varchar2 default CHR(0),
			  argument72  IN varchar2 default CHR(0),
  			  argument73  IN varchar2 default CHR(0),
			  argument74  IN varchar2 default CHR(0),
			  argument75  IN varchar2 default CHR(0),
			  argument76  IN varchar2 default CHR(0),
			  argument77  IN varchar2 default CHR(0),
			  argument78  IN varchar2 default CHR(0),
			  argument79  IN varchar2 default CHR(0),
			  argument80  IN varchar2 default CHR(0),
			  argument81  IN varchar2 default CHR(0),
			  argument82  IN varchar2 default CHR(0),
  			  argument83  IN varchar2 default CHR(0),
			  argument84  IN varchar2 default CHR(0),
			  argument85  IN varchar2 default CHR(0),
			  argument86  IN varchar2 default CHR(0),
			  argument87  IN varchar2 default CHR(0),
			  argument88  IN varchar2 default CHR(0),
			  argument89  IN varchar2 default CHR(0),
			  argument90  IN varchar2 default CHR(0),
			  argument91  IN varchar2 default CHR(0),
			  argument92  IN varchar2 default CHR(0),
  			  argument93  IN varchar2 default CHR(0),
			  argument94  IN varchar2 default CHR(0),
			  argument95  IN varchar2 default CHR(0),
			  argument96  IN varchar2 default CHR(0),
			  argument97  IN varchar2 default CHR(0),
			  argument98  IN varchar2 default CHR(0),
			  argument99  IN varchar2 default CHR(0),
			  argument100  IN varchar2 default CHR(0)
)			  return number;

Open in new window

0
ramiseesniblicAuthor Commented:
I don't know if that is the problem.

If I log onto the Oracle server, and run it throught the SQL editor, I get no errors and a request ID returned.

Is it different from within a JSP page?
0
Jinesh KamdarCommented:
Could you get and post a similar function description from ur DB instance? That could explain a lot of things i guess.
0
ramiseesniblicAuthor Commented:
The functions are supplied by a 3rd party, so I don't have any more examples.

Sorry
0
Jinesh KamdarCommented:
>> If I log onto the Oracle server, and run it throught the SQL editor, I get no errors and a request ID returned.

This seems very strange to me. I checked lots of articles on the net and they all concur with the function description that i posted, which means ur parameter ordering is incorrect. Could you please post the output of the foll. program?
SET SERVEROUT ON
BEGIN
APPS.FND_REQUEST.SUBMIT_REQUEST(...); ---> Put in ur call here.
EXCEPTION
WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

Open in new window

0
ramiseesniblicAuthor Commented:
Problem Solved.

Got the 3rd party to put the FND_REQUEST.SUBMIT_REQUEST into a stored procedure, and I'm calling it like below.

String sql = "call xxt3_web_pkg.submit_net_conn_detail_report(?, NULL, NULL, NULL, ?, ?, ?)";
 
CallableStatement callStmt = conn.prepareCall(sql);
callStmt.setInt( 1, userid );
callStmt.setString( 2, dateFrom );
callStmt.setString( 3, dateTo );		
callStmt.registerOutParameter( 4, Types.INTEGER );	
 
callStmt.executeQuery();
 
int requestId = callStmt.getInt( 4 );

Open in new window

0
ramiseesniblicAuthor Commented:
So, therefore I am going to give points to sonicefu as his answer gave insperation for stored procedure.

Any objections?
0
Jinesh KamdarCommented:
I don't have any objections though it beats me why the direct way did not work! :(
Can u pls show ur SP code - xxt3_web_pkg.submit_net_conn_detail_report
0
ramiseesniblicAuthor Commented:
Don't have access to the sp code. 3rd pary databse.
Sorry
0
Jinesh KamdarCommented:
>> Got the 3rd party to put the FND_REQUEST.SUBMIT_REQUEST into a stored procedure

I thought u supplied them that SP code !!!
0
ramiseesniblicAuthor Commented:
I asked them to create a stored procedure that wrapper the call to FND_REQUEST.SUBMIT_REQUEST.
They provided the SP name that took input and output parameters.
0
Jinesh KamdarCommented:
Ok. Finally, at the risk of being persistent, can u post the output of my last code ?
SET SERVEROUT ON
DECLARE
req_id NUMBER;
BEGIN
req_id := APPS.FND_REQUEST.SUBMIT_REQUEST
(
 'string',
 'string',
 FALSE,
 18634,
 NULL,
 NULL,
 '01-JAN-2007',
 '10-JAN-2007'
);
DBMS_OUTPUT.PUT_LINE('Request ID = ' || req_id);
EXCEPTION
WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

Open in new window

0
ramiseesniblicAuthor Commented:
FYI.

line 1: SQLPLUS Command Skipped: SET SERVEROUT ON

Error starting at line 2 in command:
DECLARE
req_id NUMBER;
BEGIN
req_id := APPS.FND_REQUEST.SUBMIT_REQUEST
(
 'XXCOMTR3',
 'XXT3_NET_CONN_DETAIL_REP_WEB',
 FALSE,
 18634,
 NULL,
 NULL,
 '01-JAN-2007',
 '10-JAN-2007'
);
DBMS_OUTPUT.PUT_LINE('Request ID = ' || req_id);
EXCEPTION
WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
Error report:
ORA-06550: line 4, column 11:
PLS-00306: wrong number or types of arguments in call to 'SUBMIT_REQUEST'
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
0
Jinesh KamdarCommented:
>> PLS-00306: wrong number or types of arguments in call to 'SUBMIT_REQUEST'
This is exactly what i had been referring to in my post id # 20815255. Thx for the Asssst anyways! :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.