Thursday, October 20, 2022

Next row and previous row in the table - LAG or Lead function

LAG or Lead function 


select order_no, installment_id, installment_id2, due_date , due_date2,number_of_days

from 

(

select t.order_no, 

        installment_id,          

        LEAD (installment_id,1) OVER (PARTITION BY t.order_no ORDER BY installment_id) AS installment_id2,

        due_date,

        LEAD (due_date,1) OVER (PARTITION BY t.order_no ORDER BY due_date) AS due_date2,

        

        (LEAD (due_date,1) OVER (PARTITION BY t.order_no ORDER BY due_date)-due_date) number_of_days


  from C_CREDIT_PAYMENT_PLAN p , PAYMENT_PLAN c, CUSTOMER_ORDER_CFV t

where p.invoice_id=c.invoice_id

and p.order_no=t.order_no

and c_early_settle <>'TRUE'

and PARTY_TYPE_DB='CUSTOMER'

--and t.order_no='L500592'

and CUSTOMER_ORDER_API.GET_C_LEASING_ACCOUNT_STATUS(t.ORDER_NO)<>'Closed'

and installment_id>1

and CUSTOMER_ORDER_API.GET_ORDER_ID(t.ORDER_NO) <>'MIG'

where number_of_days>31



//////



select order_no, installment_id, installment_id2, due_date , due_date2,number_of_days

from 

(

select t.order_no, 

        installment_id,          

        LAG (installment_id,1) OVER (PARTITION BY t.order_no ORDER BY installment_id) AS installment_id2,

        due_date,

        LAG (due_date,1) OVER (PARTITION BY t.order_no ORDER BY due_date) AS due_date2,

        

        (LAG (due_date,1) OVER (PARTITION BY t.order_no ORDER BY due_date)-due_date) number_of_days


  from C_CREDIT_PAYMENT_PLAN p , PAYMENT_PLAN c, CUSTOMER_ORDER_CFV t

where p.invoice_id=c.invoice_id

and p.order_no=t.order_no

and c_early_settle <>'TRUE'

and PARTY_TYPE_DB='CUSTOMER'

and t.order_no='L500592'

and CUSTOMER_ORDER_API.GET_C_LEASING_ACCOUNT_STATUS(t.ORDER_NO)<>'Closed'

and installment_id>1

and CUSTOMER_ORDER_API.GET_ORDER_ID(t.ORDER_NO) <>'MIG'

--where number_of_days>31


Monday, October 10, 2022

AWR report

 ps -ef|grep pmon


sqlplus / as sysdba


select * from v$version;


SQL> show parameter control_management_pack_access;


NAME                                 TYPE        VALUE

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

control_management_pack_access       string      DIAGNOSTIC+TUNING



exit;


[oracle@ifsrepdb ~]$ cd $ORACLE_HOME/rdbms/admin/@awrrpt.sql


/*

[oracle@ifsrepdb ~]$ cd $ORACLE_HOME/

[oracle@ifsrepdb dbhome_1]$ cd 


[oracle@ifsrepdb admin] ls -lrt


[oracle@ifsrepdb admin]$ ls -lrt awrrpt*


*/



[oracle@ifsrepdb admin]$ sqlplus / as sysdba


SQL> @awrrpt.sql


Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

AWR reports can be generated in the following formats.  Please enter the

name of the format at the prompt.  Default value is 'html'.


'html'          HTML format (default)

'text'          Text format

'active-html'   Includes Performance Hub active report


Enter value for report_type: html




Enter value for num_days: 2




Enter value for snapshot start: 100


Enter value for snapshot end: 102



Enter report format name: awrreportdb.html






exit





ls -lrt 


pwd 


/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin



go to WinSip 




Thursday, September 29, 2022

regexp_substr

 SELECT level,regexp_substr('Apple,Orange,Mango,Grapes','[^,]+',1,level) regexp_substr


FROM dual


  CONNECT BY level<=regexp_count('Apple,Orange,Mango,Grapes',',')+1;





SELECT ltrim(substr(text,1,15),'Order No'),length(('Order No 11069 RE Instalment ID 27')),

case 

     when length(text)=30 then 

     ltrim(substr(text,1,14),'Order No')

     when length(text) = 31 then

     ltrim(substr(text,1,15),'Order No')

     when length(text) =32 then

     ltrim(substr(text,1,16),'Order No')

     when length(text) = 33 then

     ltrim(substr(text,1,17),'Order No')

     when length(text) = 34 then

     ltrim(substr(text,1,18),'Order No')

     END ORDER_NO

from GL_AND_HOLD_VOU_ROW_QRY

where ACCOUNT='1221100'

and voucher_type='M'





select sum(CREDIT_AMOUNT) realized_Int, 0 unearned_Int,

       (select i.CREATORS_REFERENCE 

            from IAL_C_ORDER_BASE_INVOICE_VIEW i 

            left join IFSAPP.IAL_C_ORDER_BASE_INVOICE_VIEW b

            ON i.SERIES_ID||''||i.INVOICE_NO=b.SERIES_ID||''||b.INVOICE_NO

            WHERE (gl.REFERENCE_SERIE||''||gl.REFERENCE_NUMBER=i.SERIES_ID||''||i.INVOICE_NO))

from GL_AND_HOLD_VOU_ROW_QRY gl

where ACCOUNT='1221100'

and voucher_type='F'

UNION ALL 

select 0 realized_Int, sum(DEBET_AMOUNT)  unearned_Int,

   regexp_replace(substr(TEXT,10), '( [^ ]+){3}$', '')

/*  CASE 

     WHEN LENGTH(TEXT)=30 THEN 

        LTRIM(SUBSTR(TEXT,1,14),'Order No')

     WHEN LENGTH(TEXT) = 31 THEN

        LTRIM(SUBSTR(TEXT,1,15),'Order No')

     WHEN LENGTH(TEXT) =32 THEN

        LTRIM(SUBSTR(TEXT,1,16),'Order No')

     WHEN LENGTH(TEXT) = 33 THEN

        LTRIM(SUBSTR(TEXT,1,17),'Order No')

     WHEN LENGTH(TEXT) = 34 THEN

        LTRIM(SUBSTR(TEXT,1,18),'Order No')

     END ORDER_NO

  */

from GL_AND_HOLD_VOU_ROW_QRY gl

where ACCOUNT='1221100'

and voucher_type='M'

and text != 'Realization of Unrealized Interest' 





select regexp_replace(substr(TEXT,10), '( [^ ]+){3}$', '') xaa --regexp_substr(text, '(.*?)( |$)', 1, 3, NULL, 1) col3,text,length(text)

from GL_AND_HOLD_VOU_ROW_QRY gl

where ACCOUNT='1221100'

and voucher_type='M'

and text != 'Realization of Unrealized Interest' 

and length(text)>32



Order No 16072 R Instalment ID 51 16072 R

Order No 16072 R Instalment ID 52 16072 R


Order No 40735 Instalment ID 8 40735

Order No 40735 Instalment ID 9 40735

Sunday, September 11, 2022

Oracle PL SQL : Learn Bulk Collect and FOR ALL | Bulk Collect and LIMIT With Example

 1. Looping concept            -- .15seconds...
2. Bulk Collect               -- 0seconds..
3. Bulk Collect using LIMIT   --0seconds..
4. Direct Insert              --01seconds..

https://www.youtube.com/watch?v=PM6fLDgSl7I
alter session set current_schema=hr;
SET SERVEROUTPUT ON
select count(*) from EMPLOYEES ;
create table EMPLOYEES2 as select EMPLOYEE_ID,LAST_NAME, DEPARTMENT_ID from EMPLOYEES where 1=2;
select count(*) from employees2;
--(1)
DECLARE 
cursor c1 is 
  select EMPLOYEE_ID,LAST_NAME, DEPARTMENT_ID from EMPLOYEES;
  l_err_count number;
  l_ename varchar2(100);
  l_start number default dbms_utility.get_time;
  BEGIN
  for i in c1 loop 
  insert into EMPLOYEES2(EMPLOYEE_ID,LAST_NAME, DEPARTMENT_ID) values(i.EMPLOYEE_ID,i.LAST_NAME, i.DEPARTMENT_ID);
  end loop;
  commit;
  dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)|| 'seconds...');
EXCEPTION WHEN OTHERS THEN 
dbms_output.put_line('Error:'||sqlerrm);
END;

--(2)
--Bulk Collect
DECLARE 
cursor c1 is 
  select EMPLOYEE_ID,LAST_NAME, DEPARTMENT_ID from EMPLOYEES;
  type tp is table of employees2%rowtype;
  l_tp tp:=tp();
  l_err_count number;
  l_last_name varchar2(100);
  l_start number default dbms_utility.get_time;
  BEGIN
  for i in c1 
  loop 
  l_tp.extend;
  l_tp(l_tp.last).EMPLOYEE_ID   :=i.EMPLOYEE_ID;
  l_tp(l_tp.last).LAST_NAME     :=i.LAST_NAME;
  l_tp(l_tp.last).DEPARTMENT_ID :=i.DEPARTMENT_ID;
  end loop; 
  --forall insert 
  forall i in 1..l_tp.COUNT save exceptions 
  insert into EMPLOYEES2 values l_tp(i);
  commit;
  --
  dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)|| 'seconds...');
EXCEPTION WHEN OTHERS THEN 
l_err_count:=SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line('Number of failures:' ||l_err_count);
FOR i IN 1..l_err_count LOOP
l_last_name := l_tp(SQL%BULK_EXCEPTIONS (i).ERROR_INDEX).last_name;
dbms_output.put_line('Error:'|| i ||'Array Index: '
|| SQL%BULK_EXCEPTIONS(i).error_index||'last_name :'
||l_last_name||''|| 'Message:'|| SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;





--(3)
--Bulk Collect using LIMIT 
DECLARE 
cursor c1 is 
  select EMPLOYEE_ID,LAST_NAME, DEPARTMENT_ID from EMPLOYEES;
  type tp is table of employees2%rowtype index by binary_integer;
  l_tp tp:=tp();
  l_err_count number;
  l_last_name varchar2(100);
  l_start number default dbms_utility.get_time;
  BEGIN
  open c1;
  loop 
      fetch c1 bulk collect into l_tp limit 50000;
  exit when l_tp.COUNT=0;
  --forall insert 
  forall i in 1..l_tp.COUNT save exceptions 
      insert into EMPLOYEES2 values l_tp(i);
      commit;
  --
  end loop;
  close c1;
  
  dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)|| 'seconds...');
EXCEPTION WHEN OTHERS THEN 
l_err_count:=SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line('Number of failures:' ||l_err_count);
FOR i IN 1..l_err_count LOOP
l_last_name := l_tp(SQL%BULK_EXCEPTIONS (i).ERROR_INDEX).last_name;
dbms_output.put_line('Error:'|| i ||'Array Index: '
|| SQL%BULK_EXCEPTIONS(i).error_index||'last_name :'
||l_last_name||''|| 'Message:'|| SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;




--(4)
--Direct Insert
DECLARE 
  l_start number default dbms_utility.get_time;
  BEGIN
      insert into EMPLOYEES2 (EMPLOYEE_ID,LAST_NAME, DEPARTMENT_ID)
  select EMPLOYEE_ID,LAST_NAME, DEPARTMENT_ID from EMPLOYEES;
      commit;
  
  dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)|| 'seconds...');
EXCEPTION WHEN OTHERS THEN 
dbms_output.put_line('Error :' ||sqlerrm);
END;


Saturday, September 10, 2022

Employee, manager, senior manger in sql

 SELECT 

  A.DP_CODE Depot_Name,

  a.empcode rmcode,

         a.e_name rmname,

         b.empcode fmcode,

         b.e_name fmname,

         b.empcodehr hrcodefe,

         c.empcode mrcode,

         c.e_name mrname,

         c.teri_code fopin,

         c.empcode mrcode,

         c.empcodehr hrcodempo,

         SUM (

            DECODE (TO_CHAR (s.vdate, 'MON'),

                    'JAN', NVL (v_disptp, 0) - NVL (v_rettp, 0)

                    ))

            JAN,

         SUM (

            DECODE (TO_CHAR (s.vdate, 'MON'),

                    'FEB', NVL (v_disptp, 0) - NVL (v_rettp, 0)

                    ))

            FEB,

         SUM (

            DECODE (TO_CHAR (s.vdate, 'MON'),

                    'MAR', NVL (v_disptp, 0) - NVL (v_rettp, 0)

                    ))

            MAR,

         SUM (

            DECODE (TO_CHAR (s.vdate, 'MON'),

                    'APR', NVL (v_disptp, 0) - NVL (v_rettp, 0)

                    ))

            APR,

         SUM (

            DECODE (TO_CHAR (s.vdate, 'MON'),

                    'MAY', NVL (v_disptp, 0) - NVL (v_rettp, 0)

                    ))

            MAY

    FROM emp a,

         emp b,

         emp c,

         v$salesreport_f2 s

   WHERE     a.empcode = b.mgr

         AND b.empcode = c.mgr

         AND s.empcode = c.empcode

         AND s.vdate BETWEEN :dt1 AND :dt2

         AND c.empcode = NVL (:p_empcode, c.empcode)

         AND s.dp_code = NVL(:dcode,s.dp_code)

GROUP BY 

A.DP_CODE,

a.empcode,

         a.e_name,

         b.empcode,

         b.e_name,

         c.empcode,

         c.e_name,

         c.teri_code,

         c.empcodehr,

         b.empcodehr

ORDER BY c.e_name

Bulk Inserts with Oracle

 CREATE TABLE t1 AS SELECT * FROM all_objects WHERE 1 = 2;


CREATE OR REPLACE PROCEDURE test_proc (p_array_size IN PLS_INTEGER DEFAULT 100)
IS
TYPE ARRAY IS TABLE OF all_objects%ROWTYPE;
l_data ARRAY;

CURSOR c IS SELECT * FROM all_objects;

BEGIN
    OPEN c;
    LOOP
    FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;


    FORALL i IN 1..l_data.COUNT
    INSERT INTO t1 VALUES l_data(i);


    EXIT WHEN c%NOTFOUND;
    END LOOP;
    CLOSE c;
END test_proc;
/


exec test_proc;

Friday, September 9, 2022

cursor with procedure

 CREATE OR REPLACE Procedure REPORTDB.IAL_C_LEASING_DET_UNORD_IFS_PROC

IS

   CURSOR cur

   IS

      (

--last create and modify by Md. Quium Hossain

--modify date 11-09-2022 9:20 AM

--this procedure is use (1)IAL_C_LEASING_CUSTOMER_ORDER_IFS_PROC

--this procedure is use (2)IAL_C_LEASING_DET_UNORD_IFS_PROC

--this procedure is use (3)IAL_C_CO_COLLECTION_DETAIL_FULL_PROC

SELECT t.company, 

       t.order_no, 

       t.identity,

       t.invoice_id,

       t.installment_id,

       t.due_date,    

       t.open_amount,

       t.curr_amount,             

       t.state     ,

       (SELECT MAX(due_date) last_date FROM ifsapp.ial_leasing_det_view@prod l

        WHERE t.order_no=l.order_no) last_instal_date,

       t.c_interest_amount       

FROM  ifsapp.IAL_C_LEASING_DET_UNORD_VIEW@prod t

);


BEGIN


EXECUTE IMMEDIATE 'TRUNCATE TABLE reportdb.IAL_C_LEASING_DET_UNORD_TAB_IFS';

commit;


   FOR rec IN cur

   LOOP

INSERT INTO reportdb.IAL_C_LEASING_DET_UNORD_TAB_IFS(

         company                   ,

         order_no                  ,

         identity                  ,

         invoice_id                ,

         installment_id            ,

         due_date                  ,

         open_amount               ,

         curr_amount               ,

         state                     ,

         last_instal_date          ,

         c_interest_amount         

     )

VALUES(

         rec.company               ,

         rec.order_no              ,

         rec.identity              ,

         rec.invoice_id            ,

         rec.installment_id        ,

         rec.due_date              ,

         rec.open_amount           ,

         rec.curr_amount           ,

         rec.state                 ,

         rec.last_instal_date      ,

         rec.c_interest_amount     

     );

   END LOOP;

COMMIT;

END;

/


Truncate Table in Oracle Procedure

CREATE OR REPLACE PROCEDURE trnct_table (i_table_name IN VARCHAR2)
IS
BEGIN
   EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || i_table_name;
   DBMS_OUTPUT.put_line (
      'Table ' || i_table_name || ' truncated successfully.');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Truncate table failed.');
END; 


SET SERVEROUTPUT ON;

BEGIN
   trnct_table ('emp');
END;
/

Friday, July 29, 2022

update by cursor in oracle

 DECLARE 

   CURSOR CUR

   IS   

SELECT CREATORS_REFERENCE ORDER_NO, MIN(INVOICE_ID) INVOICE_ID

    FROM IFSAPP.CUST_ORDER_INV_HEAD_UIV_ALL@PROD I

    WHERE  I.CREATORS_REFERENCE IN (SELECT ORDER_NO FROM DIFF_HAPS_IFS_TAB WHERE CREATORS_REFERENCE=ORDER_NO)

    GROUP BY CREATORS_REFERENCE;

BEGIN


   FOR REC IN CUR

   LOOP

      UPDATE DIFF_HAPS_IFS_TAB SET INVOICE_ID=REC.INVOICE_ID

      WHERE ORDER_NO=REC.ORDER_NO;

   END LOOP;

   COMMIT;

END;

/


Thursday, July 7, 2022

Scheduler in all

--OS Scheduler--

Windows



reportdb/r@reportdb @"D:\Schedule Query\IAL_C_COWISE_CUSTOMER_INFO.sql"

New Add Multiple scheduler time

Linux

--DB Scheduler--

Oracle

Sql Server


Saturday, July 2, 2022

Date format

 select  ADD_MONTHS(TRUNC(SYSDATE,'MM'),-1), last_day(add_months(trunc(sysdate,'mm'),-1))-- , TRUNC(LAST_DAY (SYSDATE - 1))

from DUAL

Tuesday, June 21, 2022

Sql - comma function

 CREATE OR REPLACE FUNCTION EX_number_format( ff number) RETURN char IS

   xx varchar2(100);

   yy varchar2(1);

   tt varchar2(100);

  x number:=0;

  begin

   xx:=to_char(ff,'99G990G990G990G990G990D00');

  for i  in 1..length(xx) loop

   yy:=substr(xx,i,1);

 if yy in ('1','2','3','4','5','6','7','8','9') then

  x:=i;

 exit;

 end if;

 end loop;

 tt:=substr(xx,x,length(xx));

return tt;

 END EX_number_format;


Monday, June 20, 2022

19c- Install Oracle database version 19.3.0 on Linux OS

Basic Configuration
=======================================
---host configuration---
[root@ol8dbs ~]# vi etc/hosts
set ip and hosts
192.168.192.128 ol8dbs.localdomain ol8dbs
[root@ol8dbs ~]# vi etc/hostname 
ol8dbs.localdomain
---set selinux--
[root@ol8dbs ~]# vi /etc/selinux/config
selinux=permissive
---set fireawalld disable--
[root@ol8dbs ~]# systemctl stop firewalld
[root@ol8dbs ~]# systemctl disable firewalld
[root@ol8dbs ~]# systemctl start sshd.service
[root@ol8dbs ~]# systemctl enable sshd.service
---Prrequisite for oracle install--
[root@ol8dbs ~]# dnf install -y oracle-database-preinstall-19c
--# New for OL8--
dnf install -y libnsl
dnf install -y libnsl.i686
dnf install -y libnsl2
dnf install -y libnsl2.i686
--- Create the new group and users---
groupadd -g 54321 oinstall
groupadd -g 54322 dba
groupadd -g 54323 oper
useradd -u 54321 -g oinstall -G dba,oper oracle

--set password for oracle user-- 
passwd oracle
oracle123
---Create the directories in which the Oracle software will be installed:---
[root@ol8dbs ~]# mkdir -p /u01/app/oracle/product/19.0.0/dbhome_1
[root@ol8dbs ~]# mkdir -p /u01/oradata
[root@ol8dbs ~]# chown -R oracle:oinstall /u01
[root@ol8dbs ~]# chmod -R 775 /u01

-- Create a script directory--
mkdir /home/oracle/scripts
[root@ol8dbs ~]# cat> /home/oracle/scripts/setEnv.sh <<EOF

# Oracle Settings
export TMP=/tmp
export TMPDIR=\$TMP
export ORACLE_HOSTNAME=ifsrepdb.ifadgroup.com
export ORACLE_UNQNAME=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=\$ORACLE_BASE/product/19.0.0/dbhome_1
export ORA_INVENTORY=/u01/app/oraInventory
export ORACLE_SID=orcl
# export PDB_NAME=orclpdb
export DATA_DIR=/u01/oradata
export PATH=/usr/sbin:/usr/local/bin:\$PATH
export PATH=\$ORACLE_HOME/bin:\$PATH
export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=\$ORACLE_HOME/jlib:\$ORACLE_HOME/rdbms/jlib
EOF




Add a reference to the "setEnv.sh" file at the end of the "/home/oracle/.bash_profile" file:
----------------------
[root@ol8dbs ~]# cd /home/oracle/scripts 
echo ". /home/oracle/scripts/setEnv.sh" >> /home/oracle/.bash_profile
Create a "start_all.sh" and "stop_all.sh" script that can be called from a startup/shutdown service. Make sure the ownership and permissions are correct.--
---------------
cat > /home/oracle/scripts/start_all.sh <<EOF
#!/bin/bash
. /home/oracle/scripts/setEnv.sh
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES
dbstart \$ORACLE_HOME
EOF
cat > /home/oracle/scripts/stop_all.sh <<EOF
#!/bin/bash
. /home/oracle/scripts/setEnv.sh
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES
dbshut \$ORACLE_HOME
EOF

chown -R oracle:oinstall /home/oracle/scripts
chmod u+x /home/oracle/scripts/*.sh


Install Oracle Database 19c
=========================================
switch oralce user 
su - oracle 
[oracle@ol8dbs ~] echo $ORACLE_HOME
[oracle@ol8dbs ~] source scripts/setEnv.sh
[oracle@ol8dbs ~] echo $ORACLE_HOME
cd $ORACLE_HOME
or cd /u01/app/oracle/product/19.0.0/dbhome_1
# unzip
# This code open blank terminal 
unzip -oq /home/oracle/Downloads/LINUX.X64_193000_db_home.zip
# go to /u01/app/oracle/product/19.0.0/dbhome_1 and open terminal 
unzip /home/oracle/Downloads/LINUX.X64_193000_db_home.zip



export DISPLAY=:0
xhost +
# Fake Oracle Linux 7.
export CV_ASSUME_DISTID=OEL7.6
# go to /u01/app/oracle/product/19.0.0/dbhome_1/cv/admin
# cvu_config
CV_ASSUME_DISTID=OLE8

# MobaXterm
./runInstaller



Conn: sys as sysdba
show con_name

[oracle@ol8dbs ~]$ lsnrctl status
[oracle@ol8dbs ~]$ lsnrctl stop
[oracle@ol8dbs ~]$ lsnrctl start
[oracle@ol8dbs ~]$ source scripts/setEnv.sh
[oracle@ol8dbs ~]$ netmgr  






pass dan
Sys@123#
root
root123 
yes
DOne

https://oracle-base.com/articles/19c/oracle-db-19c-installation-on-oracle-linux-8




































Saturday, June 18, 2022

crond scheduler in oracle dba

--u02/schedule_scripts -- where is file save .sh  
 .sh 
#/bin/bash
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=***
export ORACLE_UNQNAME=***
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:/usr/local/bin:/usr/sbin:/usr/bin:/usr/openwin/bin:/usr/ucb:$ORACLE_HOME/bin
#export ORACLE_BASE ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH PATH
sqlplus scott/tiger <<EOF
exec procedure_name;
exit
EOF
=====================================================================
 
.sh 
#/bin/bash
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export ORACLE_SID=orcl
export ORACLE_UNQNAME=orcl
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:/usr/local/bin:/usr/sbin:/usr/bin:/usr/openwin/bin:/usr/ucb:$ORACLE_HOME/bin
#export ORACLE_BASE ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH PATH
sqlplus reportdb/r <<EOF
 
# write here your program code
insert into test_tab(id , edate)
select 1, sysdate from dual;
commit;
exit
EOF


--u02/schedule_scripts --crontab -e where is file in 

15 20 * * * /u02/schedule_scripts/test_crond.sh >>/u02/schedule_scripts/error_Log.txt


# go to folder to then run

sh  test_crond.sh 

//////////////////////////////////

# Oracle Settings

export TMP=/tmp
export TMPDIR=$TMP

export ORACLE_HOSTNAME=ol8dbs.localdomain
export ORACLE_UNQNAME=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1
export ORA_INVENTORY=/u01/app/oraInventory
export ORACLE_SID=orcl
export PDB_NAME=orclpdb
export DATA_DIR=/u01/oradata

export PATH=/usr/sbin:/usr/local/bin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

Saturday, May 28, 2022

4 fiscal quarters in oracle sql

 SELECT to_char( add_months( '&report_date_', 7 ), 'YYYY' ) fiscal_year,

       'Q'||to_char( add_months( '&report_date_', 7 ), 'Q' ) fiscal_quarter

  FROM dual;

Saturday, April 23, 2022

Descending order in Array in C Programming

 #include <stdio.h>
void main (){
   int num[20];
   int i, j, a, n;
   printf("Number of array :");
   scanf("%d", &n);
   printf("Enter Random Number : ");

   for (i = 0; i < n; ++i)
      scanf("%d", &num[i]);

   for (i = 0; i < n; ++i){

      for (j = i + 1; j < n; ++j){

if (num[i] < num[j]){
            a = num[i];
            num[i] = num[j];
            num[j] = a;
}
      }
   }
   printf("Descending order :\n");
   for (i = 0; i < n; ++i){
      printf("%d\n", num[i]);
   }
}

Ascending order in Array in C Programming

 #include <stdio.h>

void main (){

   int num[20];

   int i, j, a, n;

   printf("Number of array : ");

   scanf("%d", &n);

   printf("Enter Random numbers : ");


   for (i = 0; i < n; ++i)    

      scanf("%d", &num[i]);


   for (i = 0; i < n; ++i){       

      for (j = i + 1; j < n; ++j){

          

         if (num[i] > num[j]){

            a = num[i];

            num[i] = num[j];

            num[j] = a;

         }

      }

   }

   printf("Ascending order :\n");

   for (i = 0; i < n; ++i){

      printf("%d\n", num[i]);

   }

}


Wednesday, April 20, 2022

work

 What is Recursion? 

The process in which a function calls itself directly or indirectly is called recursion and the corresponding function is called as recursive function.

 

Using recursive algorithm, certain problems can be solved quite easily. Examples of such problems are Towers of Hanoi (TOH)Inorder/Preorder/Postorder Tree TraversalsDFS of Graph, etc.

 

C function argument and return values

A function in C can be called either with arguments or without arguments. These function may or may not return values to the calling functions. All C functions can be called either with arguments or without arguments in a C program. Also, they may or may not return any values. Hence the function prototype of a function in C is as below:

 

 

There are following categories:

 

 

Function with no argument and no return value : When a function has no arguments, it does not receive any data from the calling function. Similarly when it does not return a value, the calling function does not receive any data from the called function.

Syntax :

Function declaration : void function();

Function call : function();

Function definition :

                      void function()

                      {

                        statements;

                      }

 

// C code for  function with no

//  arguments and no return value

 

#include <stdio.h>

void value(void);

void main()

{

    value();

}

void value(void)

{

    int year = 1, period = 5, amount = 5000, inrate = 0.12;

    float sum;

    sum = amount;

    while (year <= period) {

        sum = sum * (1 + inrate);

        year = year + 1;

    }

    printf(" The total amount is %f:", sum);

}

Output:

 

The total amount is 5000.000000

Function with arguments but no return value : When a function has arguments, it receive any data from the calling function but it returns no values.

Syntax :

 

Function declaration : void function ( int );

Function call : function( x );

Function definition:

             void function( int x )

             {

               statements;

             }

 

// C code for function

// with argument but no return value

#include <stdio.h>

 

void function(int, int[], char[]);

int main()

{

    int a = 20;

    int ar[5] = { 10, 20, 30, 40, 50 };

    char str[30] = "geeksforgeeks";

    function(a, &ar[0], &str[0]);

    return 0;

}

 

void function(int a, int* ar, char* str)

{

    int i;

    printf("value of a is %d\n\n", a);

    for (i = 0; i < 5; i++) {

        printf("value of ar[%d] is %d\n", i, ar[i]);

    }

    printf("\nvalue of str is %s\n", str);

}

Output:

 

value of a is 20

value of ar[0] is 10

value of ar[1] is 20

value of ar[2] is 30

value of ar[3] is 40

value of ar[4] is 50

The given string is : geeksforgeeks

Function with no arguments but returns a value : There could be occasions where we may need to design functions that may not take any arguments but returns a value to the calling function. A example for this is getchar function it has no parameters but it returns an integer an integer type data that represents a character.

Syntax :

Function declaration : int function();

Function call : function();

Function definition :

                 int function()

                 {

                     statements;

                      return x;

                  }

   

 

// C code for function with no arguments

// but have return value

#include <math.h>

#include <stdio.h>

 

int sum();

int main()

{

    int num;

    num = sum();

    printf("\nSum of two given values = %d", num);

    return 0;

}

 

int sum()

{

    int a = 50, b = 80, sum;

    sum = sqrt(a) + sqrt(b);

    return sum;

}

Output:

 

Sum of two given values = 16

Function with arguments and return value

Syntax :

Function declaration : int function ( int );

Function call : function( x );

Function definition:

             int function( int x )

             {

               statements;

               return x;

             }

 

// C code for function with arguments

// and with return value

 

#include <stdio.h>

#include <string.h>

int function(int, int[]);

 

int main()

{

    int i, a = 20;

    int arr[5] = { 10, 20, 30, 40, 50 };

    a = function(a, &arr[0]);

    printf("value of a is %d\n", a);

    for (i = 0; i < 5; i++) {

        printf("value of arr[%d] is %d\n", i, arr[i]);

    }

    return 0;

}

 

int function(int a, int* arr)

{

    int i;

    a = a + 20;

    arr[0] = arr[0] + 50;

    arr[1] = arr[1] + 50;

    arr[2] = arr[2] + 50;

    arr[3] = arr[3] + 50;

    arr[4] = arr[4] + 50;

    return a;

}

Output:

 

value of a is 40

value of arr[0] is 60

value of arr[1] is 70

value of arr[2] is 80

value of arr[3] is 90

value of arr[4] is 100

 

 

Call by value and Call by reference in C

There are two methods to pass the data into the function in C language, i.e., call by value and call by reference.

call by value and call by reference in c

Let's understand call by value and call by reference in c language one by one.


Call by value in C

·       In call by value method, the value of the actual parameters is copied into the formal parameters. In other words, we can say that the value of the variable is used in the function call in the call by value method.

·       In call by value method, we can not modify the value of the actual parameter by the formal parameter.

·       In call by value, different memory is allocated for actual and formal parameters since the value of the actual parameter is copied into the formal parameter.

·       The actual parameter is the argument which is used in the function call whereas formal parameter is the argument which is used in the function definition.

Let's try to understand the concept of call by value in c language by the example given below:

 

#include<stdio.h>  

void change(int num) {    

    printf("Before adding value inside function num=%d \n",num);    

    num=num+100;    

    printf("After adding value inside function num=%d \n", num);    

}    

int main() {    

    int x=100;    

    printf("Before function call x=%d \n", x);    

    change(x);//passing value in function    

    printf("After function call x=%d \n", x);    

return 0;  

}    

Output

Before function call x=100
Before adding value inside function num=100
After adding value inside function num=200
After function call x=100

Call by Value Example: Swapping the values of the two variables

 

#include <stdio.h>  

void swap(int , int); //prototype of the function   

int main()  

{  

    int a = 10;  

    int b = 20;   

    printf("Before swapping the values in main a = %d, b = %d\n",a,b); // printing the value of a and b in main  

    swap(a,b);  

    printf("After swapping values in main a = %d, b = %d\n",a,b); // The value of actual parameters do not change by changing the formal parameters in call by value, a = 10, b = 20  

}  

void swap (int a, int b)  

{  

    int temp;   

    temp = a;  

    a=b;  

    b=temp;  

    printf("After swapping values in function a = %d, b = %d\n",a,b); // Formal parameters, a = 20, b = 10   

}  

Output

Before swapping the values in main a = 10, b = 20
After swapping values in function a = 20, b = 10
After swapping values in main a = 10, b = 20  

Call by reference in C

·       In call by reference, the address of the variable is passed into the function call as the actual parameter.

·       The value of the actual parameters can be modified by changing the formal parameters since the address of the actual parameters is passed.

·       In call by reference, the memory allocation is similar for both formal parameters and actual parameters. All the operations in the function are performed on the value stored at the address of the actual parameters, and the modified value gets stored at the same address.

 

 

 

 

Difference between call by value and call by reference in c

No.

Call by value

Call by reference

1

A copy of the value is passed into the function

An address of value is passed into the function

2

Changes made inside the function is limited to the function only. The values of the actual parameters do not change by changing the formal parameters.

Changes made inside the function validate outside of the function also. The values of the actual parameters do change by changing the formal parameters.

3

Actual and formal arguments are created at the different memory location

Actual and formal arguments are created at the same memory location

 

 

Features of C Language

C features

C is the widely used language. It provides many features that are given below.

Simple

Machine Independent or Portable

Mid-level programming language

structured programming language

Rich Library

Memory Management

Fast Speed

Pointers

Recursion

Extensible

 

 

 


1) Simple

C is a simple language in the sense that it provides a structured approach (to break the problem into parts), the rich set of library functionsdata types, etc.


2) Machine Independent or Portable

Unlike assembly language, c programs can be executed on different machines with some machine specific changes. Therefore, C is a machine independent language.


3) Mid-level programming language

Although, C is intended to do low-level programming. It is used to develop system applications such as kernel, driver, etc. It also supports the features of a high-level language. That is why it is known as mid-level language.


4) Structured programming language

C is a structured programming language in the sense that we can break the program into parts using functions. So, it is easy to understand and modify. Functions also provide code reusability.


5) Rich Library

provides a lot of inbuilt functions that make the development fast.


6) Memory Management

It supports the feature of dynamic memory allocation. In C language, we can free the allocated memory at any time by calling the free() function.


7) Speed

The compilation and execution time of C language is fast since there are lesser inbuilt functions and hence the lesser overhead.


8) Pointer

C provides the feature of pointers. We can directly interact with the memory by using the pointers. We can use pointers for memory, structures, functions, array, etc.


9) Recursion

In C, we can call the function within the function. It provides code reusability for every function. Recursion enables us to use the approach of backtracking.


10) Extensible

C language is extensible because it can easily adopt new features.

 

 

printf() and scanf() in C

The printf() and scanf() functions are used for input and output in C language. Both functions are inbuilt library functions, defined in stdio.h (header file).

printf() function

The printf() function is used for output. It prints the given statement to the console.

The syntax of printf() function is given below:

printf("format string",argument_list);  

The format string can be %d (integer), %c (character), %s (string), %f (float) etc.

 

scanf() function

The scanf() function is used for input. It reads the input data from the console.

scanf("format string",argument_list);  

Program to print cube of given number

Let's see a simple example of c language that gets input from the user and prints the cube of the given number.

#include<stdio.h>    

int main(){    

int number;    

printf("enter a number:");    

scanf("%d",&number);    

printf("cube of number is:%d ",number*number*number);    

return 0;  

}    

Output

enter a number:5
cube of number is:125

The scanf("%d",&number) statement reads integer number from the console and stores the given value in number variable.

The printf("cube of number is:%d ",number*number*number) statement prints the cube of number on the console.

 

5 Write a program to calculate the sum of series. ( odd number and even number)

 

#include<stdio.h>

int main ()

{

    int n,i,odd=0, even=0;

    printf("Enter the number : ");

    scanf("%d",&n);

 

    printf("1+2+3+...........+%d",n);

    for (i=1;i<=n; i++)

    {

        if (i%2==0)

            even = even+i;

        else

            odd = odd+i;

    }

    printf(" = sum of even = %d, sum of odd = %d, Sum %d",even,odd, even-odd);

    return 0;

}

 

 

#include<stdio.h>

C program to check whether a number is prime or not.

int main()

{

    int n;

    printf("Enter the number :   ");

    scanf("%d",&n);

 

 

    if (n%2==0)

        printf("%d is a prime number",n);

    else

        printf("%d is not a prime number",n);

 

    return 0;

}

 

 

 

 

#include <stdio.h>

void main()

{

   int i,j,rows;

   printf("Input number of rows : ");

   scanf("%d",&rows);

   for(i=1; i<=rows; i++)

   {

              for(j=1;j<=i;j++)

                 printf("*");

              printf("\n");

   }

}

 

To generate a PDF using JavaScript in Oracle APEX from a collection

  To generate a PDF using JavaScript in Oracle APEX from a collection, you can follow these steps: 1. Create a button or link on your APEX p...