Connect Oracle DB remotely by sqlplus

How to Connect Oracle DB remotely by sqlplus

1. Update tnsnames.ora with Oracle server you want to connect

C:\Oracle\Middleware\Oracle_Home\network\admin\tnsnames.ora


ex
OraServer1=

(DESCRIPTION =

  (ADDRESS = (PROTOCOL = TCP)(HOST = 203.292.222.1)(PORT = 1521))

  (LOAD_BALANCE = YES)

  (CONNECT_DATA =

   (SERVER = DEDICATED)

   (SERVICE_NAME = DEV1

   (FAILOVER_MODE =

   (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5)

   )

  )

)


2. Command line

sqlplus username/passs@OraServer1

How To Create Oracle ORDS REST webservice - APEX PLSQL

How To Create Oracle ORDS REST webservice - APEX PLSQL

• Schema: EMPLOYEE
• Module: restws
• Template: transfer

Handler: POST

Enable ORDS Schema

BEGIN

ORDS.ENABLE_SCHEMA(

     p_enabled             => TRUE,

     p_schema              => 'EMPLOYEE',

     p_url_mapping_type    => 'BASE_PATH',

     p_url_mapping_pattern => 'restws',

     p_auto_rest_auth      => FALSE);

END;

/

Create ORDs Module

BEGIN

ORDS.DEFINE_MODULE(

     p_module_name    => 'emp.v1',

     p_base_path      => '/emp.v1/',

     p_items_per_page =>  25,

     p_status         => 'PUBLISHED',

     p_comments       => NULL);

END;

/

Create ORDS Templates

BEGIN

ORDS.DEFINE_TEMPLATE(

     p_module_name    => 'emp.v1',

     p_pattern        => ‘create’,

     p_priority       => 0,

     p_etag_type      => 'HASH',

     p_etag_query     => NULL,

     p_comments       => NULL);

END;

Create ORDS Handler

BEGIN

 ORDS.DEFINE_HANDLER(

     p_module_name    => 'emp.v1',

     p_pattern        => 'update',

     p_method         => 'POST',

     p_source_type    => 'plsql/block',

     p_items_per_page =>  0,

     p_mimes_allowed  => '',

     p_comments       => NULL,

     p_source         =>

'BEGIN

  

- call any plsql package or do insert/update .....

status := 'OK';

END;'

END;

/

Add Response Params

BEGIN

ORDS.DEFINE_PARAMETER(

  p_module_name        => 'asset.track.v1',

     p_pattern            => 'transfer',

     p_method             => 'POST',

     p_name               => 'X-APEX-STATUS-CODE', -- or any Name

     p_bind_variable_name => 'status',

     p_source_type        => 'RESPONSE',

     p_param_type         => 'INT',

     p_access_method      => 'OUT',

     p_comments           => NULL);

END;/

X-APEX-STATUS-CODE Rest response in PLSQL

X-APEX-STATUS-CODE Rest response in PLSQL

BEGIN

ORDS.DEFINE_PARAMETER(

  p_module_name        => 'assetws',

     p_pattern            => 'transfer',

     p_method             => 'POST',

     p_name               => 'X-APEX-STATUS-CODE',

     p_bind_variable_name => 'status',

     p_source_type        => 'RESPONSE',

     p_param_type         => 'INT',

     p_access_method      => 'OUT',

     p_comments           => NULL);

 ORDS.DEFINE_PARAMETER(

     p_module_name        => 'assetws',

     p_pattern            => 'transfer',

     p_method             => 'POST',

     p_name               => 'alert',

     p_bind_variable_name => 'alert',

     p_source_type        => 'HEADER',

     p_param_type         => 'STRING',

     p_access_method      => 'IN',

     p_comments           => NULL);

END;

Maven update project dependencies

Maven update project dependencies

mvn dependency:resolve

Or  single dependency:
mvn dependency:get -Dartifact=groupId:artifactId:version




mvn clean install -U

-U means force update of dependencies.

can not create Oracle user ORA-65096: invalid common user or role name in oracle

ORA-65096: invalid common user or role name in oracle

can not create Oracle user 

Fix

alter session set "_oracle_script"=true;

How to Stop firewall in Linux, Oracle Linux

How to Stop firewall in Linux, Oracle Linux

Oracle Linux 7, Redhat 7

service firewalld stop
systemctl disable firewalld

Oracle linux 7, Redhat 6

chkconfig iptables off

Oracle APEX info message and error message from PL/SQL

Oracle APEX info message and error message from PL/SQL

Info message
pex_application.g_print_success_message := '<span style="color:green">'|| result ||'</span>';


Error message

DECLARE
 sMsg VARCHAR2(40) := 'aaa';
BEGIN
  apex_error.add_error (
    p_message          => sMsg,
    p_display_location => apex_error.c_inline_in_notification );
END;