jump to navigation

Multiple Matrix in one Report July 13, 2010

Posted by Muhammad Habib in Oracle Developer, Oracle General.
3 comments

When we run a wizard to create a new matrix report, we can only create a single matrix on a report.

How we can make a report having multiple matrixes?

Very simple …………

In report Builder Layout Model,

Just select “Additional Default Layout” from the tools and create an additional layout.

This will help in creating a new matrix …. .

We can even draw many matrixes.

Regards

Advertisements

Send Email via Oracle Developer December 28, 2007

Posted by Muhammad Habib in Oracle Developer.
5 comments

Dear All

I found one query regarding how to send email from Oracle Developer. I have posted it here.

create or replace procedure E_MAIL(

p_to in varchar2,
p_from in varchar2,
p_subject in varchar2,
p_text in varchar2 default null,
p_html in varchar2 default null,
p_smtp_hostname in varchar2,
p_smtp_portnum in varchar2)
is
l_boundary varchar2(255) default ‘a1b2c3d4e3f2g1’;
l_connection utl_smtp.connection;
l_body_html clob := empty_clob; –This LOB will be the email message
l_offset number;
l_ammount number;
l_temp varchar2(32767) default null;
begin
l_connection := utl_smtp.open_connection( p_smtp_hostname, p_smtp_portnum );
utl_smtp.helo( l_connection, p_smtp_hostname );
utl_smtp.mail( l_connection, p_from );
utl_smtp.rcpt( l_connection, p_to );

l_temp := l_temp || ‘MIME-Version: 1.0’ || chr(13) || chr(10);
l_temp := l_temp || ‘To: ‘ || p_to || chr(13) || chr(10);
l_temp := l_temp || ‘From: ‘ || p_from || chr(13) || chr(10);
l_temp := l_temp || ‘Subject: ‘ || p_subject || chr(13) || chr(10);
l_temp := l_temp || ‘Reply-To: ‘ || p_from || chr(13) || chr(10);
l_temp := l_temp || ‘Content-Type: multipart/alternative; boundary=’ ||
chr(34) || l_boundary || chr(34) || chr(13) ||
chr(10)||utl_tcp.CRLF;

—————————————————-
— Write the headers
dbms_lob.createtemporary( l_body_html, false, 10 );
dbms_lob.write(l_body_html,length(l_temp),1,l_temp);

—————————————————-
— Write the text boundary
l_offset := dbms_lob.getlength(l_body_html) + 1;
l_temp := ‘–‘ || l_boundary || chr(13)||chr(10);
l_temp := l_temp || ‘content-type: text/plain; charset=us-ascii’ ||
chr(13) || chr(10) || chr(13) || chr(10);
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

—————————————————-
— Write the plain text portion of the email
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(p_text),l_offset,p_text);

—————————————————-
— Write the HTML boundary
l_temp := chr(13)||chr(10)||chr(13)||chr(10)||’–‘ || l_boundary ||
chr(13) || chr(10);
l_temp := l_temp || ‘content-type: text/html;’ ||
chr(13) || chr(10) || chr(13) || chr(10);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

—————————————————-
— Write the HTML portion of the message
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(p_html),l_offset,p_html);

—————————————————-
— Write the final html boundary
l_temp := chr(13) || chr(10) || ‘–‘ || l_boundary || ‘–‘ || chr(13);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

—————————————————-
— Send the email in 1900 byte chunks to UTL_SMTP
l_offset := 1;
l_ammount := 1900;
utl_smtp.open_data(l_connection);
while l_offset < dbms_lob.getlength(l_body_html) loop
utl_smtp.write_data(l_connection,
dbms_lob.substr(l_body_html,l_ammount,l_offset));
l_offset := l_offset + l_ammount ;
l_ammount := least(1900,dbms_lob.getlength(l_body_html) – l_ammount);
end loop;
utl_smtp.close_data(l_connection);
utl_smtp.quit( l_connection );
dbms_lob.freetemporary(l_body_html);
end;

 

After creating this procedure, we can call it from any form or trigger etc. 

Hope it will be helpful.

Regards ….

ORA-12571: TNS Packet writer failure August 28, 2007

Posted by Muhammad Habib in Oracle Developer.
add a comment

Strange error!

My developer was running fine . why I got this error.

I had tried many solutions provided on web by oracle experts but unable to connect my developer to database. I had tried to remove developer from C:\orant directory and also from registry. Then I re-installed it. But I got same error. I made parameter sqlnet authentication … to “None” in “SQLNET.ORA” file – but unable to solve this problem.

At last I analyzed that after which event I was getting this error. That was installation of a software – Download Manager.

Actually it was interrupting services. At last I removed this software and connected successfully developer to DB.

Hope it will be helpful.

Protected: Creating a New Menu August 22, 2007

Posted by Muhammad Habib in Oracle Applications, Oracle Developer, Oracle Financials, Oracle General.
Enter your password to view comments.

This content is password protected. To view it please enter your password below:

Sample Stock Report August 16, 2007

Posted by Muhammad Habib in Oracle Developer, Oracle General.
add a comment

—————————————————————
SCRIPT TO CREATE TABLES (OPN, RCT, ISU)
—————————————————————
create table OPN
(
Q_ID NUMBER,
OPN_QTY NUMBER,
OPN_DT DATE
);

create table RCT
(
Q_ID NUMBER,
RCT_QTY NUMBER,
RCT_DT DATE
);

create table ISU
(
Q_ID NUMBER,
ISU_QTY NUMBER,
ISU_DT DATE
);
—————————————————————
SCRIPT TO CREATE VIEW STK_V
—————————————————————
CREATE OR REPLACE VIEW STK_V AS
(
SELECT Q_ID,
SUM(OPN_QTY) AS OPENING,
0 AS RECEIPT,
0 AS ISSUE,
OPN_DT AS T_DT
FROM OPN
GROUP BY Q_ID, OPN_DT
UNION
SELECT Q_ID,
0 AS OPENING,
SUM(RCT_QTY) AS RECEIPT,
0 AS ISSUE,
RCT_DT AS T_DT
FROM RCT
GROUP BY Q_ID, RCT_DT
UNION
SELECT Q_ID,
0 AS OPENING,
0 AS RECEIPT,
SUM(ISU_QTY) AS ISSUE,
ISU_DT AS T_DT
FROM ISU
GROUP BY Q_ID, ISU_DT
);

—————————————————————
REPORT QUERY
————————————————————— SELECT Q_ID,
SUM(OPENING) AS OPENING,
SUM(RECEIPT) AS RECEIPT,
SUM(ISSUE) AS ISSUE,
MAX(T_DT) as T_DT
FROM
(
SELECT Q_ID,
SUM(OPENING) AS OPENING,
SUM(RECEIPT) AS RECEIPT,
SUM(ISSUE) AS ISSUE,
T_DT
FROM STK_V
WHERE T_DT BETWEEN :FROM_DT AND :TO_DT
AND Q_ID = :QLTY_ID
GROUP BY Q_ID, T_DT
UNION ALL
SELECT Q_ID,
((SUM(OPENING)+SUM(RECEIPT))-SUM(ISSUANCE)) AS OPENING,
0 AS RECEIPT,
0 AS ISSUE,
T_DT
FROM STK_V
WHERE T_DT < :FROM_DT
AND Q_ID = :QLTY_ID
GROUP BY Q_ID, T_DT
)
GROUP BY Q_ID, T_DT
ORDER BY T_DT
—————————————————————
FORMULA COLUMN IN REPORT TO GET BALANCE
—————————————————————

function BALANCE return Number is
begin
RETURN :OPENING + :RECEIPT – :ISSUE;
end;
————————————————————————
MAKE A SUMMARY COLUMN ON BALANCE FORMULA AT SAME LEVEL
————————————————————————

Report columns are: Opening, Receipt, Issuance, Balance

Hope it will be helpful.

REP-0118 unable to create temp file March 21, 2007

Posted by Muhammad Habib in Oracle Developer.
14 comments

Cause:

1. No temp directory on the PC.

2. Missing statement in the Win.ini

Corrective Action:

1. Check to see if you have a temp directory on your PC’s hard drive.

2. Check the Win.ini file and see it this line exists:

[Oracle]

ora_config=c:\orant\orawin.ini

If it’s not there, add it AND ALSO create a file orawin.ini
phisically in the directory C:\ORANT
the directory C: