Awk: Count number of occurences of a string in a file
awk -F ‘{print NF-1}’ e.g.: awk -F: ‘{print NF-1}’ SUMM_DQ_KEY_ISSUE.osh
Month: September 2014
Oracle: Find tables referring to another
select table_name, constraint_name, status, owner from all_constraints where r_constraint_name in (–get primary and unique constraints on the target table. These can be used for FK references by other tables select constraint_name from all_constraints where constraint_type in (‘P’, ‘U’) and table_name …
Oracle QUOTE function: Use to avoid complicated escape character sequences
Rather than fret about how to escape 2 single quotes for example, just use the quote function and native text: QUOTE: q'{}’; vSQL := q'{ALTER TABLE DIM_ISSUE ADD (CONSTRAINT DIM_ISSUE_C9 CHECK (length(regexp_replace(UPPER(issue_name),'[ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_]’,”)) = 0 AND regexp_like(issue_name,’^[ABCDEFGHIJKLMNOPQRSTUVWXYZ]+.*$’)))}’; EXECUTE IMMEDIATE(vSQL;)
Oracle: Drop table / column if it exists
DECLARE table_exists NUMBER :=0; –column_exists NUMBER :=0; BEGIN –Drop table / column if it exists SELECT COUNT(*) INTO table_exists FROM USER_TABLES WHERE TABLE_NAME = ‘‘; –SELECT COUNT(*) INTO column_exists ALL_TAB_COLUMNS WHERE OWNER = ‘‘ AND TABLE_NAME = ‘‘ AND COLUMN_NAME …
XMLAgg to create CLOB
Use XmlAgg function to build up a dynamic string over the 4000 character limit, by using .getClobVal() to create a CLOB string –get rid of the final UNION ALL select regexp_replace ( nSQL, ‘(^.*) UNION ALL $’, ‘\1’ ) vSQL …
Information Analyzer Tips
Export current IA project Use IAAdmin.sh command line tool to export current IA project. The tool is located under your local installation of Information Server client or on server: <IIS install path>/ASBNode/bin The command to export given project content is: …
Oracle SQLPlus
Question: How to connect to sqlplus? Answer: Launch cmd window and enter: sqlplus user/password@server Question: How do I execute a SQL script file in SQLPlus? Answer: To execute a script file in SQLPlus, type @ and then the file name. …