tnsping komutu

Örnek

Şöyle yaparız

$ tnsping CUST2DB

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 30-MAR-2022 03:25:26

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/export/home/oracle/products/12.2.0.1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = corelan-3-scanvip)
(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CUST2DB)))
OK (0 msec)

MINUS Operator

Örnek

Şöyle yaparız

SELECT 'FirstPart' FROM mytable t
MINUS
SELECT'FirstPart', FROM myothertable t;

SET - System Variables

verify off

Açıklaması şöyle

The VERIFY setting controls whether or not SQL*Plus displays before and after images of each line that contains a substitution variable.

Örnek

Şöyle yaparız

SQL> DEFINE dummy_char = 'X'
SQL> SELECT * FROM dual
  2   WHERE dummy = '&&dummy_char';
old   2:  where dummy = '&&dummy_char'
new   2:  where dummy = 'X'
D
-
X

Açıklaması şöyle

Notice that line 2, which contained the reference to the &&dummy_char substitution variable, was displayed before and after the reference to the variable was replaced by its value. If you don’t like this display, you can turn it off with SET VERIFY OFF.

Örnek

Şöyle yaparız

DEFINE OBJ_NAME="foo"
DECLARE
  N INTEGER := 0;
BEGIN
  SELECT COUNT(OBJECT_NAME) INTO N FROM USER_OBJECTS WHERE OBJECT_NAME='&OBJ_NAME' AND
    OBJECT_TYPE='FUNCTION';
  IF N != 0 THEN
    RAISE_APPLICATION_ERROR( -20001, '&OBJ_NAME ALREADY EXISTS!' );
  END IF;
END;
/

Çıktı olarak şunu alırız

SQL> @foo.SQL
old   4:  SELECT COUNT(OBJECT_NAME) INTO N FROM USER_OBJECTS WHERE OBJECT_NAME='&OBJ_NAME'
  AND OBJECT_TYPE='FUNCTION';
new   4:  SELECT COUNT(OBJECT_NAME) INTO N FROM USER_OBJECTS WHERE OBJECT_NAME='FOO_LIST'
  AND OBJECT_TYPE='FUNCTION';
old   6:  RAISE_APPLICATION_ERROR( -20001, '&OBJ_NAME ALREADY EXISTS!' );
new   6:  RAISE_APPLICATION_ERROR( -20001, 'GET_FALIST ALREADY EXISTS!' );

Örnek

Şöyle yaparız

SQL> set verify off
SQL> DECLARE
  2          inputData VARCHAR2(1024);
  3  BEGIN
  4          inputData :='&&inputData' ;
  5          Dbms_Output.put_line('Value entered is:' || inputData);
  6  END;
  7  /
Enter value for inputdata: sdf

PL/SQL procedure successfully completed.

SQL>

Çıktı olarak şunu alırız

Enter value for inputdata: check
old   4:         inputData :='&&inputData' ;
new   4:         inputData :='check' ;
Value entered is:check

PL/SQL procedure successfully completed.

ALL_SOURCE - Stored Function Kaynak Kodunu Gösterir

Örnek

Şöyle yaparız

SELECT type, text FROM all_source WHERE name = 'GET_FALIST' ORDER BY type, line;

Oracle Streams Asenkron Kullanım

Eğer asenkron kullanacaksak bu iki şekilde yapabiliyoruz

1. Asynchronous Distribution HotLog

2. Asynchronous AutoLog

Bazı adımlar şöyle

-- Publisher : Create the Change Sources
BEGIN
   DBMS_CDC_PUBLISH.CREATE_HOTLOG_CHANGE_SOURCE(
       change_source_name => 'MAILING_LIST',
       description => 'test source',
       source_database => 'source_db');
END;
/

-- Publisher : Create Change Sets
BEGIN
   DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(
       change_set_name => 'DAILY_MAILING_LIST',
       description => 'change set for contact info',
       change_source_name => 'MAILING_LIST',
       stop_on_ddl => 'y');
END;
/
-- Publisher: Create the Change Tables
BEGIN
   DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
       owner             => 'staging_streamspub',
       change_table_name => 'contacts_ct',
       change_set_name   => 'DAILY_MAILING_LIST',
       source_schema     => 'SH',
       source_table      => 'CONTACTS',
       column_type_list  => 'PHONE_NUMBER NUMBER(10), CONTACT_NAME VARCHAR2(50),
          ZIP_CODE NUMBER(5),
          EMAIL_ADDRESS VARCHAR2(15), HOME_ADDRESS VARCHAR(20)',
       capture_values    => 'both',
       rs_id             => 'y',
       row_id            => 'n',
       user_id           => 'n',
       timestamp         => 'n',
       object_id         => 'n',
       source_colmap     => 'n',
       target_colmap     => 'y',
       options_string    => 'TABLESPACE TS_DAILY_MAILING_LIST');
END;
/
--Publisher: Enable the Change Source
BEGIN
  DBMS_CDC_PUBLISH.ALTER_HOTLOG_CHANGE_SOURCE(
    change_source_name => 'DAILY_MAILING_LIST',
    enable_source      => 'Y');
END;
/

Publisher: Grant Access to Subscribers
GRANT SELECT ON staging_cdcpub.products_ct TO subscriber1;

Varrays - Variable-Size Arrays

Örnek

Şöyle yaparız. Varray null değer için "" karakteri basar.

DECLARE type type_ntt IS TABLE OF NUMBER;

l_ntt_var1 type_ntt:=type_ntt();

BEGIN
  l_ntt_var1.extend();
  l_ntt_var1(1):=857;
  l_ntt_var1.extend(3);
  l_ntt_var1.extend(3,1);

  FOR index IN 1..l_ntt_var1.count
  LOOP
    dbms_output.put_line('Index value ['||ndex||']: Element value - '||l_ntt_var1(ndex));
  END LOOP loop_ntt;
END;
/
dbms_output:
Index value [1]: Element value - 857
Index value [2]: Element value - 
Index value [3]: Element value - 
Index value [4]: Element value - 
Index value [5]: Element value - 857
Index value [6]: Element value - 857
Index value [7]: Element value - 857

Örnek

Şöyle yaparız

type T_EXPIRATIONDATES is table of varchar2(14);
EXPIRATIONDATES T_EXPIRATIONDATES;

EXPIRATIONDATES := T_EXPIRATIONDATES();
EXPIRATIONDATES.EXTEND(100);

//Iterate over cursor
for REGISTRATIONS_ROW in C_REGISTRATIONS(FUNCTIONS_ROW.FUNCTION_ID) loop
  EXPIRATIONDATES(REGISTRATIONS_ROW.FUNCTIONCODE+1) := REGISTRATIONS_ROW.EXPIRATIONDATE;
end loop;

OUT_EXPIRATIONDATES_ROW VARCHAR2(32767);

for IDX in 1..100 loop
  OUT_EXPIRATIONDATES_ROW := OUT_EXPIRATIONDATES_ROW||'|'||EXPIRATIONDATES(IDX);
end loop;

TRUNCATE TABLE

Giriş

Belirtilen tabloyu boşaltır. MySQL ile de aynı şekilde kullanılır

Örnek

TRUNCATE TABLE get_falist;