This discussion is archived
3 Replies Latest reply: Aug 16, 2012 8:37 AM by rp0428 RSS

Java calling PL/SQL Stored Procedure

user10177353 Newbie
Currently Being Moderated
Hi There
I have a scenario where I should pass the RowType to PL/SQL procedure from Java. Can you please help me how I can achieve it.

Eg: RowType
EmpID, EMPName, DeptID, Salary, all these fields are to be passed from Java to PL/SQL

If you have better approach please share.

Thanks in advance
  • 1. Re: Java calling PL/SQL Stored Procedure
    rp0428 Guru
    Currently Being Moderated
    >
    I have a scenario where I should pass the RowType to PL/SQL procedure from Java. Can you please help me how I can achieve it.

    Eg: RowType
    EmpID, EMPName, DeptID, Salary, all these fields are to be passed from Java to PL/SQL

    If you have better approach please share.
    >
    See the JDBC Developer's guide for how to call stored procedures. The easiest way for you would be to pass each column value as a separate parameter.

    See the section Stored Procedure Calls in JDBC Programs
    http://docs.oracle.com/cd/B19306_01/java.102/b14355/basic.htm#i1008346
  • 2. Re: Java calling PL/SQL Stored Procedure
    836153 Newbie
    Currently Being Moderated
    You can not convert any Java type to Oracle RowType.
    There is a list of datatypes that are compatible with Java and Oracle.
    I agree with the guru, it is preferred to pass each parameter as separate argument.

    Regards,
    Parth
  • 3. Re: Java calling PL/SQL Stored Procedure
    rp0428 Guru
    Currently Being Moderated
    >
    it is preferred to pass each parameter as separate argument.
    >
    It isn't a question of preference. It is related to what you said
    >
    There is a list of datatypes that are compatible with Java and Oracle.
    >
    The datatypes have to be SQL types. Assuming OP is alluding to the %ROWTYPE syntax (as in EMP%ROWTYPE) to reference a table row that is only available in PL/SQL. External code can only use SQL types.

    OP could create a SQL type and use that as a procedure parameter
    -- type to match emp record
    create or replace type emp_scalar_type as object
      (EMPNO NUMBER(4) ,
       ENAME VARCHAR2(10),
       JOB VARCHAR2(9),
       MGR NUMBER(4),
       HIREDATE DATE,
       SAL NUMBER(7, 2),
       COMM NUMBER(7, 2),
       DEPTNO NUMBER(2)
      )
      /
    But as I said in my reply it is simpler to just pass each value separately rather than create a type instance and pass the type.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points