Link to home
Start Free TrialLog in
Avatar of ramiseesniblic
ramiseesniblicFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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
Avatar of sonicefu
sonicefu
Flag of Pakistan image
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

ASKER CERTIFIED SOLUTION
Avatar of sonicefu
sonicefu
Flag of Pakistan image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ramiseesniblic
ramiseesniblic
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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
Avatar of sonicefu
sonicefu
Flag of Pakistan image
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

Avatar of ramiseesniblic
ramiseesniblic
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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.
Avatar of sonicefu
sonicefu
Flag of Pakistan image
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
Avatar of ramiseesniblic
ramiseesniblic
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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

Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image
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);";
Avatar of ramiseesniblic
ramiseesniblic
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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 )
Avatar of ramiseesniblic
ramiseesniblic
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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
Avatar of ramiseesniblic
ramiseesniblic
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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)
SOLUTION
Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ramiseesniblic
ramiseesniblic
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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

Sorry
Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image
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

Avatar of ramiseesniblic
ramiseesniblic
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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?
Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image
Could you get and post a similar function description from ur DB instance? That could explain a lot of things i guess.
Avatar of ramiseesniblic
ramiseesniblic
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

The functions are supplied by a 3rd party, so I don't have any more examples.

Sorry
Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image
>> 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

Avatar of ramiseesniblic
ramiseesniblic
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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

Avatar of ramiseesniblic
ramiseesniblic
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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

Any objections?
Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image
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
Avatar of ramiseesniblic
ramiseesniblic
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Don't have access to the sp code. 3rd pary databse.
Sorry
Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image
>> Got the 3rd party to put the FND_REQUEST.SUBMIT_REQUEST into a stored procedure

I thought u supplied them that SP code !!!
Avatar of ramiseesniblic
ramiseesniblic
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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.
Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image
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

Avatar of ramiseesniblic
ramiseesniblic
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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:
Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image
>> 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! :)