Java-Stored Procedure returning Cursor " "

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Mon, 03 Mar 2008 11:20 am

Facebook friends, blame me, I'm creating a class to act as a mini Database link Oracle to other banks.

But I haven't been able to mount a cursor to return for this procedure. All I could do with that, when I call this method, it create a table similar to table from another database (PostgreSQL, for example) in oracle.

But ... I didn't want to create this table copy ... And Yes ... Just return to the current query.

Follows the code below:
Code: Select all
create or replace and compile java source named webservice as
import java.net.*;
import org.w3c.dom.*;
import oracle.xml.parser.v2.*;
import java.sql.*;
import oracle.jdbc.*;

public class WebService
{
  private static final String ENDERECO = "http://******";
  private static final String BANCO = "POSTGRESQL";
  private static final String IP = "******";
  private static final int PORTA = ******;
  private static final String SERVICO = "******";
  private static final String USUARIO = "******";
  private static final String SENHA = "******";
 
  private static Connection oConn = null;
 
   public static void criarTabela (String nomeTabela, String parametros) throws Exception
   {
    CallableStatement stmt;
    String strQ = "create table " + nomeTabela + " (";
    strQ += parametros;
    strQ += ")";

      try {
        stmt = oConn.prepareCall("drop table " + nomeTabela);
        stmt.execute();
    } catch (Exception e) {}

      stmt = oConn.prepareCall(strQ);
    stmt.execute();
  }
 
  private static void adicionarLinha(Node nl, String tabela, String[] nomeParametros) throws Exception {
    String strQ = "INSERT INTO " + tabela + " VALUES (";
    CallableStatement stmt;
   
    for (int i=0;i<nomeParametros.length;i++) {
        if (i!=0) {
           strQ += ", ";
        }
        strQ += "'" + nl.getAttributes().getNamedItem(nomeParametros[i]).getNodeValue() + "'";
    }
    strQ += ")";
      stmt = oConn.prepareCall(strQ);
    stmt.execute();
  }
   
   public static void consulta (String nomeView, String nomeTabela) throws Exception   {
    consulta (ENDERECO, BANCO, IP, PORTA, SERVICO, USUARIO, SENHA, nomeView, nomeTabela);
  }
 
   public static void consulta (String endereco, String banco, String ip, int porta, String servico, String usuario, String senha, String nomeView, String nomeTabela) throws Exception   {

      StringBuffer response = new StringBuffer();
      String placa = null;
    String strUrl = endereco + "?txtBanco=" + banco + "&txtIp=" + ip + "&txtPorta=" + porta + "&txtServico=" + servico + "&txtUsuario=" + usuario + "&txtSenha=" + senha + "&txtSql=SELECT%20*%20FROM%20" + nomeView;
      DOMParser parser = new DOMParser();
      URL url = new URL(strUrl);
      parser.setErrorStream(System.err);
      parser.showWarnings(true);
      parser.parse(url);
      XMLDocument doc = parser.getDocument();

      String erro = "";

      XMLElement nsElement;

      NodeList nl = doc.getElementsByTagName("FIELD");
      Node params = doc.getElementsByTagName("PARAMS").item(0);
      erro = params.getAttributes().getNamedItem("ERRO").getNodeValue();
      if (erro!=null&&!erro.equals("")) {
         throw new Exception(erro);
      }
    String parametros = "";
    String[] nomeParametros = new String[nl.getLength()];
      for (int i = 0; i < nl.getLength(); i++) {
        if (i!=0) {
           parametros += ", ";
        }
        parametros += nl.item(i).getAttributes().getNamedItem("attrname").getNodeValue() + " " + nl.item(i).getAttributes().getNamedItem("fieldtype").getNodeValue() + "(" + nl.item(i).getAttributes().getNamedItem("WIDTH").getNodeValue() + ")";   
        nomeParametros[i] = nl.item(i).getAttributes().getNamedItem("attrname").getNodeValue();
      }
    try {
        oConn = new OracleDriver().defaultConnection();
        criarTabela(nomeTabela,parametros);
          nl = doc.getElementsByTagName("ROW");
          for (int i = 0; i < nl.getLength(); i++) {
            adicionarLinha(nl.item(i),nomeTabela,nomeParametros);
          }
        oConn.commit();
    } catch (Exception e) {
        oConn.close();
        oConn = null;
        throw e;
    }
      parser.reset();
   }
}
;
davidguerra
Location: CE

Poston Mon, 03 Mar 2008 12:32 pm

Hopefully someone will help you because I don't understand anything you said and much less of your code
victorhugomuniz
Location: Rio de Janeiro - RJ

Jesus está voltando, volte antes para Ele.

Image JavaBlackBelt

Poston Mon, 03 Mar 2008 1:09 pm

hehehe is the following ... This code accesses a URL that returns the contents of a select, in XML format. With a description of the types of columns. Based on that return, I create a temporary table with the same structure and data.

What I am trying to do is, based on that return XML, assemble a cursor with the data I have, without the need to create this temporary table.

Would be more or less like this: SELECT * FROM getPostgreSQL (' SELECT * FROM TBLUSUARIO ');

The function getPostgreSQL () return a cursor. So I wouldn't have to create a temporary table with a structure and data equal to TBLUSUARIO (table on PostgreSQL).

Currently I do so: BEGIN gerarTabela (' TBLUSUARIO ', ' TBLUSUARIO_TEMP '); END;

This procedure creates a temporary table, in Oracle, identical to the official PostgreSQL.

I'm looking for a more flexible way and without having to create temporary tables for the problem.

Improved a little?

Thanks. :)
davidguerra
Location: CE


  • See also
    Replies
    Views
    Last Post


      Return to PL/SQL

      Who is online

      Users browsing this forum: No registered users and 4 guests