TechKnowledges

PL/SQL split function

noreply@blogger.com (pompa) — Fri, 06 Nov 2009 04:15:00 +0000

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

noreply@blogger.com (pompa) — Fri, 26 Oct 2007 16:58:00 +0000

### 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

noreply@blogger.com (pompa) — Fri, 26 Oct 2007 16:16:00 +0000

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

noreply@blogger.com (pompa) — Fri, 19 Oct 2007 17:16:00 +0000

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

noreply@blogger.com (pompa) — Fri, 19 Oct 2007 16:57:00 +0000

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

noreply@blogger.com (pompa) — Fri, 19 Oct 2007 16:48:00 +0000

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

noreply@blogger.com (pompa) — Fri, 19 Oct 2007 16:45:00 +0000

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

MySQL Cursors

noreply@blogger.com (pompa) — Fri, 19 Oct 2007 16:32:00 +0000

DECLARE cursor_name CURSOR FOR select_statement

OPEN cursor_name

FETCH cursor_name INTO var_name [, var_name] ...

CLOSE cursor_name

Cursors are supported inside stored procedures and functions and triggers. The syntax is as in embedded SQL. Cursors are currently asensitive, read-only, and non-scrolling. Asensitive means that the server may or may not make a copy of its result table.

Cursors must be declared before declaring handlers, and variables and conditions must be declared before declaring either cursors or handlers.

Ex
CREATE PROCEDURE curdemo()
BEGIN
 DECLARE done INT DEFAULT 0;
 DECLARE a CHAR(16);
 DECLARE b,c INT;
 DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
 DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

 OPEN cur1;
 OPEN cur2;

 REPEAT
   FETCH cur1 INTO a, b;
   FETCH cur2 INTO c;
   IF NOT done THEN
      IF b < c THEN
         INSERT INTO test.t3 VALUES (a,b);
      ELSE
         INSERT INTO test.t3 VALUES (a,c);
      END IF;
   END IF;
 UNTIL done END REPEAT;

 CLOSE cur1;
 CLOSE cur2;
END

MySQL Alter and Drop Procedure, Function

noreply@blogger.com (pompa) — Fri, 19 Oct 2007 16:04:00 +0000

Alter Procedure and Function
ALTER {PROCEDURE | FUNCTION}
sp_name [characteristic ...] characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER } | COMMENT 'stringDrop Procedure and Function
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

MySQL Call Statement Syntax

noreply@blogger.com (pompa) — Fri, 19 Oct 2007 16:04:00 +0000

CALL sp_name([parameter[,...]]) CALL sp_name[()]

Ex Call Statement

CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)
BEGIN
# Set value of OUT parameter
SELECT VERSION() INTO ver_param;
# Increment value of INOUT parameter
SET incr_param = incr_param + 1;
END;

mysql> SET @increment = 10;
mysql> CALL p(@version, @increment);mysql> SELECT @version, @increment;
+------------+------------+
| @version | @increment |
+------------+------------+
| 5.0.25-log | 11 |
+------------+------------+

mysql_query(mysql, "CALL p1(@param1, @param2)");
mysql_query(mysql, "SELECT @param1, @param2");
result = mysql_store_result(mysql);
row = mysql_fetch_row(result);
mysql_free_result(result);

MySQL BEGIN ... END Compound and DECLARE Statement Syntax

noreply@blogger.com (pompa) — Fri, 19 Oct 2007 16:03:00 +0000

BEGIN ... END Compound Statement Syntax

[begin_label:] BEGIN
 [statement_list]
END [end_label]

BEGIN ... END syntax is used for writing compound statements, which can appear within stored routines and triggers. A compound statement can contain multiple statements, enclosed by the BEGIN and END keywords

DECLARE Statement Syntax

DECLARE var_name[,...] type [DEFAULT value]

This statement is used to declare local variables. To provide a default value for the variable, include a DEFAULT clause. The value can be specified as an expression; it need not be a constant. If the DEFAULT clause is missing, the initial value is NULL.
The scope of a local variable is within the BEGIN ... END block where it is declared. The variable can be referred to in blocks nested within the declaring block, except those blocks that declare a variable with the same name.

Variable SET Statement

SET var_name = expr [, var_name = expr] ...

The SET statement in stored routines is an extended version of the general SET statement. Referenced variables may be ones declared inside a routine, or global system variables.

The SET statement in stored routines is implemented as part of the pre-existing SET syntax. This allows an extended syntax of SET a=x, b=y, ... where different variable types (locally declared variables and global and session server variables) can be mixed. This also allows combinations of local variables and some options that make sense only for system variables; in that case, the options are recognized but ignored.

SELECT ... INTO Statement

SELECT col_name[,...] INTO var_name[,...] table_expr

This SELECT syntax stores selected columns directly into variables. Therefore, only a single row may be retrieved.

SELECT id,data INTO x,y FROM test.t1 LIMIT 1;
 

Important

SQL variable names should not be the same as column names. If an SQL  statement, such as a SELECT ... INTO statement,  contains a reference to a column and a declared local variable with the same  name, MySQL currently interprets the reference as the name of a variable. For  example, in the following statement, xname is  interpreted as a reference to the xname variable rather than the xnamecolumn:   


Ex
CREATE PROCEDURE sp1 (x VARCHAR(5))
BEGIN
 DECLARE xname VARCHAR(5) DEFAULT 'bob';
 DECLARE newname VARCHAR(5);
 DECLARE xid INT;

 SELECT xname,id INTO newname,xid
   FROM table1 WHERE xname = xname;
 SELECT newname;
  END;

MySQL Create Procedure and Function

noreply@blogger.com (pompa) — Fri, 19 Oct 2007 15:04:00 +0000

Create Procedure and Function

Create Procedure

   CREATE
         [DEFINER = { user | CURRENT_USER }]
         PROCEDURE sp_name ([proc_parameter[,...]])
         [characteristic ...] routine_body

Create Function

   CREATE
         [DEFINER = { user | CURRENT_USER }]
         FUNCTION sp_name ([func_parameter[,...]])
         RETURNS type
         [characteristic ...] routine_body
    
  proc_parameter:
         [ IN | OUT | INOUT ] param_name type
    
  func_parameter:
              param_name type

  type:
         Any valid MySQL data type

   characteristic:
       LANGUAGE SQL| [NOT] DETERMINISTIC|
   { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } |
       SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'

 routine_body:
      Valid SQL procedure statement



Ex Create Procedure
mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
      -> BEGIN
      ->   SELECT COUNT(*) INTO param1 FROM t;
      -> END;
      -> //
      Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> CALL simpleproc(@a);
      Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @a;
      +------+
      | @a   |
      +------+
      | 3    |
      +------+
      1 row in set (0.00 sec)


Ex Create Function
mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
   -> RETURN CONCAT('Hello, ',s,'!');
      Query OK, 0 rows affected (0.00 sec)
mysql> SELECT hello('world');
      +----------------+
      | hello('world') |
      +----------------+
      | Hello, world!  |
      +----------------+
      1 row in set (0.00 sec)

Oracle Export and Import Database

noreply@blogger.com (pompa) — Sun, 14 Oct 2007 12:28:00 +0000

Export/Import Data to Database

Export

C:\>EXP UserName/Password@DB FILE=Name_Of_Dump_File.dmp TABLES=Name_Of_Table_For_Export QUERY=\"Where_Clause\" LOG=Name_Of_Log_File.log

Import

C:\>IMP UserName/Password@DB FILE=Name_Of_Dump_File.dmp FROMUSER=UserName_In_DB_Old TOUSER=UserName_In_DB_New TABLES=Name_Of_Table_For_Import IGNORE=Y CONSTRAINTS=N LOG=Name_Of_Log_File.log