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!

Trace on Standby: Tracing MRP Process

Hey all!
Recently I got the following error when trying to start a trace in a standby. The idea was to start a trace in MRP to check on performance, but when trying to use DBMS_MONITOR.session_trace_enable:

oracle:standby-srvr /tmp: sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 19 14:07:56 2018

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> exec DBMS_MONITOR.session_trace_enable(session_id =>1970, serial_num=>55126, waits=>TRUE, binds=>TRUE);
BEGIN DBMS_MONITOR.session_trace_enable(session_id =>1970, serial_num=>55126, waits=>TRUE, binds=>TRUE); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_MONITOR.SESSION_TRACE_ENABLE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

OMG!
Don’t panic, you can accomplish the same using oradebug:

-- In my case:
SQL> select process,pid from V$managed_standby where process like '%MRP%';

PROCESS PID
--------- ------------------------
MRP0 45693

SQL> oradebug setospid 19564
Oracle pid: 105, Unix process pid: 19564, image: oracle@standby-srvr (MRP0)
SQL> oradebug unlimit
Statement processed.
SQL> oradebug Event 10046 trace name context forever, level 12
Statement processed.
SQL>

You can apply same for any process/sid you need.

In case you desire to get the spid from a sid:

col machine format a30
col process format 999999
select p.spid,b.sid, p.pid
from v$session b, v$process p
where b.paddr=p.addr
and sid=&sid
/

Ok, and how to stop then?
Easy:

SQL> oradebug setospid 19564
SQL> oradebug Event 10046 trace name context off
SQL> oradebug tracefile_name

Enjoy!

Am I in RHEL or OEL?

OK, first time accessing a client, and you have this question?

# RHEL:

[grepora-rhel-server]$ rpm -qf /etc/redhat-release 
redhat-release-server-6Server-6.9.0.4.el6.x86_64

# OEL:

[grepora-oel-server]$ rpm -qf /etc/redhat-release
enterprise-release-5-0.0.2

You may also have some other options like:

[grepora-fedora-server]$ rpm -qf /etc/redhat-release 
fedora-release-20-3.noarch

Hope you enjoy it!
Cheers!

1º GUORS Meeting 2018 – Confirmed!

E aí galera!
Dia 28/03/2017 teremos o primeiro encontro 2018 do GUORS acontecendo em Porto Alegre/RS.

Além de encontrar conhecidos e colegas de profissão e conhecer alguns novos, teremos algumas sessões direcionadas. Uma delas comigo, estarei fazendo um 101 sobre o Oracle Autonomous Database.

image001

Data: 28/03 (Quarta-feira)
Horário: 13:15-18h
Local: PUCRS – TecnoPUC: Av. Ipiranga, 6681 – Prédio 97 – Sala 101
Clique aqui e faça sua inscrição!

Confere a agenda completa logo abaixo e fica ligado!

Início Término Programação
13:15 13:45 Credenciamento – Público Externo
13:45 14:00 Abertura – Coordenação do Grupo de Usuários Oracle
14:00 15:00 O novo DBA, change or die! – Felipe Rasche – Oracle
15:00 16:00 EBS Upgrade 12.2 – Lessons learned from DBA Team – Gustavo Tourinho – DELL
16:00 16:15 Break
16:15 17:15 Oracle Autonomous Database Cloud 101 – Matheus Boesing – Pythian
17:15 17:30 Encerramento

 

Nos encontramos lá, abraço!

guors-e1515795949364

RAC: ORA-01265: Unable to delete ARCHIVED LOG

Weird?
Found that some time ago. This error was reported because a run condition given by the 2 instances was trying to delete the same archivelog file. This can vary to more than 2 instances, depending on your RAC configuration, of course.

This is a quick way to identify if you are in presence of the same situation and ignore the error, using the sequence number reported on the error (SEQ) and the last line number reported by grep (LINE):

# Node1:

grep -n SEQ /oracle/greporadb/diag/rdbms/greporadb/greporadb2/trace/alert_greporadb1.log | tail
tail -n +(LINE-10) oracle/greporadb/diag/rdbms/greporadb/greporadb2/trace/alert_greporadb1.log| head

# Node2:

grep -n SEQ /oracle/greporadb/diag/rdbms/greporadb/greporadb2/trace/alert_greporadb2.log | tail
tail -n +(LINE-10) oracle/greporadb/diag/rdbms/greporadb/greporadb2/trace/alert_greporadb2.log| head

The result should be the archivelog sequence deleted on one of the nodes and the error reported on the other, at the same timestamp.

Hope it helps!

ERROR OGG-03533: Conversion from character set {???} of source column {???}

Hi.

If you need to replicate data using Goldengate, between different databases types, you may get the following error.

” ERROR OGG-03533: Conversion from character set {???} of source column {???} to character set {???} of target column {???} failed because the source column contains a character ‘{?}’ at offset {?}  that is not available in the target character set. “

To replace charset not accepted on the target, try using the replication parameter: REPLACEBADCHAR.

In my case, I set up a data replication between MS-SQLServer and ORACLE, this bases uses charset windows-1252 and US-ASCII respectively.

” ERROR OGG-03533 Conversion from character set windows-1252 of source column CATEGORY to character set US-ASCII of target column CATEGORY failed because the source column contains a character ‘d3’ at offset 2 that is not available in the target character set. “

I am using the following parameter in replicat:
REPLACEBADCHAR ESCAPE
Replication works and no data loss.

See you!

Guru99: PL/SQL 101 Completely Free!

Hey all!
Just passing by today to share something I found really inetresting, and the best: it’s all free!
Do you know Guru99?

It’s a preety nice repository with lots of free tutorials and guides for PLSQL, SQL and several other languages, plus some other IT-related knowledge, like Big Data, PMP and etc.

It’s not rare to have people asking me “How to Start learning SQL” or saying “All material (including grepora :D) is too much specific, we don’t have good general documentation”.
Even Oracle having a pretty nice documentation on the products and features, general concepts are usually forgotten. Aaaaand, now I have something to link on those cases! 😀

Having a look on Guru99, First I found this guide PL/SQL First Program: Hello World, including detailed steps on:
– How to access SQL* Plus
– Connecting to Database
– How to write a simple program using PL/SQL
– Declaring and usage of variables in program
– Commenting Codes

Then found an entire category PL/SQL Tutorials. Talking to Alex (Editor), seems over 120 hours were spent on it. And they were well spent, the material is really very easy to read and understand. Chapters on this Course are:
1. Introduction to PL/SQL
2. Blocks in PL/SQL
3. PL SQL First Program: Hello World
4. PL/SQL Data Types
5. Complex Data Types in PL/SQL
6. PL/SQL Decision Making Statements
7. Loops in PL/SQL
8. Subprograms: Procedures and Functions in PL/SQL
9. Exception Handling in PL/SQL
10. SQL in PL/SQL
11. Packages in PL/SQL
12. Triggers in PL/SQL
13. Object Types in PL/SQL
14. Dynamic SQL in PL/SQL
15. Nested Blocks in Pl/Sql
16. PL/SQL Identifiers

Continue reading