I haven’t done much JDBC tracing yet, but recently for a Kerberos KCM + ANO case I wanted more traces. In the old doc, on google and myoraclesupport, you’ll often see about the _g suffix. Like using ojdbc8_g.jar and -Doracle.jdbc.Trace=true and -Doracle.jdbc.diagnostic.enableLogging=true. But chance are, you use a recent jar and find no _g one. […]
You enjoyed my old trick with https://laurentschneider.com/wordpress/2012/11/tnsping-and-instant-client.html ? you will love this one ❤️ sqlplus -p db01 Local Net Naming configuration file: /usr/lib/oracle/23/client64/lib/network/admin/tnsnames.ora Attempting to contact: (description=(address_list=(address=(protocol=TCP)(host=srv01.example.com)(port=1521)))(connect_data=(service_name=db01.example.com))) Ok (1.143 msec) Remark: I first posted ping from within sqlplus, but -p is obviously nice. Thanks Beat Ramseier | LinkedIn via Post | LinkedIn
This is common sense How to enforce this in a recent Oracle version? create assertion x25 check (not exists(select * from emp e where sal*25<any(select sal from emp))); update emp set sal=100000 where job=’PRESIDENT’; ORA-08601: SQL assertion (SCOTT.X25) violated.
whenever you do mycmd | grep mytext the return code of command got lost, $? point to the return code of grep. Back in time I posted https://laurentschneider.com/wordpress/2009/05/return-code-before-grep.html ( ( ( mycmd echo $? >&3 ) |grep mytext >&4 ) 3>&1 |(read x;exit $x) )4>&1 this did work like a charm for decades. But earlier […]
When encrypting Network traffic to your database server, there are two different approaches. One is to use Oracle Native Encryption by setting SQLNET.ENCRYPTION_SERVER=requested on the server sqlnet.ora and that’s about it. Very easy to setup and nothing to do on the client. Also it was a good choice for performance and security. The other option […]
$ cat HW.java && javac HW.java && java HW void main() { System.out.println(“Hello World”); } Hello World If you know, you know
I just tried sqlite. It is ultra-light and support SQL commands. Just try this $ sqlite3 test.db SQLite version 3.26.0 2018-12-01 12:34:55 Enter “.help” for usage hints. sqlite> create table t(x number); sqlite> insert into t values(1); sqlite> select * from t; 1 sqlite> $ du -h test.db 8.0K test.db I just created a database, […]
Long long time ago, back in SunOS ages and shell Bourne, I saw this code if [ $x ]; then … The author told me, I was a junior java developer, it checks if x exists. I didn’t like the code. I just found out that “1 = 2” does not exist… $ x=’1 = […]
One user may complain about ORA-28008 when changing its old password in 19c. Many users still use the old non case sensitive passwords. Resetting User Passwords That Use the 10G Password Version is a post-upgrade task of 19c. Let’s try with the identified by values (as I wrote long time ago there alter-user-identified-by-values-in-11g ) SQL> […]
We all know how to sum up by year select to_char(trunc(hiredate,’Y’),’YYYY’) year, count(*) from scott.emp group by trunc(hiredate,’Y’) order by trunc(hiredate,’Y’) YEAR COUNT(*) —- ——– 1980 1 1981 10 1982 1 1987 2 This is quite easy. Same for day (DD), century (CC), quartal (Q), hour (HH24), month (MM) But how do you group by, […]
Using the latest instant client, the Easy Connect Plus improvement let you parametrise your jdbc url more intensively jdbc:oracle:thin:@ldaps://ldap.example.com/db01?WALLET_LOCATION=/db00/mywallet&AUTHENTICATE_BIND=true&AUTHENTICATE_BIND_METHOD=none&DIRECTORY_SERVER_TYPE=OID EasyConnect Plus appeared in 19c, but was further improved in 21c and 23ai. See more JDBC Developer’s Guide https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/data-sources-and-URLs.html#GUID-44572C63-10D2-478A-BB2E-ACF6674C59CC
my 2009 solution return code before grep ( ( ( mycmd echo $? >&3 ) |grep mytext >&4 ) 3>&1 |(read x;exit $x) )4>&1 my 2025 solution mycmd > >(grep mytext)
if you want to grep anything except foo, use grep -v or negative lookahead echo -e ‘foo\nbar\nboz’|grep -P ‘^(?!.*foo).*$’ bar boz it is just so beautiful ! e.g. get IP address except localhost in /etc/hosts grep -Po ‘^(?!127.0.0.1)[0-9.]+’ /etc/hosts 192.168.1.1 192.168.1.2 if you have no grep -P, use perl
this blog is no longer maintained. go there : OEM patch | LaurentSchneider.Com (wordpress.com)
new post on https://laurentschneider.wordpress.com/2023/07/31/wlst/ this blog is no longer maintained
Back in early 2005 I started this blog on Blogspot. Not being unserious about blogging, I bought this domain later in 2006 with paid hosting. I can recommend my GoDaddy provider for their support and attractive rates, but nowadays, I am no longer an active blogger. I’ll keep posting things occasionally on laurentschneider.wordpress.com. Saad posted […]
XMLRoot has been deprecated since 12cR2 and desupported since 21c. Still, it is not really obvious to understand how to generate a prolog. Also, you couldn’t specify the encoding. Well, one could fake the version with double quote, but this is not recommended, there even was some dirty SQL injection in oldish versions. select xmlroot(xmlelement(x),version […]
How to select xml with DTD ? Imagine this XML code SQL> select xmltype( ‘<!DOCTYPE FOO SYSTEM “/public/test/foo.dtd”><FOO/>’, wellformed => 1) xml from dual; XML —————————————————– <!DOCTYPE FOO SYSTEM “/public/test/foo.dtd”><FOO/> Most validations are done with XSD and not DTD, so it is not that easy, even google probably lead you here 😉 Create the dtd […]
Linux is not Unix, I keep finding out differences years after years. I’ve been using cp -r for over 25 years to find out today, the good old cp -r is cp -Lr on Linux. e.g. AIX $ touch foo $ ln -s foo bar $ cp -r bar boz $ ls -la boz -rw-r–r– […]
DROP TABLE [IF EXISTS] table-name Drop table if exists has been there for ages, in Oracle MySQL, Oracle NoSQL and others. Now we got this in Oracle Database 23c too. First thing I do on my sandbox database is creating SCOTT tables. Now, for the first time, I can make my script 100% error free. […]
but only for developers. You can get it from oracle.com as a RH8 rpm : https://www.google.ch/search?q=download+oracle+23c You need about 6Gb in /opt and there is a /etc/init.d/oracle-free-23c configure -i command that does create a database for you. Some goodies : select 1; select true; create table if not exists t(x number); arg 1 def xxx select […]
Quite often you select from the sysman schema, let’s say select target_name from sysman.mgmt$target; To do so, you better be sys.. because SYS can see everything ! Let’s see what’s missing with a standard user connect sys/***@oem01 as sysdba; create user user1 identified by ***; grant create session to user1; conn user1/***@oem01; select count(*) from […]
Without Administrative privilege, how would you install ODP.NET? Note 784309.1 describes the required files if you have no Oracle client download XCOPY ODAC from Oracle .NET, Visual Studio, and VS Code ODAC Downloads for Oracle Database Copy the files Oracle.DataAccess.dll OraOps.dll orannzsbb.dll oraociei.dll oci.dll to your directory (ex: C:\ODAC) Add that directory to your PATH […]
I just established a connection from R to Oracle / MSSQL using ODBC. First, I installed R sudo yum install R Then, ODBC sudo yum install unixODBC-devel freetds-libs I set up my mirror in .Rprofile options(repos=structure(c(CRAN=”http://cran.example.com/”))) I install ODBC Library in R R -s <<EOF install.packages(“odbc”) EOF I configure my odbc driver to Oracle Instant […]
done for a friend : install rpm’s sudo yum install -y freetds unixODBC unixODBC-devel freetds-libs python3-pyodbc create odbc.ini and odbcinst.ini for sybase go there Unix ODBC Sybase for oracle go there Unix ODBC Oracle for mssql ~/.odbc.ini : the Database definition [DB01] Driver = FreeTDS Description = DB01 Server = src01 Port = 1433 Database = DB01 […]
Ages ago I explained how to generate excel from the Unix command line. For instance with HTML or with XML+ZIP To do this in Windows is way more elegant. You get your dataset from Oracle or any other source. Then you create an Excel Object, you add content, saves, that’s all folk! # 1) get […]
A new 10.2 feature I mentioned in su in sqlplus – Laurent Schneider is the ability to use a proxy user in a connection string. This was documented in What’s New in Oracle Call Interface? Proxy access for a single client can be set using various connect strings. Today I got one user complaining about […]
Precondition : sqlplus can connect to the database server using a kerberos ticket. I wrote The long long route to Kerberos – Laurent Schneider in 2014, but most of the bugs in 11.2.0.2 / 12.1.0.2 are no longer interesting, so probably you should be fine to just read Configuring Kerberos Authentication (oracle.com) let’s see sqlplus […]
After part 1, where Oracle wrote in an official document there is no impact for your targets, Critical Patch Advisory April 2022 reveals multiple vulnerabilities, one of them is Log4J severity 9.8/10. 9.8 means it is kindergarden easy to compromise confidentiality, integrity and availability even without a login. In the meanwhile, per 30. April, Oracle […]
the basic : you have tns resolution over ldap. To change from ldap to ldaps, you modify : jdbc:oracle:thin:@ldap://ldap.example.com:389/DB01,cn=OracleContext,dc=example,dc=com to jdbc:oracle:thin:@ldaps://ldap.example.com:636/DB01,cn=OracleContext,dc=example,dc=com now the advanced : Oracle Support mentions, there is no support for Mode 2 SSL (note 1664857.1) So one approach was to either not use ldaps, or to use level 1, which means no […]
The easiest is : restore controlfile from autobackup Also possible : restore controlfile from “/bckdir/c-123-45” If you use tape run { allocate channel c1 type ‘SBT_TAPE’ PARMS ‘ENV=(TDPO_OPTFILE=…)’; restore controlfile from “c-3635577900-20220330-02”; }