PL/SQL split function

create or replace function utl_split(str_in in varchar2, delim_in in varchar2 default '.')
return SplitType as
/* !!!!!!!!!!!!!!!!! Warnning Please Read it before Use or Compile !!!!!!!!!!!!!!!!!!!!!!
Author by Pompa if first compile you must be create object type as below script
before compilie this function
==scrip==============================
create or replace type SplitType as table of varchar2(500); / ==scrip==============================
How to use You can use by 2 method
1. SELECT * FROM TABLE (CAST (utl_split ('10.01.03.04.234') AS SplitType))
2. declare catch_tmp SplitType;
begin
catch_tmp := utl_split('12345678910','') ;
for i in 1 .. catch_tmp.count loop
dbms_output.put_line(catch_tmp(i));
end loop;
end; */
str_tmp long default str_in delim_in;
indx_tmp number;
return_tmp SplitType := SplitType();
begin
loop indx_tmp := instr( str_tmp, delim_in );
exit when (nvl(indx_tmp,0) = 0);
return_tmp.extend;
return_tmp( return_tmp.count ) := ltrim(rtrim(substr(str_tmp,1,indx_tmp-1)));
str_tmp := substr( str_tmp, indx_tmp+length(delim_in) );
end loop;
return return_tmp;
end utl_split;

Perl Monitor State SQL server On Window

### Check Proccess Database ###################################
$state_agent = `sc \\\\acer-31cca9d11c. query sqlserveragent find /C "STOPPED"`;
$state_sql = `sc \\\\acer-31cca9d11c. query mssqlserver find /C "STOPPED"`;

### Check if SQL ServerAgent or MSsqlserver down order start ###
if (($state_agen == 1) ($state_sql == 1)) {

### Check state of SQL ServerAgent ############################
if ($state_agent == 1) { ## Shutdown SQL ServerAgent##

system('net stop sqlserveragent');
$stop_agent = `sc \\\\acer-31cca9d11c. query sqlserveragent find /C "STOPPED"`;

## Check Result of Stop SQL SeverAgent ##
if ($stop_agent == 1) { ## Stop Success ##

$agentrun = "Shutdown[STOPPED] SQL ServerAgent have Completed.\n";
print $agentrun;

}else{

$agentrun = "Shutdown[STOPPED] SQL ServerAgent have Failure.\n";
print $agentrun;

}

}else{

$agentrun = "SQL ServerAgent Not STOPPED.\n";
print $agentrun;

}
### End Check state of SQL ServerAgent ##########################

### Check State of MSsqlserver ################################
if ($state_sql == 1) { ## Shutdown MSsqlserver ##

system('net stop mssqlserver');
$stop_sql = `sc \\\\acer-31cca9d11c. query mssqlserver find /C "STOPPED"`;

## Check Result of Stop MSSQL Sever ##
if ($stop_sql == 1) { ## Stop Success ##

$sqlrun = "Shutdown[STOPPED] MSSQL Server have Completed.\n";
print $sqlrun;

}else{

$sqlrun = "Shutdown[STOPPED] MSSQL Server have Failure.\n";
print $sqlrun;

}

}else{

$sqlrun = "MSSQL Server Not STOPPED.\n";
print $sqlrun

}
### Check State of MSsqlserver ################################

### Start MSsqlserver and SQL ServerAgent ########################
print "[--Start Service--]\n";
if ($state_sql == 1) { ## Start MSSQL Server if MSSQL Server Shutdown##

system('net start mssqlserver');
$start_sql = `sc \\\\acer-31cca9d11c. query mssqlserver find /C "RUNNING"`;

## Check Result of Start MSSQL Server ##########################
if ($start_sql == 1) { ## Start Success ##

$msgservice .= "\t\tStart MSSQL Server have Completed.\n";
$state_agent = 1;

}else{

$state_agent = 0;
$msgservice .= "\t\tStart MSSQL Server have Failure.\n";

}

}

if ($state_agent == 1) { ## Start SQL ServerAgent if SQL Server Agent Down ##

system('net start sqlserveragent');
$start_agent = `sc \\\\acer-31cca9d11c. query sqlserveragent find /C "RUNNING"`;

## Check Result of Start SQL ServerAgent ########################
if ($start_agent == 1) {## Start Success ##

$msgservice .= "\t\tStart SQL ServerAgent have Completed.\n";

}else{

$msgservice .= "\t\tStart SQL ServerAgent have Failure.\n";

}

}

}

MsSQL T-SQL HTTP POST String

declare @iobject int,
@ihr int,
@data nvarchar(4000) ,
@smtpWebService varchar(200),
@retVal varchar(8000),
@src varchar(1000),
@desc varchar(1000)

exec @ihr = sp_oaCreate 'Msxml2.ServerXMLHTTP.3.0', @iobject OUTPUT
if (@ihr <> 0)
begin
exec sp_displayoaerrorinfo @iobject, @ihr return
end

set @data = 'boo=yeah'
set @smtpWebService = 'http://www.google.com'

exec @ihr = sp_OAMethod @iobject, 'Open', null, 'POST',@smtpWebService, 0
exec @ihr = sp_OAMethod @iobject, 'setRequestHeader', null, 'Content-Type', 'application/x-www-form-urlencoded'
exec @ihr = sp_OAMethod @iobject, 'send', null, @data

if @ihr <> 0
begin
exec sp_OAGetErrorInfo @iobject, @src OUT, @desc OUT
select hr = convert(varbinary(4),@ihr), Source = @src, Description = @desc
--raiserror('Error Creating COM Component 0x%x, %s, %s',16,1, @ihr, @src, @desc) return
print @src
print @desc
end

exec @ihr = sp_OAGetProperty @iObject, 'responseText', @retVal OUT
print @retVal
-- Destroy the object
exec @ihr = sp_OADestroy @iobject

Oracle job of type EXECUTABLE failed with exit code: Permission denied

Created schedule job

BEGIN
DBMS_SCHEDULER.create_job
(job_name => 'DF_Partitions',
repeat_interval => 'FREQ=HOURLY;INTERVAL=2',
job_type => 'EXECUTABLE',
job_action => '/home/osadmin/bin/df.sh',
enabled => TRUE,
comments => 'Check Partitions on System'
);
END;

set permission on file
#chown oracle:dba /home/osadmin/bin/df.sh
#chmod 750 /home/osadmin/bin/df.sh

But I found "ORA-27369: job of type EXECUTABLE failed with exit code: Permission denied " in schedule log.

So, I resolve that i changed permission on $ORACLE_HOME/bin/extjob file.
#chown oracle:dba $ORACLE_HOME/bin/extjob

Oracle pl/sql function urlencode, urldecode

----------------------------------------------------------------------
create or replace function urlencode
----------------------------------------------------------------------
(p_str in varchar2) return varchar2 is

/* Declare */
l_tmp varchar2(6000);
l_hex varchar2(16) default ‘0123456789ABCDEF’;
l_num number;
l_bad varchar2(100) default ‘ >%}\~];?@&<#{|^[`/:=$+''"';
l_char char(1);

begin
if p_str is null
then
return null;
end if;
for i in 1 .. length(p_str) loop
l_char := substr(p_str, i, 1);
if instr(l_bad, l_char) > 0
then
l_num := ascii(l_char);
l_tmp := l_tmp || ‘%’ ||
substr(l_hex, mod(trunc (l_num / 16), 16) + 1, 1) ||
substr(l_hex, mod(l_num, 16) + 1, 1);
else
l_tmp := l_tmp || l_char;
end if;
end loop;
return l_tmp;
end urlencode;
/

----------------------------------------------------------------------
create or replace function urldecode
----------------------------------------------------------------------
(p_str in varchar2) return varchar2 is

/* Declare */
l_hex varchar2(16) := ‘0123456789ABCDEF’;
l_idx number := 0;
l_ret long := p_str;

begin
if p_str is null then
return p_str;
end if;
loop
l_idx := instr(l_ret, ‘%’, l_idx + 1);
exit when l_idx = 0;
l_ret := substr(l_ret, 1, l_idx - 1) ||
chr((instr(l_hex, substr(l_ret, l_idx + 1, 1)) - 1) * 16 +
instr(l_hex, substr(l_ret, l_idx + 2, 1)) - 1) ||
substr(l_ret, l_idx + 3);
end loop;
return l_ret;
end urldecode;
/

Test:
SQL> select urlencode(’&+=’) from dual;
URLENCODE(’&+=’)
——————————————————————————–
%26%2B%3D
SQL> select urldecode(’%26%2B%3D’) from dual;
URLDECODE(’%26%2B%3D’)
——————————————————————————–

MySQL Flow Control Constructs

1. IF Statement

IF search_condition THEN statement_list
 [ELSEIF search_condition THEN statement_list] ...
 [ELSE statement_list]
END IF

IF()	If/else construct
IFNULL()	Null if/else construct
NULLIF()	Return NULL if expr1 = expr2

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.

EX
mysql> SELECT IF(1>2,2,3);
     -> 3
mysql> SELECT IF(1<2,'yes','no');
     -> 'yes'
mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
     -> 'no'

If only one of expr2 or expr3 is explicitly NULL, the result type of the IF() function is the type of the non-NULL expression.

expr1 is evaluated as an integer value, which means that if you are testing floating-point or string values, you should do so using a comparison operation.

EX
mysql> SELECT IF(0.1,1,0);
        -> 0
mysql> SELECT IF(0.1<>0,1,0);
       -> 1

In the first case shown, IF(0.1) returns 0 because 0.1 is converted to an integer value, resulting in a test of IF(0). This may not be what you expect. In the second case, the comparison tests the original floating-point value to see whether it is non-zero. The result of the comparison is used as an integer.

The default return type of IF() (which may matter when it is stored into a temporary table) is calculated as follows:

2. CASE Statement

CASE case_value
 WHEN when_value THEN statement_list
 [WHEN when_value THEN statement_list] ...
 [ELSE statement_list]
END CASE

Or:

CASE
 WHEN search_condition THEN statement_list
 [WHEN search_condition THEN statement_list] ...
 [ELSE statement_list]
END CASE

The CASE statement for stored routines implements a complex conditional construct. If a search_condition evaluates to true, the corresponding SQL statement list is executed. If no search condition matches, the statement list in the ELSE clause is executed. Each statement_list consists of one or more statements.

Note

The syntax of the CASE statement shown here for use inside stored routines differs slightly from that of the SQL CASE expression described in “Control Flow Functions”. The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END.

EX
mysql> SELECT CASE 1 WHEN 1 THEN 'one'
    ->     WHEN 2 THEN 'two' ELSE 'more' END;
        -> 'one'
mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
        -> 'true'
mysql> SELECT CASE BINARY 'B'
    ->     WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
        -> NULL

3. LOOP Statement

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

LOOP implements a simple loop construct, enabling repeated execution of the statement list, which consists of one or more statements. The statements within the loop are repeated until the loop is exited; usually this is accomplished with a LEAVE statement.

4. LEAVE Statement

LEAVE label

This statement is used to exit any labeled flow control construct. It can be used within BEGIN ... END or loop constructs (LOOP, REPEAT, WHILE).

5. ITERATE Statement

ITERATE label     

ITERATE can appear only within LOOP, REPEAT, and WHILE statements. ITERATE means “do the loop again.”

EX
CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 <>
    LEAVE label1;
  END LOOP label1;
  SET @x = p1;
END

6. REPEAT Statement

[begin_label:] REPEAT statement_list UNTIL search_condition END REPEAT [end_label]

The statement list within a REPEAT statement is repeated until the search_condition is true. Thus, a REPEAT always enters the loop at least once. statement_list consists of one or more statements.

A REPEAT statement can be labeled. end_label cannot be given unless begin_label also is present. If both are present, they must be the same.

EX
mysql> delimiter //
mysql> CREATE PROCEDURE dorepeat(p1 INT)
 -> BEGIN
 ->   SET @x = 0;
 ->   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
 -> END
 -> //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL dorepeat(1000)//
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x//
+------+
| @x   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)

7. WHILE Statement

[begin_label:] WHILE search_condition DO
 statement_list
END WHILE [end_label]

The statement list within a WHILE statement is repeated as long as the search_condition is true. statement_list consists of one or more statements.

A WHILE statement can be labeled. end_label cannot be given unless begin_label also is present. If both are present, they must be the same.

EX
CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;

WHILE v1 > 0 DO
 ...
 SET v1 = v1 - 1;
END WHILE;
END

MySQL Information Functions

Name	Description
BENCHMARK()	Repeatedly execute an expression
CHARSET()(v4.1.0)	Return the character set of the argument
COERCIBILITY()(v4.1.1)	Return the collation coercibility value of the string argument
COLLATION()(v4.1.0)	Return the collation of the string argument
CONNECTION_ID()	Return the connection ID (thread ID) for the connection
CURRENT_USER(), CURRENT_USER	Return the username and hostname combination
DATABASE()	Return the default (current) database name
FOUND_ROWS()	For a SELECT with a LIMIT clause, the number of rows that would be returned were there no LIMIT clause
LAST_INSERT_ID()	Value of the AUTOINCREMENT column for the last INSERT
ROW_COUNT()(v5.0.1)	The number of rows updated
SCHEMA()(v5.0.2)	A synonym for DATABASE()
SESSION_USER()	Synonym for USER()
SYSTEM_USER()	Synonym for USER()
USER()	Return the current username and hostname
VERSION()	Returns a string that indicates the MySQL server version