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;