Replace duplicate values only in consecutive records with NULL

-- Replace duplicate values only in consecutive records with NULL [duplicate]-- SELECT CASE WHEN lag(tran_id) over(order by NULL) = tran_id THEN NULL ELSE tran_id END tran_id, CASE WHEN lag(tran_name) over(order by NULL) = tran_name THEN NULL ELSE tran_name END tran_name, flag FROM t; TRAN_ID TRAN_N F ---------- ------ - 101 Lend A B C D … Continue reading Replace duplicate values only in consecutive records with NULL

ORDS Create Basic Authentication for a RESTful Web Service

ORDS Basic Authentication 1. To protect the web service, we need to create a role with an associated privilege, then map the privilege to the web service BEGIN ORDS.create_role( p_role_name => 'boomi_role' ); COMMIT; END; / -- Display the role. COLUMN name FORMAT A20 SELECT id, name FROM user_ords_roles WHERE name = 'boomi_role'; ID NAME … Continue reading ORDS Create Basic Authentication for a RESTful Web Service

ORDS 18.2 – The database user for the connection pool named apex_pu, is not authorized to proxy to the schema named

ORDS Version : 18.2 Oracle Version : 12cR2 Error : The database user for the connection pool named apex_pu, is not authorized to proxy to the schema named HR Desc: The error occurs when you have published a RESTful web service from a table inside a schema. When you try to call the Webs service … Continue reading ORDS 18.2 – The database user for the connection pool named apex_pu, is not authorized to proxy to the schema named

Install Oracle 18c(18.3) on CentOS 7.5 Silent Mode

Linux Version : CentOS 7.5 Oracle Version : 18c (18.3.0.0) 1. Download the Zip file from Oracle Website and create groups and oracle user(https://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle18c-linux-180000-5022980.html ) ## groupadd oinstall groupadd dba useradd oracle -g oinstall -G dba passwd oracle Create .bash_profile , in my test server i use 3 different Oracle homes and versions, so the … Continue reading Install Oracle 18c(18.3) on CentOS 7.5 Silent Mode

Oracle 18c (18.3) DBCA Issue – [DBT-50000] [DBT-50001]

While creating a on-premise 18c (18.3) database with DBCA in the silent mode I got the below error dbca -createDatabase -silent -gdbName ora18c -templateName General_Purpose.dbc -sysPassword sys123 -systemPassword sys123 -dbsnmpPassword sys123 -datafileDestination /u01/oradata -storageType FS -memoryPercentage 20 -emConfiguration NONE -sampleSchema false [FATAL] [DBT-50000] Unable to check for available memory. [FATAL] [DBT-50001] Unable to check the … Continue reading Oracle 18c (18.3) DBCA Issue – [DBT-50000] [DBT-50001]

SCP and Pass Variable in Bash Script

#!/bin/bash read -p 'Enter the Files to be Send to Remote Host: ' files destination_directory='/home/oracle' scp -i /Users/shadab/wha.pem $files oracle@192.168.1.200:$destination_directory ## SAMPLE USAGE ## # shadabs-MacBook-Pro:~ shadab$ ./scp_files.sh #Enter the Files to be Send to Remote Host: *.sh #scp_files.sh 100% 344 39.1KB/s 00:00 #scp_files_gone.sh 100% 344 52.3KB/s 00:00 # # #shadabs-MacBook-Pro:~ shadab$ ./scp_files.sh #Enter the … Continue reading SCP and Pass Variable in Bash Script

Multiple Homes and Instances Oracle Database Bash Profile

# .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs export CATALINA_HOME=/home/oracle/apache-tomcat PATH=/bin:/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:/bin/bash:$CATALINA_HOME/bin. export PATH EDITOR=vi export EDITOR=vi if [ -s "$MAIL" ] # This is at Shell startup. In normal then echo "$MAILMSG" # operation, the Shell checks fi # … Continue reading Multiple Homes and Instances Oracle Database Bash Profile

Multiple Oracle Databases and Homes on Same Listener

On my test server I have got 2 ORACLE_HOMES 12cR1 (12.1.0.2) and 12cR2 (12.2.0.1) My listener.ora file on my server looks something like below SID_LIST_LISTENER_12CR2 = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ora12cr2) (ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1) (SID_NAME = ora12cr2) ) ) LISTENER_12CR2 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.196)(PORT = … Continue reading Multiple Oracle Databases and Homes on Same Listener

Add Image to Column from URL Column Value Apex

Create a new interactive report based on SQL Query. Make sure the column where image is store is populated in the column 'PHOTO' of the table Go to Columns > Column Formatting and add to HTML Expression below code <img src="#PHOTO#" alt="Image Not Found" height="200" width="260"> - 'PHOTO' is the name of the column which … Continue reading Add Image to Column from URL Column Value Apex

A Very Cool Oracle Bash Profile

# .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/.local/bin:$HOME/bin export PATH # Oracle Settings export TMP=/tmp export ORACLE_HOSTNAME=easyoradba.com export ORACLE_UNQNAME=ora12c export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/12.2.0/dbhome_1 export ORACLE_SID=ora12c export CATALINA_HOME=/home/oracle/apache-tomcat PATH=/usr/sbin:$PATH:$ORACLE_HOME/bin:$CATALINA_HOME/bin export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; alias ob='cd $ORACLE_BASE' alias oh='cd $ORACLE_HOME' … Continue reading A Very Cool Oracle Bash Profile