[ Microsoft Dynamic AX ] Server not Available

We have to start Service `Microsoft Dynamic AX Object Server 6.2$01-MicrosoftDynamicAX`

 

 

Advertisements

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

execute from bash

$ echo "select * from adstxt;" | sqlite3 adstxt.db
$echo "delete from adstxt;" | sqlite3 adstxt.db

Import sql file

sqlite3 adstxt.db < adstxt_crawler.sql

adstxt_crawler.sql

BEGIN TRANSACTION;
DROP TABLE IF EXISTS adstxt;

CREATE TABLE adstxt(
       SITE_DOMAIN                  TEXT    NOT NULL,
       EXCHANGE_DOMAIN              TEXT    NOT NULL,
       ADSYSTEM_DOMAIN		    INTEGER     NOT NULL,
       SELLER_ACCOUNT_ID            TEXT    NOT NULL,
       ACCOUNT_TYPE                 TEXT    NOT NULL,
       TAG_ID                       TEXT    NOT NULL,
       ENTRY_COMMENT                TEXT    NOT NULL,
       UPDATED                      DATE    DEFAULT (datetime('now','localtime')),
    PRIMARY KEY (SITE_DOMAIN,EXCHANGE_DOMAIN,SELLER_ACCOUNT_ID)
);

-- Contribution by Ian Trider
DROP TABLE IF EXISTS adsystem_domain;

CREATE TABLE "adsystem_domain" (
	DOMAIN	TEXT,
	ID	INTEGER,
	PRIMARY KEY(DOMAIN,ID)
);
INSERT INTO `adsystem_domain` VALUES ('adtech.com',11);
...

DROP TABLE IF EXISTS adsystem;
CREATE TABLE "adsystem" (
	ID	INTEGER,
	NAME	TEXT,
	CANONICAL_DOMAIN	TEXT,
	PRIMARY KEY(ID)
);
INSERT INTO `adsystem` VALUES (1,'Rubicon',NULL);
...
COMMIT;

ref : https://github.com/InteractiveAdvertisingBureau/adstxtcrawler

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';