Error creating database link @11g

If you are getting a “ ORA-1017:  invalid username/password; logon denied” when creating a database link, and you ensured to enter the correct password, you probably try it against a 11g database version. In this version is a new feature the “case sensitivity rules”. This statement worked from a 11g db to a 10g database, and threw a exception from 10g to 11g.

create database link testdblink
connect to TESTUSER identified by “testpw”
using ‘tns_for_test’;

Use double-quotes, and check these documents;
What Are the Oracle Database Built-in Password Protections?
http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authentication.htm#CHDBBGFG

Enabling or Disabling Password Case Sensitivity
http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authentication.htm#CHDJDCGI

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.

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 grant a user


grant update on employees to scott


this piece of code gives the right of update on employees table of current user, for user scott

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”.

sqlplus error viewing

while creating a function or a procedure if we get compile errors from sqlplus,
after the error line, we should write this piece of code to get information about errors;
show errors