Tag: uom
dev01.yznotes.com/column2string/
Ever had a list of column (or “who knows how”) oriented values that needed to be converted into a comma separated string? – This little web application tries to assist with this simple but sometimes annoying and time consuming task.
Please report if you find any bugs.
Environment: Windows XP SP3, Oracle Client 11.1.0
Step 1:
Open your sqlnet.ora file (mine is located in C:\oracle\product\11.1.0\db_1\NETWORK\ADMIN) and add section below at the end:
1 2 3 4 5 6 7 8 9 10 11 |
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = C:\oracle\product\11.1.0\db_1\NETWORK\ADMIN) ) ) SQLNET.WALLET_OVERRIDE = TRUE SSL_CLIENT_AUTHENTICATION = FALSE SSL_VERSION = 0 |
The DIRECTORY parameter in line 5 determines where your wallet files will be created. It has to be an absolute path. To keep it simple I set mine to the same directory where sqlnet.ora, listener.ora and tnsnames.ora files sit.
Step 2:
Run shell command mkstore -wrl “C:\oracle\product\11.1.0\db_1\NETWORK\ADMIN” -create (see below). You will be asked to set up a password for the wallet. This password has no relation to the TNS credentials, it’s to protects wallet itself. The directory path has to be the same as in previous step (in my case it’s C:\oracle\product\11.1.0\db_1\NETWORK\ADMIN)
1 2 3 4 5 6 7 8 9 10 11 |
C:\>mkstore -wrl "C:\oracle\product\11.1.0\db_1\NETWORK\ADMIN" -create Enter password: Invalid Password.... PASSWORD_POLICY : Passwords must have a minimum length of eight characters and contain alphabetic characters combined with numbers or special characters. Enter password: Enter password again: C:\> |
You will notice wallet files created in that directory:
Step 3:
Open tnsnames.ora file (usually in the same directory where sqlnet.ora sits), and create a new TNS entry which you will be using with the wallet. Normally you would want to just copy an exiting entry giving it a new alias. In example below I create a TNS entry PRDSPW by copying an existing TEST one:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
TEST = (DESCRIPTION = (LOAD_BALANCE = YES) (ADDRESS = (PROTOCOL = TCP)(HOST = ...) (ADDRESS = (PROTOCOL = TCP)(HOST = ...) (CONNECT_DATA = (Service_name = ...) (FAILOVER_MODE = ... ) ) ) PRDSPW = (DESCRIPTION = (LOAD_BALANCE = YES) (ADDRESS = (PROTOCOL = TCP)(HOST = ...) (ADDRESS = (PROTOCOL = TCP)(HOST = ...) (CONNECT_DATA = (Service_name = ...) (FAILOVER_MODE = ... ) ) ) |
Step 4:
Run shell command mkstore -wrl “C:\oracle\product\11.1.0\db_1\NETWORK\ADMIN” -createCredential PRDSPW dummy_user dummy_pass (see below), where PRDSPW is the special TNS name we created in Step 3, dummy_user – database username, dummy_pass – database password. You will be asked to provide wallet password that we set up in Step 2. Path (C:\oracle\product\11.1.0\db_1\NETWORK\ADMIN) – is the same as in all previous steps.
1 2 3 4 |
C:\>mkstore -wrl "C:\oracle\product\11.1.0\db_1\NETWORK\ADMIN" -createCredential PRDSPW dummy_user dummy_pass Enter wallet password: Create credential oracle.security.client.connect_string1 |
All done.
Now if you run command mkstore -wrl “C:\oracle\product\11.1.0\db_1\NETWORK\ADMIN” -listCredential it will list a new wallet entry that we just created:
1 2 3 4 5 6 7 |
C:\>mkstore -wrl "C:\oracle\product\11.1.0\db_1\NETWORK\ADMIN" -listCredential Enter wallet password: List credential (index: connect_string username) 1: PRDSPW dummy_user C:\> |
From now on you can connect to PRDSPW by calling wallet entry without exposing your database user name and password:
SQLPlus:
1 2 3 |
C:\>sqlplus /@prdspw ... SQL> |
cx_Oracle:
1 |
cx_Oracle.connect("/@prdspw") |
Command to delete an individual credential:
1 2 3 4 5 |
C:\Program Files\ConEmu>mkstore -wrl "C:\oracle\product\11.1.0\db_1\NETWORK\ADMIN" -deleteCredential PRDSPW Enter wallet password: Delete credential Delete 1 |
Environment: Python 2.7 (Should run fine in PY3 as well after ‘fixing’ print statements)
This script was designed for searching interface log files for list of keywords. If ran as a standalone script, amend argumens highlighted in lines 29-31:
keywords – comma separated string with list of keywords we are searching for. Note that space symbol is also count, so, for example, keywords = ”’123, 345”’ means that you are interested in ‘123’ and ‘ 345’ (space and then digits ‘345’) entries
search_dir – root directory where the search starts, search is recursive
date_since – use this argument if you want to limit search to files that have been modified after specific date, so, for example, with date_since = “01/11/2012” only files modified on or after the 1st of Nov 2012 will be searched. date_since has to be entered in dd/mm/yyyy format, if not provided (or mistyped) – script runs without modified date parameter.
You may want to amend condition for file format in line 52, the current version searches only in files with extension ‘.log‘
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
#!/usr/bin/env python # author: yurz # def mod_since(date_since, file_path): import os from datetime import datetime from time import mktime try: time_since = mktime(datetime.strptime(date_since, '%d/%m/%Y').timetuple()) file_mod_time = os.path.getmtime(file_path) except: return True if file_mod_time >= time_since: return True else: return False def time_stamp(): import datetime return datetime.datetime.now().strftime("%Y%m%d_%H%M%S") def main(keywords=None, search_dir=None, date_since=None): ## ### enter arguments below if ran as a standalone script ### keywords = '''12345,678910,blah-blah''' search_dir = "//tuna/data/.../process/" date_since = "01/11/2012" ### end of arguments ### ## import os from sys import stdout results = "search_results" + "_" + time_stamp() + ".txt" keyword_list = keywords.split(",") results_file = open(results, "w") file_num = 0 line_num = 0 find_num = 0 print "Searching" for root, dirs, files in os.walk(search_dir): for file in files: print ".", fullpath = os.path.join(root, file) if mod_since(date_since, fullpath) and file.endswith(".log"): file_num += 1 with open(fullpath, "r") as search_file: for line in search_file: line_num += 1 for keyword in keyword_list: if keyword in line: find_num += 1 results_file.write(keyword + "\t" + fullpath + "::\t" + line) results_file.close() print "\n\n", str(line_num), "lines in", str(file_num), "files searched" print str(find_num), "results recorded in", results if __name__ == '__main__': import time start_time = time.time() main() print "Job ran for", time.time() - start_time, "seconds" |
Step 1: Create Test Table:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
truncate table test_table; drop table test_table; create global temporary table test_table ( txn_no number(4,0) ,txn_sq number(3,0) ) on commit preserve rows; commit; insert into test_table (txn_no) values (1001); insert into test_table (txn_no) values (1001); insert into test_table (txn_no) values (1001); insert into test_table (txn_no) values (1001); insert into test_table (txn_no) values (1002); insert into test_table (txn_no) values (1002); insert into test_table (txn_no) values (1002); insert into test_table (txn_no) values (1003); insert into test_table (txn_no) values (1003); insert into test_table (txn_no) values (1001); insert into test_table (txn_no) values (1002); insert into test_table (txn_no) values (1003); commit; |
Result:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SELECT * from test_table -------------------------- TXN_NO TXN_SQ ------ ------ 1001 1001 1001 1001 1002 1002 1002 1003 1003 1001 1002 1003 12 rows selected |
Step 2: Run PL/SQLl anonymous block to deal with the duplicates:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
declare var_txn_no test_table.txn_no%type; -- variable for transaction number var_txn_sq test_table.txn_sq%type; -- variable for our own per txn_no sequence that we are going to insert var_txn_no_pr test_table.txn_no%type; -- variable to track value of previous txn_no i integer; -- iterator cursor curs_1 is select txn_no from test_table order by txn_no for update of txn_sq; begin var_txn_no_pr := 0; open curs_1; loop fetch curs_1 into var_txn_no; exit when curs_1%notfound; if var_txn_no = var_txn_no_pr then i := (i + 1); else i := 1; end if; update test_table set txn_sq = i where current of curs_1; var_txn_no_pr := var_txn_no; end loop; close curs_1; commit; end; / |
Result:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SELECT * FROM TEST_TABLE order by TXN_NO, TXN_SQ ----------------------------------- TXN_NO TXN_SQ ------ ------ 1001 1 1001 2 1001 3 1001 4 1001 5 1002 1 1002 2 1002 3 1002 4 1003 1 1003 2 1003 3 12 rows selected |