Tuesday 31 March 2020

Export application through command prompt

Let's Start

Set your ORACLE_HOME

C:\Users\Administrator>set ORACLE_HOME=D:\app\Administrator\product\12.1.0\dbhome_1

Set your JDK PATH

C:\Users\Administrator>set PATH=%PATH%;C:\Program Files\Java\jdk1.8.0_241\bin

add jdbc driver and utilities to your classpath is where Apex is installed

C:\Users\Administrator>set CLASSPATH=D:\app\Administrator\product\12.1.0\dbhome_1\jdbc\lib\ojdbc6.jar;D:\apex\utilities

run the export

C:\Users\Administrator>java oracle.apex.APEXExport -db localhost:1521:test -user apexuser -password apexuser -applicationid 100

C:\Users\Administrator>dir f100.sql

Saturday 20 July 2019

Show Application Navigation Manu Bar On Mobile Oracle Apex

Some times Oracle Apex Applications manu bar may not show on mobile device. You can solve this problem by doing this Dynamic Action.

1. Create a Dynamic Action in 0 page.

2.Set Event : Page Load Execute JavaScript Code

3. Set Action : Execute JavaScript Code Now you can try on mobile device

4. Then copy pest this JavaScript Code :

$(".t-Header-nav").removeClass("t-Header-nav");

Now you can try on mobile device.

Monday 26 November 2018

Email Jasper Report in Oracle Apex (Attachment email)

Download Apache Tomcat 7 from the tomcat website https://tomcat.apache.org/
Installation & Configuration of Apache Tomcat

The first step is to create the ACL and define the privileges for it:
The general syntax is as follows:

Conn sys as sysdba

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'www.xml',
description => 'WWW ACL',
principal => 'SCOTT',
is_grant => true,
privilege => 'connect');

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'www.xml',
principal => 'SCOTT',
is_grant => true,
privilege => 'resolve');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'www.xml',
host => 'localhost', --Apache Tomcat URL
lower_port => 8080, --Apache Tomcat port
upper_port => 8080); --Apache Tomcat port
END;
COMMIT;

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'www.xml',
description => 'WWW ACL',
principal => 'SCOTT',
is_grant => true,
privilege => 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'www.xml',
principal => 'SCOTT',
is_grant => true,
privilege => 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'www.xml',
host => 'smtp.mycompany.com'); --- mail server address
END;
COMMIT;

grant execute on sys.utl_http to scott;

Download JasperReportsIntegration File from the link below

http://www.opal-consulting.de/downloads/free_tools/JasperReportsIntegration/2.4.0/

Now copy the library file ojdbc6, orai18n from the directory C:\JasperReportsIntegration\lib & paste into
the directory C:\Program Files\Apache Software Foundation\Tomcat 7.0\lib

Now copy the JasperReportsIntegration.war file from the directory C:\JasperReportsIntegration-2.4.0.0\webapp\JasperReportsIntegration.war and paste in the directory C:\Program Files\Apache Software Foundation\Tomcat 7.0\webapps

After starting the service of Apache Tomcat you will see there is a new folder JasperReportsIntegration has
been created under the directory C:\Program Files\Apache Software Foundation\Tomcat 7.0\webapps

Now edit the application file under the directory C:\Program Files\Apache Software Foundation\Tomcat 7.0\webapps\JasperReportsIntegration\WEB-INF\conf

Open the application file with any editor like notepad/notepad++/word pad etc. in the content you will find

edit application file
[datasource:default]
type=jdbc
name=default
url=jdbc:oracle:thin:@localhost:1521:Oracle
username=SCOTT -- Database user
password=tiger

In the URL text : localhost = the host name, 1521 = Database port, Oracle = Oracle DB Service Name
open application file and make changes

After editing the application file, do not forget to save the content.

Now open a browser window and type the link http://localhost:8080/manager (from remote/local
machine) or http://localhost:8080/manager (from local machine)

You will see the home page of Apache Tomcat and the /JasperReportsIntegration has been added in the
application list and the running status is showing true.

Now click the /JasperReportsIntegration link and you will be redirected to the sample

Now click the show report button (Senden), you will see the PDF report

Conn scott/tiger

Create Function

CREATE OR REPLACE FUNCTION load_binary_from_url (p_url IN VARCHAR2)
RETURN BLOB
AS
l_http_request UTL_HTTP.req;
l_http_response UTL_HTTP.resp;
l_blob BLOB;
l_raw RAW(32767);
BEGIN
-- Initialize the BLOB.
DBMS_LOB.createtemporary(l_blob, FALSE);

-- Make a HTTP request and get the response.
l_http_request := UTL_HTTP.begin_request(p_url);
l_http_response := UTL_HTTP.get_response(l_http_request);

-- Copy the response into the BLOB.
BEGIN
LOOP
UTL_HTTP.read_raw(l_http_response, l_raw, 32767);
DBMS_LOB.writeappend (l_blob, UTL_RAW.length(l_raw), l_raw);
END LOOP;
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
UTL_HTTP.end_response(l_http_response);
END;

RETURN l_blob;

EXCEPTION
WHEN OTHERS THEN
UTL_HTTP.end_response(l_http_response);
DBMS_LOB.freetemporary(l_blob);
RAISE;
END load_binary_from_url;

Create Attachment email procedure

CREATE OR REPLACE PROCEDURE attach_mail
(p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_subject IN VARCHAR2,
p_text_msg IN VARCHAR2 DEFAULT NULL,
p_attach_name IN VARCHAR2 DEFAULT NULL,
p_attach_mime IN VARCHAR2 DEFAULT NULL,
p_attach_blob IN BLOB DEFAULT NULL,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 25)
AS
l_mail_conn UTL_SMTP.connection;
l_boundary VARCHAR2(50) := '----=*#abc1234321cba#*=';
l_step PLS_INTEGER := 12000; -- make sure you set a multiple of 3 not higher than 24573
BEGIN
l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
UTL_SMTP.helo(l_mail_conn, p_smtp_host);
UTL_SMTP.mail(l_mail_conn, p_from);
UTL_SMTP.rcpt(l_mail_conn, p_to);

UTL_SMTP.open_data(l_mail_conn);

UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

IF p_text_msg IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

UTL_SMTP.write_data(l_mail_conn, p_text_msg);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;

IF p_attach_name IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_blob) - 1 )/l_step) LOOP
UTL_SMTP.write_data(l_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, l_step, i * l_step + 1))));
END LOOP;

UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;

UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
UTL_SMTP.close_data(l_mail_conn);

UTL_SMTP.quit(l_mail_conn);
END;

Create Ireport with parameter and generate jasper file

Now Copy JASPER FILE and paste in the directory C:\Program Files\Apache Software Foundation\Tomcat 7.0\webapps\JasperReportsIntegration\WEB-INF\reports

send attachment email using plsql code

DECLARE
l_blob BLOB;
l_name varchar2(30);
RECIPIENTS_V VARCHAR2 (200);
BEGIN
l_blob:= load_binary_from_url('http://localhost:8080/JasperReportsIntegration/report?_repName=test&_repFormat=pdf&_dataSource=default&parameter='||:P_PARAMETER);

l_name:= 'test - '||to_date(sysdate)||'.pdf';

attach_mail(
p_to => '',
p_from => '',
p_subject => 'Report',
p_text_msg => 'This is a test message.',
p_attach_name => l_name,
p_attach_mime => 'application/pdf',
p_attach_blob => l_blob,
p_smtp_host => 'mail.com' --
);
END;

Wednesday 24 October 2018

Ireport Report How to Merge Table Rows With Repeated Values

Main report

<?xml version="1.0" encoding="UTF-8"?>

</parameter>

<![CDATA[Select 'USA' as country from dual

union

Select 'INDIA' as country from dual]]>

</queryString>

<band/>

</background>

<box>

</box>

</staticText>

<box>

</box>

</staticText>

</band>

</columnHeader>

</box>

</textField>

</subreportParameter>

</subreport>

</band>

</detail>

</jasperReport>

Subreport

<?xml version="1.0" encoding="UTF-8"?>

</parameter>

<![CDATA[Select * from

(

Select 'USA' as country, 'Alabama' as state from dual

union

Select 'INDIA' as country, 'Punjab' as state from dual

union

Select 'USA' as country, 'Texas' as state from dual

union

Select 'INDIA' as country, 'Haryana' as state from dual

union

Select 'USA' as country,'Washington' as state from dual

union

Select 'INDIA' as country, 'Karnataka' as state from dual

) where country = $P{p_country}]]>

</queryString>

</box>

</textField>

</band>

</detail>

</jasperReport>

Tuesday 16 October 2018

Email From Oracle PLSQL

The first step is to create the ACL and define the privileges for it:

The general syntax is as follows:

Conn sys as sysdba

BEGIN

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'www.xml',

description => 'WWW ACL',

principal => 'SCOTT',

is_grant => true,

privilege => 'connect');

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'www.xml',

principal => 'SCOTT',

is_grant => true,

privilege => 'resolve');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'www.xml',

host => 'smtp.mycompany.com');

END;

COMMIT;

Simple Email

DECLARE

v_Subject VARCHAR2(80) := 'test subject';

v_Mail_Host VARCHAR2(30) := 'smtp.mycompany.com';

v_Mail_Conn utl_smtp.Connection;

CRLF CHAR (2) := CHR (10) || CHR (13);

BEGIN

v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);

utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);

utl_smtp.Rcpt(v_Mail_Conn, 'me@company.com’);

utl_smtp.Mail(v_Mail_Conn, 'me@company.com');

---UTL_SMTP.DATA: Sends the (complete) e-mail body

utl_smtp.DATA(v_Mail_Conn,

'From: ' || 'me@company.com' ||crlf ||

'Subject: '|| v_Subject || crlf ||

'Cc '|| 'me@company.com' || crlf ||

'To '|| 'me@company.com' || crlf || 'Message body' || crlf );

utl_smtp.Quit(v_mail_conn);

END;

Email with Attachment

CREATE TABLE "FILE_UPLOAD"

( "FILE_UPLOAD_ID" NUMBER NOT NULL ENABLE,

"BLOB_CONTENT" BLOB,

"FILE_NAME" VARCHAR2(255),

"MIME_TYPE" VARCHAR2(255),

"DOC_SIZE" NUMBER ) ;

CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,

p_from IN VARCHAR2,

p_subject IN VARCHAR2,

p_text_msg IN VARCHAR2 DEFAULT NULL,

p_attach_name IN VARCHAR2 DEFAULT NULL,

p_attach_mime IN VARCHAR2 DEFAULT NULL,

p_attach_blob IN BLOB DEFAULT NULL,

p_smtp_host IN VARCHAR2,

p_smtp_port IN NUMBER DEFAULT 25)

AS

l_mail_conn UTL_SMTP.connection;

l_boundary VARCHAR2(50) := '----=#abc1234321cba#=';

l_step PLS_INTEGER := 12000;

   CRLF CHAR (2) := CHR (10) || CHR (13);

BEGIN

l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);

UTL_SMTP.helo(l_mail_conn, p_smtp_host);

UTL_SMTP.mail(l_mail_conn, p_from);

UTL_SMTP.rcpt(l_mail_conn, p_to);

UTL_SMTP.open_data(l_mail_conn);

UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE,

'DD-MON-YYYY HH24:MI:SS') || crlf);

UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || crlf);

UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || crlf);

UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || crlf);

UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || crlf);

UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || crlf);

UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed;

boundary="' || l_boundary || '"' || crlf || crlf);

IF p_text_msg IS NOT NULL THEN

UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || crlf);

UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain;

charset="iso-8859-1"' || crlf || crlf);

UTL_SMTP.write_data(l_mail_conn, p_text_msg);

UTL_SMTP.write_data(l_mail_conn, crlf || crlf);

END IF;

IF p_attach_name IS NOT NULL THEN

UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || crlf);

UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' || p_attach_mime

|| '; name="' || p_attach_name || '"' || crlf);

UTL_SMTP.write_data(l_mail_conn, 'Content-Transfer-Encoding: base64' || crlf);

UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment;

filename="' || p_attach_name || '"' || crlf || crlf);

FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_blob) - 1 )/l_step) LOOP

UTL_SMTP.write_data(l_mail_conn,

UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, l_step, i * l_step + 1))));

END LOOP;

UTL_SMTP.write_data(l_mail_conn, crlf || crlf);

END IF;
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || crlf);

UTL_SMTP.close_data(l_mail_conn);

UTL_SMTP.quit(l_mail_conn);

END send_mail;

DECLARE

l_blob blob;

l_file_name varchar2(30);

l_mime_type varchar2(30);

BEGIN

Select t.Blob_Content, t.FILE_NAME, t. MIME_TYPE

INTO l_blob , l_file_name , l_mime_type from file_upload t Where t.file_upload_id = 1;

send_mail(p_to => 'smtp.mycompany.com',

p_from => 'smtp.mycompany.com',

p_subject => 'Test Message',

p_text_msg => 'This is a test message.',

p_attach_name => l_file_name,

p_attach_mime => l_mime_type,

p_attach_blob => l_blob,

p_smtp_host => 'smtp.mycompany.com');

END;

Enable email in oracle Apex

Create the ACL and define the privileges for it:

The general syntax is as follows:

Conn sys as sysdba

BEGIN

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'www.xml',

description => 'WWW ACL',

principal => 'APEX_050100',

is_grant => true,

privilege => 'connect');

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'www.xml',

principal => 'APEX_050100',

is_grant => true,

privilege => 'resolve');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'www.xml',

host => 'smtp.mycompany.com');

END;

COMMIT;

Tuesday 9 January 2018

Google Pie Chart In Oracle Apex

Url: https://apex.oracle.com/pls/apex/f?p=131188:3:7582634102136::NO:::

Create Page Item 1

P12_JSON_DATA

Item Type Hidden

Create Dynamic Action

Action --> Plsql

DECLARE
CUR_SQL SYS_REFCURSOR;
BEGIN
OPEN CUR_SQL FOR
'select account_head ,SUM(qty) from GP t
Group By account_head';

apex_json.initialize_clob_output;
apex_json.open_object();
apex_json.open_array('cols');
apex_json.open_object();
apex_json.write('id',' ');
apex_json.write('label','Team');
apex_json.write('pattern',' ');
apex_json.write('type','string');
apex_json.close_object();
apex_json.open_object();
apex_json.write('id',' ');
apex_json.write('label','Goals');
apex_json.write('pattern',' ');
apex_json.write('type','number');
apex_json.close_object();
apex_json.close_array();

apex_json.open_array('rows');

FOR i IN ( select null as link, account_head as label ,SUM(qty) as value from GP
Group By account_head ) LOOP

apex_json.open_object();
apex_json.open_array('c');
apex_json.open_object();
apex_json.write('v',i.label);
apex_json.write('f','',TRUE);
apex_json.close_object();
apex_json.open_object();
apex_json.write('v',i.value);
apex_json.write('f','',TRUE);
apex_json.close_object();
apex_json.close_array();
apex_json.close_object();

END LOOP;
apex_json.close_array();
apex_json.close_object();

:P12_JSON_DATA := apex_json.get_clob_output;
apex_json.free_output;

END;

Create Region

Static Content

Source

<html>
<head>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);

function drawChart() {
var jsonData = &P12_JSON_DATA!RAW.;
var data = new google.visualization.DataTable(jsonData);
var options = {
title: 'My Daily Activities'
};
var chart = new google.visualization.PieChart(document.getElementById('piechart'));
chart.draw(data, options);
}
</script>
</head>
<body>
<div id="piechart" style="width: 900px; height: 500px;"></div>
</body>
</html>

Friday 27 October 2017

Oracle Apex Find Out All Live IP Addresses

inurl:"apex/f" 8080
inurl:"ords/f" 8080