| Abfrage | MS-SQL | Oracle |
|---|---|---|
| Aktuelle Zeit | SELECT getdate() | SELECT sysdate FROM dual |
| DB User | SELECT db_name() | SELECT sysdate FROM dual |
| DB Servername | SELECT @@servername
| SELECT UTL_INADDR.GET_HOST_NAME() FROM dual |
| Nur das 1. Ergebnis | SELECT top 1 date FROM user | SELECT max(date) FROM user |
| Nur die ersten 10 Ergebnisse (#1 - unsortiert) | SELECT top 10 uid_person FROM person | SELECT uid_person FROM PERSON WHERE ROWNUM <= 10 |
| Nur die ersten 10 Ergebnisse (#2 - sortiert) | SELECT top 10 uid_person FROM person ORDER BY xdateinserted | SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY xdateinserted DESC) AS rownumber, uid_person, xdateinserted FROM person WHERE XDATEINSERTED IS NOT NULL ) WHERE rownumber <= 10 |
| Arithmetik (+,-,*) | SELECT 1+1 | SELECT 1+1 FROM dual |
| DB Version | SELECT SERVERPROPERTY('ProductVersion') SELECT @@VERSION
| SELECT banner FROM v$version |
| Stringverknüpfung | SELECT 'Hello ' + 'World' | SELECT 'Hello ' || 'World' FROM dual |
| Alle Tabellen listen | sp_tables SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' | SELECT * FROM tab |
| Letzter Tag im Monat | SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,GETDATE())),DATEADD(m,1,GETDATE()))) | SELECT last_day(sysdate) FROM dual |
#!/bin/sh # ORACLE_PLUS_PATH='/opt/oracle/product/9ir2/bin/sqlplus' ORACLE_USER='myuser' ORACLE_PWD='mypass' ORACLE_SID='mysid' function sqlquery() { typeset expr=$1 echo " set feedback off verify off heading off pagesize 0 $expr; exit " | $ORACLE_PLUS_PATH -S "$ORACLE_USER/$ORACLE_PWD@$ORACLE_SID" } sqlquery "select spalte1,spalte2 from tabelle where bedingung1 is not null and bedingung2 = 'abc'" |while read sp1 sp2 do echo $sp1 echo $sp2 done
Hinweis: Funktioniert in der Regel nur als User oracle.