Errors while executing the stored procedure using NamedQuery.
Nooruddin Shaik
I am getting errors while executing the stored procedure using NamedQuery.
I am wondering what sshould be the return value both in the named
query and stored procedure.
I am running on DAS with Oracle.
<item-descriptor - ..
....
<named-query>
<sql-query>
<query-name>metricQuery</query-name>
<sql stored-procedure="true">
{ ? = call METRIC_INSERT () }
</sql>
<returns>skuId</returns>
</sql-query>
</named-query>
....
</item-descriptor>
My Stored Procedure
CREATE OR REPLACE PROCEDURE METRIC_INSERT()
IS
// inserting some records
END METRIC_INSERT_FUNC;
I am executing the procedure using
..........
NamedQueryView recNamedQueryView = (NamedQueryView)
recRepo.getView("metricRecommendation");
Query namedQuery = recNamedQueryView.getNamedQuery("metricQuery");
recNamedQueryView.executeQuery(namedQuery);
...............
I am getting exception
java.sql.SQLException: ORA-00900: invalid SQL statement
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:215)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:954)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1169)
at oracle.jdbc.driver.OracleStatement.doScrollExecuteCommon(OracleStatement.java:4044)
at oracle.jdbc.driver.OraclePreparedStatement.doScrollPstmtExecuteUpdate(OraclePreparedStatement.java:10763)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3283)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3390)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4223)
at atg.service.resourcepool.MonitoredCallableStatement.execute(MonitoredCallableStatement.java:2051)
at atg.adapter.gsa.OracleStoredProcedureHelper.executeQuery(OracleStoredProcedureHelper.java:96)
at atg.adapter.gsa.SQLStatement.executeQuery(SQLStatement.java:570)
at atg.adapter.gsa.GSAItemDescriptor.executeDirectSqlQuery(GSAItemDescriptor.java:7086)
at atg.adapter.gsa.GSAItemDescriptor.executeDirectSqlQuery(GSAItemDescriptor.java:6919)
at atg.adapter.gsa.GSAItemDescriptor.executeQuery(GSAItemDescriptor.java:6572)
at atg.adapter.gsa.GSAView.executeUncachedQuery(GSAView.java:291)
at atg.adapter.gsa.GSAView.executeQuery(GSAView.java:1027)
at atg.adapter.gsa.GSAView.executeQuery(GSAView.java:978)
at atg.adapter.gsa.GSAView.executeQuery(GSAView.java:826)
Any help would be appreciated.
Thanks,
Rajashekar.
Ryan Sullivan
Hello,
I am working on a project that uses a stored procedure and I too am having a bit of trouble. I've set up the following based on a lot of trial, error, and reading docs/posts online, but I am still getting an error.
Currently I have:
CREATE OR REPLACE
FUNCTION Generate_Bulk_Promo_Coupons(
outer_limit IN integer,
Coupon_Prefix IN VARCHAR2,
Promo_Code IN VARCHAR2)
RETURN generate_bpc_types.ref_cursor
AS
results_cursor generate_bpc_types.ref_cursor;
[...]
BEGIN
[...]
OPEN results_cursor FOR SELECT id,
promotion_id,
used FROM Uoi_Bulk_Coupon WHERE promotion_id = promo_code;
RETURN results_cursor;
END;
/
And
<table type="primary" id-column-name="id" shared-table-sequence="1" […]>
<property column-name="id" data-type="string" name="id"/>
<property column-name="promotion_id" item-type="[…]" name="promotion"/>
<property column-name="used" data-type="boolean" name="used"/>
</table>
<named-query>
<sql-query>
<query-name>generateBulkPromoCoupons</query-name>
<sql stored-procedure="true">
{ ? := call generate_bulk_promo_coupons (?, ?, ?) }
</sql>
<returns>id,promotion,used</returns>
<input-parameter-types>java.lang.Integer,java.lang.String,java.lang.String
</input-parameter-types>
<dependencies/>
</sql-query>
</named-query>
And
try {
RepositoryItemDescriptor desc = repo.getItemDescriptor("bulkCoupon");
RepositoryView view = desc.getRepositoryView();
NamedQueryView nameView = (NamedQueryView) view;
Query namedQuery = nameView.getNamedQuery("generateBulkPromoCoupons");
ParameterSupportView paramView = (ParameterSupportView) view;
paramView.executeQuery(namedQuery, new Object[] { couponCount, prefix, atgCoupon });
} catch (RepositoryException e) {
What I get back is
[my exception…]
Caused by: CONTAINER:atg.repository.RepositoryException; SOURCE:java.sql.SQLException: Missing IN or OUT parameter at index:: 4
at atg.adapter.gsa.GSAItemDescriptor.executeDirectSqlQuery(GSAItemDescriptor.java:7630)
at atg.adapter.gsa.GSAItemDescriptor.executeQuery(GSAItemDescriptor.java:7265)
at atg.adapter.gsa.GSAView.executeUncachedQuery(GSAView.java:332)
at atg.repository.query.QueryCache.executeUncachedQuery(QueryCache.java:693)
at atg.repository.query.QueryCache.populateEntry(QueryCache.java:905)
at atg.repository.query.QueryCache.executeCachedQuery(QueryCache.java:609)
at atg.adapter.gsa.GSAView.executeQuery(GSAView.java:1110)
at atg.adapter.gsa.GSAView.executeQuery(GSAView.java:1049)
at atg.adapter.gsa.GSAView.executeQuery(GSAView.java:897)
at uo.promotions.BulkPromotionsTools.createCoupons(BulkPromotionsTools.java:224)
... 66 more
Caused by: java.sql.SQLException: Missing IN or OUT parameter at index:: 4
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1844)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3311)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3422)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4394)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:220)
at atg.adapter.gsa.OracleStoredProcedureHelper.executeQuery(OracleStoredProcedureHelper.java:96)
at atg.adapter.gsa.SQLStatement.executeQuery(SQLStatement.java:607)
at atg.adapter.gsa.GSAItemDescriptor.executeDirectSqlQuery(GSAItemDescriptor.java:7794)
at atg.adapter.gsa.GSAItemDescriptor.executeDirectSqlQuery(GSAItemDescriptor.java:7621)
... 75 more
I’ve tried adding a fourth parameter to the beginning like the following and setting it to OracleTypes.CURSOR, but that gets me into a problem of no lass loader for CURSER..
<input-parameter-types>OracleTypes.CURSOR,java.lang.Integer,java.lang.String,java.lang.String</input-parameter-types>
I’ve tried a lot of other stuff but none of it is working. I’ve been at this for ~6 combined working hours and it’s getting to the point where I may need to just bypass ATG and go for a SQL connection.
My env: ATG 9.1 on Jboss 4.2 eap on Oracle 10gr2
ANY help is GREATELY appreciated.
Thanks.
Ryan J. Sullivan | O:215.454.5191 | M:412.254.3676 | google/skype/aim: rgs258
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "ATG_Tech" group.
To post to this group, send email to atg_...@googlegroups.com To unsubscribe from this group, send email to atg_tech-u...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/atg_tech?hl=en
-~----------~----~----~----~------~----~------~--~---
kartheek desineedi
Hi Ryan,
--
You received this message because you are subscribed to the Google Groups "ATG_Tech" group.
To post to this group, send email to atg_...@googlegroups.com
To unsubscribe from this group, send email to atg_tech-u...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/atg_tech?hl=en
Adam Petri
From: Ryan Sullivan <RSul...@urbn.com>
To: "atg_...@googlegroups.com" <atg_...@googlegroups.com>; Nooruddin Shaik <shaik.n...@gmail.com>
Sent: Friday, May 11, 2012 12:00 PM
Subject: RE: [atg_tech:6063] Errors while executing the stored procedure using NamedQuery.