Oracle Apex, Forms, Reports

Md. Quium Hossain who I'm Oracle DBA & APEX Developer. All-rounder in building small, medium, and enterprise applications. Extensive knowledge in various areas of web-driven applications in Back-end (PL/SQL, SQL, Java), Front-end (Oracle APEX, Oracle Forms, Oracle Reports, HTML, JavaScript, CSS, jQuery, OracleJET, ReactJS), RESTful APIs, Third-party library integrations (Apex Office Print (AOP), Payment Gateways, SMS, Syncfusion, HighCharts) and APEX Plugins (HighChart, StarRating)

Wednesday, March 31, 2021

Ledger report in crystal report

--Formula CURRENT_BALANCE

{#RT_OPENING_BALANCE} + IF ISNULL ({#RT_DEBIT}) THEN 0 ELSE {#RT_DEBIT}

- IF ISNULL ({#RT_CREDIT}) THEN 0 ELSE {#RT_CREDIT}

Monday, March 29, 2021

How to connect Php to oracle

This video describes how Oracle 12c database can be connected with PHP using XAMPP.

Pre-requisite:

1) XAMPP installation completed

https://www.apachefriends.org/download.html

2) Instant Client Package Download Link :

https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html

3) C:\instantclient_12_1 Add Path in Environment Variable

4) XAMPP Apache -- Config -- PHP.ini

extension=oci8_12c ; Use with Oracle Database 12c Instant Client

;extension=oci8_19 ; Use with Oracle Database 19 Instant Client

4) C:\xampp\htdocs\test.php

<?php

$conn = oci_connect('test', 'test', 'orcl1', 'AL32UTF8');

if (!$conn) {

$e = oci_error();

trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);

}

Else echo "connection successful";

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

<?php

$conn = oci_connect('system', 'oracle', 'localhost/orcl', 'AL32UTF8');

if (!$conn) {

$e = oci_error();

trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);

}

Else echo "connection successful";

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

Example. oracle.php

<?php

$conn = oci_connect('hr', 'hr', 'orcl1', 'AL32UTF8');

if (!$conn) {

$e = oci_error();

trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);

}

Else echo "connection successful";

<html>

<head>

</head>

<body>

<h2>Test Results</h2>

<?php

$result =oci_parse($conn, "select department_name from departments");

oci_execute($result);

<ul>

<?php

while ($row =oci_fetch_array($result))

{

<?php

}

</ul>

</body>

</head>

</html>

Saturday, March 27, 2021

Return the checkmark symbol from a formula

Hi....this comes from the Crystal Knowledgebase artical C2010279

********************************
4. Create a formula similar to the following:
// (xxx) represents the character you want to display
CHR(xxx)
For example:
CHR(254) displays a check box with a check mark in the report and CHR(252) only displays the checkmark without the box in the report.
// CHR(254) - displays a box with a check mark
// CHR(253) - displays a box with an X
// CHR(111) - displays an empty box
// CHR(252) - displays a check mark only
// CHR(251) - displays an X only
5. Insert the formula into the report.
6. Right-click the @CheckBox formula field, and select 'Font'.
7. From 'Font', select 'Wingdings', and click 'OK'.

If ({X_BD_MUL_COMP_REP.MULTI_COM}='FALSE') THEN

Chr(251)

ELSE

Chr(252)

IF({Customers.Registered}='Yes')THEN
Chr(254)
ELSE
Chr(168)

Tuesday, March 23, 2021

Oracle Date Function

select to_char(add_months(to_date((26||'-'||substr('&W2021M02',7,2)||'-'||substr('&W2021M02',2,4)),'DD-MM-RRRR'),-1),'DD-MON-RRRR')

,to_char(to_date((25||'-'||substr('&W2021M02',7,2)||'-'||substr('&W2021M02',2,4)),'DD-MM-RRRR'),'DD-MON-RRRR') from dual;

to_char(to_date(substr('W2021M02',7,2),'MM'),'Month')||'-'||to_char(to_date(substr('W2021M02',2,4),'RRRR'),'RRRR') MMRR

Friday, March 19, 2021

Oracle Sql Most uses Function

ROLLUP (Grand total)

select department_id , sum(salary)

from hr.employees

group by rollup(department_id)

select department_id ,E.JOB_ID, sum(salary)

from hr.employees e

group by rollup(department_id,e.job_id)

How to get the employees with their managers

Self -Join

SELECT e.last_name, e.employee_id, m.last_name as manager, e.manager_id, m.last_name,m.employee_id

FROM

hr.employees e, hr.employees m

WHERE e.manager_id = m.employee_id

order by m.employee_id DESC

And if you want to include the president which has no manager then instead of an inner join use an outer join in Oracle syntax:

inner join

SELECT e.last_name, e.employee_id, NVL(m.last_name,'No Manager') as manager, e.manager_id, m.last_name,m.employee_id

FROM

hr.employees e, hr.employees m

WHERE e.manager_id = m.employee_id (+)

order by m.employee_id DESC

ANSI SQL

SELECT e.last_name, e.employee_id, m.last_name as manager, e.manager_id, m.last_name,m.employee_id

FROM

hr.employees e LEFT OUTER JOIN hr.employees m

on e.manager_id = m.employee_id

order by m.employee_id DESC

Count All Employees Under Each Manager

SELECT

sup.employee_id,

sup.first_name,

sup.last_name,

COUNT (sub.employee_id) AS number_of_employees

FROM hr.employees sub

JOIN hr.employees sup

ON sub.manager_id = sup.employee_id

GROUP BY sup.employee_id, sup.first_name, sup.last_name;

Find All Direct Subordinates Under Each Manager

SELECT

sub.employee_id AS subordinate_id,

sub.first_name AS subordinate_first_name,

sub.last_name AS subordinate_last_name,

sup.employee_id AS superior_id,

sup.first_name AS superior_first_name,

sup.last_name AS superior_last_name

FROM hr.employees sub

JOIN hr.employees sup

ON sub.manager_id = sup.employee_id

ORDER BY superior_id;

Tuesday, March 16, 2021

Presentation of Software

Sunday, March 14, 2021

To set password limit Unlimited in Oracle 11G

When You tried login your user(TEST) it is showing password will expired with 7 Days.

[oracle@k44-db1]$ sqlplus test/test

SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar 11 22:34:42 2013

Enter password:

ERROR:

ORA-28002: the password will expire within 7 days

Connected to:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL>

Get to know which profile the test user is using from dba_users view,

SQL> SELECT profile FROM dba_users WHERE username='TEST';

USERNAME PROFILE

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

TEST DEFAULT

Now check the password lifetime of the default profile from dba_profiles

SQL> SELECT resource_name,limit FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

and than set default profile limit to unlimited for below query,

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.

after profile limit changed and Alter user(TEST) password for the below statement,

SQL> alter user test identified by test;

User altered.

after that alter username and try to connect test user,

[oracle@k44-db1]$ sqlplus test/test

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 25 11:50:15 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

The database is down. Please specify the host credentials to access database restart and diagnostics tools

The database is down. Please specify the host credentials to access database restart and diagnostics tools.

Enterprise manager shows that database is down but in real it is not.

[oracle@grhldb01 trace]$ sqldba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 13 10:38:01 2013

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

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

TCHDB READ WRITE

check the listener status.

[oracle@grhldb01 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 26-AUG-2014 13:16:23

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date 13-MAR-2014 12:53:12

Uptime 165 days 23 hr. 23 min. 10 sec

Trace Level off

Security ON: Local OS Authentication

SNMP ON

Listener Parameter File /orabin/grid11gR2/grid/network/admin/listener.ora

Listener Log File /orabin/grid11gR2/base/diag/tnslsnr/grhldb01/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.128.0.42)(PORT=1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for this service...

Service "TCHDB" has 2 instance(s).

Instance "TCHDB1", status READY, has 2 handler(s) for this service...