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

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