OPEN PO_EXPCUR FOR V_QUERY_SQL USING …

This code will throw ora-01008 when the parameter setting is wrong: not all variables are bound

Reason: when executing a dynamic SQL statement, the number of variables filled in the SQL string is inconsistent with the number of variables bound in the using keyword.

There are many times that all kinds of reasons can cause this problem. Now we will talk about a relatively small reason.

V_QUERY_SQL  =  ‘SELECT  COUNT(DISTINCT (B.BATCH_ID))  FROM T_DD_RES T
        JOIN T_DD_REQ Q
        ON T.ID_DD_REQ = Q.ID
        JOIN T_DD_BATCH B
        ON T.BATCH_ID = B.BATCH_ID  WHERE T.INSERT_TIME >= TRUNC(PI_DATE)
         AND T.INSERT_TIME < TRUNC(PI_DATE) + 1
         AND Q.REQUEST_DATE >= TRUNC(PI_DATE)
         AND Q.REQUEST_DATE < TRUNC(PI_DATE) + 1 
         AND B.CDATE >= TRUNC(PI_DATE)
         AND B.CDATE < TRUNC(PI_DATE) + 1’

OPEN PO_EXPCUR FOR V_QUERY_SQL USING …

The SQL in the stored procedure, such as the above code, throws the exception that ora-01006: bound variable does not exist when calling. When viewing the SQL statement, make sure that all variables exist. But why is this exception thrown?

Finally, we find out the reason, because the SQL statement returns an integer value, but here we give this value to the cursor, so we throw this exception. Change the cursor related statements to the following SQL, and everything will be OK.

EXECUTE IMMEDIATE V_QUERY_SQL INTO PO_AMOUNT USING…

Our WinForm program is isomorphic to WCF and is connected to the server deployed on the server. Today, many WinForm users report that they have encountered problems when using WinForm and thrown an exception. The server has rejected the client credentials

Now let’s talk about the security authentication of WCF. Generally, our LAN applications do not have any security configuration, so what is the default security configuration of WCF?If no security configuration is made, WCF adopts windows security authentication. The default configuration is as follows:

<netTcpBinding>

  <binding name="netTcp">

    <security mode="Transport">

      <transport clientCredentialType="Windows" />

    </security>

  </binding>

</netTcpBinding>

After checking, it is found that all computers that throw exceptions have been disabled in the ad domain. WCF in the server because it is windows authentication, so found that the user has a problem, it threw this exception. When the users in the ad domain are restored, WCF will be normal.

It should be pointed out that there are other possible reasons for this exception. For example, when the WCF server needs to impersonate the client identity, it needs the client to provide the user name and password. If it does not, it will throw this exception.

In addition, if the security requirements are not so high, we can disable all security related settings. The configuration is as follows:

<bindings>

  <netTcpBinding>

    <binding name="customTcpBinding" maxReceivedMessageSize="20480000" transferMode="Streamed" >

      <security mode="None"></security>

    </binding>

  </netTcpBinding>

</bindings>

In this way, you will no longer carry out identity authentication, and you will never throw an exception similar to identity authentication. As long as the corresponding server IP and port can be accessed, you can call it normally.

https://stackoverflow.com/questions/53098405/wcf-throws-exception-that-the-server-has-rejected-the-client-credentials-what-i

https://stackoverflow.com/questions/8577612/the-server-has-rejected-the-client-credentials

Recently, I encountered the problem that the target code version of Maven sub module in IntelliJ idea has been rolled back. On the first day, in the idea setting, set the target code version of all sub modules to 1.8, and on the second day, roll back to 1.5, 1.6 and other versions.

The way to search online is to add the following plug-in information to Maven’s POM file:

<plugin>
		<groupId>org.apache.maven.plugins</groupId>
		<artifactId>maven-compiler-plugin</artifactId>
		<version>3.6.0</version>
		<configuration>
		    <encoding>UTF-8</encoding>
		    <source>1.8</source>
		<target>1.8</target>
		</configuration>
</plugin>

We did, but the problem still recurred. After several struggles, we finally found the existence of the problem.

In my project, the design of each sub module pom.xml In the file, either the parent information is not specified, or the parent of its own sub module pom.xml The target version is defined by myself, so the modification added in the parent POM can’t affect the setting of the sub module. The problem still exists.

Now, after sorting out the parent-child relationship of each module, this problem never appears again.

Procedures:

WinForm program (user management system) written by C #, the back-end WCF service provides data for it, and Oracle related operations are completed in WCF server.

Problems encountered: Recently, a user reported an exception, describing that a certain search interface of WinForm (to call WCF service to find data in Oracle) has been waiting. Someone in the DBA also reported that some Oracle sessions that call the stored procedures related to the user management system have been running for three hours, which has affected the Oracle database, and these processes need to be terminated manually.

Finally, the reasons are found

A large amount of data is suddenly inserted into the table of Oracle database, which makes the query slow. Then the user clicks the search button when searching in the WinForm interface, and the WCF service will be called to retrieve the database. However, the retrieval is very slow. After waiting for 3 minutes, the user doesn’t want to wait any longer, so he closes the interface directly. Notice that in the process,

Although WinForm forced the client to shut down during the connection with WCF, the server code of WCF was running half way and waiting for the result returned by Oracle. The WCF was not shut down and was still running. The connection with oracle was always connected.

Let’s talk about Oracle first. If C # calls one of its stored procedures, a connection or session will be established. Oracle has a mechanism to automatically clean up inactive sessions. Although it is not real-time, it has this mechanism. If the session between c#and Oracle is continuous, the session will not be cleaned up. If C # calls a time-consuming stored procedure, but C # exits in the middle of the way, the session will become inactive and will be cleaned up by Oracle at the right time.

Now the problem is that the user exits in the WinForm interface, but it is still executing in WCF, so the connection between WCF server and Oracle is always active session, so the stored procedure has been executing for three hours. At this time, the user opens a new WinForm interface and tries to query again. WCF will open a new session with Oracle, Until the available connection pool is used up (or the maximum number of connections that the Oracle server can accept at the same time), the WCF server and the Oracle session will be in the state of waiting for available connections, and then the WinForm interface will naturally be in the state of waiting for no response.

Solution:

Add the timeout attribute to WCF’s code to connect to Oracle. When the code is executed for more than 5 minutes, the connection will be automatically disconnected. After that, the inactive session in Oracle will be cleared, and the connection pool of C # WCF server will be enough, and there will be no waiting state.

Reasons for slow stored procedures and queries in databases:

This table has a large amount of data, and a large amount of data has just come in. Oracle sometimes encounters this problem. The same query statement was executed very slowly last time, and it may be completed very soon next time (I don’t know much about this DBA category). If a statement occupies the table for three hours, then other people’s session is also very slow to execute the query statement (maybe other people’s session is lucky and should have been right soon). Now use timeout to end this session, and other sessions will execute query statements, which may be faster.

https://stackoverflow.com/questions/12660636/oraclecommand-timeout?utm_ medium=organic&utm_ source=google_ rich_ qa&utm_ campaign=google_ rich_ qa  comand tiem out

https://forums.devart.com/viewtopic.php?t=25872

Recently, we are using C # code to connect Oracle database, which is divided into two parts, client and server of WCF. There is no problem in the startup and running of the program, and there is no problem in the running after it is deployed to the server. But when you visit again the next day, you will throw the exception shown below. What’s going on?

Oracle.DataAccess.Client.OracleException ORA-03135: connection lost contact
	Process ID: 22574
	Session ID: 799 Serial number: 43225    Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
	   at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
	   at Oracle.DataAccess.Client.OracleDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
	   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
	

The relevant code used to connect Oracle database is also very typical, without any complexity.

public DataSet ExcuteDataSetForOralce(string sql, CommandType type, List<OracleParameter> paras)
        {
            DataSet ds = new DataSet();
            OracleCommand cmd = null;
            try
            {
                using (OracleConnection conn = new OracleConnection(ConnectionString))
                {
                    using (cmd = new OracleCommand(sql, conn))
                    {
                        if (paras != null)
                        {
                            foreach (OracleParameter p in paras)
                            {
                                cmd.Parameters.Add(p);
                            }
                        }
                        cmd.CommandType = type;
                        OracleDataAdapter oda = new OracleDataAdapter();
                        oda.SelectCommand = cmd;
                        conn.Open();
                        oda.Fill(ds, "tempTable");
                        conn.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                log.Error("error", ex, LogType.Error);
            }
            return ds;
        }

Through multi-party search, it is found that this is not a code problem. Moreover, the problem of the connection between the provider and the database. In fact, when we create a new connection, we get the connection from the database connection pool. These connections are placed in the. Net provider. Conn.Close () does not close the connection, but returns it to the connection pool.

Now the problem is Oracle provider, not code. Your program hasn’t interacted with the database for a long time, and the database server turned off the open state of the connection, but the provider didn’t handle it in time, and still gave it to the C # code, so this exception was thrown.

The solution is to directly connect to the database without using the connection pool. Although this problem will not occur, there will be performance loss.

stay app.config In the configuration file of, in the string datasource = ((description…), add do not use connection pool.

Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx)(PORT=1521)))(CONNECT_DATA =(SERVICE_NAME=xxx)(SERVER = DEDICATED)));User ID=xxx;password=xxxxx;Pooling=false;

http://www.csdn.net/topics/3920225524