MacLochlainns Weblog

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Jan 7 21:00:42 2022
Version 18.4.0.0.0
 
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 
ERROR:
ORA-12541: TNS:no listener

lsnrctl start

LSNRCTL FOR 64-bit Windows: Version 18.0.0.0.0 - Production ON 07-JAN-2022 21:02:20
 
Copyright (c) 1991, 2018, Oracle.  ALL rights reserved.
 
Starting tnslsnr: please wait...
 
Unable TO OpenSCManager: err=5
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error

127.0.0.1      localhost
::1            localhost

C:\Windows\system32\drivers\etc\hosts

# listener.ora Network Configuration File: C:\app\username\product\21.0.0\dbhomeXE\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.
 
DEFAULT_SERVICE_LISTENER = XE
 
SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (SID_NAME = CLRExtProc)
     (ORACLE_HOME = C:\app\username\product\21.0.0\dbhomeXE)
     (PROGRAM = extproc)
     (ENVS = "EXTPROC_DLLS=ONLY:C:\app\username\product\21.0.0\dbhomeXE\bin\oraclr21.dll")
   )
 )
 
LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
   )
 )

# tnsnames.ora Network Configuration File: C:\app\mclaughlinm\product\21.0.0\dbhomeXE\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
 
XE =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = XE)
   )
 )
 
LISTENER_XE =
 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
 
 
ORACLR_CONNECTION_DATA =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
   )
   (CONNECT_DATA =
     (SID = CLRExtProc)
     (PRESENTATION = RO)
   )
 )

sqlplus ()
{ 
    path=`which rlwrap 2>/dev/null`;
    file='';
    if [ -n ${path} ]; then
        file=${path##/*/};
    fi;
    if [ -n ${file} ] && [[ ${file} = "rlwrap" ]]; then
        rlwrap sqlplus "${@}";
    else
        echo "Command-line history unavailable: Install the rlwrap package.";
        $ORACLE_HOME/bin/sqlplus "${@}";
    fi
}

CREATE OR REPLACE
  TYPE list IS TABLE OF VARCHAR2(10);
/

CREATE OR REPLACE
  FUNCTION defrag
  ( sparse  LIST ) RETURN LIST IS
  /* Declare return collection. */
  dense  LIST := list();
BEGIN
  /* Mimic an iterator in the loop. */
  dense := sparse;
 
  /* Return the densely populated collection. */
  RETURN dense;
END defrag;
/

CREATE OR REPLACE
  FUNCTION defrag
  ( sparse  LIST ) RETURN LIST IS
  /* Declare return collection. */
  dense  LIST := list();
 
  /* Declare a current index variable. */
  CURRENT  NUMBER;
BEGIN
  /* Mimic an iterator in the loop. */
  CURRENT := sparse.FIRST;
  WHILE NOT (CURRENT > sparse.LAST) LOOP
    dense.EXTEND;
    dense(dense.COUNT) := sparse(CURRENT);
    CURRENT := sparse.NEXT(CURRENT);
  END LOOP;
  /* Return the densely populated collection. */
  RETURN dense;
END defrag;
/

DECLARE  
  /* Declare the collection. */
  lv_list  LIST := list('Moe','Shemp','Larry','Curly');
 
  /* Declare a current index variable. */
  CURRENT  NUMBER;
BEGIN
  /* Create a gap in the densely populated index. */
  lv_list.DELETE(2);
 
  /* Mimic an iterator in the loop. */
  CURRENT := lv_list.FIRST;
  WHILE NOT (CURRENT > lv_list.LAST) LOOP
    dbms_output.put_line('['||CURRENT||']['||lv_list(CURRENT)||']');
    CURRENT := lv_list.NEXT(CURRENT);
  END LOOP;
 
  /* Print a line break. */
  dbms_output.put_line('----------------------------------------');
 
  /* Call defrag function. */
  lv_list := defrag(lv_list);
 
  FOR i IN 1..lv_list.COUNT LOOP
    dbms_output.put_line('['||i||']['||lv_list(i)||']');
  END LOOP;
END;
/

[1][Moe]
[3][Larry]
[4][Curly]
----------------------------------------
[1][Moe]
[2][Larry]
[3][Curly]

DECLARE
  /* Create a local table collection. */
  TYPE list IS TABLE OF VARCHAR2(10);
 
  /* Declare the collection. */
  lv_list  LIST := list('Moe','Shemp','Larry','Curly');
 
  /* Declare a current index variable. */
  CURRENT  NUMBER;
BEGIN
  /* Create a gap in the densely populated index. */
  lv_list.DELETE(2);
 
  /* Mimic an iterator in the loop. */
  CURRENT := lv_list.FIRST;
  WHILE NOT (CURRENT > lv_list.LAST) LOOP
    dbms_output.put_line('['||CURRENT||']['||lv_list(CURRENT)||']');
    CURRENT := lv_list.NEXT(CURRENT);
  END LOOP;
END;
/

DECLARE
  /* Create a local table collection. */
  TYPE list IS TABLE OF VARCHAR2(10);
 
  /* Declare the collection. */
  lv_list  LIST := list('Moe','Shemp','Larry','Curly');
 
  /* Declare a current index variable. */
  CURRENT  NUMBER;
BEGIN
  /* Create a gap in the densely populated index. */
  lv_list.DELETE(2);
 
  /* Mimic an iterator in the loop. */
  CURRENT := lv_list.LAST;
  WHILE NOT (CURRENT < lv_list.FIRST) LOOP
    dbms_output.put_line('['||CURRENT||']['||lv_list(CURRENT)||']');
    CURRENT := lv_list.PRIOR(CURRENT);
  END LOOP;
END;
/

DECLARE
  /* Create a local associative array type. */
  TYPE list IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(10);
 
  /* Define a variable of the associative array type. */
  lv_list  LIST; --  := list('Moe','Shemp','Larry','Curly');
 
  /* Declare a current index variable. */
  CURRENT  VARCHAR2(5);
BEGIN
  /* Assign values to an associative array (PL/SQL structure). */
  lv_list('One') := 'Moe';
  lv_list('Two') := 'Shemp';
  lv_list('Three') := 'Larry';
  lv_list('Four') := 'Curly';
 
  /* Mimic iterator. */
  CURRENT := lv_list.FIRST;
  dbms_output.put_line('Debug '||CURRENT);
  WHILE NOT (CURRENT < lv_list.LAST) LOOP
    dbms_output.put_line('['||CURRENT||']['||lv_list(CURRENT)||']');
    CURRENT := lv_list.NEXT(CURRENT);
  END LOOP;
END;
/

sqlplus -s student/student @call.sql

SQL> DECLARE
  2    lv_input  VARCHAR2(20);
  3  BEGIN
  4    lv_input := '&1';
  5    dbms_output.put_line('['||lv_input||']');
  6  END;
  7  /

Enter value for 1: machine
old   4:   lv_input := '&1';
new   4:   lv_input := 'machine';
[machine]
 
PL/SQL procedure successfully completed.

Enter value for mystery: machine
old   4:   lv_input := '&mystery';
new   4:   lv_input := 'machine';
[machine]
 
PL/SQL procedure successfully completed.

Enter value for mystery: machine
old   4:   lv_input := &mystery;
new   4:   lv_input := machine;
  lv_input := machine;
              *
ERROR at line 4:
ORA-06550: line 4, column 15:
PLS-00201: identifier 'MACHINE' must be declared
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored

SQL> DECLARE
  2    lv_input  VARCHAR2(20);
  3    machine   VARCHAR2(20) := 'Mystery Machine';
  4  BEGIN
  5    lv_input := &mystery;
  6    dbms_output.put_line('['||lv_input||']');
  7  END;
  8  /

Enter value for mystery: machine
old   5:   lv_input := &mystery;
new   5:   lv_input := machine;
[Mystery Machine]
 
PL/SQL procedure successfully completed.

DECLARE
  /* Create a local table collection. */
  TYPE list IS TABLE OF VARCHAR2(10);
 
  /* Statically allocate memory and assign values
     to for elements. */
  lv_list  LIST := list('Moe','Shemp','Larry','Curly');
BEGIN
  /* Remove the second element, Shemp, from the 
     collection of variable length strings. */
  lv_list.DELETE(2);
 
  /* Loop through the target with a for loop, which
     depends on densely populated index values. */
  FOR i IN 1..lv_list.COUNT LOOP
    dbms_output.put_line('['||lv_list(i)||']');
  END LOOP;
END;
/

[Moe]
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 16

  FOR i IN 1..lv_list.COUNT LOOP
    IF lv_list.EXISTS(i) THEN
      dbms_output.put_line('['||lv_list(i)||']');
    END IF;
  END LOOP;

[Moe]
[Larry]

  FOR i IN 1..lv_list.LAST LOOP
    IF lv_list.EXISTS(i) THEN
      dbms_output.put_line('['||lv_list(i)||']');
    END IF;
  END LOOP;

[Moe]
[Larry]
[Curly]

DECLARE
  /* Create a local table collection. */
  TYPE list IS TABLE OF VARCHAR2(10);
 
  /* Statically allocate memory and assign values
     to for elements. */
  lv_list  LIST := list('Moe','Shemp','Larry','Curly');
 
  /* Declare a current index variable. */
  CURRENT  NUMBER;
BEGIN
  /* Remove the second element, Shemp, from the 
     collection of variable length strings. */
  lv_list.DELETE(2);
 
  /* Loop through the target with a while loop, which
     doesn't depend on densely populated index values
     by setting the starting index value and increment
     as if with an iterator. */
  CURRENT := lv_list.FIRST;
  WHILE NOT (CURRENT > lv_list.LAST) LOOP
    dbms_output.put_line('['||lv_list(CURRENT)||']');
    CURRENT := lv_list.NEXT(CURRENT);
  END LOOP;
END;
/

[Moe]
[Larry]
[Curly]

  CURRENT := lv_list.LAST;
  WHILE NOT (CURRENT < lv_list.FIRST) LOOP
    dbms_output.put_line('['||lv_list(CURRENT)||']');
    CURRENT := lv_list.PRIOR(CURRENT);
  END LOOP;

[Curly]
[Larry]
[Moe]

CREATE OR REPLACE
  TYPE list IS TABLE OF VARCHAR2(30);
/

CREATE OR REPLACE
  FUNCTION pack
  ( pv_list  LIST ) RETURN list IS
 
  /* Declare a new list. */
  lv_new  LIST  := list();
BEGIN
  /* Read, check, and pack an old list into a new one. */
  FOR i IN 1..pv_list.LAST LOOP
    IF pv_list.EXISTS(i) THEN
      lv_new.EXTEND;
      lv_new(lv_new.COUNT) := pv_list(i);
    END IF;
  END LOOP;
  RETURN lv_new;
END;
/

DECLARE
  /* Declare a list value. */
  lv_test  LIST := list('Moe','Shemp','Larry','Curly');
BEGIN
  /* Remove one element in the middle. */
  lv_test.DELETE(2);
 
  /* Pack the list of elements into a sequence of values. */
  lv_test := pack(lv_test);
 
  /* Print the list of elements from the packed list. */
  FOR i IN 1..lv_test.COUNT LOOP
    dbms_output.put_line('['||lv_test(i)||']');
  END LOOP;
END;
/

[Moe]
[Larry]
[Curly]

CREATE OR REPLACE
  TYPE base_t IS OBJECT
  ( oid  NUMBER )
  INSTANTIABLE NOT FINAL;
/
 
CREATE OR REPLACE
  TYPE base_list IS TABLE OF base_t;
/

CREATE OR REPLACE
  TYPE book_t UNDER base_t
  ( title  VARCHAR2(30)
  , COST   NUMBER);
/
 
CREATE OR REPLACE
  TYPE book_list IS TABLE OF book_t;
/

COL oid   FORMAT 999
COL title FORMAT A20
COL COST  FORMAT 99.99
SELECT *
FROM   TABLE(book_list(book_t(1,'Neuromancer',15.30)
                      ,book_t(2,'Count Zero',7.99)
                      ,book_t(3,'Mona Lisa Overdrive',7.99)
                      ,book_t(4,'Burning Chrome',8.89)));

 OID TITLE                  COST
---- -------------------- ------
   1 Neuromancer           15.30
   2 Count Zero             7.99
   3 Mona Lisa Overdrive    7.99
   4 Burning Chrome         8.89

CREATE OR REPLACE
  PACKAGE packer IS
 
  /* A simple ADT list of strings. */
  FUNCTION pack
  ( pv_list  LIST ) RETURN list;
 
  /* A UDT list of base objects. */
  FUNCTION pack
  ( pv_list  BASE_LIST ) RETURN base_list;
 
  /* A UDT list of subtype objects. */
  FUNCTION pack
  ( pv_list  BOOK_LIST ) RETURN book_list;
 
END;
/

CREATE OR REPLACE
  PACKAGE BODY packer IS
 
  /* A simple ADT list of strings. */
  FUNCTION pack
  ( pv_list  LIST ) RETURN list IS
 
    /* Declare a new list. */
    lv_new  LIST  := list();
  BEGIN
    /* Read, check, and pack an old list into a new one. */
    FOR i IN 1..pv_list.LAST LOOP
      IF pv_list.EXISTS(i) THEN
        lv_new.EXTEND;
        lv_new(lv_new.COUNT) := pv_list(i);
      END IF;
    END LOOP;
    RETURN lv_new;
  END pack;
 
  /* A simple ADT list of strings. */
  FUNCTION pack
  ( pv_list  BASE_LIST ) RETURN base_list IS
 
    /* Declare a new list. */
    lv_new  BASE_LIST  := base_list();
  BEGIN
    /* Read, check, and pack an old list into a new one. */
    FOR i IN 1..pv_list.LAST LOOP
      IF pv_list.EXISTS(i) THEN
        lv_new.EXTEND;
        lv_new(lv_new.COUNT) := pv_list(i);
      END IF;
    END LOOP;
    RETURN lv_new;
  END pack;
 
  /* A simple ADT list of strings. */
  FUNCTION pack
  ( pv_list  BOOK_LIST ) RETURN book_list IS
 
    /* Declare a new list. */
    lv_new  BOOK_LIST  := book_list();
  BEGIN
    /* Read, check, and pack an old list into a new one. */
    FOR i IN 1..pv_list.LAST LOOP
      IF pv_list.EXISTS(i) THEN
        lv_new.EXTEND;
        lv_new(lv_new.COUNT) := pv_list(i);
      END IF;
    END LOOP;
    RETURN lv_new;
  END pack;
 
END packer;
/

DECLARE
  lv_test  BASE_LIST :=
    base_list(base_t(1),base_t(2)
             ,base_t(3),base_t(4));
BEGIN
  /* Remove one element in the middle. */
  lv_test.DELETE(2);
 
  /* Pack the list of elements into a sequence of values. */
  lv_test := packer.pack(lv_test);
 
  /* Print the list of elements from the packed list. */
  FOR i IN 1..lv_test.LAST LOOP
    dbms_output.put_line('['||lv_test(i).oid||']');
  END LOOP;
END;
/

[1]
[3]
[4]

DECLARE
  lv_test  BOOK_LIST := 
    book_list(book_t(1,'Neuromancer',15.30)
             ,book_t(2,'Count Zero',7.99)
             ,book_t(3,'Mona Lisa Overdrive',7.99)
             ,book_t(4,'Burning Chrome',8.89));
BEGIN
  /* Remove one element in the middle. */
  lv_test.DELETE(2);
 
  /* Pack the list of elements into a sequence of values. */
  lv_test := packer.pack(lv_test);
 
  /* Print the list of elements from the packed list. */
  FOR i IN 1..lv_test.LAST LOOP
    dbms_output.put_line( '['||lv_test(i).oid||']'
                        ||'['||lv_test(i).title||']'
                        ||'['||lv_test(i).COST||']');
  END LOOP;
END;
/

[1][Neuromancer][15.3]
[3][Mona Lisa Overdrive][7.99]
[4][Burning Chrome][8.89]

#!/usr/bin/bash
 
tnsping()
{
  if [ ! -z ${1} ]; then
    # Set default return value.
    stdout=`$ORACLE_HOME/bin/tnsping ${1} | tail -1`
 
    # Check stdout to return 0 for success and 1 for failure.
    if [[ `echo ${stdout} | cut -c1-9` = 'TNS-03505' ]]; then
      python -c 'import os, sys; arg = sys.argv[1]; os.write(2,arg + "\n")' "${stdout}"
    else
      echo "${1}"
    fi
  fi
}

#!/usr/bin/bash
 
tnsping()
{
  if [ ! -z ${1} ]; then
    # Set default return value.
    stdout=`$ORACLE_HOME/bin/tnsping ${1} | tail -1`
 
    # Check stdout to return 0 for success and 1 for failure.
    if [[ `echo ${stdout} | cut -c1-9` = 'TNS-03505' ]]; then
      echo ${stdout} 1>&2
    else
      echo "${1}"
    fi
  fi
}

tnsping wrong

TNS-03505: Failed to resolve name

tnsping wrong 2>/dev/null

source library.sh

 Oracle SQL Developer
 Copyright (c) 2005, 2018, Oracle and/or its affiliates. All rights reserved.
 
/opt/sqldeveloper/sqldeveloper/bin/../../ide/bin/launcher.sh: line 954: [: : integer expression expected
The JDK (/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.222.b10-0.fc30.x86_64/) is not a valid JDK.
The JDK was specified by a SetJavaHome directive in a .conf file or by a --setjavahome option.
Type the full pathname of a JDK installation (or Ctrl-C to quit), the path will be stored in /home/student/.sqldeveloper/19.2.0/product.conf
 
Error:  Unable to get APP_JAVA_HOME input from stdin after 10 tries

sqlplus () 
{
    # Discover the fully qualified program name. 
    path=`which rlwrap 2>/dev/null`
    file=''
 
    # Parse the program name from the path.
    if [ -n ${path} ]; then
        file=${path##/*/}
    fi;
 
    # Wrap when there is a file and it is rewrap.
    if [ -n ${file} ] && [[ ${file} = "rlwrap" ]]; then
        rlwrap sqlplus "${@}"
    else
        echo "Command-line history unavailable: Install the rlwrap package."
        $ORACLE_HOME/bin/sqlplus "${@}"
    fi
}

DECLARE
 /* Control variable. */
 container  BOOLEAN := FALSE;
 
 /* Weakly structured system reference cursor. */
 container_sql  SYS_REFCURSOR;
 
 /* Constant required for pre-container databases to avoid a
    a compile time error. */
 sql_statement  CONSTANT VARCHAR2(50) :=
                  'SELECT cdb FROM v$database WHERE cdb = ''YES''';
BEGIN
 /* Check if the current user is the superuser. */
 FOR i IN (SELECT USER FROM dual) LOOP
 
   /* Perform tasks as superuser. */
   IF i.USER = 'SYSTEM' THEN
     /* Check for a container-enabled column, which enables this to
        work in both pre-container Oracle databases, like 10g and 11g. */
     FOR j IN (SELECT DISTINCT column_name
               FROM   dba_tab_columns
               WHERE  column_name = 'CDB') LOOP
 
       /* Check for a container database, set control variable
          and exit when found. */
       OPEN container_sql FOR sql_statement;
       LOOP
         container := TRUE;
         EXIT WHEN container_sql%FOUND;
       END LOOP;
     END LOOP;
 
      /* Conditionally drop existing user and role. */   
      IF container THEN
        /* Conditionally drop a container user. */
        FOR j IN (SELECT username
                  FROM   dba_users
                  WHERE  username = 'C##STUDENT') LOOP
          EXECUTE IMMEDIATE 'DROP USER c##student CASCADE';
        END LOOP;
 
        /* Conditionally rop the container c##studentrole role. */
        FOR j IN (SELECT ROLE
                  FROM   dba_roles
                  WHERE  ROLE = 'C##STUDENTROLE') LOOP
          EXECUTE IMMEDIATE 'DROP ROLE c##studentrole';
        END LOOP;
 
        /* Create a container user with 200 MB of space. */
        EXECUTE IMMEDIATE 'CREATE USER c##student'||CHR(10)
        ||                'IDENTIFIED BY student'||CHR(10)
        ||                'DEFAULT TABLESPACE users'||CHR(10)
        ||                'QUOTA 200M ON users'||CHR(10)
        ||                'TEMPORARY TABLESPACE temp';
 
        /* Create a container role. */
        EXECUTE IMMEDIATE 'CREATE ROLE c##studentrole CONTAINER = ALL';
 
        /* Grant privileges to a container user. */
        EXECUTE IMMEDIATE 'GRANT CREATE CLUSTER, CREATE INDEXTYPE,'||CHR(10)
        ||                'CREATE PROCEDURE, CREATE SEQUENCE,'||CHR(10)
        ||                'CREATE SESSION, CREATE TABLE,'||CHR(10)
        ||                'CREATE TRIGGER, CREATE TYPE,'||CHR(10)
        ||                'CREATE VIEW TO c##studentrole';
 
        /* Grant role to user. */
        EXECUTE IMMEDIATE 'GRANT c##studentrole TO c##student';
      ELSE
        /* Conditonally drop the non-container database user. */
        FOR j IN (SELECT username
                  FROM   dba_users
                  WHERE  username = 'STUDENT') LOOP
          EXECUTE IMMEDIATE 'DROP USER student CASCADE';
        END LOOP;
 
        /* Create the student database. */
        EXECUTE IMMEDIATE 'CREATE USER student'||CHR(10)
        ||                'IDENTIFIED BY student'||CHR(10)
        ||                'DEFAULT TABLESPACE users'||CHR(10)
        ||                'QUOTA 200M ON users'||CHR(10)
        ||                'TEMPORARY TABLESPACE temp';
 
        /* Grant necessary privileges to the student database. */
        EXECUTE IMMEDIATE 'GRANT CREATE CLUSTER, CREATE INDEXTYPE,'||CHR(10)
        ||                'CREATE PROCEDURE, CREATE SEQUENCE,'||CHR(10)
        ||                'CREATE SESSION, CREATE TABLE,'||CHR(10)
        ||                'CREATE TRIGGER, CREATE TYPE,'||CHR(10)
        ||                'CREATE VIEW TO student';
       END IF;
    ELSE
      /* Print an message that the user lacks privilegs. */
      dbms_output.put_line('You must be the SYSTEM user to drop and create a user.');
    END IF;
  END LOOP;
END;
/