Query/Where Property

Change to Default Setting for FORMS_RESTRICT_ENTER_QUERY Environment Variable

The environment setting FORMS_RESTRICT_ENTER_QUERY has a default value of TRUE. This setting disallows users from using QUERY-WHERE functionality until it is set to FALSE in the default.env file.

Rhombus Design

*
***
*****
***
*
SET serveroutput ON;

DECLARE
i NUMBER := 6;
j NUMBER := 0;
BEGIN
i := 2;
j := 2;

LOOP
DBMS_OUTPUT.put_line (LPAD (LPAD (' ', i, '*'), i + j, ' '));
i := i + 2;
j := j - 1;
EXIT WHEN i >= 6;
END LOOP;

i := 6;
j := 0;

LOOP
DBMS_OUTPUT.put_line (LPAD (LPAD (' ', i, '*'), i + j, ' '));
i := i - 2;
j := j + 1;
EXIT WHEN i <= 1;
END LOOP;
END;
/

*****
***
*
***
*****
SET serveroutput ON;

DECLARE
i NUMBER := 6;
j NUMBER := 0;
BEGIN
i := 6;
j := 0;

LOOP
DBMS_OUTPUT.put_line (LPAD (LPAD (' ', i, '*'), i + j, ' '));
i := i - 2;
j := j + 1;
EXIT WHEN i <= 1;
END LOOP;

i := 4;
j := 1;

LOOP
DBMS_OUTPUT.put_line (LPAD (LPAD (' ', i, '*'), i + j, ' '));
i := i + 2;
j := j - 1;
EXIT WHEN i >= 7;
END LOOP;
END;
/

access windows shared folder from linux

mkdir -p /home/oracle/xls
mount -t cifs //192.168.0.28/sh -o username=os_username,password=os_password /home/oracle/xls

Dump_xml

CREATE
OR
REPLACE
PROCEDURE rmver721.dump_xml
(

p_query VARCHAR2,

p_file VARCHAR2
DEFAULT
'TEMP',

p_dir VARCHAR2
DEFAULT
'XLS_DIR'

)

IS

a
CLOB;

ctx DBMS_XMLGEN.ctxhandle;

BEGIN

ctx :=
DBMS_XMLGEN.newcontext
(p_query);

DBMS_XMLGEN.setrowsettag
(ctx,
'ROWSET');

DBMS_XMLGEN.setrowtag
(ctx,
'ROW');

a
:=
DBMS_XMLGEN.getxml
(ctx);

dpr_clobtofile
(p_file || '.xml', p_dir,
a);

END;

/

CREATE
OR
REPLACE
PROCEDURE rmver721.dpr_clobtofile
(

p_filename IN
VARCHAR2,

p_dir IN
VARCHAR2,

p_clob IN
CLOB

)

IS

c_amount CONSTANT
BINARY_INTEGER
:=
32767;

l_buffer VARCHAR2
(32767);

l_chr10 PLS_INTEGER;

l_cloblen PLS_INTEGER;

l_fhandler UTL_FILE.file_type;

l_pos PLS_INTEGER
:=
1;

BEGIN

l_cloblen :=
DBMS_LOB.getlength
(p_clob);

l_fhandler := UTL_FILE.fopen (p_dir, p_filename,
'W', c_amount);

WHILE l_pos < l_cloblen

LOOP

l_buffer :=
DBMS_LOB.SUBSTR
(p_clob, c_amount, l_pos);

EXIT
WHEN l_buffer IS
NULL;

l_chr10 :=
INSTR
(l_buffer,
CHR
(10),
-1);

IF l_chr10 !=
0

THEN

l_buffer :=
SUBSTR
(l_buffer,
1, l_chr10 -
1);

END
IF;

UTL_FILE.put_line (l_fhandler, l_buffer,
TRUE);

l_pos := l_pos +
LEAST
(LENGTH
(l_buffer)
+
1, c_amount);

END
LOOP;

UTL_FILE.fclose (l_fhandler);

EXCEPTION

WHEN
OTHERS

THEN

IF UTL_FILE.is_open (l_fhandler)

THEN

UTL_FILE.fclose (l_fhandler);

END
IF;

RAISE;

END;

/

Top SQL

 

By buffer gets

select substr(sql_text,1,500) "SQL",

(cpu_time/1000000) "CPU_Seconds",

disk_reads "Disk_Reads",

buffer_gets "Buffer_Gets",

executions "Executions",

case when rows_processed = 0 then null

else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))

end "Buffer_gets/rows_proc",

round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",

(elapsed_time/1000000) "Elapsed_Seconds",

module "Module"

from v$sql s

order by buffer_gets desc nulls last;

 

By buffer gets/rows proc

select substr(sql_text,1,500) "SQL",

round((cpu_time/1000000),3) "CPU_Seconds",

disk_reads "Disk_Reads",

buffer_gets "Buffer_Gets",

executions "Executions",

case when rows_processed = 0 then null

else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))

end "Buffer_gets/rows_proc",

round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",

(elapsed_time/1000000) "Elapsed_Seconds",

module "Module"

from v$sql s

order by (buffer_gets/nvl(replace(rows_processed,0,1),1)) desc nulls last;

 

By CPU

select substr(sql_text,1,500) "SQL",

(cpu_time/1000000) "CPU_Seconds",

disk_reads "Disk_Reads",

buffer_gets "Buffer_Gets",

executions "Executions",

case when rows_processed = 0 then null

else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))

end "Buffer_gets/rows_proc",

round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",

(elapsed_time/1000000) "Elapsed_Seconds",

module "Module"

from v$sql s

order by cpu_time desc nulls last;

 

By disk reads

select substr(sql_text,1,500) "SQL",

(cpu_time/1000000) "CPU_Seconds",

disk_reads "Disk_Reads",

buffer_gets "Buffer_Gets",

executions "Executions",

case when rows_processed = 0 then null

else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))

end "Buffer_gets/rows_proc",

round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",

(elapsed_time/1000000) "Elapsed_Seconds",

module "Module"

from v$sql s

order by disk_reads desc nulls last;

 

By Executions

select substr(sql_text,1,500) "SQL",

(cpu_time/1000000) "CPU_Seconds",

disk_reads "Disk_Reads",

buffer_gets "Buffer_Gets",

executions "Executions",

case when rows_processed = 0 then null

else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))

end "Buffer_gets/rows_proc",

round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",

(elapsed_time/1000000) "Elapsed_Seconds",

module "Module"

from v$sql s

order by executions desc nulls last;

 

By waits

select INST_ID,

(cpu_time/1000000) "CPU_Seconds",

disk_reads "Disk_Reads",

buffer_gets "Buffer_Gets",

executions "Executions",

case when rows_processed = 0 then null

else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))

end "Buffer_gets/rows_proc",

round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",

(elapsed_time/1000000) "Elapsed_Seconds",

--round((elapsed_time/1000000)/nvl(replace(executions,0,1),1)) "Elapsed/Execution",

substr(sql_text,1,500) "SQL",

module "Module",SQL_ID

from gv$sql s

where sql_id in (

select distinct sql_id from (

WITH sql_class AS

(select sql_id, state, count(*) occur from

(select sql_id

, CASE WHEN session_state = 'ON CPU' THEN 'CPU'

WHEN session_state = 'WAITING' AND wait_class IN ('User I/O') THEN 'IO'

ELSE 'WAIT' END state

from gv$active_session_history

where session_type IN ( 'FOREGROUND')

and sample_time between trunc(sysdate,'MI') - :minutes/24/60 and trunc(sysdate,'MI') )

group by sql_id, state),

ranked_sqls AS

(select sql_id, sum(occur) sql_occur , rank () over (order by sum(occur)desc) xrank

from sql_class

group by sql_id )

select sc.sql_id, state, occur from sql_class sc, ranked_sqls rs

where rs.sql_id = sc.sql_id

--and rs.xrank <= :top_n

order by xrank, sql_id, state ))

order by elapsed_time desc nulls last;

SMS Transceiver

http://www.wrankl.de/SMST/SMST.html

Blocking locks by user

select /*+ ORDERED */
blocker.sid blocker_sid
, waiting.sid waiting_sid
, TRUNC(waiting.ctime/60) min_waiting
, waiting.request
from (select *
from v$lock
where block != 0
and type = 'TX') blocker
, v$lock waiting
where waiting.type='TX'
and waiting.block = 0
and waiting.id1 = blocker.id1

Locks by user

SELECT p.username ,
p.pid ,
s.sid ,
s.serial#,
p.spid ,
s.username ora ,
DECODE(l2.type,
'TX','TRANSACTION ROW-LEVEL' ,
'RT','REDO-LOG' ,
'TS','TEMPORARY SEGMENT ' ,
'TD','TABLE LOCK' ,
'TM','ROW LOCK' ,
l2.type ) vlock,
DECODE(l2.type,
'TX','DML LOCK' ,
'RT','REDO LOG' ,
'TS','TEMPORARY SEGMENT' ,
'TD',DECODE(l2.lmode+l2.request ,
4,'PARSE ' ||
u.name ||
'.' ||
o.name ,
6,'DDL' ,
l2.lmode+l2.request),
'TM','DML ' ||
u.name ||
'.' ||
o.name ,
l2.type ) type ,
DECODE(l2.lmode+l2.request ,
2 ,'RS' ,
3 ,'RX' ,
4 ,'S' ,
5 ,'SRX' ,
6 ,'X' ,
l2.lmode+l2.request ) lmode ,
DECODE(l2.request ,
0,NULL ,
'WAIT' ) wait
FROM v$process p ,
v$_lock l1,
v$lock l2,
v$resource r ,
sys.obj$ o ,
sys.user$ u ,
v$session s
WHERE s.paddr = p.addr
AND s.saddr = l1.saddr
AND l1.raddr = r.addr
AND l2.addr = l1.laddr
AND l2.type <> 'MR'
AND r.id1 = o.obj# (+)
AND o.owner# = u.user# (+)
--AND u.name = 'GME'
AND (:USER_NAME is null or s.username LIKE upper(:USER_NAME))
ORDER BY 1, 2, 5, 6, 7

oracle related blogs

http://wiki.oracle.com/page/List+of+Oracle-related+blogs

The Q-quote mechanism

SQL> select q'(name LIKE '%DBMS_%%')'

2 from dual

3 /

Q'(NAMELIKE'%DBMS_%%

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

name LIKE '%DBMS_%%'

SQL> select q'<'Data,' he said, 'Make it so.'>'

2 from dual

3 /

Q'<'DATA,'HESAID,'MAKEITSO.'>'

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

'Data,' he said, 'Make it so.'

SQL> select q'"name like '['"'

2 from dual

3 /

Q'"NAMELIKE'[

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

name like '['

regexp_instr

select * from emp where

regexp_instr(empnumber,'7566|7698')<>0

character sets

Deploy Multiple Repositories in Oracle OBIEE

Deploy Multiple Repositories in Oracle OBIEE:

1. Open the NQSConfig.ini File in the following location

“E:\oracle\bise1\bi\server\Config “

2. Add multiple repositories under [REPOSITORY] like below

[ REPOSITORY]

ROADMAP = ROADMAP.rpd, DEFAULT;

ROADMAP_VASS = ROADMAP_VASS.rpd;

3. Open Microsoft ODBC Administrator. Move to “System DSN “ tab

4. Choose “ADD” Button to Add New Service. And on the pop up list choose Oracle BI server and proceed as in the below image.

And then Press “ Finish ”.

5. Go to the Location “E:\oracle\bise1\bidata\web\config”.

Copy “instanceconfig.xml “ File and Save as another file with Different Name.

Edit AnalyticsWeb as with the DSN you have created with Oracle BI

Server. Next to that add the following

And change the catalog path to the location you have copied the catalog root directory.

6. Go to Oracle BI Enterprise Manager , Application Tab.

Then press next to proceed to the next step.

Then press Next and Press Deploy .

7. Go to the Location “ E:\oracle\bise1\bi\oc4j_bi\j2ee\home\applications “ you will find the folder named “ Vass “ in that move to “E:\oracle\bise1\bi\oc4j_bi\j2ee\home\applications\analyticsvs\analytics”

And replace the “ res “ file with that of original analytics folder in application folder .

And move to “WEB-INF “ you will find a “web.xml” file .open the file in the notepad edit port no 9710 as 9711 specified in the “instanceconfig.xml “ file.

8. Go to command Prompt. Type the following

sc create sawsvc2 binpath= SEARCHFORMEPLEASE displayname= "Oracle BI Presentation Server 2"

9. open Registry editor through run Prompt. Go to “HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\sawsvc2”

Edit the Image Path Value "E:\oracle\bise1\bi\web\bin\sawserver.exe" /service /c E:\oracle\bise1\bidata\web\config\instanceconfigvs.xml

10. Go to Services and you should see Oracle BI Presentation Server 2 which when started should bring up your new Presentation Services.

Mail Fetch

package mail;

import java.io.UnsupportedEncodingException;

import java.security.*;

import java.util.Properties;

import javax.mail.*;

import javax.mail.internet.MimeUtility;

public class GmailFetch {

public static void main(String[] args) throws Exception {

Security.addProvider(new com.sun.net.ssl.internal.ssl.Provider());

final String SSL_FACTORY = "javax.net.ssl.SSLSocketFactory";

// Get a Properties object

Properties props = System.getProperties();

props.setProperty("proxySet", "true");

//props.setProperty("http.ProxyHost", "192.168.0.6");

//props.setProperty("http.ProxyPort", "8181");

props.setProperty("mail.pop3.socketFactory.class", SSL_FACTORY);

props.setProperty("mail.pop3.socketFactory.fallback", "false");

props.setProperty("mail.pop3.port", "995");

props.setProperty("mail.pop3.socketFactory.port", "995");

Session session = Session.getDefaultInstance(props, null);

URLName urln =

new URLName("pop3", "pop.gmail.com", 995, null, "from@gmail.com",

"pass");

Store store = session.getStore(urln);

Folder inbox = null;

try {

store.connect();

inbox = store.getFolder("INBOX");

inbox.open(Folder.READ_ONLY);

FetchProfile profile = new FetchProfile();

profile.add(FetchProfile.Item.ENVELOPE);

Message[] messages = inbox.getMessages();

inbox.fetch(messages, profile);

System.out.println("Inbox Number of Message" + messages.length);

/*for (int i = 0; i < messages.length; i++) {

String from = decodeText(messages[i].getFrom()[0].toString());

InternetAddress ia = new InternetAddress(from);

System.out.println("FROM:" + ia.getPersonal() + '(' +

ia.getAddress() + ')');

System.out.println("TITLE:" + messages[i].getSubject());

System.out.println("DATE:" + messages[i].getSentDate());

}*/

} finally {

try {

inbox.close(false);

} catch (Exception e) {

System.out.println(e.toString());

}

try {

store.close();

} catch (Exception e) {

System.out.println(e.toString());

}

}

}

protected static String decodeText(String text) throws UnsupportedEncodingException {

if (text == null)

return null;

if (text.startsWith("=?GB") || text.startsWith("=?gb"))

text = MimeUtility.decodeText(text);

else

text = new String(text.getBytes("ISO8859_1"));

return text;

}

}

Mail Sender

package mail;

import java.security.Security;

import java.util.Date;

import java.util.Properties;

import javax.mail.Authenticator;

import javax.mail.Message;

import javax.mail.MessagingException;

import javax.mail.PasswordAuthentication;

import javax.mail.Session;

import javax.mail.Transport;

import javax.mail.internet.AddressException;

import javax.mail.internet.InternetAddress;

import javax.mail.internet.MimeMessage;

public class GmailSender {

public static void main(String[] args) throws AddressException,

MessagingException {

Security.addProvider(new com.sun.net.ssl.internal.ssl.Provider());

final String SSL_FACTORY = "javax.net.ssl.SSLSocketFactory";

// Get a Properties object

Properties props = System.getProperties();

props.setProperty("proxySet", "true");

//props.setProperty("http.ProxyHost", "192.168.0.6");

//props.setProperty("http.ProxyPort", "8181");

props.setProperty("mail.smtp.host", "smtp.gmail.com");

props.setProperty("mail.smtp.socketFactory.class", SSL_FACTORY);

props.setProperty("mail.smtp.socketFactory.fallback", "false");

props.setProperty("mail.smtp.port", "465");

props.setProperty("mail.smtp.socketFactory.port", "465");

props.put("mail.smtp.auth", "true");

props.put("mail.debug", "true");

props.put("mail.store.protocol", "pop3");

props.put("mail.transport.protocol", "smtp");

final String username = "from@gmail.com";

final String password = "password";

Session session =

Session.getDefaultInstance(props, new Authenticator() {

protected PasswordAuthentication getPasswordAuthentication() {

return new PasswordAuthentication(username, password);

}

});

// -- Create a new message --

Message msg = new MimeMessage(session);

// -- Set the FROM and TO fields --

msg.setFrom(new InternetAddress("from@gmail.com"));

msg.setRecipients(Message.RecipientType.TO,

InternetAddress.parse("to@gmail.com",

false));

msg.setSubject("Hello");

msg.setText("How are you");

msg.setSentDate(new Date());

Transport.send(msg);

System.out.println("Message sent.");

}

}

load file into blob column

create table test
(
id number primary key,
docs BLOB
);

create or replace directory doc_loc
as 'c:\test';

CREATE OR REPLACE PROCEDURE Load_BLOB_From_File (file_name in varchar2)
AS
src_loc bfile:= bfilename('DOC_LOC',Load_BLOB_From_File.file_name);
dest_loc BLOB;
begin
insert into test values(1,empty_blob()) returning docs
into dest_loc;
dbms_lob.open(src_loc,DBMS_LOB.LOB_READONLY);
DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.LOADFROMFILE(
dest_lob => dest_loc
,src_lob => src_loc
,amount => DBMS_LOB.getLength(src_loc));
DBMS_LOB.CLOSE(dest_loc);
DBMS_LOB.CLOSE(src_loc);
COMMIT;
end;
/

show errors;
exec Load_BLOB_From_File('test.pdf');

dump_csv

CREATE OR REPLACE FUNCTION RMVER721.dump_csv (
p_query IN VARCHAR2,
p_filename IN VARCHAR2 DEFAULT 'TEMP',
p_separator IN VARCHAR2 DEFAULT ',',
p_dir IN VARCHAR2 DEFAULT 'XLS_DIR'
)
RETURN NUMBER
IS
l_output UTL_FILE.file_type;
l_thecursor PLS_INTEGER DEFAULT DBMS_SQL.open_cursor;
l_columnvalue VARCHAR2 (4000);
l_status PLS_INTEGER;
l_colcnt NUMBER (10) DEFAULT 0;
l_separator VARCHAR2 (10) DEFAULT '';
l_cnt NUMBER (10) DEFAULT 0;
l_sht NUMBER (5) DEFAULT 0;
l_tot NUMBER (10) DEFAULT 0;
rec_tab DBMS_SQL.desc_tab;
BEGIN
l_output := UTL_FILE.fopen (p_dir, p_filename || l_sht || '.xls', 'w');
DBMS_SQL.parse (l_thecursor, p_query, DBMS_SQL.native);
DBMS_SQL.describe_columns (l_thecursor, l_colcnt, rec_tab);

FOR i IN rec_tab.FIRST .. rec_tab.LAST
LOOP
UTL_FILE.put (l_output, l_separator || rec_tab (i).col_name);
l_separator := CHR (9);
END LOOP;

UTL_FILE.new_line (l_output);

FOR i IN 1 .. 255
LOOP
BEGIN
DBMS_SQL.define_column (l_thecursor, i, l_columnvalue, 2000);
l_colcnt := i;
EXCEPTION
WHEN OTHERS
THEN
IF (SQLCODE = -1007)
THEN
EXIT;
ELSE
RAISE;
END IF;
END;
END LOOP;

DBMS_SQL.define_column (l_thecursor, 1, l_columnvalue, 2000);
l_status := DBMS_SQL.EXECUTE (l_thecursor);

LOOP
EXIT WHEN (DBMS_SQL.fetch_rows (l_thecursor) <= 0);
l_separator := '';

FOR i IN 1 .. l_colcnt
LOOP
DBMS_SQL.COLUMN_VALUE (l_thecursor, i, l_columnvalue);
UTL_FILE.put (l_output, l_separator || l_columnvalue);
l_separator := CHR (9);
END LOOP;

UTL_FILE.new_line (l_output);
l_cnt := l_cnt + 1;
l_tot := l_tot + 1;

IF l_cnt >= 65000
THEN
l_cnt := 1;
l_sht := l_sht + 1;
UTL_FILE.fclose (l_output);
l_output :=
UTL_FILE.fopen (p_dir, p_filename || l_sht || '.xls', 'w');
l_separator := '';

FOR i IN rec_tab.FIRST .. rec_tab.LAST
LOOP
UTL_FILE.put (l_output, l_separator || rec_tab (i).col_name);
l_separator := CHR (9);
END LOOP;

UTL_FILE.new_line (l_output);
END IF;
END LOOP;

DBMS_SQL.close_cursor (l_thecursor);
UTL_FILE.fclose (l_output);
RETURN l_tot;
END dump_csv;
/

proc_dlmt

PROCEDURE proc_dlmt (qry VARCHAR2, filename VARCHAR2, nr OUT NUMBER)
IS
cursor_number exec_sql.curstype;
ln$empno NUMBER;
lc$name VARCHAR2 (30);
ln$count NUMBER;
BEGIN
cursor_number := exec_sql.open_cursor;
exec_sql.parse (cursor_number, qry);
ln$count := exec_sql.EXECUTE (cursor_number);
exec_sql.close_cursor (cursor_number);

SELECT dump_csv (qry, filename)
INTO nr
FROM DUAL;

FOR fcnt IN 0 .. TRUNC (nr / 65000)
LOOP
web.show_document ('/forms/webutil/' || filename || fcnt || '.xls',
'_blank'
);
END LOOP;
EXCEPTION
WHEN exec_sql.invalid_connection
THEN
alert_msg ('Invalid Connection error.', 'S');
WHEN exec_sql.package_error
THEN
alert_msg (SUBSTR (exec_sql.last_error_mesg,
INSTR (exec_sql.last_error_mesg, ':') + 2
),
'S'
);

IF exec_sql.is_open (cursor_number)
THEN
exec_sql.close_cursor (cursor_number);
alert_msg ('Exception - Cleaned up Cursor', 'N');
END IF;
END;

Flash back - Google Notebook

Flash back - Google Notebook