search

yajra / laravel-oci8

Oracle DB driver for Laravel via OCI8
https://yajrabox.com/docs/laravel-oci8
MIT License
832 stars 237 forks source link

ORA-02248: invalid option for ALTER SESSION Position #499

Closed enextiarg closed 5 years ago

enextiarg commented 5 years ago

Summary of problem or feature request

We receive the following error when try to connect to oracle server:

Error Code : 2248 Error Message : ORA-02248: invalid option for ALTER SESSION Position : 231 Statement : ALTER SESSION SET NLS_TIME_FORMAT = 'HH24:MI:SS' NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS TZH:TZM' NLS_NUMERIC_CHARACTERS = '.,' EDITION = Bindings : [] (SQL: ALTER SESSION SET NLS_TIME_FORMAT = 'HH24:MI:SS' NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS TZH:TZM' NLS_NUMERIC_CHARACTERS = '.,' EDITION = )

Code snippet of problem

I just comment this line at the moment to fix temporarily 


/vendor/yajra/laravel-oci8/src/Oci8/Oci8ServiceProvider.php
line 81 : //$db->setSessionVars($sessionVars);

System details

xxb1s commented 5 years ago

I have the same problem, my oracle version is 10.2

enextiarg commented 5 years ago

I have commented these lines in Oci8ServicePeovider until resolve this issue.

'NLS_TIME_FORMAT'. => 'HH24:MI:SS', 'NLS_DATE_FORMAT' => 'YYYY-MM-DD HH24:MI:SS', 'NLS_TIMESTAMP_FORMAT' => 'YYYY-MM-DD HH24:MI:SS', 'NLS_TIMESTAMP_TZ_FORMAT' => 'YYYY-MM-DD HH24:MI:SS TZH:TZM', 'NLS_NUMERIC_CHARACTERS' => '.,',

Please let me know if it works.

El mar., 2 de abr. de 2019 8:06 PM, Brighton Saldaña < notifications@github.com> escribió:

I have the same problem, my oracle version is 10.2

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/yajra/laravel-oci8/issues/499#issuecomment-479246202, or mute the thread https://github.com/notifications/unsubscribe-auth/AEfrBthuNhV7JJacekTC6wrUOqLYntcBks5vc-KEgaJpZM4cFKG1 .

xxb1s commented 5 years ago

Yes, I comment on line 81 but it's a vendor file, so I created new class in my app directory that extends of Yajra\Oci8\Oci8ServiceProvider and overrides the register function, later I change the array providers of config/app.php and replace Oci8ServiceProvider with my class

El jue., 4 de abr. de 2019 a la(s) 11:25, Manuel Romero ( notifications@github.com) escribió:

I have commented these lines in Oci8ServicePeovider until resolve this issue.

'NLS_TIME_FORMAT'. => 'HH24:MI:SS', 'NLS_DATE_FORMAT' => 'YYYY-MM-DD HH24:MI:SS', 'NLS_TIMESTAMP_FORMAT' => 'YYYY-MM-DD HH24:MI:SS', 'NLS_TIMESTAMP_TZ_FORMAT' => 'YYYY-MM-DD HH24:MI:SS TZH:TZM', 'NLS_NUMERIC_CHARACTERS' => '.,',

Please let me know if it works.

El mar., 2 de abr. de 2019 8:06 PM, Brighton Saldaña < notifications@github.com> escribió:

I have the same problem, my oracle version is 10.2

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <https://github.com/yajra/laravel-oci8/issues/499#issuecomment-479246202 , or mute the thread < https://github.com/notifications/unsubscribe-auth/AEfrBthuNhV7JJacekTC6wrUOqLYntcBks5vc-KEgaJpZM4cFKG1

.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/yajra/laravel-oci8/issues/499#issuecomment-479989445, or mute the thread https://github.com/notifications/unsubscribe-auth/AV4zjzu7YKWWiidtW4NwrP21p6jFsuUiks5vdjWegaJpZM4cFKG1 .

enextiarg commented 5 years ago

Excellent... better solution at moment

El jue., 4 abr. 2019 a las 15:12, Brighton Saldaña (< notifications@github.com>) escribió:

Yes, I comment on line 81 but it's a vendor file, so I created new class in my app directory that extends of Yajra\Oci8\Oci8ServiceProvider and overrides the register function, later I change the array providers of config/app.php and replace Oci8ServiceProvider with my class

El jue., 4 de abr. de 2019 a la(s) 11:25, Manuel Romero ( notifications@github.com) escribió:

I have commented these lines in Oci8ServicePeovider until resolve this issue.

'NLS_TIME_FORMAT'. => 'HH24:MI:SS', 'NLS_DATE_FORMAT' => 'YYYY-MM-DD HH24:MI:SS', 'NLS_TIMESTAMP_FORMAT' => 'YYYY-MM-DD HH24:MI:SS', 'NLS_TIMESTAMP_TZ_FORMAT' => 'YYYY-MM-DD HH24:MI:SS TZH:TZM', 'NLS_NUMERIC_CHARACTERS' => '.,',

Please let me know if it works.

El mar., 2 de abr. de 2019 8:06 PM, Brighton Saldaña < notifications@github.com> escribió:

I have the same problem, my oracle version is 10.2

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub < https://github.com/yajra/laravel-oci8/issues/499#issuecomment-479246202 , or mute the thread <

https://github.com/notifications/unsubscribe-auth/AEfrBthuNhV7JJacekTC6wrUOqLYntcBks5vc-KEgaJpZM4cFKG1

.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub <https://github.com/yajra/laravel-oci8/issues/499#issuecomment-479989445 , or mute the thread < https://github.com/notifications/unsubscribe-auth/AV4zjzu7YKWWiidtW4NwrP21p6jFsuUiks5vdjWegaJpZM4cFKG1

.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/yajra/laravel-oci8/issues/499#issuecomment-480005917, or mute the thread https://github.com/notifications/unsubscribe-auth/AEfrBlNRkkIdMCJ1AWmWRUMKxJ2ugC-8ks5vdkB4gaJpZM4cFKG1 .

-- Manuel E. Romero

yajra commented 5 years ago

I think this might be an Oracle version specific issue. What I can suggest is you create your own Oci8ServiceProvider (duplicate the current file) and adjust the NLS settings as needed. Just note that you need to disable auto package discovery for the package inside composer.json.

rvgoncalves commented 5 years ago

Summary of problem or feature request

We receive the following error when try to connect to oracle server:

Error Code : 2248 Error Message : ORA-02248: invalid option for ALTER SESSION Position : 231 Statement : ALTER SESSION SET NLS_TIME_FORMAT = 'HH24:MI:SS' NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS TZH:TZM' NLS_NUMERIC_CHARACTERS = '.,' EDITION = Bindings : [] (SQL: ALTER SESSION SET NLS_TIME_FORMAT = 'HH24:MI:SS' NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS TZH:TZM' NLS_NUMERIC_CHARACTERS = '.,' EDITION = )

Code snippet of problem

I just comment this line at the moment to fix temporarily


/vendor/yajra/laravel-oci8/src/Oci8/Oci8ServiceProvider.php
line 81 : //$db->setSessionVars($sessionVars);

System details

  • Operating System Mac Mojave and Debian 9
  • PHP Version: 7.3
  • Laravel Version: 5.6
  • Laravel-OCI8 Version: 5.6

You are trying set EDITION in your Statement :

ALTER SESSION SET NLS_TIME_FORMAT = 'HH24:MI:SS' NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS TZH:TZM' NLS_NUMERIC_CHARACTERS = '.,' EDITION = Bindings : [] (SQL: ALTER SESSION SET NLS_TIME_FORMAT = 'HH24:MI:SS' NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS TZH:TZM' NLS_NUMERIC_CHARACTERS = '.,' EDITION = )

And I think Oracle 10g does not have this session parameter. Check it: SELECT * FROM nls_session_parameters

enextiarg commented 5 years ago

Maybe, the provider could get the params from the config file oracle.php. So anyone could set the params as need

El jue., 4 de abr. de 2019 11:13 PM, Arjay Angeles notifications@github.com escribió:

I think this might be an Oracle version specific issue. What I can suggest is you create your own Oci8ServiceProvider (duplicate the current file) and adjust the NLS settings as needed. Just note that you need to disable auto package discovery for the package inside composer.json.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/yajra/laravel-oci8/issues/499#issuecomment-480125500, or mute the thread https://github.com/notifications/unsubscribe-auth/AEfrBiSjSMnA3LFNYJCdhEZXYXPVHgkdks5vdrFZgaJpZM4cFKG1 .

yajra commented 5 years ago

@rvgoncalves thanks for pointing this out. edition is conditionally loaded. Maybe making sure you have no edition value on config would fix the issue?

            if (isset($config['edition'])) {
                $sessionVars = array_merge(
                    $sessionVars,
                    ['EDITION' => $config['edition']]
                );
            }
rvgoncalves commented 5 years ago

@yajra fixed!

enextiarg commented 5 years ago

I fixed this adding 'session' => [] in oracle driver settings

El lun., 8 abr. 2019 a las 10:01, RODOLFO GONÇALVES (< notifications@github.com>) escribió:

@yajra https://github.com/yajra fixed!

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/yajra/laravel-oci8/issues/499#issuecomment-480822033, or mute the thread https://github.com/notifications/unsubscribe-auth/AEfrBq5B9uSC2w1sNvyx7av22fcrwZRMks5vez2_gaJpZM4cFKG1 .

-- Manuel E. Romero

fathalfath30 commented 5 years ago

i fixed by adding : /*'edition' => env('DB_EDITION', 'ora$base'),*/ (Oracle 9i)

return [
    'oracle' => [
        'driver'         => 'oracle',
        'tns'            => env('DB_TNS', ''),
        'host'           => env('DB_HOST', ''),
        'port'           => env('DB_PORT', '1521'),
        'database'       => env('DB_DATABASE', ''),
        'username'       => env('DB_USERNAME', ''),
        'password'       => env('DB_PASSWORD', ''),
        'charset'        => env('DB_CHARSET', 'AL32UTF8'),
        'prefix'         => env('DB_PREFIX', ''),
        'prefix_schema'  => env('DB_SCHEMA_PREFIX', ''),
       /* 'edition'        => env('DB_EDITION', 'ora$base'),*/
        'server_version' => env('DB_SERVER_VERSION', '11g'),
    ],
]
HeartlandTechie commented 5 years ago

I'm on Oracle 12g, Laravel 5.7.13, and PHP 7.2.17 and getting this same error. Commenting out the 

/vendor/yajra/laravel-oci8/src/Oci8/Oci8ServiceProvider.php
line 81 : //$db->setSessionVars($sessionVars);

fixed mine - but I'm not good with that . . . what is the right way to fix this?