SQL Developer: Rest Development connection setup to DBCS and https

There are two possible hiccups with the setup of “Rest Development” with SQL Developer 18c, when trying to connect to some https respoint, for example running on a DBcs on OCI.

#1 PKIX path building failed trying to connect after https is specified, basically getting the the error below trying to connect to a RestData service:

Cannot connect to <XX>.
sun.security.validator.ValidatorException: PKIX path building failed: 
sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

 

The Solution is to add the destination server certificate to the cacerts file from the Java JDK embedded with SQL Developer, for example:

C:\Oracle\SQLDev\182\jdk\lib\security\cacerts
or
C:\Oracle\SQLDev\183\jdk\jre\lib\security\cacerts

 

At this point, the easiest  is to transfer that certificate file on Linux and run the following command before transferring it back to Windows.

$ORACLE_HOME/jdk/jre/bin/keytool -storepass changeit -import -trustcacerts -keystore cacerts -file <mycertificate> -alias "myalias"

 

#2 Invalid resource owner credentials during login

The username to be specified is a special user that one can create from the compute instance running ORDS:

cd /u01/app/oracle/product/ords/18.2
or
cd /u01/app/oracle/product/ords/18.3

then

java -jar ords.war user ords_dev   "SQL Developer"
and/or
java -jar ords.war user ords_admin "Listener Administrator"

The command above will store these credentials in the ORDS configuration directory, where the user_name will be specified in clear, for example

/u01/app/oracle/product/ords/config/ords/credentials

Then use ords_dev or ords_admin to connect to the restful admin service to either develop new services or administer the service

 

Note that when upgrading from Apex 18.1 to 18.2, this is a matter to copy the cacerts file into the new SQLdev directory.

Advertisements

ADW: dbms_cloud.copy_data and record delimiter

To import via dbms_cloud.copy_data a windows file that include window CR+LF at the end of each record, specify the record delimiter as follow:

BEGIN
DBMS_CLOUD.COPY_DATA
(table_name      => '<table>'
,credential_name => '<credential_name>'
,file_uri_list   => 'https://<restpoint>/<file>'
,schema_name     => '<schema>'
,format           => json_object('recorddelimiter'      value '''\\r\\n''')
);
END;
/

 

An invalid specification of this delimiter would return the error below:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 801
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 782
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "xxx": expecting one of: "double-quoted-string, hexprefix, newline, single-quoted-string"
KUP-01007: at line 2 column 2
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 757
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 819
ORA-06512: at line 2

OCI-c: Project Fn setup

Basically following the instructions posted in the fn project page:

 

#1 Setup a VM running OL7.2 with 50GB

Preferably pick OL72 over OL68 as fn require docker > 17.5

 

#2 Setup security policy (OL7)

# setenforce permission

 

#3 Install docker (root)

Follow the instructions posted in the documentation.

Update /etc/yum.repos.d/public-yum-ol7.repo to enable the ol7_addons then

# yum install docker-engine wget tree
# chkconfig docker on
# yum update
# service docker start

 

#4 Install fn (as root)

curl -LSs https://raw.githubusercontent.com/fnproject/cli/master/install | sh

 

 

#5 Create a runtime user and add it to the docker group

# useradd -g 500 -d /home/oracle oracle
# sudo usermod -a -G docker oracle

 

#6 Login as oracle and start the fn server

$ fn start

 

#5 Login again as oracle in a second window and configure a demo project

$ mkdir project
$ cd project
$ fn init --runtime go hello
$ cd hello
$ fn run
$ fn deploy --app myapp --local
$ fn invoke myapp hello

Update the file func.go, changing Hello with bonjour, deploy and test again

$ fn deploy --app myapp --local
$ fn invoke myapp hello
{"message":"Bonjour World"}

 

 

 

DevCS: Storage configuration

When configuring the storage for a Developer Cloud Service, make sure that the user password is reasonable short.

If not, the error “Cannot add configuration. Check the credentials and Authorization URL” may be returned.

In addition, make sure that the Authorization URL uses the protocol https, or the other error may be returned:

Unable to authenticate. Check the Service ID and/or credentials