Category Archives: web development

[django] Could not resolve URL for hyperlinked relationship using view name “-detail” [SOLVED]

When trying to build my first rest-django app, i faced below error message, which was not easy to solve: Could not resolve URL for hyperlinked relationship using view name: “<model>-detail”. You may have failed to include the related model in your API, or incorrectly configured the `lookup_field` attribute on this field. For simplicity reason, below example is based on django-rest-framework tutorial, where project is named “tutorial” and one app called “snippets” is created inside this project After investigation, the following happens: in snippets/urls.py, ensure to use “name=” attribute as per below, and use the convention of ‘<model>-detail’ for detail view […]

how to setup cx_Oracle on python – windows [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 […]

[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

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

[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 […]

[oracle] 11g – export data with expdp

Approach is as follow: create directory object inside oracle grant export rights perform export step by step create physical directory on the drive where dump will be created: sudo mkdir /u01/app/oracle/exported_dbs sudo chown oracle:dba /u01/app/oracle/exported_dbs login as system admin into sqlplus: $ORACLE_HOME/bin/sqlplus sys/<password> as sysdba create oracle directory object: create directory db_dumps  as ‘/u01/app/oracle/exported_dbs’; grant read,write on directory db_dumps to <dbuser>; grant export rights to db user: grant create any directory to <dbuser>; grant exp_full_database to <dbuser>; generate dump file: $ORACLE_HOME/bin/expdp <dbuser>/<password> schemas=<db_schema_to_export> directory=db_dumps dumpfile=db_dumpfile.dmp logfile=expdp_dbdump.log note: replace values between <xxx> with your own values source http://el.web.id/how-to-export-and-import-oracle-database-using-impdp-and-expdp-in-linux-212 (awesome tutorial!) http://docs.oracle.com/cd/B28359_01/server.111/b28319/dp_export.htm#i1009049 […]

[oracle] create new data file

To create a new data file in oracle 11g is pretty easy. Steps are: login as sysdba (you can use sqldeveloper or sqlplus, user sys/<password>@<SID> as SYSDBA) execute below statements, replacing values as per requirement on your side (this is creating auto extendable db files): — create a user tablespace to be assigned as the default tablespace for users CREATE TABLESPACE rddbfile LOGGING DATAFILE ‘/u01/app/oracle/oradata/rddbfile_data.dbf’ SIZE 25M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL; — create a tablespace for indexes, separate from user tablespace CREATE TABLESPACE indx LOGGING DATAFILE ‘/u01/app/oracle/oradata/rddbfile_idx.dbf’ SIZE 25M REUSE AUTOEXTEND ON NEXT  1280K […]

which version of oracle db am i using?

Simply run below query to identify which version you are currently running: SELECT * FROM PRODUCT_COMPONENT_VERSION; sources http://docs.oracle.com/cd/B28359_01/server.111/b28310/dba004.htm#ADMIN11039