cdoe

 select p.product_id,

       p.product_name CARD_TITLE, 
       p.product_description CARD_TEXT, 
       p.category, 
       decode(p.product_avail, 'Y','Yes','N','No') product_avail,
       p.list_price,
       (select sum(quantity) from demo_order_items where product_id = p.product_id) units,
       (select sum(quantity * p.list_price) from demo_order_items where product_id = p.product_id) sales,       
       (select count(o.customer_id) from demo_orders o, demo_order_items t where o.order_id = t.order_id and t.product_id = p.product_id group by p.product_id) customers,
       (select max(o.order_timestamp) od from demo_orders o, demo_order_items i where o.order_id = i.order_id and i.product_id = p.product_id) last_date_sold,
       p.product_id img,
       apex_util.prepare_url(p_url=>'f?p='||:app_id||':6:'||:app_session||'::::P6_PRODUCT_ID,P6_BRANCH:'||p.product_id||','||3,p_dialog=> 'null') CARD_LINK,
       decode(nvl(dbms_lob.getlength(p.product_image),0),0,null,
       '<img alt="'||apex_escape.html_attribute(p.product_name)||'" title="'||apex_escape.html_attribute(p.product_name)
              ||'" style="border: 4px solid #CCC; -moz-border-radius: 4px; -webkit-border-radius: 4px;" '
              ||'src="'||apex_util.get_blob_file_src('P6_PRODUCT_IMAGE',p.product_id)||'" height="75" width="75" />') detail_img,
       decode(nvl(dbms_lob.getlength(p.product_image),0),0,null,
       apex_util.get_blob_file_src('P6_PRODUCT_IMAGE',p.product_id))
       CARD_IMAGE,
       tags CARD_SUBTEXT
from demo_product_info p

Scripts

 

.t-Button--navBar .t-Button-badge {

//color: rgb(253, 253, 253);

//font-size: 11px;

//font-weight: bold;

background-color: #f60000;

border-radius: var(--ut-navbar-button-badge-border-radius);

/*padding: 3px 8px 3px 8px;*/

margin: -6px 6px 6px -10px;

padding-right: 5px;

// padding-left: 5px;

align-items: center;

// width: 100%;

// justify-content: center;

Interactive Grid Set multiple Row using Array in oracle apex

 var widget      = apex.region('IG_AGENDAPOINTS').widget();

var grid        = widget.interactiveGrid('getViews','grid');  

var model       = grid.model;

var myJSONString = $v("P303_STANDARD_AGENDA_JSON");

var myObject = JSON.parse(myJSONString);

model.clearData();

for ( var i = 0; i < myObject.length; i++) {

  var obj = myObject[i];

      

   //insert new record on a model

    var myNewRecordId = model.insertNewRecord();

    //get the new record

    var myNewRecord = model.getRecord(myNewRecordId);

    

    //update record values

    model.setValue(myNewRecord, 'ITEM_NO', obj.ITEM_NO);

    model.setValue(myNewRecord, 'UOM_NO', obj.UOM_NO);

    model.setValue(myNewRecord, 'REQ_QTY', obj.REQ_QTY);

    model.setValue(myNewRecord, 'ISU_QTY', obj.ISU_QTY);

    model.setValue(myNewRecord, 'PENDING', obj.PENDING);

    

};

// So to conclude, these are the steps:

// ORACLE table => JSON => javascript => new records in Interactive Grid

  MENU_CODE

, MENU_SEQID

, MENU_NAME

, PARENT_MENU_CODE

, PARENT_MENU_NAME

, PAGE_NO

, SUB_PAGE_NO

, ICON

, STATUS

select '['||(listagg( '{"ITEM_NO":"'||a.ITEM_NO||'",'||

                      '"UOM_NO":"'||a.UOM||'",'||

                      '"REQ_QTY":"'||a.REQ_QTY||'",'||

                      '"PENDING":"'||a.PENDING||'",'||

                      '"ISU_QTY":"'||a.ISU_QTY||                     

                      '"}', ',') within group (order by a.ITEM_NO DESC))||

       ']' myJson

  into :P303_STANDARD_AGENDA_JSON                             

  from ( select saa.ITEM_NO

              , saa.UOM

              , saa.REQ_QTY

              ,saa.PENDINGQTY PENDING

              ,saa.PENDINGQTY ISU_QTY

          from V_ISSUE_PENDING saa

          where saa.REQ_NO = :P303_P_REQ_NO 

          and PENDINGQTY>0

       ) a;

Upload Excel in database directly In Oracle Apex,Without using Wizard Easy Way

  

***************Insert Excel Data into Collection************************

begin

IF  APEX_COLLECTION.COLLECTION_EXISTS('Collection_name') THEN

    APEX_COLLECTION.TRUNCATE_COLLECTION('Collection_name');

END IF;

IF NOT APEX_COLLECTION.COLLECTION_EXISTS('Collection_name') THEN

    APEX_COLLECTION.CREATE_COLLECTION('Collection_name');

END IF; 

for r1 in (select *  from

                    apex_application_temp_files f, table( apex_data_parser.parse(

                                    p_content                     => f.blob_content,

                                    p_add_headers_row             => 'Y',

                                   -- p_store_profile_to_collection => 'FILE_PROV_CASH',

                                    p_file_name                   => f.filename,

                                      p_skip_rows => 1 ) ) p     / /This line will skip excel the first row, as I contain heading only

                where      f.name = :P3_UPLOAD_FILE  //Page Item name

                )

        loop

        APEX_COLLECTION.ADD_MEMBER(P_COLLECTION_NAME => 'Collection_name',

                                            p_c001            => nvl(REPLACE(r1.col001,'-',''),0),

                                            p_c002            => nvl(REPLACE(r1.col002,'-',''),0),

                                            P_C003            => nvl(REPLACE(r1.col003,'-',''),0),

                                            p_c004            => nvl(REPLACE(r1.col004,'-',''),0),

                                            p_c005            => nvl(REPLACE(r1.col005,'-',''),0),

                                            P_C006            => nvl(REPLACE(r1.col006,'-',''),0),

                                            p_c007            => nvl(REPLACE(r1.col007,'-',''),0),

                                            p_c008            => nvl(REPLACE(r1.col008,'-',''),0),

                                            P_C009            => nvl(REPLACE(r1.col009,'-',''),0),

                                            P_C010            => nvl(REPLACE(r1.col010,'-',''),0),

                                            P_C011            => nvl(REPLACE(r1.col011,'-',''),0),

                                            P_C012            => nvl(REPLACE(r1.col012,'-',''),0),

                                            P_C013            => nvl(REPLACE(r1.col013,'-',''),0)

                                            );

           

                

        END LOOP; 

    end;

***********Insert Collection data into Table***********

DECLARE

CURSOR C2 IS

SELECT C001, C002, C003, C004, C005, C006,

       C007, C008, C009, C010, C011, C012,

       C013, C014, C015, C016, C017, C018,

       C019, C020, C021, C022, C023, C024, C025

       

FROM APEX_COLLECTIONS 

WHERE

COLLECTION_NAME = 'Collection_name';

BEGIN

FOR I IN C2

LOOP

INSERT INTO (Table Name)

(

Table_column,   --All table column

Table_column,

Table_column,

Table_column,

Table_column,

Table_column ,

Table_column ,

Table_column ,

Table_column ,

Table_column

)

VALUES(

        I.C002,   --collection data

        I.C003, 

        I.C004, 

        I.C005,

        I.C006,

        I.C007,    

        I.C008,

        I.C009, 

        I.C010

        

);

END LOOP;

END;

*********Delete or trun collection on clear button***************

IF  APEX_COLLECTION.COLLECTION_EXISTS('Collection_name') THEN

    APEX_COLLECTION.TRUNCATE_COLLECTION('Collection_name');

END IF;  

**********Interactive Report Using Collection Data of Excel*******

 select seq_id, c001,c002,c003,c004,c005,c006,c007,c008,c009,c010,c011,c012,c013 from

 APEX_COLLECTIONS 

 WHERE COLLECTION_NAME='Collection name' and  seq_id!=1;

image preview in apex

 --page level javascripts 

function showPreview(file, itenName) {

tet src= URL.createObjectURL,

imageContainer = $( "#${itenName}_CONTAINER .t-Form-itemWrapper" );

imageContainer.empty();

imageContainer.append("<img src=$(src) class="cc-image-preview"/>' );

}

--dynamic action P36_DISPLAY_IMAGE

let files = this.browserEvent.target.files;

if (files.length > 0){ showPreview(files[0], "P36_DISPLAY_IMAGE"); }

--inline css in apge level 

.cc-image-preview {

width: 400px;

}

--use advance column attribute  P36_DISPLAY_IMAGE

cc-image-preview 

Highlight empty required items

Here is how to highlight form text items, witch Value Required attribute is set to "Yes" and do not have any value.
Create dynamic action to page zero

Name: Highlight required
Event: Change
Selection Type: jQuery Selector
jQuery Selector: input[type='text'][required]
Condition: -No Condition-
Action: Execute JavaScript code
Fire On Page Load: True

Code:

if(!$v(this.triggeringElement)){
 $(this.triggeringElement).css({"border-color":"red"});
}else{
 $(this.triggeringElement).css({"border-color":""});
}

Selection Type: None

Now run application and items having Value Required set to "Yes" background color is changed if item do not have any value.
You can find Value Required setting from item attributes.

Simple Floating Button in Oracle Apex

Add following CSS into page header or in theme roller – which ever is your preference to use it.

.float{
  z-index:100;
  position:fixed;
  width:60px;
  height:60px;
  bottom:40px;
  right:40px;
  background-color: rgba(55, 76, 139, 0.81);
  color:#FFF;
  border-radius:50px;
  text-align:center;
  box-shadow: 2px 2px 3px #999;
}
.my-float{
  margin-top:22px;
}

Open your existing button and make following changes

• Set CSS Classes as “float my-float”
• Change Button Template to “Icon”
• Set Icon as “fa-plus”

Connect Sql server to Oracle Database using ODBC

===========Oracle Database Gateways=======

To access non Oracle DB systems we need to use Oracle Heterogeneous Services.
Oracle Database Gateways Provide Heterogeneous data access.
Oracle Database Gateways provide the ability to transparently access data residing
in a non Oracle systems from an Oracle Environment

Steps 1: Download and install Oracle database gateway.

Oracle Database 19c Download for Microsoft Windows x64 (64-bit)

Step 2: Create new Listener_ODBC installed software

----done
Create ODBC data source.
---name mytestodbc

Configure initdg4odbc.ora file in oracle gateway home.
---gateway home : C:\app\tg\Ekramul\product\19.0.0\tghome_1\hs\admin
copy initdg4odbc.ora and past with a new name

HS_FDS_CONNECT_INFO = mytestodbc
HS_FDS_TRACE_LEVEL = OFF

Configure listener.ora in oracle gateway home.

---add below lines

SID_LIST_LISTENER_ODBC =
(SID_LIST =
(SID_DESC =
(SID_NAME = mytestodbc)
(ORACLE_HOME = C:\app\tg\Ekramul\product\19.0.0\tghome_1)
(PROGRAM = dg4odbc)
)
)

Configure tnsname.ora in oracle database home.
---oracle 19c database tns file
---add below lines

mytestodbc =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-53PA6MR)(PORT = 1525))
(CONNECT_DATA =(SID=mytestodbc))
(HS=OK)
)

now restart services

lsnrctl reload or from services
now check from cmd by tnsping
My 19c database tns name is : orcl
My 19c gateway database tns name is : mytestodbc

tnsping orcl
tnsping mytestodbc
export environment
set oracle_home=E:\app\19c
set oracle_sid=orcl

Step 3: Create a database link to connect with the MSSQL server ODBC source
sqlplus / as sysdba
create database link mylink01 connect to "sa" identified by "sa" using 'mytestodbc';
select * from test_tbl@mylink01;

SQL> select * from emp@mylink01;

select * from emp@mylink01

                  *

ERROR at line 1:

ORA-28545: error diagnosed by Net8 when connecting to an agent

Unable to retrieve text of NETWORK/NCR message 65535

ORA-02063: preceding 2 lines from MYLINK01

Solution: reconfigure listener_odbc

SQL> select * from emp@mylink01;

emp_id    emp_name   Date_time

----------  --------------   -------------

10            LIMA            21-AUG-23

20          Abdul Quium  21-AUG-23

PIVOT in Oracle Database

 CREATE TABLE pivot_test (

  id            NUMBER,

  customer_id   NUMBER,

  product_code  VARCHAR2(5),

  quantity      NUMBER

);

INSERT INTO pivot_test VALUES (1, 1, 'A', 10);

INSERT INTO pivot_test VALUES (2, 1, 'B', 20);

INSERT INTO pivot_test VALUES (3, 1, 'C', 30);

INSERT INTO pivot_test VALUES (4, 2, 'A', 40);

INSERT INTO pivot_test VALUES (5, 2, 'C', 50);

INSERT INTO pivot_test VALUES (6, 3, 'A', 60);

INSERT INTO pivot_test VALUES (7, 3, 'B', 70);

INSERT INTO pivot_test VALUES (8, 3, 'C', 80);

INSERT INTO pivot_test VALUES (9, 3, 'D', 90);

INSERT INTO pivot_test VALUES (10, 4, 'A', 100);

commit;

SELECT *

FROM   (SELECT product_code, quantity

        FROM   pivot_test)

PIVOT  (SUM(quantity) AS sum_quantity FOR (product_code) IN ('A' AS a, 'B' AS b, 'C' AS c));

fiscal year mm

select

TO_CHAR(ADD_MONTHS(TO_DATE(CONCAT(&P_FROM_DATE,'01'), 'yyyymmdd'),6),'YYYYMM') 

from dual;

-----------------

CREATE OR REPLACE FUNCTION fiscal_month_year( period IN varchar2 ) RETURN VARCHAR2 IS

    fiscal_month_year VARCHAR2(20);

/******************************************************************************

   NAME:       fiscal_month_year

   PURPOSE:    

   REVISIONS:

   Ver        Date        Author           Description

   ---------  ----------  ---------------  ------------------------------------

   1.0        5/15/2023   Md. Quium Hossain       1. Created this function.

   NOTES:

   Automatically available Auto Replace Keywords:

      Object Name:     fiscal_month_year

      Sysdate:         5/15/2023

      Date and Time:   5/15/2023, 1:03:33 PM, and 5/15/2023 1:03:33 PM

      Username:        Md. Quium Hossain (set in TOAD Options, Procedure Editor)

      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/

   

BEGIN

fiscal_month_year := 0;

  IF SUBSTR(period, 5, 2) = '01' THEN

    fiscal_month_year := CONCAT('Jul-', TO_CHAR(SUBSTR(period, 1, 4)));

  ELSIF SUBSTR(period, 5, 2) = '02' THEN

    fiscal_month_year := CONCAT('Aug-', TO_CHAR(SUBSTR(period, 1, 4)));

  ELSIF SUBSTR(period, 5, 2) = '03' THEN

    fiscal_month_year := CONCAT('Sep-', TO_CHAR(SUBSTR(period, 1, 4)));

  ELSIF SUBSTR(period, 5, 2) = '04' THEN

    fiscal_month_year := CONCAT('Oct-', TO_CHAR(SUBSTR(period, 1, 4)));

  ELSIF SUBSTR(period, 5, 2) = '05' THEN

    fiscal_month_year := CONCAT('Nov-', TO_CHAR(SUBSTR(period, 1, 4)));

  ELSIF SUBSTR(period, 5, 2) = '06' THEN

    fiscal_month_year := CONCAT('Dec-', TO_CHAR(SUBSTR(period, 1, 4)));

  ELSIF SUBSTR(period, 5, 2) = '07' THEN

    fiscal_month_year := CONCAT('Jan-', TO_CHAR(SUBSTR(period, 1, 4) + 1));

  ELSIF SUBSTR(period, 5, 2) = '08' THEN

    fiscal_month_year := CONCAT('Feb-', TO_CHAR(SUBSTR(period, 1, 4) + 1));

  ELSIF SUBSTR(period, 5, 2) = '09' THEN

    fiscal_month_year := CONCAT('Mar-', TO_CHAR(SUBSTR(period, 1, 4) + 1));

  ELSIF SUBSTR(period, 5, 2) = '10' THEN

    fiscal_month_year := CONCAT('Apr-', TO_CHAR(SUBSTR(period, 1, 4) + 1));

  ELSIF SUBSTR(period, 5, 2) = '11' THEN

    fiscal_month_year := CONCAT('May-', TO_CHAR(SUBSTR(period, 1, 4) + 1));

  ELSIF SUBSTR(period, 5, 2) = '12' THEN

    fiscal_month_year := CONCAT('Jun-', TO_CHAR(SUBSTR(period, 1, 4) + 1));

  END IF;

   RETURN fiscal_month_year;

END fiscal_month_year;

/

Months

 SELECT   ADD_MONTHS (  TRUNC (  TO_DATE('31-MAR-2023','DD-MON-YYYY')-1,  'MM'),  -1), 

TRUNC ( LAST_DAY ( ADD_MONTHS ( TO_DATE('31-MAR-2023','DD-MON-YYYY')-1, -1))),

TRUNC (TO_DATE('31-MAR-2023','DD-MON-YYYY') - 1, 'MM')

,TRUNC (LAST_DAY (TO_DATE('31-MAR-2023','DD-MON-YYYY') - 1)) 

FROM DUAL

SELECT 

TRUNC(ADD_MONTHS('&FROM_DATE_', -1),'MM') P_START_DATE,

LAST_DAY(ADD_MONTHS('&FROM_DATE_', -1)) P_END_DATE, 

TRUNC(TO_DATE(SYSDATE),'MM') C_START_DATE,

TRUNC(LAST_DAY(sysdate)) C_END_DATE

--

--    TRUNC(ADD_MONTHS(SYSDATE, -0),'MM') , 

--    LAST_DAY(ADD_MONTHS(SYSDATE,-0)) 

    FROM DUAL

===========================

SELECT   

 TRUNC(ADD_MONTHS (  TRUNC (  SYSDATE-1,  'MM'),  -1)) a , 

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

 TRUNC (SYSDATE - 1, 'MM') c,

 TRUNC (LAST_DAY (SYSDATE - 1)) d,

 TRUNC (SYSDATE - 1,  'MM') e,  

 TRUNC (SYSDATE - 1,  'MM')

 TRUNC ( LAST_DAY (  SYSDATE - 1))  f,

 TRUNC (LAST_DAY (   SYSDATE - 1)) 

 

 TRUNC (  ADD_MONTHS ( (SYSDATE - 1),  -1),  'MM') g  ,

 ADD_MONTHS (  TRUNC (  SYSDATE-1,  'MM'),  -1)ggg,

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

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

  

FROM DUAL

Convert English number 2 Bangla number in oracle sql

CREATE OR REPLACE FUNCTION convert_en2bn_number (num varchar2)

   RETURN VARCHAR2

/******************************************************************************

   NAME:       convert_en2bn_number

   PURPOSE:

   REVISIONS:

   Ver        Date        Author           Description

   ---------  ----------  ---------------  ------------------------------------

   1.0        3/21/2023   Md. Quium Hossain       1. Created this function.

   NOTES:

   Automatically available Auto Replace Keywords:

      Object Name:     convert_en2bn_number

      Sysdate:         3/21/2023

      Date and Time:   3/21/2023, 1:48:13 AM, and 3/21/2023 1:48:13 AM

      Username:        Md. Quium Hossain (set in TOAD Options, Procedure Editor)

      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/

IS

   p_num    VARCHAR2 (100) := TO_CHAR (num);

   p_char   CHAR (1);

   o_str    NUMBER := 14722982;

   vBnNum   VARCHAR2 (500);

BEGIN

   FOR i IN 1 .. LENGTH (p_num)

   LOOP

          p_char := SUBSTR (p_num, i, 1);

                  IF p_char = '.'

                  THEN

                     o_str := 46;

                  ELSE

                     o_str := 14722982 + TO_NUMBER (p_char);

                  END IF;

          vBnNum := vBnNum || CHR (o_str);

   END LOOP;

   RETURN vBnNum;

   

   /*

   CREATE OR REPLACE FUNCTION format_comma_en2bn_number(in_value NUMBER)

    RETURN VARCHAR2 IS

    BEGIN

      RETURN TRANSLATE(TO_CHAR(in_value,'fm99G99G99G99G99G99G990D99',

                                        'nls_numeric_characters=''.,'''),

                       '0123456789','০১২৩৪৫৬৭৮৯');

    END;

/

   */

EXCEPTION

   WHEN NO_DATA_FOUND

   THEN

      NULL;

   WHEN OTHERS

   THEN

      -- Consider logging the error and then re-raise

      RAISE;

END convert_en2bn_number;

/