How to insert and execute parameters in a precedent in oracle 11g in vb6

Question

PROCEDURE        pr_consulta_rg_site (
p_rg_cidadao in varchar2,
p_dt_nascimento in varchar2,
p_rg_cidadao_o out varchar2,
p_no_cidadao_o out varchar2,
p_dt_nascimento_o out varchar2,
p_dt_expedicao_o out varchar2,
p_texto_o out varchar2
) as
   w_count number;
   w_count_del number;
   w_count_bloq number;
   w_rg_cidadao number;
   w_teste_data varchar2(10);
   abandona exception

Answer 1

See if this reference helps: http://www.codeproject.com/Articles/15222/How-to-Use-Stored-Procedures-in-VB and http://www.w3schools.com/asp/met_comm_createparameter.asp

Answer 2

1. Include the nuget package Oracle.ManagedDataAccess in your project (This is a driver connection to the database Oracle).

2. Import these two namespaces into your code:

Imports Oracle.ManagedDataAccess.Client
Imports Oracle.ManagedDataAccess.Types

3. To create a parameter do as follows:

Dim rgCidadaoParameter As OracleParameter = oracleCommand.CreateParameter()
rgCidadaoParameter.ParameterName = "p_rg_cidadao"
rgCidadaoParameter.OracleDbType = OracleDbType.Varchar2
rgCidadaoParameter.Size = 4096
rgCidadaoParameter.Value = "1.111.111"
oracleCommand.Parameters.Add(rgCidadaoParameter)

You can also change the "direction" of your parameter to indicate if it is of type IN, OUT or RETURNVALUE (That serves to receive values of functions).

This is the default value and serves for type parameters IN:

rgCidadaoOutParameter.Direction = ParameterDirection.Input

To OUT:

rgCidadaoOutParameter.Direction = ParameterDirection.Output

For returns of functions:

rgCidadaoOutParameter.Direction = ParameterDirection.ReturnValue.

Below is a practical example based on your question:

Dim connectionString As String = "Data Source=banco;User ID=usuario;Password=senha;"

Using oracleConnection As OracleConnection = New Oracle.ManagedDataAccess.Client.OracleConnection(connectionString)

    oracleConnection.Open()

    Dim oracleCommand As OracleCommand = oracleConnection.CreateCommand()
    oracleCommand.CommandType = CommandType.StoredProcedure
    oracleCommand.CommandText = "pr_consulta_rg_site"

    Dim rgCidadaoParameter As OracleParameter = oracleCommand.CreateParameter()
    rgCidadaoParameter.ParameterName = "p_rg_cidadao"
    rgCidadaoParameter.OracleDbType = OracleDbType.Varchar2
    rgCidadaoParameter.Size = 4096
    rgCidadaoParameter.Value = "1.111.111"
    oracleCommand.Parameters.Add(rgCidadaoParameter)

    Dim rgCidadaoOutParameter As OracleParameter = oracleCommand.CreateParameter()
    rgCidadaoOutParameter.ParameterName = "p_rg_cidadao_o"
    rgCidadaoOutParameter.OracleDbType = OracleDbType.Varchar2
    rgCidadaoParameter.Size = 4096
    rgCidadaoOutParameter.Direction = ParameterDirection.ReturnValue

    oracleCommand.Parameters.Add(rgCidadaoOutParameter)

    Dim result As Int32 = oracleCommand.ExecuteNonQuery()

    Dim rgCidadaoOut As OracleString = rgCidadaoOutParameter.Value
    Console.WriteLine(rgCidadaoOut.Value)

End Using