Apex 3.2 upgrade and Image Loading

Before installing, check this requirement documentation according to your environment.

Note that, if your Apex version is lower than 3.1, make shared_pool_size 100M

Steps in installing Oracle Application Express 3.2:

1. Download apex zip file
2. Unzip
3. Copy extracted directory “apex” to $ORACLE_HOME\
   ex. $ORACLE_HOME or C:\\oracle\\product\\10.2.0\\db_1\\
4. Change working directory to apex (on cmd)
   ex. cd C:\\oracle\\product\\10.2.0\\db_1\\apex
5. start sqlplus and connect as SYS
   ex. sqlplus sys/ as sysdba
6. run apexins.sql with the following arguments
   - tablespace_apex – tablespace to be used by Application Express (ex. SYSAUX)
   - tablespace_files – file tablespace to be used by Application Express (ex. SYSAUX)
   - tablespace_temp – temporary table space to be used by Application Express (ex. TEMP)
   - images – the images folder for Application Express (ex./i/)
   ex. @apexins SYSAUX SYSAUX TEMP /i/
7. Load Images & JS:   @apexldimg c:\oracle <apex location>
8. To Change Admin PWD:   @apxchpwd
9. Shutdown immediate & Startup force
10. Enjoy your Application Express 3.2!

If you have any troubles, get the documentation; Oracle Application Express Installation Guide

Good luck.

Some grant hints

Firstly create a “sample” user with “sample_pass” password;

create user sample identified by sample_pass;

Some commands that grant user on objects and roles;

grant dba to sample;   –gives dba role to sample user

grant create database link to sample; –now, user can create db link

grant create table to sample; –user can create table under defined schema

grant drop any table to sample; –grants user to drop table under any schema, there is no “drop table” –option!

grant create procedure to sample; –grants user to create function, package or procedure

If we use, “with admin option” clause at the end of command, this gives grantee to give privileges to any user under the defined grant option.

Oracle and Sun & Sun and Oracle

As announced, today is historical day for our technologic world.

Oracle Corporation (NASDAQ: ORCL) gives $9.50 per share for Sun Microsystems (NASDAQ: JAVA).Two company announced that they have entered into a definitive agreement, so we can say ‘Oracle Bought Sun!’.

You can read the press release here and here. At this point, there is a lot of mind-mending questions about leaders’ behaviour to these lots of technologics.

We were expecting to buy from IBM , but they disagreed about share, NASDAQ:JAVA, about $7 billion acquisition of shares.Today, unexpectedly Sun Microsystems goes to worlds DB monster, Oracle Company.

Screenshots for imaginal memories,

DBMS_CRYPTO package – error management

If you receive error messages like;

ORA-28817: PL/SQL function returned an error.

ORA-06512: at “SYS.DBMS_CRYPTO_FFI”, line XXX.

ORA-06512: at “SYS.DBMS_CRYPTO”, line XX .. etc.

Then, check these conditions;

* the encryption key and encryption type must be the same as while encrption/decryption

*the character set used by Oracle and your machine must be overlapped

*the data that you are decrypting is NOT the same as the one encrypted

how to create file @ 10gR2

Using utl_file package, we can create files if we follow these steps;
As we think we had folder like C:/utl;

1-

alter system set utl_file_dir='C:/utl' scope=spfile;

2-

shutdown immediate

3-

startup

4- test as;

declare
f1 utl_file.file_type;
begin
f1:=utl_file.fopen('C:\utl','test.dat','W');
utl_file.put_line(f1,'memocan');
utl_file.fclose(f1);
end;

ALTERNATIVE – The Preferred Solve

At this solution we do NOT need database shutdown and startup, so as TOM says,
” you don’t need to shutdown the database and restart to add a directory to the list of available
directories for reading/writing. utl_file_dir is not dynamic, directories are. “

Firstly specify directory with;

CREATE OR REPLACE DIRECTORY utl_file_dir AS 'C:/my_utl_file'

Unless shutdown and startup we can use these piece of test code to create our file;

declare
f1 utl_file.file_type;
begin
f1:=utl_file.fopen('UTL_FILE_DIR','test.dat','W');
utl_file.put_line(f1,'memocan');
utl_file.fclose(f1);
end;

Note that you should use ‘UTL_FILE_DIR’ uppercase and also your ‘C:/my_utl_file’ path should be avaible.

Oracle/PLSQL: Cast Function

In Oracle/PLSQL, the cast function converts one datatype to another.

The syntax for the cast function is:

cast ( { expr | ( subquery ) | MULTISET ( subquery ) } AS type_name )

The following casts are allowed:

TO	FROM
	char, varchar2	number	datetime / interval	raw	rowid, urowid	nchar, nvarchar2
char, varchar2	X	X	X	X	X
number	X	X
datetime / interval	X		X
raw	X			X
rowid, urowid	X				X
nchar, nvarchar2		X	X	X	X	X

Applies To:

• Oracle 9i, Oracle 10g, Oracle 11g

For example:

select cast( ’22-Aug-2003′ AS varchar2(30) )
from dual;

This would convert the date (ie: 22-Aug-2003) into a varchar2(30) value.

Source Link

how to create trigger

**TABLE TRIGGER
We can express a trigger creater code as a general aspect;

CREATE [OR REPLACE] TRIGGER trig_name
{BEFORE|AFTER} {DELETE OR INSERT OR UPDATE}
[OF column_name] ON table_name
[FOR EACH ROW]
DECLARE
--variables
BEGIN
--some code
END;


Brackets represents optionality and “|” means “OR”.