Executar uma procedure oracle com uma variavel do tipo IS TABLE

Olá
Pessoal
Estou tentando executar essa procedure oracle com uma variavel do tipo IS TABLE e ele esta me dando o seguinte erro:

Oracle Error: 17004 - Tipo de coluna inválido

Alguem saberia me dizer o que esta rolnado ?

Grato

String query = “{call  qry(?)}”;

OracleCallableStatement cs = (OracleCallableStatement)conn.prepareCall(query);

cs.registerOutParameter(1, oracle.jdbc.driver.OracleTypes.OTHER);

cs.execute();

TYPE cgc$rec_tab IS TABLE OF cgc$rec INDEX BY BINARY_INTEGER;

PROCEDURE qry(cgc$rec_rows OUT cgc$rec_tab) IS

–  PL/SQL records used to call API procedures

inds           cg$GPT_PRODUCT.cg$ind_type;

sql_stmt       VARCHAR2(4000);

err_str        VARCHAR2(4000);

query_cursor   INTEGER;

rows_processed INTEGER;

idx            INTEGER;

length         INTEGER := 2000;

value_length   INTEGER;

offset         INTEGER;

value          VARCHAR2(2000);

–  Set up define_column datatype variables

PRODUCT_NAME1 GPT_PRODUCT.PRODUCT_NAME%TYPE;

PRODUCT_ORIGCOD2 GPT_PRODUCT.PRODUCT_ORIGCOD%TYPE;

PRODUCT_BRDCOD3 GPT_PRODUCT.PRODUCT_BRDCOD%TYPE;

L_LOCAL_NAME4 GPT_LOCAL.LOCAL_NAME%TYPE;

L_STRUCT_NAME5 GPT_STRUCT.STRUCT_NAME%TYPE;

PRODUCT_CODE6 GPT_PRODUCT.PRODUCT_CODE%TYPE;

PRODUCT_GPTCOD7 GPT_PRODUCT.PRODUCT_GPTCOD%TYPE;

PRODUCT_DESCR8 GPT_PRODUCT.PRODUCT_DESCR%TYPE;

PHASE_CODE9 GPT_PRODUCT.PHASE_CODE%TYPE;

LK_PHASE_NAME10 GPT_PHASE.PHASE_NAME%TYPE;

STRPAT_CODE11 GPT_PRODUCT.STRPAT_CODE%TYPE;

CATEG_CODE12 GPT_PRODUCT.CATEG_CODE%TYPE;

AGENT_CODE13 GPT_PRODUCT.AGENT_CODE%TYPE;

LK_AGENT_NAME14 GPT_AGENT.AGENT_NAME%TYPE;

LOCAL_CODE15 GPT_PRODUCT.LOCAL_CODE%TYPE;

PRODUCT_STATE16 GPT_PRODUCT.PRODUCT_STATE%TYPE;

STRUCT_CODE17 GPT_PRODUCT.STRUCT_CODE%TYPE;

MKTLOC_CODE18 GPT_PRODUCT.MKTLOC_CODE%TYPE;

REQUIRM_CODE19 GPT_PRODUCT.REQUIRM_CODE%TYPE;

PRODUCT_TPCALT20 GPT_PRODUCT.PRODUCT_TPCALT%TYPE;

SEGMENT_CODE21 GPT_PRODUCT.SEGMENT_CODE%TYPE;

L_SEGMENT_NAME22 GPT_SEGMENT.SEGMENT_NAME%TYPE;

PRODUCT_ORIGIN23 GPT_PRODUCT.PRODUCT_ORIGIN%TYPE;

AGENT_CODE_GEN124 GPT_PRODUCT.AGENT_CODE_GEN1%TYPE;

L_AGENT_NAME_GEN125 GPT_AGENT.AGENT_NAME%TYPE;

AGENT_CODE_GEN226 GPT_PRODUCT.AGENT_CODE_GEN2%TYPE;

L_AGENT_NAME_GEN227 GPT_AGENT.AGENT_NAME%TYPE;

AGENT_CODE_GEN328 GPT_PRODUCT.AGENT_CODE_GEN3%TYPE;

L_AGENT_NAME_GEN329 GPT_AGENT.AGENT_NAME%TYPE;

L_AGENT_TYPE_GEN130 GPT_AGENT.AGNTYP_CODE%TYPE;

L_AGENT_TYPE_GEN231 GPT_AGENT.AGNTYP_CODE%TYPE;

L_AGENT_TYPE_GEN332 GPT_AGENT.AGNTYP_CODE%TYPE;

PRODUCT_USR_ALT33 GPT_PRODUCT.PRODUCT_USR_ALT%TYPE;

PRODUCT_DAT_ALT34 GPT_PRODUCT.PRODUCT_DAT_ALT%TYPE;

BEGIN

–  API Logic Pre-Query <>

–  API Logic Pre-Query << End >>

– setup the indicators variables to show the columns that

– are used by this module component usage

inds.PRODUCT_NAME := TRUE;

inds.PRODUCT_ORIGCOD := TRUE;

inds.PRODUCT_BRDCOD := TRUE;

inds.PRODUCT_CODE := TRUE;

inds.PRODUCT_GPTCOD := TRUE;

inds.PRODUCT_DESCR := TRUE;

inds.PHASE_CODE := TRUE;

inds.STRPAT_CODE := TRUE;

inds.CATEG_CODE := TRUE;

inds.AGENT_CODE := TRUE;

inds.LOCAL_CODE := TRUE;

inds.PRODUCT_STATE := TRUE;

inds.STRUCT_CODE := TRUE;

inds.MKTLOC_CODE := TRUE;

inds.REQUIRM_CODE := TRUE;

inds.PRODUCT_TPCALT := TRUE;

inds.SEGMENT_CODE := TRUE;

inds.PRODUCT_ORIGIN := TRUE;

inds.AGENT_CODE_GEN1 := TRUE;

inds.AGENT_CODE_GEN2 := TRUE;

inds.AGENT_CODE_GEN3 := TRUE;

inds.PRODUCT_USR_ALT := TRUE;

inds.PRODUCT_DAT_ALT := TRUE;

sql_stmt := ‘SELECT GPT_PRODUCT.PRODUCT_NAME PRODUCT_NAME,

GPT_PRODUCT.PRODUCT_ORIGCOD PRODUCT_ORIGCOD,

GPT_PRODUCT.PRODUCT_BRDCOD PRODUCT_BRDCOD,

L_GPT_LOCAL.LOCAL_NAME L_LOCAL_NAME,

L_GPT_STRUCT.STRUCT_NAME L_STRUCT_NAME,

GPT_PRODUCT.PRODUCT_CODE PRODUCT_CODE,

GPT_PRODUCT.PRODUCT_GPTCOD PRODUCT_GPTCOD,

GPT_PRODUCT.PRODUCT_DESCR PRODUCT_DESCR,

GPT_PRODUCT.PHASE_CODE PHASE_CODE,

GPT_PHASE.PHASE_NAME LK_PHASE_NAME,

GPT_PRODUCT.STRPAT_CODE STRPAT_CODE,

GPT_PRODUCT.CATEG_CODE CATEG_CODE,

GPT_PRODUCT.AGENT_CODE AGENT_CODE,

GPT_AGENT.AGENT_NAME LK_AGENT_NAME,

GPT_PRODUCT.LOCAL_CODE LOCAL_CODE,

GPT_PRODUCT.PRODUCT_STATE PRODUCT_STATE,

GPT_PRODUCT.STRUCT_CODE STRUCT_CODE,

GPT_PRODUCT.MKTLOC_CODE MKTLOC_CODE,

GPT_PRODUCT.REQUIRM_CODE REQUIRM_CODE,

GPT_PRODUCT.PRODUCT_TPCALT PRODUCT_TPCALT,

GPT_PRODUCT.SEGMENT_CODE SEGMENT_CODE,

L_GPT_SEGMENT.SEGMENT_NAME L_SEGMENT_NAME,

GPT_PRODUCT.PRODUCT_ORIGIN PRODUCT_ORIGIN,

GPT_PRODUCT.AGENT_CODE_GEN1 AGENT_CODE_GEN1,

L_GPT_AGENT_GEN1.AGENT_NAME L_AGENT_NAME_GEN1,

GPT_PRODUCT.AGENT_CODE_GEN2 AGENT_CODE_GEN2,

L_GPT_AGENT_GEN2.AGENT_NAME L_AGENT_NAME_GEN2,

GPT_PRODUCT.AGENT_CODE_GEN3 AGENT_CODE_GEN3,

L_GPT_AGENT_GEN3.AGENT_NAME L_AGENT_NAME_GEN3,

L_GPT_AGENT_GEN1.AGNTYP_CODE L_AGENT_TYPE_GEN1,

L_GPT_AGENT_GEN2.AGNTYP_CODE L_AGENT_TYPE_GEN2,

L_GPT_AGENT_GEN3.AGNTYP_CODE L_AGENT_TYPE_GEN3,

GPT_PRODUCT.PRODUCT_USR_ALT PRODUCT_USR_ALT,

GPT_PRODUCT.PRODUCT_DAT_ALT PRODUCT_DAT_ALT

FROM GPT_PRODUCT GPT_PRODUCT,

GPT_AGENT L_GPT_AGENT_GEN3,

GPT_AGENT GPT_AGENT,

GPT_AGENT L_GPT_AGENT_GEN2,

GPT_STRUCT_PATTERN L_GPT_STRUCT_PATTERN,

GPT_STRUCT L_GPT_STRUCT,

GPT_LOCAL L_GPT_LOCAL,

GPT_SEGMENT L_GPT_SEGMENT,

GPT_AGENT L_GPT_AGENT_GEN1,

GPT_PHASE GPT_PHASE’;

sql_stmt := sql_stmt || ’ WHERE  GPT_PRODUCT.AGENT_CODE_GEN3 = L_GPT_AGENT_GEN3.AGENT_CODE (+) AND

GPT_PRODUCT.AGENT_CODE = GPT_AGENT.AGENT_CODE AND

GPT_PRODUCT.AGENT_CODE_GEN2 = L_GPT_AGENT_GEN2.AGENT_CODE (+) AND

GPT_PRODUCT.STRUCT_CODE = L_GPT_STRUCT_PATTERN.STRUCT_CODE (+) AND

GPT_PRODUCT.CATEG_CODE = L_GPT_STRUCT_PATTERN.CATEG_CODE (+) AND

GPT_PRODUCT.STRPAT_CODE = L_GPT_STRUCT_PATTERN.STRPAT_CODE (+) AND

L_GPT_STRUCT_PATTERN.STRUCT_CODE = L_GPT_STRUCT.STRUCT_CODE (+) AND

L_GPT_STRUCT_PATTERN.CATEG_CODE = L_GPT_STRUCT.CATEG_CODE (+) AND

GPT_PRODUCT.LOCAL_CODE = L_GPT_LOCAL.LOCAL_CODE AND

GPT_PRODUCT.SEGMENT_CODE = L_GPT_SEGMENT.SEGMENT_CODE (+) AND

GPT_PRODUCT.AGENT_CODE_GEN1 = L_GPT_AGENT_GEN1.AGENT_CODE (+) AND

GPT_PRODUCT.PHASE_CODE = GPT_PHASE.PHASE_CODE (+)’;

–  Prepare a cursor to select from the query

query_cursor := dbms_sql.open_cursor;

dbms_sql.parse(query_cursor, sql_stmt, dbms_sql.v7);

dbms_sql.define_column(query_cursor, 1, PRODUCT_NAME1, 100);

dbms_sql.define_column(query_cursor, 2, PRODUCT_ORIGCOD2, 50);

dbms_sql.define_column(query_cursor, 3, PRODUCT_BRDCOD3, 100);

dbms_sql.define_column(query_cursor, 4, L_LOCAL_NAME4, 100);

dbms_sql.define_column(query_cursor, 5, L_STRUCT_NAME5, 100);

dbms_sql.define_column(query_cursor, 6, PRODUCT_CODE6);

dbms_sql.define_column(query_cursor, 7, PRODUCT_GPTCOD7, 50);

dbms_sql.define_column(query_cursor, 8, PRODUCT_DESCR8, 500);

dbms_sql.define_column(query_cursor, 9, PHASE_CODE9);

dbms_sql.define_column(query_cursor, 10, LK_PHASE_NAME10, 100);

dbms_sql.define_column(query_cursor, 11, STRPAT_CODE11);

dbms_sql.define_column(query_cursor, 12, CATEG_CODE12);

dbms_sql.define_column(query_cursor, 13, AGENT_CODE13);

dbms_sql.define_column(query_cursor, 14, LK_AGENT_NAME14, 100);

dbms_sql.define_column(query_cursor, 15, LOCAL_CODE15);

dbms_sql.define_column(query_cursor, 16, PRODUCT_STATE16, 1);

dbms_sql.define_column(query_cursor, 17, STRUCT_CODE17);

dbms_sql.define_column(query_cursor, 18, MKTLOC_CODE18);

dbms_sql.define_column(query_cursor, 19, REQUIRM_CODE19);

dbms_sql.define_column(query_cursor, 20, PRODUCT_TPCALT20, 1);

dbms_sql.define_column(query_cursor, 21, SEGMENT_CODE21);

dbms_sql.define_column(query_cursor, 22, L_SEGMENT_NAME22, 100);

dbms_sql.define_column(query_cursor, 23, PRODUCT_ORIGIN23, 1);

dbms_sql.define_column(query_cursor, 24, AGENT_CODE_GEN124);

dbms_sql.define_column(query_cursor, 25, L_AGENT_NAME_GEN125, 100);

dbms_sql.define_column(query_cursor, 26, AGENT_CODE_GEN226);

dbms_sql.define_column(query_cursor, 27, L_AGENT_NAME_GEN227, 100);

dbms_sql.define_column(query_cursor, 28, AGENT_CODE_GEN328);

dbms_sql.define_column(query_cursor, 29, L_AGENT_NAME_GEN329, 100);

dbms_sql.define_column(query_cursor, 30, L_AGENT_TYPE_GEN130);

dbms_sql.define_column(query_cursor, 31, L_AGENT_TYPE_GEN231);

dbms_sql.define_column(query_cursor, 32, L_AGENT_TYPE_GEN332);

dbms_sql.define_column(query_cursor, 33, PRODUCT_USR_ALT33, 50);

dbms_sql.define_column(query_cursor, 34, PRODUCT_DAT_ALT34);

rows_processed := dbms_sql.execute(query_cursor);

idx := 0;

loop

if dbms_sql.fetch_rows(query_cursor) > 0 then

–  Get column values of row into table of records

dbms_sql.column_value(query_cursor, 1, cgc$rec_rows(idx).PRODUCT_NAME);

dbms_sql.column_value(query_cursor, 2, cgc$rec_rows(idx).PRODUCT_ORIGCOD);

dbms_sql.column_value(query_cursor, 3, cgc$rec_rows(idx).PRODUCT_BRDCOD);

dbms_sql.column_value(query_cursor, 4, cgc$rec_rows(idx).L_LOCAL_NAME);

dbms_sql.column_value(query_cursor, 5, cgc$rec_rows(idx).L_STRUCT_NAME);

dbms_sql.column_value(query_cursor, 6, cgc$rec_rows(idx).PRODUCT_CODE);

dbms_sql.column_value(query_cursor, 7, cgc$rec_rows(idx).PRODUCT_GPTCOD);

dbms_sql.column_value(query_cursor, 8, cgc$rec_rows(idx).PRODUCT_DESCR);

dbms_sql.column_value(query_cursor, 9, cgc$rec_rows(idx).PHASE_CODE);

dbms_sql.column_value(query_cursor, 10, cgc$rec_rows(idx).LK_PHASE_NAME);

dbms_sql.column_value(query_cursor, 11, cgc$rec_rows(idx).STRPAT_CODE);

dbms_sql.column_value(query_cursor, 12, cgc$rec_rows(idx).CATEG_CODE);

dbms_sql.column_value(query_cursor, 13, cgc$rec_rows(idx).AGENT_CODE);

dbms_sql.column_value(query_cursor, 14, cgc$rec_rows(idx).LK_AGENT_NAME);

dbms_sql.column_value(query_cursor, 15, cgc$rec_rows(idx).LOCAL_CODE);

dbms_sql.column_value(query_cursor, 16, cgc$rec_rows(idx).PRODUCT_STATE);

dbms_sql.column_value(query_cursor, 17, cgc$rec_rows(idx).STRUCT_CODE);

dbms_sql.column_value(query_cursor, 18, cgc$rec_rows(idx).MKTLOC_CODE);

dbms_sql.column_value(query_cursor, 19, cgc$rec_rows(idx).REQUIRM_CODE);

dbms_sql.column_value(query_cursor, 20, cgc$rec_rows(idx).PRODUCT_TPCALT);

dbms_sql.column_value(query_cursor, 21, cgc$rec_rows(idx).SEGMENT_CODE);

dbms_sql.column_value(query_cursor, 22, cgc$rec_rows(idx).L_SEGMENT_NAME);

dbms_sql.column_value(query_cursor, 23, cgc$rec_rows(idx).PRODUCT_ORIGIN);

dbms_sql.column_value(query_cursor, 24, cgc$rec_rows(idx).AGENT_CODE_GEN1);

dbms_sql.column_value(query_cursor, 25, cgc$rec_rows(idx).L_AGENT_NAME_GEN1);

dbms_sql.column_value(query_cursor, 26, cgc$rec_rows(idx).AGENT_CODE_GEN2);

dbms_sql.column_value(query_cursor, 27, cgc$rec_rows(idx).L_AGENT_NAME_GEN2);

dbms_sql.column_value(query_cursor, 28, cgc$rec_rows(idx).AGENT_CODE_GEN3);

dbms_sql.column_value(query_cursor, 29, cgc$rec_rows(idx).L_AGENT_NAME_GEN3);

dbms_sql.column_value(query_cursor, 30, cgc$rec_rows(idx).L_AGENT_TYPE_GEN1);

dbms_sql.column_value(query_cursor, 31, cgc$rec_rows(idx).L_AGENT_TYPE_GEN2);

dbms_sql.column_value(query_cursor, 32, cgc$rec_rows(idx).L_AGENT_TYPE_GEN3);

dbms_sql.column_value(query_cursor, 33, cgc$rec_rows(idx).PRODUCT_USR_ALT);

dbms_sql.column_value(query_cursor, 34, cgc$rec_rows(idx).PRODUCT_DAT_ALT);

idx := idx + 1;

else

–  No more rows

exit;

end if;

end loop;

dbms_sql.close_cursor(query_cursor);

EXCEPTION

WHEN OTHERS THEN

err_str := ‘ORA’||to_char(SQLCODE)||’ Last SQL code: '||dbms_sql.last_sql_function_code||

’ Posn in SQL stmt: '||dbms_sql.last_error_position||

’ Row Cnt: ‘||dbms_sql.last_row_count||’ Rowid: '||dbms_sql.last_row_id;

cg$errors.push(err_str);

cg$errors.raise_failure;

–  API Logic Post-Query <>

–  API Logic Post-Query << End >>

END qry;

3 Respostas

Topicos relacionados