POP Keywords: apache oracle sybase ...

how to setup cx_Oracle on python [solved]

When trying to use cx_Oracle in python on windows, I faced following error: >>> import cx_Oracle Traceback (most recent call last): File “<stdin>”, line 1, in <module> ImportError: DLL load failed: The specified module could not be found. In my case, oracle instant client was not installed. Here is how to solve it. Steps Download and unzip oracle instant client appropriate for your configuration: http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html note: on my side i downloaded basic client + odbc + jdbc + sqlplus Ensure environment variables ORACLE_HOME and PATH points to instant client folder note: in windows, go to control panel > System > […]

oracle – lead/lag functions returning inconsistent results [solved]

When using oracle lead() and lag() functions, I faced “inconsistent” results where running same query twice was returning different values for lead()/lag() calls. select reference_id, field1, lead(field1 1, 0) over (partition by col1, col2 order by STATUS_TIME_LONG)  next_val; This simply came from fact that my sorting key was not unique! Though STATUS_TIME_LONG recoreded entry timestamp in milliseconds, several entries within my grouping still shared same timestamp value for this field. Solution to get consistent output for lead/lag functions is simply to ensure unique-id field is part of sorting key. In my case, field reference_id is unique auto-incrementing field, so  perfect for […]

python logging – KeyError: ‘fileHandler’ [solved]

I tried to use python native logging mechanism, with logging config loaded from external .conf file via logging.config module. I got hard time finding out what was causing below error: File “/usr/lib/python3.4/logging/config.py”, line 253, in _install_loggers logger.addHandler(handlers[hand]) KeyError: ‘fileHandler’ Top of my configuration file was as per below, declaring 2 loggers, 1 handler and 1 formatter: [loggers] keys=root,defaultLogger [handlers] keys=consoleHandler [formatters] keys=simpleFormatter Body of my configuration file contained the following: [handler_fileHandler] class=FileHandler level=DEBUG formatter=simpleFormatter args=(‘log/NPID[NPID].spo.log’, ‘a’, ‘UTF-8′, False) [handler_consoleHandler] class=StreamHandler level=DEBUG formatter=simpleFormatter args=(sys.stdout,) In other words, I am defining 2 handlers, but only declared 1 in the top part. That […]

[oracle] steps to connect to oracle 11g via sqlplus

Steps: ensure variable ORACLE_HOME points to oracle installation path: echo $ORACLE_HOME /u01/app/oracle/product/11.2.0/xe if not set, set variable to appropriate path via: export ORACLE_HOME=<path_to_oracle> ensure variable ORACLE_SID is set up to correct value (XE in this case): echo $ORACLE_SID XE if not set, set variable to appropriate path via: export ORACLE_HOME=<path_to_oracle> connect as admin user via: $ORACLE_HOME/bin/sqlplus sys as sysadmin <enter password> That should be it. Note: to permanently add env variables, simply add them to ~/.bashrc as per below: export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe export ORACLE_OWNR=oracle export PATH=$PATH:$ORACLE_HOME/bin export ORACLE_SID=XE Upon new login, such variable will be loaded automatically

install .whl files for python

Simply run pip install <path_to_whl_file>.whl Easy! On windows, pip.exe is under “<python_installation_folder>/Scripts”  

oracle – convert date to unix timestamp and vice versa

Convert date to unix timestamp (milliseconds): (what a pain!!! incredible there is no native oracle function in 11g) select extract(day from (systimestamp – timestamp ‘1970-01-01 00:00:00′)) * 86400000 + extract(hour from (systimestamp – timestamp ‘1970-01-01 00:00:00′)) * 3600000 + extract(minute from (systimestamp – timestamp ‘1970-01-01 00:00:00′)) * 60000 + extract(second from (systimestamp – timestamp ‘1970-01-01 00:00:00′)) * 1000 unix_time from dual; Warning: this function does not take into account timezone delay Convert unix timestamp (milliseconds)  to date: select TO_DATE(‘1970-01-01′, ‘YYYY-MM-DD’) + <unix_timestamp> / 86400000 from dual; sources http://stackoverflow.com/questions/7147982/oracle-systimestamp-sysdate-to-milliseconds-seconds http://dumplings-blog.logdown.com/posts/178885-oracle-sql-convert-a-datetime-string-into-a-unix-timestamp https://blogs.oracle.com/mock/entry/converting_oracle_dates_to_unix http://dba.stackexchange.com/questions/16461/convert-a-unix-timestamp-to-a-datetime-in-a-view

oracle – “DUAL” table

In oracle, there is a special table called “DUAL” which contains only one column and one row. It’s basically used to return queries with single select statement to return current system date for instance, as per below: select sysdate from dual; Should you run “select * from dual”, output is single row, single column called DUMMY with value X. sources http://stackoverflow.com/questions/73751/what-is-the-dual-table-in-oracle http://en.wikipedia.org/wiki/DUAL_table http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries009.htm

git – enable push over http [solved]

What a pain it was to configure git to enable push over http!! Error messages I continuously faced following error: error: Cannot access URL http://my_hostname/git/my_repository.git/, return code 22 and then error: fatal: unable to access ‘http://my_hostname/git/my_repository.git/': The requested URL returned error: 403 and by looking at apache log, underlying error msg was: AH01630: client denied by server configuration: /usr/lib/git-core/git-http-backend Steps to configure git with push over http: let’s assume all your repository are located under /home/myuser/projects/git_repos/project1.git note: project1.git is a bare git repository go to your project folder and run: git update-server-info git config http.receivepack true grant ownership of folder […]

git clone using http – repository not found [solved]

When trying to clone a git repository on my server using http protocol, following error was thrown though I could clearly access .git folder via any web browser. fatal: repository ‘http://<hostname>/path/to/project/folder/’ not found Steps to correct this error: log into your git remote server navigate to your project folder run git update-server-info On client side, when running git clone instruction, type in path up to .git folder. That means in case repository was not create as bare repository, run: git clone http://<hostname>/path/to/project/folder/.git This time it should be successful. note: it is recommended to create a bare git repository, instead of […]

[oracle] 11g – import data with impdp

Approach is as follow: create directory object inside oracle grant import rights perform import step by step login as system admin into sqlplus: $ORACLE_HOME/bin/sqlplus sys/<password> as sysdba create oracle directory object pointing to folder where dump is located: create directory db_dumps as ‘/u01/app/oracle/dbs_to_load'; grant read,write on directory db_dumps to <dbuser>; grant import rights to db user: grant create any directory to <dbuser>; grant imp_full_database to <dbuser>; import dump file: $ORACLE_HOME/bin/impdp <dbuser>/<password> schemas=<db_schema_to_export> directory=db_dumps dumpfile=db_dumpfile.dmp logfile=expdp_dbdump.log remap_schema=<old_schema>:<new_schema> remap_tablespace=<old_schema>:<new_schema> note: replace values between <xxx> with your own values Importing with data filtering Data filtering can be added using QUERY=<table_name>:”<where clause>” When using […]