Difference Between Oracle 11g , 12c, 18c, 19c Versions And New Features

Version 11 g 
1. 11g have not PDB (Pluggable Database)
2. 11g (g for Grid Computing)
3. Released in 2008
4. Dose Not have Cloud Support
5. Oracle 11g does not provide in-memory capabilities
6. No JSON support 

Version 12c 
1. 12c starts PDB 
2. 12c (C for Cloud)
3. Released in 2014 
4. PDB and multitenant architecture
5. Starts Cloud Service
6. Oracle 11g provide in-memory capabilities
7. Support JSON
8. Better performance in input/output then 11 G 

NEW :
1.Online migration of an active data file
2.Online table partition or sub-partition migration
3.Invisible column
4.Multiple indexes on the same column
5.DDL logging
6.Temporary undo in- and- outs
7.New backup user privilege
8.How to execute SQL statement in RMAN
9.Table level recovery in RMAN
10.Restricting PGA size

Version 18c
1. Starts Autonomous Database (self-driving, self-securing,self-reparing)
2. Released in 2018
3.Manage private temporary tables
4.Use the Data Pump Import CONTINUE_LOAD_ON_FORMAT_ERROR option
5.Convert a HASH partitioned table to a RANGE partitioned table, online
6.Convert a LIST partitioned table on two keys to a LIST AUTOMATIC partitioned table on one key, online
7.Convert a LIST AUTOMATIC partitioned table to a LIST AUTOMATIC partitioned table with SUBPARTITIONING, online
8.Merge partitions of partitioned tables online
9.Use batched DDL

Version 19c
1. Released in 2019
2. Query Quarantine
3. JSON Support  (Intrudesed new JSON functions)
4. Automatic Indexing
5. Hybrid Patitioned Tables
6. Active Data Guard
7.19c is the final 12.2 release (equivalent to 12.2.0.3)
8.19c can be deployed as a BM, VM or Exadata database, or as Autonomous
9.Deploying currently uses on 19c by default
10.To benefit from Autonomous Database features, it must be specifically selected during DBS creation

Modify CSS for Single Item in Form

 #P9_SPEED_COUNTER{

padding-top: .1rem;

}

#P9_SPEED_LABEL{

font-size: 2.5rem;

}

#P9_SPEED{

font-size: 5rem;

min-height:13rem;

}

Show profile picture in top right corner on menu bar in oracle apex

1.  Create a table with blob cloumn 

create table user_info 

(

 user_id varchar2(50),

 user_name varchar2(200),

 user_photo    blob,

 filename    varchar2(3000),

 mimetype    varchar2(3000),

 last_update    date ,

 wk_date    date  default  to_date(sysdate,'DD-MON-RR'),

 wk_time    varchar2(50)  default  to_char (sysdate,'HH24:MI:SS')

)

/

1.Create application item name > 'USER_ID',> Session State Protection > Unrestricted.
2.Create Application Processes > 'GETIMAGEFILE',> Process Point > Ajax Callback, > PL/SQL Code >
begin
for c1 in (select * from employee_information
                where USER_ID= :USER_ID) loop
        sys.htp.init;
      sys.owa_util.mime_header( c1.MIME_TYPE, FALSE );
      sys.htp.p('Content-length: ' || sys.dbms_lob.getlength( c1.PICTURE));
     sys.htp.p('Content-Disposition: attachment; filename="' || c1.FILENAME || '"' );
       sys.htp.p('Cache-Control: max-age=3600');  -- tell the browser to cache for one hour, adjust as necessary
      sys.owa_util.http_header_close;
      sys.wpg_docload.download_file( c1.PICTURE);
     apex_application.stop_apex_engine;
         end loop;
         end;
3.Create/Select navigation bar where you want to show image > User Defined Attributes 1.
<img style="width:25px;height:25px;border-radius:50%;cornflowerblue" src="f?p=&APP_ID.:0:&APP_SESSION.:APPLICATION_PROCESS=GETIMAGEFILE:::USER_ID:&APP_USER." alt=" ">

Combination of some fields in a formula where some fields are null

 If IsNull({X_MONEY_RECEIPTS_REP.ORDER_CODE}) Then

    {X_MONEY_RECEIPTS_REP.IDENTITY}

Else

    {X_MONEY_RECEIPTS_REP.IDENTITY}+" / "+{X_MONEY_RECEIPTS_REP.ORDER_CODE}

remove ORA Code from process execution in Oracle APEX

Error

Error Message

 <h7 style="color: #d00;">#SQLERRM_TEXT# </h7>

SEND MAIL form Gmail to Gmail using oracle apex

If u use gmail as mail server, then configuration like
SMTP server: smtp.gmail.com
SMTP username: Your full gmail address (e.g. example@gmail.com)
SMTP password: Your Gmail password
SMTP port: 465

Or if own mail server, then configure like
SMTP server: mail.yourdomain.com or smtp.yourdomain.com
SMTP username: Your full email address (e.g. example@yourdomain.com)
SMTP password: Your email password
SMTP port: 25/26

DECLARE

  l_acl_name         VARCHAR2(30) := 'utl_tcp.xml';

  l_ftp_server_ip    VARCHAR2(20) := 'smtp.gmail.com';

  l_ftp_server_name  VARCHAR2(20) := 'smtp.gmail.com';

  l_username         VARCHAR2(30) := 'TEST';

BEGIN

  DBMS_NETWORK_ACL_ADMIN.create_acl (

    acl          => l_acl_name, 

    description  => 'Allow connections using UTL_TCP',

    principal    => l_username,

    is_grant     => TRUE, 

    privilege    => 'connect',

    start_date   => SYSTIMESTAMP,

    end_date     => NULL);

  COMMIT;

  DBMS_NETWORK_ACL_ADMIN.add_privilege ( 

    acl         => l_acl_name, 

    principal   => l_username,

    is_grant    => FALSE, 

    privilege   => 'connect', 

    position    => NULL, 

    start_date  => NULL,

    end_date    => NULL);

  COMMIT;

  DBMS_NETWORK_ACL_ADMIN.assign_acl (

    acl         => l_acl_name,

    host        => l_ftp_server_ip, 

    lower_port  => NULL,

    upper_port  => NULL);

  DBMS_NETWORK_ACL_ADMIN.assign_acl (

    acl         => l_acl_name,

    host        => l_ftp_server_name, 

    lower_port  => NULL,

    upper_port  => NULL);

  COMMIT;

END;

Oracle Apex Cloud Messenger

create table cloud_userinformation_tab

(

    id    number,

username  varchar2(80),

oldusername   varchar2(80),

nickname     varchar2(50),

gender    varchar2(50),

country   varchar2(80),

profilpic   blob,

MimeType  varchar2(2000),

FileName  varchar2(2000),

CharacterSet varchar2(2000),

BLOBLastUpdated date,

entrydate date,

updatedate date

)

 create table cloud_Messenger

(id  number,

text  varchar2(4000),

blobfile  blob,

MimeType  varchar2(2000),

FileName  varchar2(2000),

CharacterSet varchar2(2000),

BLOBLastUpdated date,

username varchar2(80),

entrydatetime  varchar2(80) , 

updatedatetime varchar2(80)

)

ORA-01476: divisor is equal to zero

select 

 

'&field1' field1,

'&field2' field2,

case 

    when '&field1' = 0 then 0

    when '&field1' != 0 then (('&field1'/'&field2')*100) 

end zero1_,

decode('&field2',0,0,(('&field1'/'&field2')*100)) zero2_,

decode('&field2',0,null,(('&field1'/'&field2')*100)) zero3_ 

from dual 

Difference between row_number(), rank() and dense_rank() window functions in Oracle SQL

-- difference between row_number(), rank(), and dense_rank()
-- will only be visible when there were duplicates.
-- row_number gives consecutive ranking even with duplicate
-- rank and dense_rank give the same ranking but rank has a jump
-- while dense_rank doesn't have jump

select e.*,
row_number() over (order by salary desc) row_number,
rank() over (order by salary desc) rank,
dense_rank() over (order by salary desc) as dense_rank
from #Employee e

                                        row_number rank dense_rank

AD_PRES 24000 90 1 1 1

AD_VP 17000 100 90 2 2 2

AD_VP 17000 100 90 3 2 2

SA_MAN 14000 0.4 100 80 4 4 3

ICON STATUS

 create table city_statuses
 (city_name varchar2(50),
  status1   varchar2(2),
    status2   varchar2(2),
    status3   varchar2(2)
  )
  
  insert into city_statuses
  values('Dhaka','1','0','-1')
  
  
  
  insert into city_statuses
  values('Dhaka','1','0','-1')
    insert into city_statuses
  values('Dhaka','1','0','-1')
    insert into city_statuses
  values('Dhaka','1','0','-1')
    insert into city_statuses
  values('Dhaka','1','0','-1')
    insert into city_statuses
  values('Dhaka','1','0','-1')
    insert into city_statuses
  values('Dhaka','1','0','-1')
    insert into city_statuses
  values('Dhaka','-1','1','1')
    insert into city_statuses
  values('Dhaka','1','0','-1')
    insert into city_statuses
  values('Dhaka','1','0','-1')

 insert into city_statuses
  values('BARISHAL','1','0','-1')
  
  
  
  insert into city_statuses
  values('BARISHAL','1','0','-1')
    insert into city_statuses
  values('BARISHAL','1','0','-1')
    insert into city_statuses
  values('BARISHAL','1','0','-1')
    insert into city_statuses
  values('BARISHAL','1','0','-1')
    insert into city_statuses
  values('BARISHAL','1','0','-1')
    insert into city_statuses
  values('BARISHAL','1','0','-1')
    insert into city_statuses
  values('BARISHAL','-1','1','1')
    insert into city_statuses
  values('BARISHAL','1','0','-1')
    insert into city_statuses
  values('BARISHAL','1','0','-1')
  
  For the icons and modifiers, I used:

• Success:  fa-check-circle-o u-success-text

• Warning:  fa-exclamation-triangle u-warning-text

• Error:  fa-exception u-danger-text

  SQL
  select city_name,
       status1,
       status2,
       status3,
       case status1 
           when '1' then 'fa-check-circle-o u-success-text'
           when '0' then 'fa-exclamation-triangle u-warning-text'
           when '-1' then 'fa-exception u-danger-text'
       end status1_icon,
       case status1 
           when '1'  then 'OK'
           when '0'  then 'Warning'
           when '-1' then 'Danger'
       end status1_description,        
       case status2 
           when '1'  then 'fa-check-circle-o u-success-text'
           when '0'  then 'fa-exclamation-triangle u-warning-text'
           when '-1' then 'fa-exception u-danger-text'
       end status2_icon,
       case status2 
           when '1'  then 'OK'
           when '0'  then 'Warning'
           when '-1' then 'Danger'
       end status2_description,                
       case status3 
           when '1'  then 'fa-check-circle-o u-success-text'
           when '0'  then 'fa-exclamation-triangle u-warning-text'
           when '-1' then 'fa-exception u-danger-text'
       end status3_icon,
       case status3 
           when '1'  then 'OK'
           when '0'  then 'Warning'
           when '-1' then 'Danger'
       end status3_description
 from city_statuses
  

Then, for the columns STATUS1, STATUS2 and STATUS3, in Page Designer I changed the property HTML Expression to:

<span class="fa #STATUS1_ICON#" title="#STATUS1_DESCRIPTION#"></span>

APEX Workspace Activity Logs

 select apex_user as who

  ,application_id||':'||page_id as what
  ,view_date as when
  ,elapsed_time as how_long
  ,page_view_type as why
  ,request_value as more_why
from apex_workspace_activity_log
where application_id >= 4000
and apex_user != 'nobody'

Get records with max value for each group of grouped SQL

SELECT cf$_order_no order_no,
cf$_recovery_officer recovery_officer,
cf$_recovery_officer_name recovery_officer_name,
cf$_recovery_zone recovery_zone_code,
cf$_recovery_zone_name recovery_zone_name
FROM c_recovery_officer_history_clv t1
INNER JOIN
(
SELECT cf$_order_no order_no, MAX(ROWID) AS MAX_ROWID
FROM c_recovery_officer_history_clv
GROUP BY cf$_order_no
) t2
ON cf$_order_no = t2.order_no AND t1.ROWID = t2.MAX_ROWID

if an Item Value Changed then check message in Oracle Apex

-- page on page load Daynamic Action 

if (apex.item("P10_SAL").isChanged()) {

apex.message.alert('You modified the salary, please verify again!');

}

 

Set/Convert an Item Value to Upper Case & Lower Case in Oracle Apex

Column Property 

--Custom Attributes property:

--UpperCase

style="text-transform: uppercase;" onKeyUp="this.value=this.value.toUpperCase();"

--LowerCase

style="text-transform: lowercase;" onKeyUp="this.value=this.value.toLowerCase();"

Highlight Item on Focus in Oracle Apex

1) change the item background color using jquery in oracle apex

  --create page execute when page loads section in daynamic action

  $('input').on('focus', function() {

$("#"+this.id).css("background-color", "yellow");

}).on('blur', function() {

$("#"+this.id).css("background-color", "white");

});

2) highlight page item using css

-- Page Inline CSS

input[type=text], textarea {

-webkit-transition: all 0.30s ease-in-out;

-moz-transition: all 0.30s ease-in-out;

-ms-transition: all 0.30s ease-in-out;

-o-transition: all 0.30s ease-in-out;

outline: none;

padding: 3px 0px 3px 3px;

margin: 5px 1px 3px 0px;

border: 1px solid #DDDDDD;

background-color: white;

}

input[type=text]:focus, textarea:focus {

box-shadow: 0 0 5px rgba(81, 203, 238, 1);

padding: 3px 0px 3px 3px;

margin: 5px 1px 3px 0px;

border: 1px solid rgba(81, 203, 238, 1);

background-color: yellow;

}

ROUND function

SELECT ROUND (SYSDATE, 'YEAR') "As of day" FROM DUAL

/

1/1/2022

SELECT ROUND (TO_DATE ('12-mar-2021'), 'YEAR') "Current Year" FROM DUAL

/

1/1/2021

SELECT ROUND (TO_DATE ('12-aug-2021'), 'YEAR') "New Year" FROM DUAL

1/1/2022

First and Last Day of the year

SELECT TRUNC (SYSDATE, 'YEAR') "First Day" FROM DUAL;

/

1/1/2021

SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 "Last Day" FROM DUAL;

/

12/31/2021

First and last Day of Month

SELECT TRUNC (SYSDATE) - (TO_NUMBER (TO_CHAR (SYSDATE, 'DD')) - 1) FIRST_DAY FROM DUAL;

/

10/1/2021

SELECT ADD_MONTHS ( TRUNC (SYSDATE) - (TO_NUMBER (TO_CHAR (SYSDATE, 'DD')) - 1), 1) - 1 LAST_DAY FROM DUAL;

/

10/31/2021

SELECT  ADD_MONTHS ( TRUNC (SYSDATE) - (TO_NUMBER (TO_CHAR (SYSDATE, 'DD')) - 1), 1) - 1 LAST_DAY,

        TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1)))+1, TRUNC(LAST_DAY(SYSDATE)) ,

        TRUNC(LAST_DAY(SYSDATE)) LAST_D

FROM DUAL;

10/31/2021    10/1/2021    10/31/2021    10/31/2021

ORACLE SQL | Find Number between two values, odd, even, number is odd or even

select '&START_NUM' + level - 1 "Number"

    from dual

  connect by level <= '&END_NUM' - '&START_NUM' + 1

ORACLE APEX | Apex URL Syntax

Anyhow, this is an Oracle related blog, and I wanted to share an idea to remember the Apex URL syntax of:

f?p=App:Page:Session:Request:Debug:ClearCache:itemNames:itemValues:PrinterFriendly

Most of the time I remember the order fairly well off the top of my head, though sometimes 

I need to double check; and sometimes within the mix of a bunch of colons it's hard to work

out what is what. Perhaps if you're reading a sentence in your head as you move the cursor

between colons

f?p=MY_APP:HOME::::::

Details

All  -->   Application

Programmers   -->   Page

Should   -->   Session

Read   -->    Request

Delightfully   -->   Debug

Creative   -->   Cache

Information   -->   Items

Values   -->   Printer Friendly

Apexing 

Without Plugins | Upload XLSX, CSV, XML or JSON data using Oracle Apex to Database without plugins

1) 

CREATE TABLE EMP
 (

EMPLOYEE_ID                                              VARCHAR2(50),

FIRST_NAME                                                 VARCHAR2(50),

LAST_NAME                                                 VARCHAR2(50),

EMAIL                                                             VARCHAR2(50),

PHONE_NUMBER                                         VARCHAR2(50),

HIRE_DATE                                                    DATE,

JOB_ID                                                            VARCHAR2(50),

SALARY                                                          VARCHAR2(50),

COMMISSION_PCT                                      VARCHAR2(50),

MANAGER_ID                                              VARCHAR2(50),

DEPARTMENT_ID                                         VARCHAR2(50)

 )
 /

CONVERT EXECL | ORACLE APEX | Convert simple CSV, JSON, XML and XLSX data to rows and columns (APEX_DATA_PARSER )

CREATE OR REPLACE DIRECTORY TEMP_DIR AS 'C:\tmp';
GRANT READ, WRITE ON DIRECTORY TEMP_DIR TO test;

CREATE OR REPLACE FUNCTION file_to_blob (p_dir       IN  VARCHAR2,

                                         p_filename  IN  VARCHAR2)

  RETURN BLOB

AS

  l_bfile  BFILE;

  l_blob   BLOB;

  l_dest_offset INTEGER := 1;

  l_src_offset  INTEGER := 1;

BEGIN

  l_bfile := BFILENAME(p_dir, p_filename);

  DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);

  DBMS_LOB.createtemporary(l_blob, FALSE);

  IF DBMS_LOB.getlength(l_bfile) > 0 THEN

    DBMS_LOB.loadblobfromfile (

      dest_lob    => l_blob,

      src_bfile   => l_bfile,

      amount      => DBMS_LOB.lobmaxsize,

      dest_offset => l_dest_offset,

      src_offset  => l_src_offset);

  END IF;

  DBMS_LOB.fileclose(l_bfile);

  RETURN l_blob;

END file_to_blob;

/

https://oracle-base.com/articles/misc/apex_data_parser#xml

Send SMS from Oracle Database | Ad Tec | Bulksms api | IP Base api

---SEND SMS FROM ORACLE DATABASE

মোবাইল নাম্বার কমা (,) সেপারেটেড  অথবা  Newline/Space থাকতে হবে যেমন  (88017,017,17)

SMS

'This is a sample English testing message from oracle Database'

'এটি ওরাকল ডেটাবেস থেকে একটি নমুনা বাংলা পরীক্ষার বার্তা' 

'This is a sample English testing message from oracle Database or এটি ওরাকল ডেটাবেস থেকে একটি নমুনা বাংলা পরীক্ষার বার্তা '

Oracle Apex | Image Show | Product Image

decode(nvl(dbms_lob.getlength(p.prod_img),0),0,null,
'<img alt="'||apex_escape.html_attribute(p.prod_nm)||'" title="'||apex_escape.html_attribute(p.prod_nm)
||'" style="border: 4px solid #CCC; -moz-border-radius: 4px; -webkit-border-radius: 4px;" '
||'src="'||apex_util.get_blob_file_src('P11_PROD_IMG',p.prod_id)||'" height="75" width="75" />') detail_img

ORACLE DATABASE | PL/SQL | PACKAGE | Create Package

create or replace package x_customer_ledger_pkg
as
FUNCTION GET_TOTAL_PRICE ( order_no_ IN VARCHAR2) RETURN NUMBER;
FUNCTION GET_ORDER_NO ( pvoucher_type IN VARCHAR2,pvoucher_no NUMBER) RETURN VARCHAR2;
procedure test_proc (in_val in number);
end;

ORACLE APEX | STORE BLOB FILE | Store Blob file Outside the Oracle database in Oracle APEX

-- New Table 

CREATE TABLE  "EMP_IMAGE" 

   (    "EMP_ID" varchar2(10), 

    "FILENAME" VARCHAR2(3000) ,  

    "MIMETYPE" VARCHAR2(3000), 

    "LAST_UPDATE" DATE, 

    "IMAGE" BLOB

   )

/

1)  Add privilege to use UTL_FILE to Your User
grant execute on utl_file to TEST;

select * from dba_tab_privs where table_name='UTL_FILE';

2 ) Create Directory to Save File and add privilege to write on Directory to You User
CREATE OR REPLACE DIRECTORY TEST_DIR AS 'G:\STORE_IMAGES';
GRANT read, write ON DIRECTORY TEST_DIR TO TEST;

3) Use this Procedure to write a BLOB file to your disk
Create or Replace PROCEDURE Store_Blob_FileOutside (p_blob IN OUT NOCOPY BLOB,
p_dir IN VARCHAR2,
p_filename IN VARCHAR2)
AS
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob_len INTEGER;
BEGIN
l_blob_len := DBMS_LOB.getlength(p_blob);
-- Open the destination file.
l_file := UTL_FILE.fopen(p_dir, p_filename,'wb', 32767);
-- Read chunks of the BLOB and write them to the file until complete.
WHILE l_pos <= l_blob_len LOOP
DBMS_LOB.read(p_blob, l_amount, l_pos, l_buffer);
UTL_FILE.put_raw(l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;
-- Close the file.
UTL_FILE.fclose(l_file);
EXCEPTION
WHEN OTHERS THEN
-- Close the file if something goes wrong.
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
RAISE;
END Store_Blob_FileOutside;

4) Use this process to upload file

DECLARE
l_blob BLOB;
BEGIN
-- Ge SELECT filename,
select
BLOB_CONTENT INTO l_blob
FROM apex_application_temp_files
where NAME= :P5_PRODUCT_IMAGE ;

blob_to_file(p_blob => l_blob,
p_dir => 'TEST_DIR',
p_filename => :P5_PRODUCT_IMAGE);
END;

Search Oracle DBA Sql Query

 SELECT owner, table_name, column_name FROM all_tab_columns WHERE column_name LIKE '%PAPER%';

Search All Fields In All Tables For A Specific Value (Oracle)

SELECT * from dba_objects WHERE object_name like '%PAPER_FORMAT%' ;

SELECT table_name, column_name FROM all_tab_cols where column_name LIKE '%PAPER%';

variable val varchar2(10)
exec :val := 'KING'

SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",

      SUBSTR (table_name, 1, 14) "Table",

      SUBSTR (column_name, 1, 14) "Column"

   FROM cols,

     TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '

      || column_name

      || ' from '

      || table_name

      || ' where upper('

     || column_name

     || ') like upper(''%'

     || :val

     || '%'')' ).extract ('ROWSET/ROW/*') ) ) t

   ORDER BY "Table"

/

variable val NUMBER
exec :val := 20

SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
     SUBSTR (table_name, 1, 14) "Table",
      SUBSTR (column_name, 1, 14) "Column"
    FROM cols,
     TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
     || column_name
     || ' from '
      || table_name
      || ' where upper('
     || column_name
     || ') like upper(''%'
    || :val
     || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
   ORDER BY "Table"

How to Remove decimal from string in Crystal Report

ToText({#any_number},0,"")

How to Page N of M in formula in crystal report

 numbervar n := PageNumber - 1;

numbervar m := TotalPageCount -1;

stringvar page := "Page " + toText(int(n)) + " of " + toText(int(m));

page

How to Page wise Records Break in crystal report

 Section Expart> Go Details > Pagin> X-2  Paste Code 

if pagenumber=1 and recordNumber=3 then true
else if pagenumber=2 and RecordNumber=36 then true
else if pagenumber=3 and RecordNumber=36 then true
else if pagenumber=4 and RecordNumber=36 then true

//
////this also working
//if pagenumber=6 and Remainder(recordnumber,4)=0 then true
//
//else if pagenumber=7 and Remainder(recordnumber,20)=0 then true
//
//else  false

//IF Remainder (RecordNumber, 4) = 0 THEN
//    TRUE
//ELSE
//    FALSE

Caps Lock Detected in Oracle apex

/* Create Region & Add Item like (P10_BOOK_NAME) in Region Name

---Function and Global Variable Declaration

*/

 /* check for CAPS LOCK on all password fields */
$(P10_BOOK_NAME).keypress(function(e) {
    var kc = e.which; // get keycode
    var isUpperCase = ((kc >= 65 && kc <= 90) || (kc >= 33 && kc <= 34) || (kc >= 36 && kc <= 39) || (kc >= 40 && kc <= 42) || kc == 47 || (kc >= 58 && kc <= 59) || kc == 61 || kc == 63 || kc == 167 || kc == 196 || kc == 214 || kc == 220) ? true : false; // uppercase A-Z or 'Ä', 'Ö', 'Ü', '!', '"', '§', '$', '%', '&', '/', '(', ')', '=', ':', ';'
    var isLowerCase = ((kc >= 97 && kc <= 122) || (kc >= 48 && kc <= 57) || kc == 35 || (kc >= 43 && kc <= 44) || kc == 46 || kc == 228 || kc == 223 || kc == 246 || kc == 252) ? true : false; // lowercase a-Z or 0-9 or 'ä', 'ö', 'ü', '.', ','
    // event.shiftKey does not seem to be normalized by jQuery(?) for IE8-
    var isShift = (e.shiftKey) ? e.shiftKey : ((kc == 16) ? true : false); // shift is pressed
    // uppercase w/out shift or lowercase with shift == caps lock
    if ((isUpperCase && !isShift) || (isLowerCase && isShift)) {
        $(this).next('.form-control-feedback').show().parent().addClass('has-warning has-feedback').next(".capsWarn").show();
    } else {
        $(this).next('.form-control-feedback').hide().parent().removeClass('has-warning has-feedback').next(".capsWarn").hide();
    }
}).after('<span class="glyphicon glyphicon-warning-sign form-control-feedback" style="display:none;"></span>').parent().after("<span class='capsWarn text-danger' style='display:none; color:red;'>Is your CAPSLOCK on?</span>");

Export to PDF line spacing changed

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Export\Pdf

Preamble: i set ForceLargerFonts=1, TruncationAdjustment=2, UsePrecisePositioningForText=1 on registry.

C:\Windows\ServiceProfiles\LocalService

%systemroot%\ServiceProfiles\LocalService

Networking Commands

//reboot registry in cmd mode - Micrrosoft Windows 10

sfc /scannow

ORA-04098: trigger 'IPIHR.NAVIGATION MENU UPD' is invalid and failed re-validation

 ALTER TRIGGER NAVIGATION_MENU_UPD DISABLE;

ALTER TRIGGER NAVIGATION_MENU_UPD ENABLE;

Oracle Apex Signature pad

 Begin 

APEX_COLLECTION.DELETE_COLLECTION(p_collection_name=>'APEX_SIGNATURE');

exception when others then null;

End;

Begin 

APEX_COLLECTION.DELETE_COLLECTION(p_collection_name=>'APEX_SIGNATURE');

exception when others then null;

End;

begin 

for x in (select c001 as filename,

c002 as mine_type,

c001  as dae_created,

blob001 as img_content

from apex_collections

where collection_name='APEX_SIGNATURE');

loop

update HR_EMPLOYEES set 

sig_pic=x.img_content,

sig_file_name=x.filename,

sig_file_update_date=x.date_created,

sig_file_mine_type=x.mine_type

where pid=:p22_emp_id;

end loop;

end;

create table hr_employees

(

emp_id varchar2(30),

emp_name varchar2(120),

join_date date,

sig_pic  blob,

sig_file_name  varchar2(200),

sig_file_update_date date,

sig_file_mine_type varchar2(50)

)

/

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 3200M, ORA-01078: failure in processing system parameters

 SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 24 09:49:39 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter user-name: hr/hr@orcl

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect

descriptor

Enter user-name: sys as sysdba

Enter password:

Connected to an idle instance.

SQL> startup

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 3200M

ORA-01078: failure in processing system parameters

SQL> startup pfile=D:\appdb19c\app\OracleHomeUser1\admin\orcl\pfile\init.ora.513202112044

ORACLE instance started.

Total System Global Area 2516579304 bytes

Fixed Size                  9053160 bytes

Variable Size             541065216 bytes

Database Buffers         1958739968 bytes

Redo Buffers                7720960 bytes

Database mounted.

Database opened.

SQL> create spfile from pfile='c:\temp\init.ora';

File created.

SQL> startup force;

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 3200M

ORA-01078: failure in processing system parameters

SQL> show parameter memory_target

ORA-01034: ORACLE not available

Process ID: 0

Session ID: 496 Serial number: 49087

SQL> startup pfile=D:\appdb19c\app\OracleHomeUser1\admin\orcl\pfile\init.ora.513202112044

ORACLE instance started.

Total System Global Area 2516579304 bytes

Fixed Size                  9053160 bytes

Variable Size             541065216 bytes

Database Buffers         1958739968 bytes

Redo Buffers                7720960 bytes

Database mounted.

Database opened.

SQL> create spfile from pfile=D:\appdb19c\app\OracleHomeUser1\admin\orcl\pfile\init.ora.513202112044

  2  /

create spfile from pfile=D:\appdb19c\app\OracleHomeUser1\admin\orcl\pfile\init.ora.513202112044

                         *

ERROR at line 1:

ORA-02236: invalid file name

SQL> create spfile from pfile='D:\appdb19c\app\OracleHomeUser1\admin\orcl\pfile\init.ora.513202112044'

  2  /

File created.

SQL> startup force;

ORACLE instance started.

Total System Global Area 2516582224 bytes

Fixed Size                  9031504 bytes

Variable Size             553648128 bytes

Database Buffers         1946157056 bytes

Redo Buffers                7745536 bytes

Database mounted.

Database opened.

SQL>

How to startup database from pfile

SQL>  SHUTDOWN

ERROR:

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Process ID: 0

Session ID: 0 Serial number: 0

 SQL> startup pfile=D:\appdb19c\app\OracleHomeUser1\admin\orcl\pfile\init.ora.513202112044

ORACLE instance started.

Total System Global Area 2516579304 bytes

Fixed Size                  9053160 bytes

Variable Size             541065216 bytes

Database Buffers         1958739968 bytes

Redo Buffers                7720960 bytes

Database mounted.

Database opened.

Getting Cumulative Sum (Running Total) Using Analytical Functions in oralce

SELECT
    DEPTNO,
    ENAME,
    SAL,
    SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL,ENAME) CUMDEPTTOT,
    SUM(SAL) OVER (PARTITION BY DEPTNO) DEPTTOTAL,
    SUM(SAL) OVER (ORDER BY DEPTNO, SAL) CUMTOT,
    SUM(SAL) OVER () TOTSAL
FROM
    SCOTT.EMP
ORDER BY
    DEPTNO

Sql Puzzle - Calendar of Current Year

BREAK ON MONTH

BREAK ON MONTH SKIP PAGE

COL MONTH FORMAT A15

 with curr_year as

   (

     select

       trunc(sysdate,'year') -1 + level dt

     from dual

       connect by level <=

         add_months(trunc(sysdate,'year'),12) - trunc(sysdate,'year')

   ),

   data as

   (

     select

       dt,

       to_char(dt,'d') d,

       sum(case when to_char(dt,'d') = 1 or to_char(dt,'dd') = 1 then 1 else 0 end) over (order by dt) week_no

     from

       curr_year

   )

   select

     to_char(min(dt),'Month') Month,

     max(case when d=1 then to_char(dt,'dd') end) sun,

     max(case when d=2 then to_char(dt,'dd') end) mon,

     max(case when d=3 then to_char(dt,'dd') end) tue,

     max(case when d=4 then to_char(dt,'dd') end) wed,

     max(case when d=5 then to_char(dt,'dd') end) thu,

     max(case when d=6 then to_char(dt,'dd') end) fri,

     max(case when d=7 then to_char(dt,'dd') end) sat

   from

     data

   group by week_no

   order by week_no;