Sql - comma function

 CREATE OR REPLACE FUNCTION EX_number_format( ff number) RETURN char IS

   xx varchar2(100);

   yy varchar2(1);

   tt varchar2(100);

  x number:=0;

  begin

   xx:=to_char(ff,'99G990G990G990G990G990D00');

  for i  in 1..length(xx) loop

   yy:=substr(xx,i,1);

 if yy in ('1','2','3','4','5','6','7','8','9') then

  x:=i;

 exit;

 end if;

 end loop;

 tt:=substr(xx,x,length(xx));

return tt;

 END EX_number_format;

19c- Install Oracle database version 19.3.0 on Linux OS

Basic Configuration
=======================================
---host configuration---
[root@ol8dbs ~]# vi etc/hosts
set ip and hosts
192.168.192.128 ol8dbs.localdomain ol8dbs
[root@ol8dbs ~]# vi etc/hostname 
ol8dbs.localdomain
---set selinux--
[root@ol8dbs ~]# vi /etc/selinux/config
selinux=permissive
---set fireawalld disable--
[root@ol8dbs ~]# systemctl stop firewalld
[root@ol8dbs ~]# systemctl disable firewalld
[root@ol8dbs ~]# systemctl start sshd.service
[root@ol8dbs ~]# systemctl enable sshd.service
---Prrequisite for oracle install--
[root@ol8dbs ~]# dnf install -y oracle-database-preinstall-19c
--# New for OL8--
dnf install -y libnsl
dnf install -y libnsl.i686
dnf install -y libnsl2
dnf install -y libnsl2.i686
--- Create the new group and users---
groupadd -g 54321 oinstall
groupadd -g 54322 dba
groupadd -g 54323 oper
useradd -u 54321 -g oinstall -G dba,oper oracle

--set password for oracle user-- 
passwd oracle
oracle123
---Create the directories in which the Oracle software will be installed:---
[root@ol8dbs ~]# mkdir -p /u01/app/oracle/product/19.0.0/dbhome_1
[root@ol8dbs ~]# mkdir -p /u01/oradata
[root@ol8dbs ~]# chown -R oracle:oinstall /u01
[root@ol8dbs ~]# chmod -R 775 /u01

-- Create a script directory--
mkdir /home/oracle/scripts
[root@ol8dbs ~]# cat> /home/oracle/scripts/setEnv.sh <<EOF

# Oracle Settings
export TMP=/tmp
export TMPDIR=\$TMP
export ORACLE_HOSTNAME=ifsrepdb.ifadgroup.com
export ORACLE_UNQNAME=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=\$ORACLE_BASE/product/19.0.0/dbhome_1
export ORA_INVENTORY=/u01/app/oraInventory
export ORACLE_SID=orcl
# export PDB_NAME=orclpdb
export DATA_DIR=/u01/oradata
export PATH=/usr/sbin:/usr/local/bin:\$PATH
export PATH=\$ORACLE_HOME/bin:\$PATH
export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=\$ORACLE_HOME/jlib:\$ORACLE_HOME/rdbms/jlib
EOF




Add a reference to the "setEnv.sh" file at the end of the "/home/oracle/.bash_profile" file:
----------------------
[root@ol8dbs ~]# cd /home/oracle/scripts 
echo ". /home/oracle/scripts/setEnv.sh" >> /home/oracle/.bash_profile
Create a "start_all.sh" and "stop_all.sh" script that can be called from a startup/shutdown service. Make sure the ownership and permissions are correct.--
---------------
cat > /home/oracle/scripts/start_all.sh <<EOF
#!/bin/bash
. /home/oracle/scripts/setEnv.sh
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES
dbstart \$ORACLE_HOME
EOF
cat > /home/oracle/scripts/stop_all.sh <<EOF
#!/bin/bash
. /home/oracle/scripts/setEnv.sh
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES
dbshut \$ORACLE_HOME
EOF

chown -R oracle:oinstall /home/oracle/scripts
chmod u+x /home/oracle/scripts/*.sh


Install Oracle Database 19c
=========================================
switch oralce user 
su - oracle 
[oracle@ol8dbs ~] echo $ORACLE_HOME
[oracle@ol8dbs ~] source scripts/setEnv.sh
[oracle@ol8dbs ~] echo $ORACLE_HOME
cd $ORACLE_HOME
or cd /u01/app/oracle/product/19.0.0/dbhome_1
# unzip
# This code open blank terminal 
unzip -oq /home/oracle/Downloads/LINUX.X64_193000_db_home.zip
# go to /u01/app/oracle/product/19.0.0/dbhome_1 and open terminal 
unzip /home/oracle/Downloads/LINUX.X64_193000_db_home.zip



export DISPLAY=:0
xhost +
# Fake Oracle Linux 7.
export CV_ASSUME_DISTID=OEL7.6
# go to /u01/app/oracle/product/19.0.0/dbhome_1/cv/admin
# cvu_config
CV_ASSUME_DISTID=OLE8

# MobaXterm
./runInstaller



Conn: sys as sysdba
show con_name

[oracle@ol8dbs ~]$ lsnrctl status
[oracle@ol8dbs ~]$ lsnrctl stop
[oracle@ol8dbs ~]$ lsnrctl start
[oracle@ol8dbs ~]$ source scripts/setEnv.sh
[oracle@ol8dbs ~]$ netmgr  






pass dan
Sys@123#
root
root123 
yes
DOne

https://oracle-base.com/articles/19c/oracle-db-19c-installation-on-oracle-linux-8

crond scheduler in oracle dba

--u02/schedule_scripts -- where is file save .sh  

 .sh 
#/bin/bash
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=***
export ORACLE_UNQNAME=***
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:/usr/local/bin:/usr/sbin:/usr/bin:/usr/openwin/bin:/usr/ucb:$ORACLE_HOME/bin
#export ORACLE_BASE ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH PATH
sqlplus scott/tiger <<EOF
exec procedure_name;
exit
EOF
=====================================================================
 
.sh 
#/bin/bash
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export ORACLE_SID=orcl
export ORACLE_UNQNAME=orcl
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:/usr/local/bin:/usr/sbin:/usr/bin:/usr/openwin/bin:/usr/ucb:$ORACLE_HOME/bin
#export ORACLE_BASE ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH PATH
sqlplus reportdb/r <<EOF
 
# write here your program code
insert into test_tab(id , edate)
select 1, sysdate from dual;
commit;
exit
EOF

--u02/schedule_scripts --crontab -e where is file in 

15 20 * * * /u02/schedule_scripts/test_crond.sh >>/u02/schedule_scripts/error_Log.txt

# go to folder to then run

sh  test_crond.sh 

//////////////////////////////////

# Oracle Settings

export TMP=/tmp

export TMPDIR=$TMP

export ORACLE_HOSTNAME=ol8dbs.localdomain

export ORACLE_UNQNAME=orcl

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1

export ORA_INVENTORY=/u01/app/oraInventory

export ORACLE_SID=orcl

export PDB_NAME=orclpdb

export DATA_DIR=/u01/oradata

export PATH=/usr/sbin:/usr/local/bin:$PATH

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib