Welcome Guest, Not a member yet? Register   Sign In
Oracle issues

First, good day to all!

Now, let's go to business. I'm trying to create a simple application with CI, with 2 databases, one in Oracle, and the other in PostgreSQL. I've managed to successfully connect and perform queries to both databases, thanks to what I've found in these two posts:


I've had issues with Oracle, returning an unnumbered error, then the errors listed in the first site, and finally errors with multiple queries. So the first question is: will all these changes apply to CI? Are they indeed correct? These changes worked fine for me. :lol:

Another thing, now about encoding. Both of the databases have LATIN1 encoding. The code I used for testing follows:

First, the Controller:

    class Noticia extends Controller {
        function Noticia() {
        function index() {
            $db = $this->load->database('seguranca', TRUE);
            $data['query'] = $db->get('unidades_federais');
            $data['query2'] = $db->get('s_pessoa');
            $this->load->view('noticia_view', $data);

Then, the view:

        <title>Componente de noticias</title>
            &lt;?php if($query->num_rows() > 0) : ?&gt;
                &lt;?php foreach($query->result() as $uf) : ?&gt;
                    <li>&lt;?= $uf->SIGLA ?&gt; - &lt;?= $uf->UNIDADE ?&gt;</li>
                &lt;?php endforeach; ?&gt;
            &lt;?php endif; ?&gt;
            &lt;?php if($query2->num_rows() > 0) : ?&gt;
                &lt;?php foreach($query2->result() as $pessoa) : ?&gt;
                    <li>&lt;?= $pessoa->NU_CPF ?&gt; - &lt;?= $pessoa->NO_PESSOA ?&gt;</li>
                &lt;?php endforeach; ?&gt;
            &lt;?php endif; ?&gt;

And finally, my database connection settings:

&lt;?php  if (!defined('BASEPATH')) exit('No direct script access allowed');
| -------------------------------------------------------------------
| -------------------------------------------------------------------
| This file will contain the settings needed to access your database.
| For complete instructions please consult the "Database Connection"
| page of the User Guide.
| -------------------------------------------------------------------
| -------------------------------------------------------------------
|    ['hostname'] The hostname of your database server.
|    ['username'] The username used to connect to the database
|    ['password'] The password used to connect to the database
|    ['database'] The name of the database you want to connect to
|    ['dbdriver'] The database type. ie: mysql.  Currently supported:
                 mysql, mysqli, postgre, odbc, mssql
|    ['dbprefix'] You can add an optional prefix, which will be added
|                 to the table name when using the  Active Record class
|    ['pconnect'] TRUE/FALSE - Whether to use a persistent connection
|    ['db_debug'] TRUE/FALSE - Whether database errors should be displayed.
|    ['cache_on'] TRUE/FALSE - Enables/disables query caching
|    ['cachedir'] The path to the folder where cache files should be stored
|    ['char_set'] The character set used in communicating with the database
|    ['dbcollat'] The character collation used in communicating with the database
| The $active_group variable lets you choose which connection group to
| make active.  By default there is only one group (the "default" group).
| The $active_record variables lets you determine whether or not to load
| the active record class

$active_group = "default";
$active_record = TRUE;

$db['default']['hostname'] = "";
$db['default']['username'] = "mmacorporativo";
$db['default']['password'] = "*********";
$db['default']['database'] = "adsi";
$db['default']['dbdriver'] = "postgre";
$db['default']['dbprefix'] = "";
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = "";
$db['default']['char_set'] = "latin1";
$db['default']['dbcollat'] = "latin1_general_ci";

$db['seguranca']['hostname'] = "//";
$db['seguranca']['username'] = "systemsecurity";
$db['seguranca']['password'] = "********";
$db['seguranca']['database'] = "mmadsv";
$db['seguranca']['dbdriver'] = "oci8";
$db['seguranca']['dbprefix'] = "";
$db['seguranca']['pconnect'] = TRUE;
$db['seguranca']['db_debug'] = TRUE;
$db['seguranca']['cache_on'] = FALSE;
$db['seguranca']['cachedir'] = "";
$db['seguranca']['char_set'] = "latin1";
$db['seguranca']['dbcollat'] = "latin1_general_ci";

Some results in my page are as follows:

Gerencia de Conservac?o da Biodiversidade (SBF/DCBIO)
Gerencia de Gest?o de Recursos Geneticos (SBF/DCBIO)
Gerencia de Gest?o de Recursos Pesqueiros (SBF/DCBIO)
SBF/DCBIO/PROBIO II - Projeto de Conservac?o e Uso Sustentavel da Biodiversidade e Ac?es Integradas, Publicas/Privadas para Biodiversidade (SBF/DCBIO)

It does not matter which browser encoding I choose, the ? characters still appear. Can you help me with this one?

Thanks to all, and happy coding!!

Igor Felix


i'm not sure what CI does with
$db['seguranca']['char_set'] = "latin1";
$db['seguranca']['dbcollat'] = "latin1_general_ci";
to set a proper Oracle NLS Environment.

Try setting NLS_LANG=.... at your apache startup script or try
putenv("NLS_LANG=...."); in your php code.

Which Oracle Version do you use ?
Which nls setting does the database have ? (select * from v$nls_parameters)

Thanks a lot!! =)

I've set NLS_LANG to


And everything worked just fine!!


God, I wish I could convince everyone here at work to use CI!! It's just like natural programming, much much better than the framework we use here...

Again, thanks!!

Igor Felix

Well I don't want to dig this up but :
both db_connect AND db_pconnect method of oci8_driver missed 4th parameter (charset) and if you are using Oracle 9.2+ you MUST set charset to something sensible so here is my 2 cents
edit CI_root/database/drivers/oci8/oci8_drive.php
around line 73 change this:
return @ocilogon($this->username, $this->password, $this->hostname);
to this
return @ocilogon($this->username, $this->password, $this->hostname, $this->char_set);
and the same for db_pconnect method

Now you can set your NLS lang under CI_root/config/database.php
something like this for utf8
$db['default']['char_set'] = "al32utf8";

Theme © iAndrew 2016 - Forum software by © MyBB