Problems with logical data service - Data Service Integrator

This is related to thread 837141 (Problems creating physical data service to relational table)
I have been able to create the logical data service that uses the physical service (mentioned above).
However when I try to create a library function to update (shown below)
declare function lper:update($new as element(per:Persistance2)) as xs:boolean {*
* let $changed :=*
* for $n in $new*
* let $old := lper:read($new/Project, $ew/Property)*
* return*
* fn-bea:replace-value(fn-bea:changed-element($old), "Value", $n/Value)*
_let $return := {color:#ff0000}pper:update{color}($changed)_*
* return fn:true()*
*};*
WorkSpace Studio gives the following error (for the line underlined above, and the function in red)
*{bea-err}FUNC002: Illegal use of side-effect procedure {ld:Physical/Persistance2}update with arity 1. Side-effect procedures cannot be invoked from a side-effect free context*
Any idea why? 

functions, by definition, do not have side-effects. So calling something that had a side-effect (procedure) is not allowed.
You need to create a library procedure.
Your
declare function lper:update($new as element(per:Persistance2)) as xs:boolean {*
needs to be
declare procedure lper:update($new as element(per:Persistance2)) as xs:boolean {*
- mike
Edited by: mikereiche on Dec 12, 2008 5:10 PM

Related

How to retrieve Table of Records output param from stored procedure ?

Hi,
I'm trying to retrieve data from a PL/SQL stored proc. It seems I can't modify this procedure (I'm not allowed to and I don't know PL/SQL :)).
My only documentation is the PL/SQL code and its comments. And that's the first I have to deal with output of a defined complex PL/SQL type
So the signature of the procedure is :
FUNCTION FUN_AFF_EVELEG_IHM (
    pEntumTyp       IN          NUMBER,
    pEntnum         IN          VARCHAR2,
    pEveListSize      IN OUT      NUMBER,
    pEveList       IN OUT      pkg_funaff_eveleg.TableRecordEVL,
    pErrCode   IN OUT      VARCHAR2,
    pMessage        IN OUT      VARCHAR2)
  RETURN NUMBER;pkg_funaff_eveleg.TableRecordEVL type is defined as "TABLE of RecordEVL"
pkg_funaff_eveleg.RecordEVL type is defined as "RECORD" (struct of 12 different fields : NUMBER or VARCHAR2)
What is the correct syntax to call the stored procedure then ? I don't find how to manage the pEveList output param. Is it a Cursor ? An ARRAY ? And how to register it ?
My code so far :
public static void callFunaffEVL(Connection con, String rcs) {
    // CallableStatement procCstmt=null;
    OracleCallableStatement oraCstmt = null;
    try {
      // Identifiy the Stored procedure
      // package synonyme : pkg_aff_EVELEG_IHM
      // stored procedure name : FUN_AFF_EVELEG_IHM
      String command = new StringBuilder("{? = call pkg_aff_EVELEG_IHM.FUN_AFF_EVELEG_IHM(?,?,?,?,?,?");
      // 1 RETURN 
      // 2 pEntumTyp IN NUMBER
      // 3 pEntnum IN VARCHAR2
      // 4 pEveListSize IN OUT NUMBER,
      // 5 pEveList IN OUT pkg_funaff_eveleg.TableauRecordEVL,
      // 6 pErrCpde IN OUT VARCHAR2,
      // 7 pMessage IN OUT VARCHAR2)
     
      // Create a Callable Statement Object:
      oraCstmt = (OracleCallableStatement) con.prepareCall(command);
      // Assign IN and OUT parameters
      oraCstmt.registerOutParameter(1, OracleTypes.NUMBER); // RET
      oraCstmt.setInt(2, 0); // ENTNUMTYP
      oraCstmt.setString(3, rcs); // ENTNUM
      oraCstmt.registerOutParameter(4, OracleTypes.NUMBER); // pEveListSize
      oraCstmt.registerOutParameter(5, OracleTypes.ARRAY); // pEveList
      oraCstmt.registerOutParameter(6, OracleTypes.VARCHAR); // pErrCode
      oraCstmt.registerOutParameter(7, OracleTypes.VARCHAR); // pMessage
      // Execute the Procedure or Function Call:
      oraCstmt.execute();
      // Process the OUT Placeholders:
      int ret = oraCstmt.getInt(1);
      String errCode = oraCstmt.getString(6);
      String message = oraCstmt.getString(7);
      System.out.println("RCS : " + rcs);
      System.out.println("ret : " + ret);
      System.out.println("errCode : " + errCode );
      System.out.println("message : " + message);
     
    } catch (SQLException sqle) {
      sqle.printStackTrace();
    } finally {
      // Close the CallableStatement Object:
      try {
        oraCstmt.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
Return : java.sql.SQLException: Parameter type Conflict: sqlType=2003
Any help ? I found several examples that might refer to this case, but everything I tried end by a SQL exception of one type or another...
(and sorry for my poor english :))
Cy 
What is pkg_funaff_eveleg.TableRecordEVL ? 
As I said, "pkg_funaff_eveleg.TableRecordEVL" is TABLE of RecordEVL.
i.e : I can find 2 defined types under the package pkg_funaff_eveleg :
TYPE TableRecordEVL is TABLE of RecordEVL INDEX BY BINARY_INTEGER;
TYPE RecordEVL is RECORD (
  EVLENTNUM_PK        EVENEMENTS_LEGAUX.EVLENTNUM_PK%TYPE,
  EVLENTNUMTYP_PK     EVENEMENTS_LEGAUX.EVLENTNUMTYP_PK%TYPE,
  EVLSEQ_PK           EVENEMENTS_LEGAUX.EVLSEQ_PK%TYPE,
  EVLTYPSRC           EVENEMENTS_LEGAUX.EVLTYPSRC%TYPE,
  EVLPK1              EVENEMENTS_LEGAUX.EVLPK1%TYPE,
  EVLPK2              EVENEMENTS_LEGAUX.EVLPK2%TYPE,
  EVLPK3              EVENEMENTS_LEGAUX.EVLPK3%TYPE,
  EVLPK4              EVENEMENTS_LEGAUX.EVLPK4%TYPE,
  EVLPK5              EVENEMENTS_LEGAUX.EVLPK5%TYPE,
  EVLPK6              EVENEMENTS_LEGAUX.EVLPK6%TYPE,
  EVLCODEVTSRC        EVENEMENTS_LEGAUX.EVLCODEVTSRC%TYPE,
  EVLLEGEVECOD        EVENEMENTS_LEGAUX.EVLLEGEVECOD%TYPE,
  EVLSEQREF_FK        EVENEMENTS_LEGAUX.EVLSEQREF_FK%TYPE,
  EVLCOMMENT          EVENEMENTS_LEGAUX.EVLCOMMENT%TYPE,
  EVLETATCOD          EVENEMENTS_LEGAUX.EVLETATCOD%TYPE,
  EVLHISDATPUB        EVENEMENTS_LEGAUX.EVLHISDATPUB%TYPE,
  EVLHISPUBPRE        EVENEMENTS_LEGAUX.EVLHISPUBPRE%TYPE,
  EVLHISDATEFF        EVENEMENTS_LEGAUX.EVLHISDATEFF%TYPE,
  EVLHISEFFPRE        EVENEMENTS_LEGAUX.EVLHISEFFPRE%TYPE,
  EVLHISPOIDATEFF     EVENEMENTS_LEGAUX.EVLHISPOIDATEFF%TYPE,
  EVLHISORICOD        EVENEMENTS_LEGAUX.EVLHISORICOD%TYPE,
  EVLHISSUPPORTCOD    EVENEMENTS_LEGAUX.EVLHISSUPPORTCOD%TYPE,
  EVLHISNUMSUPPORT    EVENEMENTS_LEGAUX.EVLHISNUMSUPPORT%TYPE,
  EVLHISNUMINF        EVENEMENTS_LEGAUX.EVLHISNUMINF%TYPE,
  ANNNUMBODPCL        CBODACCPROD.CODANN2.ANNNUMBOD%TYPE
);If needed, I can translate each "EVENEMENTS_LEGAUX.EVLENTNUM_PK%TYPE", but they must be VARCHAR2 or NUMBER
Do I answer your question ? 
What if you just map that parameter to an Object? Does that then not produce your error? 
moshikang wrote:
As I said, "pkg_funaff_eveleg.TableRecordEVL" is TABLE of RecordEVL.
i.e : I can find 2 defined types under the package pkg_funaff_eveleg :You have a LOT of code to write then.
You have to translate Oracle types to Java types. You must do that by using Oracle driver specific calls. That means, for example, that you will not be able to use any proxy pools.
You can google for examples of coding to Oracle Type values. 
Thanks. I'm not sure of what you mean by "tanslating OracleTypes".
I tried to declare / bind OracleTypes with :
StructDescriptor recordDesc = StructDescriptor.createDescriptor(RECORD_EVL, con);
ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor(TABLE_RECORD_EVL, con);  I struggled a bit to find the convenient uppercase strings for the 2 packages types. Indeed it seems the package types are invisible from outside and that I need to declare them as public types under the schema I am allowed to use...
This seems to work (at least for Descriptors).
For the moment I'm stuck because this new public type is not the one used by my stored procedure so I'm waiting for a modification by our PL/SQL maintenance team...
So, my question is : am I totally wrong about this ? ;-)
Cy
Edited by: moshikang on 10 janv. 2011 08:29 
moshikang wrote:
Thanks. I'm not sure of what you mean by "tanslatating OracleTypes".
I tried to declare / bind OracleTypes with :
StructDescriptor recordDesc = StructDescriptor.createDescriptor(RECORD_EVL, con);
ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor(TABLE_RECORD_EVL, con);  I struggled a bit to find the convenient uppercase strings for the 2 packages types. Indeed it seems the package types are invisible from outside and that I need to declare them as public types under the schema I am allowed to use...
This seems to work (at least for Descriptors).
For the moment I'm stuck because this new public type is not the one used by my stored procedure so I'm waiting for a modification by our PL/SQL maintenance team...
So, my question is : am I totally wrong about this ? ;-)
CyINTEGER, etc table types are supported, but PL/SQL RECORDs aren't.
If you define an Oracle TYPE instead of a PL/SQL RECORD, so outside of the package, then you can do this. At least I'm pretty sure you can. 
>
INTEGER, etc table types are supported, but PL/SQL RECORDs aren't.
If you define an Oracle TYPE instead of a PL/SQL RECORD, so outside of the package, then you can do this. At least I'm pretty sure you can.I don't get it... what difference do you make between an Oracle TYPE and a declaration like
TYPE RecordEVL is RECORD (
  EVLENTNUM_PK        EVENEMENTS_LEGAUX.EVLENTNUM_PK%TYPE,
  EVLENTNUMTYP_PK     EVENEMENTS_LEGAUX.EVLENTNUMTYP_PK%TYPE,
  EVLSEQ_PK           EVENEMENTS_LEGAUX.EVLSEQ_PK%TYPE,
  ....
);As OracleType.RECORD exists, I assumed RECORD was an OracleType :)
Cy 
moshikang wrote:
Thanks. I'm not sure of what you mean by "tanslating OracleTypes".You know what varchar2 and timestamp are - right?
Those are normal java database types.
Anything with 'Record' and/or 'Type' isn't. Anything with those must be translated incrementally (each data member in each) into the types that java understands. You must write code specific to the Oracle jdbc driver that translates each. 
moshikang wrote:
INTEGER, etc table types are supported, but PL/SQL RECORDs aren't.
If you define an Oracle TYPE instead of a PL/SQL RECORD, so outside of the package, then you can do this. At least I'm pretty sure you can.I don't get it... what difference do you make between an Oracle TYPE and a declaration like
TYPE RecordEVL is RECORD (
EVLENTNUM_PK        EVENEMENTS_LEGAUX.EVLENTNUM_PK%TYPE,
EVLENTNUMTYP_PK     EVENEMENTS_LEGAUX.EVLENTNUMTYP_PK%TYPE,
EVLSEQ_PK           EVENEMENTS_LEGAUX.EVLSEQ_PK%TYPE,
....
);As OracleType.RECORD exists, I assumed RECORD was an OracleType :)
CyNo they're not.
One is a PLSQL construct and the other is a SQL construct.
PL/SQL is not SQL.
So, the driver can handle TYPEs (ie SQL) but not RECORDs (ie PL/SQL).
That's the way it is. So your only solution (if you want to return a TABLE of records) is to create a TYPE, therefore outside of the package (and that's the important bit). The TABLE can be declared in the package, but the TYPE (ie RECORD) cannot be.
I thin k (though don't quote me on this) it's to do with what the Driver can access easily. it can get the definition of the TYPE easily enough, but the RECORD definition is not so easy to get at. Without access to the definition then the driver can't turn the row into something useable. 
>
>
So, the driver can handle TYPEs (ie SQL) but not RECORDs (ie PL/SQL).
That's the way it is. So your only solution (if you want to return a TABLE of records) is to create a TYPE, therefore outside of the package (and that's the important bit). The TABLE can be declared in the package, but the TYPE (ie RECORD) cannot be.
I thin k (though don't quote me on this) it's to do with what the Driver can access easily.
iit can get the definition of the TYPE easily enough, but the RECORD definition is not so easy to get at.
Without access to the definition then the driver can't turn the row into something useable.OK, so I'm on the good way since I'm waiting for the stored procedure to use a global defined TYPE that I will be able to see from JDBC.
And FWIW, that's not I want to return a TABLE of records, but that this is a relic of old times (we are refunding a Oracle Forms application in a J2EE one). I don't code the PL/SQL side... Maybe the final choice will be to change the signature of the stored procedure, but for the moment, I'm not implied in tech choices on the DB side... 
Then you'll have to inform whoever that in order for Java to be able to use a package like this that there will be refactoring required. 
Tolls wrote:
Then you'll have to inform whoever that in order for Java to be able to use a package like this that there will be refactoring required.Yep. And thanks to you, that's done since last friday :). But unfortunately, I have to wait till next week for the changes to be made... 
So, I assume my question is answered with : "you can't register out a param of type "TABLE of RECORD". My jdbc code is now compiling without any error, but the return from stored procedure is an error code ORA-06531. I will close this question and ask another one. Thanks. 
So, I assume my question is answered with : "you can't register out a param of type "TABLE of RECORD". My jdbc code is now compiling without any error, but the return from stored procedure is an error code ORA-06531. I will close this question and ask another one. Thanks.

Cannot fetch data from a table

Here is the code
<code>
if(isset($_POST["submit"])){
//heading();
$uid=$_POST['uname'];
$pwd=$_POST['pwd'];
echo $uid."<br>".$pwd."<br>";
$connection=oci_pconnect("ran","rantech","localhost/test01");
if($connection!=null){
// echo "Oracle Connection Successfull";
$statement=oci_parse($connection,"select trim(UNAME) as a,trim(PASS_WORD) as b from ran.usrlog where trim(uname)='".$uid."' and trim(pass_word)='".$pwd."'");
if(oci_execute($statement)){
                         $num=oci_fetch_all($statement,$result);
                         if($num<1){
                              var_dump($result);
               $username=$result["a"];
                              echo $username;
                              $pass=$result["b"];
               
               oci_free_statement($statement);     
                         }
                         else{
                              header("Location: index.html");          
                         }
                    
                    // header("Location: /ranreport/ranreport/frame.php");
               }
}
else{
header("Location: index.html");
}
}
else{
echo "Hello";
header("location:index.html");
}
</code>
but the var_dupm shows the fillowing
<b>
array(2){["A"]=>array(0){} ["B"]=>array(0){}}
</b>
User RAN has almost a DBA permission.
Though the table has data but data could not be retrived.
can anyone help me on the issue. 
- The $_POST data is not sanitized, either with a custom function or
with http://php.net/manual/en/book.filter.php
- The SQL statement uses string concatenation for $uid & $pwd which
leaves you open to SQL injection attacks. Use oci_bind_by_name()
instead.
- I'd be suspicious of the trim() functions, which indicate your
inserted data wasn't sanitized. Is is also something to EXPLAIN
PLAN and check your indexes are being used.
- You don't check error return values from all the OCI calls.
- The "$num < 1" test makes your code only do the var_dump when no
rows are found, which makes your output what I'd expect.
- You reference the $result array keys "a" and "b" but should
reference "A" and "B" instead.
- There are authentication & authorizations solutions already
available that could be repurposed for your needs. PHP frameworks
seem to offer the better integrated solutions. 
Heed cj's advice and I would give you one more tool for your logins.
Write a stored procedure to handle the logins eg:
create procedure sp_auth_client
( vUname varchar2, vPword varchar2, vResult integer)
is
begin
select count(*) into vResult from your_users where upper(uname) = upper(vUname) and pword = vPword ;
end;
call this from php error checking after parses, binds and executes.
Even is something gets passed oci_bind the only thing that will get returned by the stored procedure is a number.
stored procedures are an excellent way to defend your system. Since the native oci_8 calls support reference cursors you can use them return data sets when the queries involved are insanely complex. Also consider packages as well.
Best of Luck
Edited by: FlyingGuy on Jan 14, 2011 5:24 PM
Edited by: FlyingGuy on Jan 14, 2011 5:25 PM

How to make input parameters as optional in the function definition

I have created a function for a data service which is at the Normalized layer and has 3 input parameters.
This function is called at the Integration layer in which we have to pass only one parameter. But for the remaning 2 parameters it is throwing an error as we cannot pass those parameters.
Is it possible that we can pass less number of parameters in the higher layer.
I had also made those parameters as optional in the function definition(by applying '?' while defining the arguments-e.g. $x as xs:string?), but error was thrown while generating the query plan.
I have attached a Demo project, in which i have defined a function at the normalized layer and this function is been called at the integration layer.
Thanks,
Kinjal 
I have done this by using the following type of construct in my XQuery code in the where clause:
WHERE fn:not(fn:exists($Request/ns1:CaseName)) or $Request/ns1:CaseName eq $CASE/CASE_NAME
The fn:not(fn:exists()) resolves to 'TRUE' when the parameter is not provided, so the expressoin after the "or" is not evaluated.
Does this answer your question? 
function myfunction( arg1 as xs:string, $arg2 as xs:string?) { ... }
The question mark indicates that $arg2 is optional. Now this does not mean you can omit the argument - it just means that the argument can be empty (like null in Java). In Xquery you use () to indicate an empty item.
So you could call
for $f in mfunction( 'test', () )
...
Note that in ALDSP 2.5 - you cannot call a function with an optional argument directly from the client. You can call it from another function. 
Thanks mreiche for the reply
But the problem is that while writing
function myfunction( arg1 as xs:string, $arg2 as xs:string?) { ... }
in function definition in the normalized layer, the query plan is throwing following error:-
com.bea.ld.QueryException: Cannot generate XQuery for the function {ld:DemoProject/Normalized/TestGeo}getGeo:3
at weblogic.rjvm.BasicOutboundRequest.sendReceive(BasicOutboundRequest.java:108) at weblogic.rmi.cluster.ReplicaAwareRemoteRef.invoke(ReplicaAwareRemoteRef.java:290)
at weblogic.rmi.cluster.ReplicaAwareRemoteRef.invoke(ReplicaAwareRemoteRef.java:248) at com.bea.ld.Server_ydm4ie_EOImpl_816_WLStub.executeFunction(Unknown Source)     at workshop.liquiddata.xds.views.queryplan.QueryPlanPanel.compileFunction(QueryPlanPanel.java:583) at workshop.liquiddata.xds.views.queryplan.QueryPlanPanel.access$900(QueryPlanPanel.java:39) at workshop.liquiddata.xds.views.queryplan.QueryPlanPanel$5.run(QueryPlanPanel.java:469) at java.lang.Thread.run(Thread.java:534)
Caused by: com.bea.ld.QueryException: Cannot generate XQuery for the function {ld:DemoProject/Normalized/TestGeo}getGeo:3 at com.bea.ld.EJBRequestHandler.invokeFunction(EJBRequestHandler.java:720) at com.bea.ld.EJBRequestHandler.executeFunction(EJBRequestHandler.java:339) at com.bea.ld.ServerBean.executeFunction(ServerBean.java:95) at com.bea.ld.Server_ydm4ie_EOImpl.executeFunction(Server_ydm4ie_EOImpl.java:312)
at com.bea.ld.Server_ydm4ie_EOImpl_WLSkel.invoke(Unknown Source)
at weblogic.rmi.internal.BasicServerRef.invoke(BasicServerRef.java:491)
at weblogic.rmi.cluster.ReplicaAwareServerRef.invoke(ReplicaAwareServerRef.java:120)
at weblogic.rmi.internal.BasicServerRef$1.run(BasicServerRef.java:434)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:363)
at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:147)
at weblogic.rmi.internal.BasicServerRef.handleRequest(BasicServerRef.java:429)
at weblogic.rmi.internal.BasicExecuteRequest.execute(BasicExecuteRequest.java:35)
at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:224)
at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:183)
Caused by: com.bea.ld.server.FunctionCallQueryBuilder$QueryBuilderException: The following parameter type is not supported: {http://www.w3.org/2001/XMLSchema}string?
at com.bea.ld.server.FunctionCallQueryBuilder.addParameter(FunctionCallQueryBuilder.java:257)
at com.bea.ld.server.FunctionCallQueryBuilder.buildQuery(FunctionCallQueryBuilder.java:99)
at com.bea.ld.EJBRequestHandler.invokeFunction(EJBRequestHandler.java:716)
But no error is thrown when I call this function at the Integration layer,using
for $f in mfunction( 'test', () )
and the output which i get is as expected. 
As I said earlier :
Note that in ALDSP 2.5 - you cannot call a function with an optional argument directly from the client. You can call it from another function. The stack trace shows that the call is from the client (FunctionCallQueryBuilder)
This is your function with the optiona parameters, correct?
{ld:DemoProject/Normalized/TestGeo}getGeo:3
You cannot call that from the Test View (or Query Plan Viewer). It will fail as above because it has optional parameters.
You can however, call a function that calls getGeo.
--
Edited by mreiche at 02/14/2008 10:33 AM 
WHERE fn:not(fn:exists($Request/ns1:CaseName)) or $Request/ns1:CaseName eq $CASE/CASE_NAME
The below solution works well in c ase of String type parameters but doesnt work with LONG type //// please guide me how should i proceed .. i am using
WHERE fn:not($Request!=0) or $Request/ns1:CaseName eq $CASE/CASE_NAME
passing the value 0 by default ...
Thanks
Navtej 
You want to be a little bit careful here. Behavior between DSP 2.5 and 3.x is different - and there is a bug in 3.x where it will silently not execute SQL when a passed parameter is null - so you could get incorrect results unless you are careful.
For DSP 3.x :
You will need to use code like this - notice that it uses an intermediate variable ($ARG0), and that when the argument is empty, it assigns some dummy value to it. (and of course if your arg is an integer, it will need to be a dummy integer value, when it is a date, you'll need to use a dummy date value).
declare function f1:getCustomerByLastName($arg0 as xs:string?) as element(f1:CUSTOMER)*{
let $ARG0:= if(empty($arg0)) then "dummy" else $arg0
for $CUSTOMER in f1:CUSTOMER()
where ($ARG0 eq $CUSTOMER/LAST_NAME or ($ARG0 eq "dummy") treat as xs:boolean)
return
$CUSTOMER
};
Please check the query plan to ensure that both conditions are pushed to the database.

iBatis : PLSQL TableType/ref Cursor as input to Stored Procedure

Hi, I need to call an Oracle stored procedure which takes one Oracle TableType and one Records type as input parameters. How do I specify this in teh SQLMapConfig file. I am able to retrieve Oracle ref Cursor as an ouput parameter from the stored procedure using: <parameter property="srHeaderCur" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" resultMap="srHeaderResultMap" /> Can I specify the same when giving an input parameter? Does iBatis support Oracle tableType or Oracle Record type as input parameter to stored procedure? Any help will be greatly appreciated. Thanks.
Disclaimer: I work for the makers of OrindaBuild, a product that generates Java Web Services for SQL and PL/SQL.
I need to call an Oracle stored procedure which takes one Oracle TableType and one Records type as input parameters. How do I specify this in teh SQLMapConfig file. I am able to retrieve Oracle ref Cursor as an ouput parameter from the stored procedure using:
<parameter property="srHeaderCur" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" resultMap="srHeaderResultMap" />
Can I specify the same when giving an input parameter? Does iBatis support Oracle tableType or Oracle Record type as input parameter to stored procedure?
Any help will be greatly appreciated.
Thanks.I can see two parts to this problem - passing a table and passing a record as a parameter to PL/SQL with JDBC.
1. Passing a record to Oracle PL/SQL using JDBC
For the purposes of the discussion let's assume this is our table and procedure:
CREATE TABLE ""FOO"
  (
    "TEXT_COL" VARCHAR2
    "DATE_COL" DATE,
    "NUM_COL" NUMBER
  );
create or replace
procedure fooproc (p_row in foo%rowtype) as
begin null; end;{code}
The procedure has 1 parameter, which is a record which has the same structure as a row in FOO.
*Oracle's JDBC driver has no concept or understanding of this, so it's not a question of picking the right data type to bind. It's simply not possible to pass in a %ROWTYPE record as a parameter directly, so any technology (such as iBatis) that relies on the JDBC driver won't be able to do this*.
Instead you can
(a) Rewrite the procedure:
{code}create or replace
procedure fooproc (p_row_textcol in varchar2
                  ,p_row_date_col in date
                  ,p_row_num_col in number) as
begin
null;
end;{code}
This is clearly a poor alternative - what happens if foo has 200 columns? Or someone adds a column?
We use plan (b) with OrindaBuild - which is to create an 'anonymous block' of PL/SQL which defines the record, loads it using a sequence of bind variables and them calls the procedure:
{code}public String getProcCallStatement()                                            
    {                                                             
    if (procCall == null)
      {                                                             
      procCall = new StringBuffer("DECLARE \n"); // 1
      procCall.append("/* Created  By OrindaBuild 6.0.2699 */ \n"); // 2
      procCall.append("/* Which can be obtained at www.orindasoft.com */ \n"); // 3
      procCall.append("p_row FOO%ROWTYPE; \n"); // 4
      procCall.append("BEGIN  \n"); // 5
      procCall.append("p_row.text_col := ?; \n"); // 6
      procCall.append("p_row.date_col := ?; \n"); // 7
      procCall.append("p_row.num_col := ?; \n"); // 8
      procCall.append(" \n"); // 9
      procCall.append("FOOPROC(p_row); \n"); // 10
      procCall.append("END; "); // 11 192 characters
     
      }    
    return(procCall.toString());
    }  {code}
There shouldn't be any reason why you can't use this approach to pass in records using any mechanism for working with Oracle JDBC that supports CallableStatement.
2. Passing an Array to PL/SQL using JDBC.
This is partially supported by Oracle's JDBC driver - I say 'partially' because 1 out of the possible 4 ways to represent an is supported by the driver. To pass an array it will need to be an Oracle TYPE object that is an array of other TYPE objects. This is not a common programming construct for pre-existing PL/SQL programs and to be honest I've no idea if iBatis would support this.
Edited by: dwrolfe on Mar 7, 2010 9:52 AM - Clarifed why iBatis can't do %ROWTYPE
Edited by: dwrolfe on Mar 7, 2010 9:54 AM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               

XQueryTypeException: bad value for type...

Hi,
I have the following function:
declare function tns:getColHeader($excel as schema-element(t25:excel), $colName as xs:string) as xs:string {
for $excel0 in $excel
for $row in $excel0/row
where $row/#rowNumber = 12
for $col in $row/col
where $col/#columnName eq $colName
return $col
};
when I supply $excel as in the attached excel.txt and $colName = "B", I should get "Systolic" returned, however I get an XQueryTypeException which informs me that the following is returned:
"Systolic (Anon_U_col_U_row_E_excel_D_excel_http___www.bea.com_excel)"
Any ideas? 
It's difficult for me to tell you what should or shouldn't be without seeing the (full) exception and without seeing the input that it is complaining about and without seeing the (complete) code (i.e. the DSP project) and without seeing the schema.
use element(t25:excel) instead of schema-element(t25:excel). schema-element is pretty much reserved to be used to declare the return type of an external function. If that doesn't fix the problem, please post your DSP project and the input you are using and the jar file containing the excel function. 
Thanks. Using element as opposed to schema-element didn't make a difference. I've attached my dsp project and the code for the java function that provides the excel functionality. The java function makes use of excel.jar which was taken form BEA samples (too big too attach)
The data service which is providing the problem is in SPIRITDataServices/normalised/cardiovascular/cvs.ds - the problem code is in a private function getColHeader.
If you need any more let me know. Thanks for your time. 
Your function is defined to return an xs:string, but the implementation returns $row/col (which is a Anon_U_col_U_row_E_excel_D_excel_http___www.bea.com_excel).
you want to return data($row/col). 
I changed my function to this:
declare function tns:getColHeader($excel as element(t25:excel), $colName as xs:string) as xs:string {
for $excel0 in $excel
for $row in $excel0/row
where $row/#rowNumber = 12
for $col in $row/col
where $col/#columnName eq $colName
return data($col)
};
And the exception is still the same. 
Ok I've resolved this issue. I found that if I used the string function as follows:
fn:string($col)
I received the correct string representation.
Thanks for your time.

Categories

Resources