Oracle cheat sheet

Last updated: July 24rd, 2007

• Installing xe oracle and cx_oracle (on Fedora 7)
• Make sure you have enough swap space (check your swap space with free) or with /proc/swaps the latter being a file)
• Usage: /etc/init.d/oracle-xe {start|stop|restart|force-reload|configure|status|enable|disable}
• Open the proper ports (see when you configure oracle)
• source /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh
• If you have problems login in with the password you gave through the web page. Try the following:

        su oracle
	sqlplus / as sysdba
	ALTER USER SYS IDENTIFIED BY newPassword;
	commit;
        

• go to: Adminatration --> Database users and edit/add users.
• now install cx_oracle. make sure you have setup the oracle environment.
• Your now ready to do a few simple tests with these files:

        # set the oracle environment
	# enter user name and passwd after doing:
	sqlplus
        # import your schema
	@test.ora
        # in the sql client do:
	select * from test_table;
	# now try to execute the python script (test.py) (on command line)
        # make sure the contact parameters are set correctly
        python test.py
	# now try and use the session object:
        # NOTE: the session object is only for people working on the prodagent
        # other people can ignore it.
        # make sure the contact parameters are set correctly
        python testSession.py

        

• Note:In cx_Oracle do not use ';' at the end, it will give an error.
• Importing sql files in sqlplus: use '@' (e.g. @mysqlfile.ora)
• Viewing the tables in your database: select table_name from tabs order by table_name;
• View a description of the table:describe (table name
• Create an primary key, set 2 enum constraints and one unique constraint, create an autoincrement (using triggers and sequences) and 1 index:

CREATE TABLE ws_request
     (
     id                     integer,
     CONSTRAINT cons_ws_request_id primary key(id),
     request_type           varchar2(100) not null,
     CONSTRAINT cons_ws_request_request_type CHECK (request_type IN ('event', 'file')),
     status                 varchar2(100) default 'new',
     CONSTRAINT cons_ws_request_status CHECK(status IN ('new','active','finished','failed')),
     string_id              varchar2(100) not null,
     CONSTRAINT cons_ws_request_string_id unique(string_id)
     workflow_spec          clob,
     );

CREATE SEQUENCE ws_request_seq
start with 1
increment by 1
nomaxvalue;

CREATE TRIGGER ws_request_autoincrement
BEFORE INSERT ON ws_request
FOR EACH ROW
    DECLARE m_no INTEGER;
BEGIN
    SELECT ws_request_seq.nextval INTO :new.id FROM dual;
END;
.
run;

create index ws_request_string_id on ws_request(string_id),
	

• inserting data into a clob (using cx_Oracle):

curs = orcl.cursor()
curs.setinputsizes(
    workflow_spec=cx_Oracle.CLOB,
    policies=cx_Oracle.CLOB,
    parameters=cx_Oracle.CLOB)
sqlStr="INSERT INTO
ws_request(string_id,owner,workflow_spec,policies,parameters,request_type)
VALUES ('myOwnRequestID0','fvlingen',:workflow_spec,:policies,:parameters,'event')"
curs.execute(sqlStr,
    workflow_spec="long text",
    policies="another long text",
    parameters="another very long text")
	

NOTE:You can also use dictionaries here: {'workflow_spec':'long text','policies':....}
• Reading data from a clob (using cx_Oracle). You use an iterator here (do not use fetchall). If no more rows
are available it generates an exception.

curs = orcl.cursor()
sqlStr="SELECT workflow_spec FROM ws_request"
curs.execute(sqlStr)
while True:
   try:
       row=curs.next()
       lobObject=row[0]
       print(lobObject.read())
   except:
       break
	 

• encrypting and decrypting data (e.g. passwords) in Oracle.. Run this example file to see how it works.