Application Named Variables Returning ORA-01008: not all variables bound

Hi all,
I saw it a long time ago and last week I was involved in a discussion for this same error. So, this deserve a post for future reference. 🙂

Imagine the situation: You are building an application or a module to perform queries on database. You want to use variables in the query and fill the values using text fields on application. Sounds easy and works fine for SQLServer and others, but Oracle database is returning:

ORA-01008: not all variables bound

What to do?

Fist let’s clear the issue: this is not related to database layer or oracle interpreter/parser. This error happens when a bind variable being used on SQL have no value. The official reasoning is:

Cause: A SQL statement containing substitution variables was executed without all variables bound. All substitution variables must have a substituted value before the SQL statement is executed.

In my case ODP.Net with C# (but apply to other languages). Interesting fact:
“ODP.Net provider from oracle uses bind by position as default. To change the behavior to bind by name, set property BindByName to true.”

This means Oracle may be waiting for “:1”, “:2” as bind variables and this can also not being set correctly by application.
In this case, please try to set BindByName to true in application code for Oracle command. Example below:

using(OracleCommand cmd = con.CreateCommand()) {
    ...
    cmd.BindByName = true;
    ...
}

Now, try again. 🙂

Hope this helps you!
Cheers!

ORA-29278 When Trying to Use SMTP with GMail

Hi all,
Some time ago I got reached by a client with this error to send SMTP emails using GMail service with a wallet.
This is a generic error, in general the string identified the error returned from email service layer.

# Generic:

ORA-29278: SMTP transient error: string

# Error I was facing:

ORA-29278: SMTP transient error: 421 Service not available

Basically it was related to some intermittence on GMail’s service and got back to normal after a while. This is also well documented in this post by Hâvard Kristiansen.
However, I couldn’t use scripts there because of my wallet. Then I found the very useful commands below, that is what I want to share with you:

# Checking for SSL Certificate:

[oracle@grepora-srvr]$ openssl s_client -crlf -quiet -connect smtp.gmail.com:587 -starttls smtp
depth=3 C = US, O = Equifax, OU = Equifax Secure Certificate Authority
verify return:1
depth=2 C = US, O = GeoTrust Inc., CN = GeoTrust Global CA
verify return:1
depth=1 C = US, O = Google Inc, CN = Google Internet Authority G2
verify return:1
depth=0 C = US, ST = California, L = Mountain View, O = Google Inc, CN = smtp.gmail.com
verify return:1
250 SMTPUTF8
^C
(return:1 means "passed".)

# Testing SMTP on Database (code):

Continue reading

ORA-07445: exception encountered: core dump [nstimexp()+45] [SIGSEGV] [ADDR:0x58] [PC:0x7F42ABB] [Address not mapped to object] []

Hello all,
I had faced some occourrences of this error in a 11.2.0.1 database recently.

ORA-07445: exception encountered: core dump [nstimexp()+45] [SIGSEGV] [ADDR:0x58] [PC:0x7F42ABB] [Address not mapped to object] []

After some investigation I found a match to Bug 3934729.
This issue is originally to matched to Bug 6918493, that is a reintroduction of Bug 2752985 but it’s fixed in 11.2.0.1.
However, on upgrading to 11.2.0.1 it’s a hit on Bug 3934729 which is fixed in 11.2.0.2.

Recommended actions are:
– Upgrade databases do 11.2.0.2 or higher. (best solution, but may require more efforts to validate the upgrade).
– Apply Patch 3934729: RANDOM ORA-07445 CORE DUMPS FROM DATABASE AND ORA-3113 FROM APPLICATION
– Set sqlnet.expire_time=0 (workaround)
– Ignore error.

After some research I decided to apply workaround, based on recommended usage of sqlnet.expire_time (Next weeks post is about this parameter :)).
This might be the root cause for the ORA-03135: connection lost contact and the actual value of this parameter on environment was 1, which is a very low value.

So, check which action is more suitable for your environment!
Hope it helps 🙂

Below some additional informations on my situation:

Continue reading

ORA-00001: unique constraint (RMAN.CKP_U1) violated

Hey,
Don’t create so much expectations on this post.

This is because I don’t exactly fixed the issue, but workarounded…
The thing is: This error is caused in catalog database, so the workaround is simple: do a RMAN-nocatalog, I mean, simply don’t connect in catalog to perform the backup.

After completing the backup, I’d suggest you to force a synchronization with command “RESYNC CATALOG“. In worst case, on next execution the implicit resync will fix everything. 🙂

There is no bigger explanations on this, but you can same workaround in MOS Bug 12588237 – RMAN-3002 ORA-1: unique constraint (ckp_u1) violated after dataguard switchover (Doc ID 12588237.8).

And this is it for today!
See you next week!

Exadata: ORA-07445: exception encountered: core dump [ocl_lock_get_waitobj_owner()+26] [11] [0x000000000] [] [] []

Hello all,

This is because the error is generated by an unpublished bug 17891564, as per described in MOS ORA-7445 [ocl_lock_get_waitobj_owner] on an Exadata storage cell (Doc ID 1906366.1).

It affects Exadata storage cell with image version between 11.2.1.2.0 and 11.2.3.3.0. The CELLSRV process crash with this error as per:

Cellsrv encountered a fatal signal 11
Errors in file /opt/oracle/cell11.2.3.3.0_LINUX.X64_131014.1/log/diag/asm/cell//trace/svtrc_11711_27.trc  (incident=257):
ORA-07445: exception encountered: core dump [ocl_lock_get_waitobj_owner()+26] [11] [0x000000000] [] [] []
Incident details in: /opt/oracle/cell11.2.3.3.0_LINUX.X64_131014.1/log/diag/asm/cell//incident/incdir_257/svtrc_11711_27_i257.trc

The CELLSRV process should auto restart after this error.

Continue reading

x$kglob: ORA-02030: can only select from fixed tables/views

Hi all!
While selecting on x$kglob with DBA credentials hanging on:

SQL> select count(*) from sys.x$kglob;
ERROR at line 1:
ORA-00942: a tabela ou view não existe

But with sys it succeed. Ok, let’s grant privilege:

SQL> grant select on sys.x$kglob to dba;
grant select on sys.x$kglob to dba
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

What a hell! I couldn’t grant it any way!
So the MCGayver solution was:

Continue reading

ORA-01950 On Insert but not on Create Table

Sounds weird creating table does not raise any error, but inserting a correct tuple in this table raise a permission error, right? Just take a look:

SQL> create table matheusdba.table_test(a number) tablespace TEST_TABLESPACE;
Table created.
SQL> insert into matheusdba.table_test values (1);
insert into matheusdba.table_test values (1)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'TEST_TABLESPACE'

Continue reading

Package Body APEX_030200.WWV_FLOW_HELP Invalid after Oracle Text Installing

Hi all!
The package body APEX_030200.WWV_FLOW_HELP become invalid after Oracle Text installation with the follow errors:

Compilation errors for PACKAGE BODY APEX_030200.WWV_FLOW_HELP
#13#10Error: PL/SQL: ORA-00942: table or view does not exist
Line: 189
#13#10Error: PL/SQL: SQL Statement ignored
Line: 188
#13#10Error: PLS-00201: identifier 'CTX_DDL.DROP_PREFERENCE' must be declared
Line: 191
#13#10Error: PL/SQL: Statement ignored
Line: 191
#13#10Error: PL/SQL: ORA-00942: table or view does not exist
Line: 197
#13#10Error: PL/SQL: SQL Statement ignored
Line: 196
#13#10Error: PLS-00201: identifier 'CTX_DDL.DROP_PREFERENCE' must be declared
Line: 199
#13#10Error: PL/SQL: Statement ignored
Line: 199
#13#10Error: PLS-00201: identifier 'CTX_DDL.CREATE_PREFERENCE' must be declared
Line: 261
#13#10Error: PL/SQL: Statement ignored
Line: 261
#13#10Error: PLS-00201: identifier 'CTX_DDL.SET_ATTRIBUTE' must be declared
Line: 262
#13#10Error: PL/SQL: Statement ignored
Line: 262
#13#10Error: PLS-00201: identifier 'CTX_DDL.SET_ATTRIBUTE' must be declared
Line: 265
#13#10Error: PL/SQL: Statement ignored
Line: 265
#13#10Error: PLS-00201: identifier 'CTX_DDL.CREATE_PREFERENCE' must be declared
Line: 280
#13#10Error: PL/SQL: Statement ignored
Line: 280
#13#10Error: PLS-00201: identifier 'CTX_DOC.FILTER' must be declared
Line: 292
#13#10Error: PL/SQL: Statement ignored
Line: 292
#13#10Error: PLS-00201: identifier 'CTX_DOC.FILTER' must be declared
Line: 312
#13#10Error: PL/SQL: Statement ignored
Line: 312

It happens bassically because APEX schema has not been granted with execute privileges for CTX_DDL and CTX_DOC. The note below it’s exactly about it:
The WWV_FLOW_HELP PACKAGE Status is Invalid After Installing Oracle Text (Doc ID 1335521.1)

The solution is simple:

mydb> grant execute on ctx_ddl to APEX_030200;
Grant succeeded.
mydb> grant execute on ctx_doc to APEX_030200;
Grant succeeded.
mydb> alter package APEX_030200.WWV_FLOW_HELP compile;
Package altered.
mydb> alter package APEX_030200.WWV_FLOW_HELP compile body;
Package body altered.

Have a nice day!
Matheus.