Advertisement
Guest User

Untitled

a guest
Mar 15th, 2019
127
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.65 KB | None | 0 0
  1. CREATE OR REPLACE PACKAGE "PKG_PERSONA" AS TYPE typ_persona IS RECORD(codigo st_personas.persona.codigo%TYPE, nombre persona.cod_per_1%TYPE);
  2.  
  3. TYPE tbl_persona IS TABLE OF typ_persona INDEX BY binary_integer;
  4. TYPE crs_persona IS REF CURSOR
  5. RETURN typ_persona;
  6. END PKG_PERSONA;
  7.  
  8. CREATE OR REPLACE PACKAGE BODY "PKG_PERSONA_BODY" AS**
  9. ------------------------Procedimiento que procesa datos de personas---------
  10. ----------------------------
  11. PROCEDURE prc_procesa_datos(pic_persona IN tbl_persona, -- Datos de persona
  12. pov_error OUT VARCHAR2, -- Variable de retorno de error personalizado
  13. pov_error_sql OUT VARCHAR2)
  14. IS
  15. vrn_registro NUMBER := pic_persona.COUNT;
  16. BEGIN
  17. -- Procesa datos de persona
  18. FOR i IN 1 .. vrn_registro LOOP
  19. IF pic_persona(i).codigo IS NULL THEN
  20. -- Ingresa Nuevo
  21. pkg_persona.prc_inserta (params ..);
  22. IF pov_error IS NOT NULL THEN
  23. RETURN;
  24. END IF;
  25. -- Fin de ingresa Nuevo
  26. END IF;
  27. END LOOP;
  28. -- Fin de procesa datos de persona
  29. pov_error:= vrn_registro;
  30. EXCEPTION
  31. WHEN OTHERS THEN
  32. pov_error := 'Ocurrio un error al tratar de ingresar datos de una persona';
  33. pov_error_sql := 'Error en prc_procesa_datos ' || SQLERRM;
  34. END;
  35. END PKG_PERSONA_BODY;
  36.  
  37. public static void main(String[] args) {
  38.  
  39. try {
  40. DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
  41. Connection conn = DriverManager.getConnection(
  42.  
  43. "jdbc:oracle:thin:@localhost:1521:sid", "usuario", "claves");
  44.  
  45. conn.setAutoCommit(false);
  46. final StructDescriptor recDescriptor = StructDescriptor.createDescriptor("pkg_persona.typ_persona", conn);
  47. final ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("pkg_persona.tbl_persona", conn);
  48. CallableStatement stmt = conn.prepareCall("{ call pkg_persona.pkg_persona_body(?) }");
  49.  
  50. Object[] array_of_records = new Object[5];
  51.  
  52. Object[] java_record_array = new Object[2];
  53.  
  54. for (int i = 0; i < 5; i++) {
  55. java_record_array[0] = "col1";
  56. java_record_array[1] = "col2";
  57.  
  58. STRUCT oracle_record = new STRUCT(recDescriptor, conn, java_record_array);
  59. array_of_records[i] = oracle_record;
  60. }
  61.  
  62. ARRAY oracle_array = new ARRAY(arrayDescriptor, conn, array_of_records);
  63.  
  64. stmt.setObject(1, oracle_array);
  65. stmt.execute();
  66. conn.commit();
  67. } catch (Exception e) {
  68.  
  69. System.out.println(e);
  70.  
  71. }
  72. }
  73.  
  74. java.sql.SQLException: patrón de nombre no válido: tbl_persona
  75.  
  76. final StructDescriptor recDescriptor = StructDescriptor.createDescriptor("pkg_persona.typ_persona", conn);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement