on parsing arguments in shell

While most programming languages are accepting arguments as an array of strings, shell doesn’t arglist.c #include int main(int argc, char **argv) { int i; for (i=1; argc>i; i++) printf(“$%d=%s\n”,i,argv[i]); } $ make arglist cc arglist.c -o arglist $ ./arglist one two three four $1=one $2=two $3=three $4=four To do the same in shell, it requires […]

Dump TNSNAMES.ORA from ActiveDirectory

Having all connections string in ActiveDirectory is nice, but maybe you need sometimes to push it to an external system (e.g. DMZ or Linux). echo “# AD” > tnsnames.ora $o = New-Object DirectoryServices.DirectorySearcher $o.Filter = ‘objectclass=orclNetService’ foreach ($p in $o.FindAll().Properties) { [String]($p.name+”=”+$p.orclnetdescstring) >> tnsnames.ora } goodies

Active Dataguard : read only with apply

A common frustration with standby is that your database is doing nothing else than applying logs. One may want to run some reports on it. Usually, the database is MOUNTED and not OPEN. This means, apart from selecting from DUAL and performance views like v$$managed_standby or v$session, there is little you can do. Possibly, you […]

Select from cdb_* views

There is no privileges strong enough for you to view all objects in all databases Let’s try as sys: SQL> select con_id, count(*) from cdb_objects group by con_id; CON_ID COUNT(*) ———- ———- 1 22749 3 22721 as non-sys SQL> create user c##u identified by ***; User created. SQL> grant create session, select any dictionary to […]

Standard date format

Let’s start with Powershell Get-Date -format “o” 2019-03-08T17:41:02.7346332+01:00 The “O” or “o” standard format specifier represents a custom date and time format string using a pattern that preserves time zone information and emits a result string that complies with ISO 8601 docs.microsoft.com Now Linux date “+%Y%m%dT%H:%M:%S.%3N%z” In SQL to_char(current_timestamp, ‘YYYYMMDD”T”HH24:MI:SS.FF3TZH:TZM’) for my XML fans extractvalue(xmlelement(t, […]

Audit pluggable database

In the old now-deprecated maybe-soon-desupported non-cdb infrastructure, AUDIT’ing was done right after connect / as sysdba. In single-tenant (or multi-tenant), things get complicated. Once again, the doc must be read at least twice 😉 If you issue an audit statement in the root, then the database performs auditing across the entire CDB, that is, in […]

19c

19c is a mini-release. Remember it is a new name for the second 12cR2 patchset, after 12.2.0.2/18c https://mikedietrichde.com/2019/02/13/oracle-database-19-2-for-exadata-is-now-available-for-download/ was the first to mention it. By looking up in the doc I found . Distinct listagg . Desupport sqlplus product profile . listener.log log rotation Go to the doc to find more https://docs.oracle.com/en/database/oracle/oracle-database/19/whats-new.html

Unix ODBC Sybase

very similar to Unix ODBC Oracle instead of tnsnames, the connections are defined in $SYBASE/interfaces. the odbc.ini must exists as well in $SYBASE. if you test with unixODBC-devel, keep in mind to use /usr/bin/isql and not $SYBASE_OCS/bin/isql $ODBCSYSINI/odbc.ini [syb] Driver = Sybase16 DSN = syb ServerName=SYB01 $ODBCINI/odbcinst.ini [Sybase16] Description = Adaptive Server Enterprise Driver = […]

Unix ODBC Oracle

To connect via ODBC, check https://laurentschneider.com/wordpress/tag/odbc This article is related to Unix/Linux. Often you have a fat client written in C, while java uses JDBC instead of ODBC. Okay, it’s pretty easy, if you have an oracle client, you probably already have libsqora.so.xx.1 in your LD_LIBRARY_PATH. In this case you can connect using ODBC. What […]

Privileges on a view

Granting too many privileges on a view could be disastrous. A view is often used as a security element; you grant access to only a subset of columns and rows to one user. Mostly only SELECT. If you want to grant update to only some rows, the security could be enhanced with the WITH CHECK […]

super-long-lines in CLOB

Sometimes you use sqlplus and your line is longer than your linesize SQL> select n||’;’||x from t2; 1;one 2;twoPxMQztzLaqjWjGKOXIVIVrrHC fJkTLbRgCPiENfWrrCjUMRSkmCfLUY RdVASFacGtyEnUplOAXspDJZmSLPfg ziKYQBKoHdkqGXoBCgkKuVOdxEvhQy lZLQROFxIxOzqeQeFMGXcGkuJwnGGg zvNSuCFMVxyQgboLAUpDcYnBsuVyXX vFtWPICwqmcEdmzRRkOzzPethrqjRR aBSOTOZiYfxcYSqbqFlGuVOBRdxjFh ZcxJBvSCVXwONmS prVzTykEfSsePyYwyLVoyYrVLynUzs MLFWQxwUKNsVcYzUOAhslNldnBpITS rxPlpJbLSjJqgxNxsGVsrYhkWAMufk QnRayieEkSDYrNqyLejJuggADNxcgV tszjJIYKCxPweNGhXsOFKGbMkTBPCf DXwjBNgQYswbaNWBOEtSTHjIhdLAyM nbhyhRKKdfaTTpTgHqQelVWmnkBHjA ZTrGqdtlYAgoXNHnoryxHxVVyaMiGR SjdVlRwMas 3;three SQL> There are two extra line breaks, one after ONmS and one after wMas An easy solution is […]

in doubt transaction

Distributed transactions allows you to have multiple DML’s over multiple databases within a single transaction For instance, one local and one remote insert into t values(1); insert into t@db02 values(2); commit; If you lose connection to db02 and wants to commit, your database server may/does not know about the state of the remote transaction. The […]

Categories: .

Single Tenant duplicates

I recently reported an issue regarding single tenant. In old-time non-cdb, the SID used to be unique on a server. If you connect to srv01:port:sid, then you know where you connect. Unfortunately, this is no longer true. If for instance you have two database sid’s S01 and S02 with a pluggable P01, and both run […]

Add-OdbcDsn

I wrote a few odbc articles using ODBCCONF in my blog, so I edit them because ODBCCONF will be removed; read https://docs.microsoft.com/en-us/sql/odbc/odbcconf-exe Using Powershell Add-OdbcDsn is much easier PS> remove-OdbcDsn -name DB01 -dsntype User PS> Add-OdbcDsn -name DB01 -DriverName “Oracle in client12201” -DsnType “User” -SetPropertyValue @(“Server=DB01”) PS> Get-OdbcDsn Name : DB01 DsnType : User Platform […]

How to get dbms_output to print line before the end of the procedure?

begin dbms_output.put_line(‘Kilroy1’); dbms_lock.sleep(1); dbms_output.put_line(‘Kilroy2’); dbms_lock.sleep(1); dbms_output.put_line(‘Kilroy3’); dbms_lock.sleep(1); dbms_output.put_line(‘Kilroy4’); dbms_lock.sleep(1); dbms_output.put_line(‘Kilroy5’); dbms_lock.sleep(1); end; / You wait five seconds, then get the output. This is the way it works with dbms_output. Now I try a new trick The output does not wait the end. There was a trick I wrote in 2007 using utl_file, but it was […]

Dynamic number of columns revisited

Itching to start playing with 18c? Now you can on @oraclelivesql ! Here's a script to get you started: a dynamic CSV-to-columns converter using polymorphic table functionshttps://t.co/UfddLQ2tn5 pic.twitter.com/BxnXeIKoCx — Chris Saxon (@chrisrsaxon) February 17, 2018 The ingenious solution of Anton Scheffer using Data Cartridge is now beaten in 18c using polymorphic table function Anthologic post […]

better than CTAS

SQL> create table t1(x number primary key); Table created. SQL> desc t1 Name Null? Type ———————– ——– —————- X NOT NULL NUMBER SQL> create table t2 as select * from t1; Table created. SQL> desc t2 Name Null? Type ———————– ——– —————- X NUMBER The table T2 has the column X, but not the constraint […]

check invalid directories

To get the status of a directory, I wrote my own function, which uses DBMS_LOB.FILEEXISTS. CREATE FUNCTION status (DIRECTORY_NAME VARCHAR2) RETURN VARCHAR2 IS BEGIN IF (DBMS_LOB.FILEEXISTS( BFILENAME (DIRECTORY_NAME, ‘.’)) = 1) THEN RETURN ‘VALID’; ELSE RETURN ‘INVALID’; END IF; EXCEPTION WHEN OTHERS THEN RETURN SQLERRM; END; / SELECT directory_name NAME, directory_path PATH, status (directory_name) STATUS […]