sqlite3

Create New database

In DOS,

sqlite3 test.db

Now we go to sqlite prompt

Help

sqlite> .help

List Tables

sqlite> .tables

Describe Table

sqlite> .schema TableName

For example,

sqlite> .schema SoundOption
CREATE TABLE SoundOption (isSoundOn int);

SELECT

sqlite> SELECT * FROM SoundOption;

UPDATE

sqlite> UPDATE SoundOption SET isSoundOn=1;

EXIT

sqlite> .exit

python connect oracle

Install

cx_Oracle binary install for windows

Example code

import cx_Oracle
conn_str = u'user/password@host:port/service'
conn = cx_Oracle.connect(conn_str)
c = conn.cursor()
c.execute(u'select your_col_1, your_col_2 from your_table')
for row in c:
   print row[0], "-", row[1]
conn.close()

To set encoding

 import os
os.environ["NLS_LANG"] = "AMERICAN_AMERICA.AL32UTF8"

The database encoding can be found using the below query

SELECT USERENV ('language') FROM DUAL

ref : oracleDecimusXIV

[ oracle ] export sql oracle command line

use DBMS_METADATA package

Syntax

DBMS_METADATA.GET_DDL (
object_type     IN VARCHAR2,
name            IN VARCHAR2,
schema          IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
model           IN VARCHAR2 DEFAULT 'ORACLE',
transform       IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;

object_type

 ASSOCIATION
 AUDIT
 AUDIT_OBJ
 CLUSTER
 COMMENT
 CONSTRAINT
 CONTEXT
 DB_LINK
 DEFAULT_ROLE
 DIMENSION
 DIRECTORY
 FUNCTION
 INDEX
 INDEXTYPE
JAVA_SOURCE
LIBRARY
MATERIALIZED_VIEW
MATERIALIZED_VIEW_LOG
OBJECT_GRANT
OPERATOR
OUTLINE
PACKAGE
PACKAGE_SPEC
PACKAGE_BODY
PROCEDURE
PROFILE
PROXY
REF_CONSTRAINT
ROLE
ROLE_GRANT
ROLLBACK_SEGMENT
SEQUENCE
SYNONYM
SYSTEM_GRANT
TABLE
TABLESPACE
TABLESPACE_QUOTA
TRIGGER
TRUSTED_DB_LINK
TYPE
TYPE_SPEC
TYPE_BODY
USER
VIEW 

NOTE:

– PACKAGE will export both PACKAGE_SPEC and PACKAGE_BODY

– TYPE will export both TYPE_SPEC and TYPE_BODY

– to exclude the table detail, i.e. tablespace storage, etc., use the below anonymous block

BEGIN
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',FALSE);
END;

– to extract w/o schema, use the add the below command to the anonymous block

 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'EMIT_SCHEMA', false);

ref : oracle, yanfeng

[ oracle ] not a valid month

select * from nls_session_parameters;

NLS_LANGUAGE    AMERICAN
NLS_TERRITORY    AMERICA
NLS_CURRENCY    $
NLS_ISO_CURRENCY    AMERICA
NLS_NUMERIC_CHARACTERS    .,
NLS_CALENDAR    GREGORIAN
NLS_DATE_FORMAT    DD-MON-RR
NLS_DATE_LANGUAGE    AMERICAN
NLS_SORT    BINARY
NLS_TIME_FORMAT    HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT    HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT    DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY    $
NLS_COMP    BINARY
NLS_LENGTH_SEMANTICS    BYTE
NLS_NCHAR_CONV_EXCP    FALSE

Since my import date format is `5/26/2013`, I have to change the nls_date_format.

alter session set nls_date_format='MM-DD-YY';