fibonacci in PL/SQL

create or replace  package  as

    procedure print_fibonacci ( n number);

end fibonacci;

create or replace package body fibonacci as

  function calculate_fibonacci ( n number) return number

  as

   begin

      if (n <= 1) then

      return n;

      else

          return calculate_fibonacci(n-1) + calculate_fibonacci(n-2);

      end if;

   end calculate_fibonacci;

 

  procedure print_fibonacci ( n number) as

   counter pls_integer := 1;

  begin

      loop

          dbms_output.put_line(counter-1||':'||calculate_fibonacci(counter-1));

          exit when counter > n;

          counter := counter +1;

      end loop;

  end print_fibonacci;

end fibonacci;

Create Oracle 11g Database Manually on AIX5L

db_name='GATEWAY'

memory_target=1G

processes = 150

audit_file_dest='/oradata/db11g/audit'

audit_trail ='db'

db_block_size=8192

db_domain='nbg.com.cy'

db_recovery_file_dest='/oradata/db11g/flash_recovery_area'

db_recovery_file_dest_size=100G

diagnostic_dest='/oradata/db11g/diag'

dispatchers='(PROTOCOL=TCP) (SERVICE=GATEWAYXDB)'

open_cursors=300

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

control_files = ('/oradata/db11g/databases/GATEWAY/control1.ctl','/oradata/db11g/databases/GATEWAY/control1.ctl')

compatible ='11.2.0'

aq_tm_processes=1

sessions=400

db_file_multiblock_read_count=16

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

orapwd FILE=/oradata/db11g/product/11.2.0/dbhome_1/dbs/orapwGATEWAY ENTRIES=10

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

export ORACLE_SID=GATEWAY

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

CREATE DATABASE GATEWAY

USER SYS IDENTIFIED BY oracle

USER SYSTEM IDENTIFIED BY oracle

LOGFILE GROUP 1 ('/oradata/db11g/databases/GATEWAY/redo01.log') SIZE 50M,

GROUP 2 ('/oradata/db11g/databases/GATEWAY/redo02.log') SIZE 50M,

GROUP 3 ('/oradata/db11g/databases/GATEWAY/redo03.log') SIZE 50M

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXLOGHISTORY 1

MAXDATAFILES 100

CHARACTER SET EL8ISO8859P7

NATIONAL CHARACTER SET UTF8

EXTENT MANAGEMENT LOCAL

DATAFILE '/oradata/db11g/databases/GATEWAY/system01.dbf' SIZE 325M REUSE

SYSAUX DATAFILE '/oradata/db11g/databases/GATEWAY/sysaux01.dbf' SIZE 325M REUSE

DEFAULT TABLESPACE users

DATAFILE '/oradata/db11g/databases/GATEWAY/users01.dbf'

SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

DEFAULT TEMPORARY TABLESPACE tempts1

TEMPFILE '/oradata/db11g/databases/GATEWAY/temp01.dbf'

SIZE 200M REUSE AUTOEXTEND ON MAXSIZE 4096M

UNDO TABLESPACE UNDOTBS1

DATAFILE '/oradata/db11g/databases/GATEWAY/undotbs01.dbf'

SIZE 200M REUSE AUTOEXTEND ON MAXSIZE 4096M;

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

@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql

@?/sqlplus/admin/pupbld.sql

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

CREATE TABLESPACE CHASER_TBS LOGGING

DATAFILE '/oradata/db11g/databases/GATEWAY/chaser_tbs01.dbf'

SIZE 500M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE 4096M

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO;

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

Using DBMS_PROFILER Oracle 11g

Using DBMS_PROFILER

exec DBMS_PROFILER.start_profiler;

ERROR at line 1:

ORA-06528: Error executing PL/SQL profiler

ORA-06512: at "SYS.DBMS_PROFILER", line 123

ORA-06512: at "SYS.DBMS_PROFILER", line 132

ORA-06512: at line 1

@?\rdbms\admin\proftab.sql

create or replace function tablecounter

return number

as

   c pls_integer;

begin

  select count(*) into c from lineitem;

  return c;

end tablecounter;

exec DBMS_PROFILER.start_profiler('tablecounter');

variable a number

exec :a:= tablecounter;

PL/SQL procedure successfully completed.

exec DBMS_PROFILER.stop_profiler;

BREAK ON  runid ON run_owner ON  run_comment ON run_secs

SELECT a.runid, a.run_owner, a.run_comment,

       a.run_total_time / 1000000000 run_secs, c.total_occur,

       c.total_time / 1000000000 line_total_secs, c.line#, u.text

  FROM plsql_profiler_runs a,

       plsql_profiler_units b,

       plsql_profiler_data c,

       user_source u

 WHERE a.runid = b.runid

   AND a.runid = c.runid

   AND b.unit_name = u.NAME

   AND c.line# = u.line

/

RUNID	RUN_OWNER	RUN_COMMENT	RUN_SECS	TOTAL_OCCUR	LINE_TOTAL_SECS	LINE#	TEXT
1	TPCH2	tablecounter	99.864557	2	0.000073	1	function tablecounter
1	TPCH2	tablecounter	99.864557	0	0.000007	1	function tablecounter
1	TPCH2	tablecounter	99.864557	2	0.000059	1	function tablecounter
1	TPCH2	tablecounter	99.864557	1	0.000002	1	function tablecounter
1	TPCH2	tablecounter	99.864557	1	0.309489	6	select count(*) into c from lineitem;
1	TPCH2	tablecounter	99.864557	1	0.000001	7	return c;
1	TPCH2	tablecounter	99.864557	1	0.000006	8	end tablecounter;

Handling the error ORA-01438

Handling the error ORA-01438: value larger than specified precision allowed for this column.



This error happens when inserting or updating records into Oracle tables. The numeric value that user / process tries to place is exceeding the precision defined for the column.



For example:



Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

Connected as someschema



SQL> create table SOMETABLE (a number(5,2), b number(3,2), c number(5));



Table created



SQL> insert into SOMETABLE values (2.0, 53.34, 123456);



insert into SOMETABLE values (2.0, 53.34, 123456)



ORA-01438: value larger than specified precision allowed for this column



SQL>



When a user runs some stand alone PL/SQL queries it’s not difficult to identify the value that causes the error.



The issue seems more complex when the error comes from a process that inserts and/or updates a few hundreds / thousands of records in a table. How to know the exact record and/or value that leads to the problem?



The first approach is to create a copy of the original table, but with increased precision(s) for numeric columns. Trying to insert / update values from the “improved” changed table to the original one, combining the records could point to the problematic record(s) and/or value(s).



For example:



SQL> create table SOMETABLEPLUS as select * from SOMETABLE where 1 = 2;



Table created



SQL> alter table SOMETABLEPLUS modify b number(4, 2);



Table altered



SQL> alter table SOMETABLEPLUS modify c number(6);



Table altered



SQL> insert into SOMETABLEPLUS values (2.0, 53.34, 123456);



1 row inserted



SQL> commit;



Commit complete



This way is easy enough, transparent and useful sometimes. It can help to find out the reason for the ORA-01438 error. From the other hand, it’s a sort of some manual activity.



The second approach that usually comes to mind is to debug the application side, to place some prints, to check some values that come from application to understand the behavior leading to the error. The downside of this method is that you have to access and manipulate the application code. I think you’ll agree with me, it’s a sort of manual activity too. After all, we’re hi-tech .



And the Oracle has something more powerful to suggest.



The idea is to get trace of insert(s)/update(s) and to catch the error precisely. It’s possible to turn on the trace on the level of session or on the level of system.



Assuming we have a process with no any access inside. It runs kind of isolated thread that we can’t “incapsulate” or “inject” something internally. In addition, the thread runs so quickly that we can’t even catch its session number in Oracle. All we have – it fails with the Oracle error ORA-01438.



In such case DBA can assist to find the problematic record(s).

Here are the steps to do. (I’ll illustrate it based on Oracle 11g release 1. You can do the same with other Oracle versions).



1. Connect to the Oracle instance when the problem happens as sysdba.



2. Turn on the following trace.



ALTER SYSTEM SET EVENTS=’1438 TRACE NAME ERRORSTACK FOREVER, LEVEL 12?;



It should be done for a short period of time, when reconstructing the problem, because invoked trace files will occupy more and more disk space while this event trace is turned on.



3. Re-run the process that causes the Oracle error ORA-01438.



4. Be sure to turn the activated trace off. Failure to perform this step will cause Oracle server file system to be overflowed with huge trace files.



ALTER SYSTEM SET EVENTS=’1438 TRACE NAME ERRORSTACK OFF’;



5. Find out where the trace files for the Oracle server are placed on:



SQL> show parameter user_dump_dest



NAME TYPE VALUE

———————————— ———– ——————————

user_dump_dest string /u01/app/oracle/diag/rdbms/dev

11g/DEV11G/trace

SQL>



6. Get to the dump destination directory and search the specific trace that has the error logged:



[oracle@localhost trace]$ cd /u01/app/oracle/diag/rdbms/dev11g/DEV11G/trace/



[oracle@localhost trace]$ grep -l 1438 *

alert_DEV11G.log

DEV11G_ora_8771.trc

[oracle@localhost trace]$



7. Open the trace file(s) to get the brief information about the error.



Here we can find the exact timestamp the problem happens, exact session, service name, module name, action name that caused the error. More important you can see the exact SQL statement that caused the problem and its exact value(s). In addition, for more advanced Oracle users the trace contains call stack trace.



…

*** 2010-10-04 09:19:35.131

*** SESSION ID:(2077.13457) 2010-10-04 09:19:35.132

*** CLIENT ID:() 2010-10-04 09:19:35.132

*** SERVICE NAME:(DEV11G) 2010-10-04 09:19:35.132

*** MODULE NAME:(PL/SQL Developer) 2010-10-04 09:19:35.132

*** ACTION NAME:(SQL Window – New) 2010-10-04 09:19:35.132



—– Error Stack Dump —–

ORA-01438: value larger than specified precision allowed for this column

—– Current SQL Statement for this session (sql_id=6w3w8fyhg39a4) —–

insert into SOMETABLEPLUS values (2.0, 153.34, 12345)



—– Call Stack Trace —–

calling call entry argument values in hex

location type point (? means dubious value)

——————– ——– ——————– —————————-

skdstdst()+41 call kgdsdst() 000000000 ? 000000001 ?

7FFF488E3488 ? 7FFF488E1F70 ?

…



REFERENCE : http://dba-star.blogspot.com/2010/12/handling-error-ora-01438.html

Session Trace and Bind Variables

AS SYS


----------

execute dbms_system.set_sql_trace_in_session(144,951, true);



SQL_ID

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

SQL_TEXT

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

4nsz84fyk5wf5

select ANNOUNCEMENTS_TITLE from ann where ANNOUNCEMENTS_ID= :ann_id







SQL> SELECT

2 a.sql_text,

3 b.name,

4 b.position,

5 b.datatype_string,

6 b.value_string

7 FROM

8 v$sql_bind_capture b,

9 v$sqlarea a

10 WHERE

11 b.sql_id = '4nsz84fyk5wf5'

12 AND

13 b.sql_id = a.sql_id;



SQL_TEXT

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

NAME POSITION DATATYPE_STRING

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

VALUE_STRING

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

select ANNOUNCEMENTS_TITLE from ann where ANNOUNCEMENTS_ID= :ann_id

:ANN_ID 1 NUMBER

39





AS USER

---------

variable ann_id number

exec :ann_id := 39

select ANNOUNCEMENTS_TITLE from ann where ANNOUNCEMENTS_ID= :ann_id;

Create Explain Plan Automatically

create or replace
function createplan
(
  sql_text in varchar2
)
return varchar2
as
     plan_stmt_id_ varchar2(4000);
begin
  select to_char(plan_stmt_id.nextval) into plan_stmt_id_ from dual;
  execute immediate  ( 'EXPLAIN PLAN SET STATEMENT_ID = '''||plan_stmt_id_||''' INTO plan_table FOR '||sql_text);
  return plan_stmt_id_;
end createplan;

Calculate top 10 database wait events per wait class

select * from (
select c.wait_class as wait_class,e.event as event
from V$system_event e ,V$SYSTEM_WAIT_CLASS c
where e.wait_class_id=c.wait_class_id
order by e.total_waits desc
) tab
where rownum <=10

Configure and send email from database using UTL_MAIL.SEND

---Tested on AIX 5 , Oracle 10.1 EE

create or replace
Procedure Sendemail
(
Receiver_1 Varchar2,
Subject Varchar2,
Message Varchar2,
exit_code OUT NUMBER
)
As
/*
start /oradata/db10g/rdbms/admin/utlmail.sql
start /oradata/db10g/rdbms/admin/prvtmail.plb
GRANT execute ON utl_mail TO cyag;
alter system set SMTP_OUT_SERVER='mymailServer' scope=spfile;
*/
Begin
Utl_Mail.Send('cyag@GATEWAY.nbg.com.cy',Receiver_1,Message => Message,Subject => Subject);
Exit_Code :=0;
Exception
When Others Then
exit_code :=1;
END SENDEMAIL;

Find logs for java application that is deployed on OC4J

tail -f $ORACLE_HOME /j2ee//application-deployments//_default_island_1/application.log

Find logs for java application that is deployed on OC4J

tail -f $ORACLE_HOME /j2ee//application-deployments//_default_island_1/application.log

Deploy ear application on Standalone oc4j

Deploy ear application on Standalone oc4j
1. Right click on “Project” and select “New”.
2. Select “Deployment Profiles” and on the right screen select “WAR file”. Click OK
3. Write the “Deployment Profile Name”: webapp1. Click OK.
4. Select under WEB-INF/classes, the Contributors and on the right screen select all the checkboxes. Click OK.
5. Right click on the webapp1 /weapp2 and select “Deploy to EAR file”.
6. Put ear under : C:\FileMover\oc4j_home\oc4j\j2ee\home\applications
7. Add the line below in the file C:\FileMover\oc4j_home\oc4j\j2ee\home\config\server.xml.
---application name="FileMoverMg" path="../../home/applications/FileMoverMg.ear" auto-start="true" />
8. Add the line below in the file C:\FileMover\oc4j_home\oc4j\j2ee\home\config\default-web-site.xml
---web-app application="FileMoverMg" name="FileMoverMg" root="/FileMoverMg" />