Tag: plsql

APEX

Say Hello to Oracle Apex and for the new Blog member too!

 , , , , , , , ,

Hi people!

That’s my first post and I would like introduce me and this great tool what I will talk about here, at this site, but Let’s start about Oracle Application Express, or Apex, which is probably your most intention here! You can read about my history with Apex in the end of this article.

Oracle Apex is a development tool that enables you to build applications using only your web browser, using basically PL-SQL, this familiarity helps to create departmental applications. Even DBA’s can create good web applications easily. Apex is not a new tool, was first released on 2006, and previously it was called HTML DB. After 10 years of development, offers a modern IDE, and with more dedication, you can use to build complex solutions using CSS and JavaScript.

Apex also comes with a entire system to manage your development life cycle. Using the Team Development it is possible to track your project progress from brainstorm to tracking bugs and continuous maintenances.

You can start using and testing Oracle Apex right now, just accessing apex.oracle.com and creating your own workspace. Just click Get Started and select Free Workspace. Remember that should be used for educational propose.

get_workspace

By the way, in the next weeks and some articles from now, I intend to write about how to create an entire application, describing most of standards options and explaining Oracle Apex in details.

I start using Apex in version 2, when the standard templates produces applications that looked like Enterprise Manager some years ago. The latest version 5.02 was released in October 2015. The Apex 5 has a revolutionary IDE, which is in the same way powerful, intuitive, clean and easy to use.

Version 2
Version 5

Enjoy and welcome to Apex World! There is an active community on OTN that supports mostly users needs and questions through discussion web forums.

Cassiano.

Database, RAC | Data Guard, SQL | PLSQL

Scheduler Job by Node (RAC Database)

 , ,

Sometimes you want to run something just in one node of the RAC. Here is an example to do it:

create or replace procedure USER_JOB.PRC_SOMETHING is
begin
-- do something
null;
end;
/
begin
sys.dbms_scheduler.create_job(job_name => 'USER_JOB.JOB_SOMETHING',
job_type            => 'PLSQL_BLOCK',
job_action          => 'USER_JOB.PRC_SOMETHING;',
start_date          => sysdate,
repeat_interval     => 'Freq=Minutely;Interval=30',
end_date            => to_date(null),
job_class           => 'DEFAULT_JOB_CLASS',
enabled             => true,
auto_drop           => false,
comments            => 'Something Job.');
end;
/
begin
dbms_scheduler.set_attribute(name => 'USER_JOB.JOB_SOMETHING',
 attribute=>'INSTANCE_ID', value=> 1);
end;
/

Matheus.

Database, Errors and Bugs, Security, SQL | PLSQL

ORA-28004: invalid argument for function specified in PASSWORD_VERIFY_FUNCTION

 , , , ,

An unexpected error, right?

SQL> CREATE PROFILE TEST_PROF LIMIT
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 30
PASSWORD_REUSE_MAX 15
PASSWORD_VERIFY_FUNCTION fnc_validation;
CREATE PROFILE TEST_PROF LIMIT
*
ERROR at line 1:
ORA-28004: invalid argument for function specified in PASSWORD_VERIFY_FUNCTION FNC_VALIDATION

That is a simple need. You have to use 3 parameters on function: username varchar2, password varchar2, old_password varchar2.

Matheus.

Database, SQL | PLSQL

Oracle Convert Number into Days, Hours, Minutes

 , ,

There’s a little trick…
Today I had to convert a “number” of minutes into hours:minutes format. Something like convert 570 minutes in format hour:minutes. As you know, 570/60 is “9,5” and should be “9:30”.

Lets use 86399 seconds (23:59:59) as example:

I began testing “to_char(to_date)” functions:
boesing@db>select to_char(to_date(86399,'sssss'),'hh24:mi:ss') formated from dual;

FORMATED
——–
23:59:59

Ok, it works. But using “seconds past midnight” (sssss). By the way, it works between 0 and 86399 only:

boesing@db> select to_char(to_date(86400,'sssss'),'hh24:mi:ss') from dual;
select to_char(to_date(86400,'sssss'),'hh24:mi:ss') from dual
*
ERROR at line 1:
ORA-01853: seconds in day must be between 0 and 86399

The problem remains. How to use minutes in 3 digits (570 minutes -> 9:30), for example?
The best way I solve was:

--- Seconds in hours:minutes:seconds
--- If you comment the first "TO_CHAR" line, can be minutes in hours:minutes too..
select
TO_CHAR(TRUNC(vlr/3600),'FM9900') || ':' || -- hours
TO_CHAR(TRUNC(MOD(vlr,3600)/60),'FM00') || ':' || -- minutes
TO_CHAR(MOD(vlr,60),'FM00') -- second
from dual;

It always works. 🙂

boesing@db>select
2 TO_CHAR(TRUNC(86399/3600),'FM9900') || ':' || -- hours
3 TO_CHAR(TRUNC(MOD(86399,3600)/60),'FM00') || ':' || -- minutes
4 TO_CHAR(MOD(86399,60),'FM00') -- second
5 from dual;

TO_CHAR(TRUNC
————-
23:59:59

boesing@db>select
2 TO_CHAR(TRUNC(570/3600),’FM9900′) || ‘:’ || — hours
3 TO_CHAR(TRUNC(MOD(570,3600)/60),’FM00′) || ‘:’ || — minutes
4 TO_CHAR(MOD(570,60),’FM00′) — second
5 from dual;

TO_CHAR(TRUNC
————-
00:09:30

boesing@db>select
2 TO_CHAR(TRUNC(MOD(570,3600)/60),’FM00′) || ‘:’ || — hours
3 TO_CHAR(MOD(570,60),’FM00′) — minutes
4 from dual;

TO_CHAR
——-
09:30

Any better way? Leave a comment. Thanks!

Matheus.