Banner 9 clear Global, setGlobal variables

Banner 9 clear Global, setGlobal variables

setGlobal (key, value);

removeGlobal (key);

Oracle index hint syntax

select /*+ index(customer cust_primary_key_idx) */ * from customer;

Also note that of you alias the table, you must use the alias in the index hint:
select /*+ index(c cust_primary_key_idx) */ * from customer c;

Also, be vary of issuing hints that conflict with an index hint.  In this index hint example, the full hint is not consistent with an index hint:
select /*+ full(c) index(c cust_primary_key_idx) */ * from customer c;

https://docs.oracle.com/cd/B19306_01/server.102/b14211/hintsref.htm#i8327

https://logicalread.com/using-index-hints-to-tune-oracle-performance-h01/#.W3zv984zbIU

weblogic.common.ResourceException: Could not create pool connection. The DBMS driver exception was: null in Weblogic 10.3

weblogic.common.ResourceException: Could not create pool connection. The DBMS driver exception was: null  in Weblogic 10.3


1. Check DataSource xml It's in the domain's

config/jdbc directory. 

2.     Check DB Port 

3. Check user=xxx in properties

4. Some times JRF template causing issue due to ojdbcdms.jar is loaded first 

       Solution: - remove JRF

                      - or exclude ojdbcdms.jar from CLASSPATH

                      - or Upgrade to Weblogic12c

SQLPlus output to a file

SQLPlus output to a file

spool c:/output.txt

turn off:

spool off

Oracle PLSQL Cursor Duplicate of final Record

Oracle PLSQL Cursor Duplicate of final Record

loop
 fetch s_cur into s_rec;
 exit  when s_cur%notfound;
  dbms_output.put_line('id:'||s_rec.id||':name:'||s_rec.name);
end loop;

When you have fetched the last record and trying to fetch the next s_cur%notfoundbecomes true but before it has a chance to exit from loop you are outputting the last record once again.

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;/