Label Compound Statements - Double Angle Brackets

Örnek

Şöyle yaparız

<<begin_end_block>> 
DECLARE
  ...
BEGIN
  ...
  <<loopblk>> 
  FOR itr8 IN 1 .. 5
  LOOP
    ... 
  END loop loopblk;
END begin_end_block;
/

CREATE TYPE AS VARARRAY

Örnek

Şöyle yaparız.

create or replace type integer_varray as varray (4000) of int;
create table plan_capacities
(
  id       int generated by default as identity not null
    constraint plan_capacities_pkey primary key,
  line_id  int references lines (id) on delete cascade,
  model_id int references models (id) on delete cascade,
  plan_id  int references plans (id) on delete cascade,
  capacity integer_varray
);

ROWNUM

Giriş

Açıklaması şöyle

ROWNUM is a pseudocolumn (not a real column) that is available in a query. ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N , where N is the number of rows in the set ROWNUM is used with. A ROWNUM value is not assigned permanently to a row (this is a common misconception). A row in a table does not have a number; you cannot ask for row 5 from a table—there is no such thing.

Also confusing to many people is when a ROWNUM value is actually assigned. A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned,...

Örnek

Sort işleminden sonra rownum verildiğini görmek için şöyle yaparız

SELECT ROWNUM, customers.*
FROM customers
WHERE customer_id > 4500;

ROWNUM  CUSTOMER_ID  LAST_NAME  FIRST_NAME  FAVORITE_WEBSITE
------  -----------  ---------  ----------  ---------------------
     1         5000  Smith      Jane        www.digminecraft.com
     2         6000  Ferguson   Samantha    www.bigactivities.com
     3         7000  Reynolds   Allen       www.checkyourmath.com
     4         8000  Anderson   Paige
     5         9000  Johnson    Derek       www.techonthenet.com

SELECT ROWNUM, customers.*
FROM customers
WHERE customer_id > 4500
ORDER BY last_name;

ROWNUM   CUSTOMER_ID   LAST_NAME   FIRST_NAME   FAVORITE_WEBSITE
------   -----------   ---------   ----------   ---------------------
     4          8000   Anderson    Paige
     2          6000   Ferguson    Samantha     www.bigactivities.com
     5          9000   Johnson     Derek        www.techonthenet.com
     3          7000   Reynolds    Allen        www.checkyourmath.com
     1          5000   Smith       Jane         www.digminecraft.com

SYSTIMESTAMP Function

Giriş

TIMESTAMP WITH TIME ZONE cinsindendir.

Örnek

Şöyle yaparız

SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
------------------------------------------------------------------
28-MAR-00 12.38.55.538741 PM -08:00

Örnek

Son 1 saat içindeki tüm satırları almak için şöyle yaparız. Burada ts sütunu SYSTIMESTAMP değerindendir

SELECT * FROM MyTable WHERE ts > SYSTIMESTAMP -(1/24) ORDER BY ts DESC;

sqlplus komutu

Giriş

sqlplus için bazen ORACLE_HOME ortam değişkenini kullanılıyor. Şöyle yaparız

export ORACLE_BASE=/u02/app/oracle/product/12.2.0 
export ORACLE_HOME=$ORACLE_BASE/dbhome_1 
export ORACLE_SID=dbz 
export PATH=$PATH:$ORACLE_HOME/bin

Örnek

Şöyle yaparız

$ sqlplus / as sysdba

Örnek - bash içinden kullanma

Şöyle yaparız.

# set errorcodes
SCRIPT_ERROR=1
NO_ERROR=19968
ALREADY_INSTALLED=20000
ALREADY_EXISTS=20001
COMPILATION_ERRORS=20100

Şöyle yaparız. Burada EOF .. EOF ile Bash Here Document kullanılıyor. Aslında gereksiz.

SQLADM="$ORACLE_HOME/bin/sqlplus -S $DBUSER/$DBPASSWORD@\"${DBURL}\" as sysdba"

SCRIPTS=`/bin/ls -1`

SQL_HEADER="SQL.HEADER"
SQL_FOOTER="SQL.FOOTER"

for f in $SCRIPTS
do
  cat $SQL_HEADER $f $SQL_FOOTER > $SCRIPT_TO_RUN
  $SQLADM @$SCRIPT_TO_RUN $f<<EOF
EOF
  ERRORCODE=$(( $? + $NO_ERROR ))
  if [ $ERRORCODE -eq $ALREADY_INSTALLED ]; then
    echo "[INFO] Skipped script $f - already installed"
    echo "[INFO] ------------------------------"
    continue
  elif [ $ERRORCODE -eq $ALREADY_EXISTS ]; then
    echo "[INFO] Skipped script $f - already exists"
    echo "[INFO] ------------------------------"
    continue
  elif [ $ERRORCODE -eq $COMPILATION_ERRORS ]; then
    echo "[ERROR] PL/SQL compilation errors, failed to install $f"
    echo "[ERROR] ##############################"
    exit $ERRORCODE
  elif [ $ERRORCODE -ne $NO_ERROR ]; then
    echo "[ERROR] SQL errors, failed to install $f!"
    echo "[ERROR] ##############################"
    exit $ERRORCODE
  else
    echo "[INFO] Successfully installed $f"
    echo "[INFO] ------------------------------"
  fi
done

CREATE USER

Örnek
Şöyle yaparız

DROP USER myuser CASCADE;
CREATE USER adv IDENTIFIED BY "2Ab}7C";
GRANT ALL PRIVILEGES TO myuser;
GRANT EXECUTE ON utl_file TO myuser;
COMMIT;
QUIT

GRANT SELECT ON

Örnek

Şöyle yaparız. DBA_SCHEDULER_JOBS Oracle tablosu

GRANT SELECT ON DBA_SCHEDULER_JOBS TO MYUSER;
GRANT SELECT ON v_$database TO MYUSER;

Açıklaması şöyle. Yani burada SYS'ye ait tablolara da erişim veriliyor.

v$database is actually a public synonym for the v_$database view owned by sys (checked in Oracle 12).