2nd time, less text
On 1/27/07, Charles Schultz <sacrophyte_at_gmail.com> wrote:
>
> By way of an update, I now have a working version thanks to help from
> Maxim Demenko, Job Miller and Ian Cary. I hear that JPublisher would have
> helped a bit, but I still can't get it installed. =)
>
> We are still having trouble using a "nested" sql table (table of user-type
> record), but I was able to use a table of varchar2 and that works
> sufficiently. My biggest concern is performance down the road when the
> transaction rate kicks up. Right now, I have to loop through each element of
> the table (which is really an array) and assign it to a member of my pl/sql
> table. I wish I could do a CAST, but I have not found anything that works
> (yet).
>
> For the curious, here is a couple code snippets:
> CREATE OR REPLACE TYPE jobLaborNestedHybrid is TABLE OF VARCHAR2(60);
> /
>
> create or replace package body np_job_labor_jdbc_wrapper
> as
> PROCEDURE p_create
> (p_hybrid IN OUT jobLaborNestedHybrid,
> p_change_reason varchar2 default null,
> p_default_labor_ind varchar2 default 'N',
> p_warnings_out OUT varchar2
> )
> is
> p_job_labor_tab nb_job_labor.job_labor_tab;
> offset number;
> begin
> -- convert IN TABLE to pl/sql table
> if p_hybrid is null then
> null;
> else
> -- Loop for each record, which consists of 34 rows each.
> for i in 1..p_hybrid.COUNT/34 loop
> offset := (i-1)*34;
> p_job_labor_tab(i).R_PIDM := to_number(p_hybrid(offset+1));
> ...
> ...
> p_job_labor_tab(i).r_internal_record_id := p_hybrid(offset+34);
> end loop;
> end if;
> -- Send pl/sql table to p_create
>
> np_job_labor.p_create(p_job_labor_tab,p_change_reason,p_default_labor_ind,p_warnings_out);
>
> end;
> end;
> /
>
> private void testSQL1(Connection db_connection) throws SQLException
> {
> String sql = "{call np_job_labor_jdbc_wrapper.p_create(?, ?, ?,
> ?)}";
> CallableStatement statement = db_connection.prepareCall(sql);
> final String DATA_TYPE = "JOBLABORNESTEDHYBRID";
> ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor
> (
> DATA_TYPE,
> db_connection);
> String[] content = {
> "88659",
> ...
> ...
> "1"};
> Array array = new ARRAY(
> arrayDescriptor,
> db_connection,
> content);
>
> statement.registerOutParameter(1, OracleTypes.ARRAY, DATA_TYPE);
> statement.setArray(1, array);
> statement.setString(2, "change reason");
> statement.setString(3, "labor ind");
> statement.registerOutParameter (4, OracleTypes.VARCHAR);
>
> logger.info(sql);
> statement.execute();
>
> array = statement.getArray(1);
> String warnings = statement.getString (4);
> statement.close();
> }
>
--
Charles Schultz
--
http://www.freelists.org/webpage/oracle-l
Received on Sat Jan 27 2007 - 10:30:21 CST
