” is equal to NULL?

January 29, 2008 at 2:23 pm (Oracle & PL/SQL)

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as extoperSQL> set serveroutput on

SQL>

SQL> DECLARE

  2       test VARCHAR2(20) := '';

  3  BEGIN

  4       IF test IS NULL

  5       THEN

  6            dbms_output.put_line(''''' is equal to NULL');

  7       END IF;

  8 

  9       IF test = ''

 10       THEN

 11            dbms_output.put_line(''''' is equal to ''''');

 12       END IF;

 13  END;

 14  /

'' is equal to NULL

PL/SQL procedure successfully completed

SQL>

Permalink 2 Comments

Ora-01652 : Unable to extend temp segment by 128 in tablespace

January 24, 2008 at 2:49 pm (Oracle & PL/SQL)

That error occurs when failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.
So we can this in two ways.

1.
Check the datafiles sizes..

SELECT * FROM DBA_DATA_FILES;
FILE_NAME                      FILE_ID TABLESPACE_NAME BYTES    BLOCKS STATUS
1 D:\ORACLEXE\ORADATA\XE\USERS.DBF 4     USERS       104857600  12800 AVAILABLE
2 D:\ORACLEXE\ORADATA\XE\SYSAUX.DBF 3    SYSAUX      450887680  55040 AVAILABLE
3 D:\ORACLEXE\ORADATA\XE\UNDO.DBF   2    UNDO        94371840   11520 AVAILABLE
4 D:\ORACLEXE\ORADATA\XE\SYSTEM.DBF 1    SYSTEM      629145600  76800 AVAILABLE

Then resize your datafile or add a new datafile to current tablespace
Resizing Datafile :
ALTER DATABASE DATAFILE ‘c:\oracle\oradata\orabase\USERS.DBF’ RESIZE 200M;
Addind Datafile to existing Tablespace:
ALTER TABLESPACE USERS ADD DATAFILE ‘c:\oracle\oradata\orabase\USERS2.DBF’ SIZE 50M;

2.
Change the user’s default tablespace to a bigger one :)

SELECT * FROM Dba_Users;
USERNAME USER_ID ACCOUNT_STATUS DEFAULT_TABLESPACE
1 ASD 36 OPEN SYSTEM
2 SYS 0 OPEN SYSTEM
3 SYSTEM 5 OPEN SYSTEM
4 ANONYMOUS 28 OPEN SYSAUX   
ALTER USER asd DEFAULT TABLESPACE users;
SELECT * FROM Dba_Users;
USERNAME USER_ID ACCOUNT_STATUS DEFAULT_TABLESPACE
1 ASD 36 OPEN USERS
2 SYS 0 OPEN SYSTEM
3 SYSTEM 5 OPEN SYSTEM
4 ANONYMOUS 28 OPEN SYSAUX  

References :
http://www.psoug.org/reference/tablespaces.html
http://www.psoug.org/reference/datafiles.html

Permalink 3 Comments

Men vs. Women War

January 24, 2008 at 9:45 am (Non-technical)

Permalink No Comments

Rownum & Order by

September 26, 2007 at 8:27 am (Non-technical)

Warning,

If you user rownum like this,

SELECT *

FROM TABLE

WHERE rownum < 2

ORDER BY column

you do this : get the first row that readed from database, and sort the 1 row.

Correct :

SELECT *

FROM (SELECT *

   FROM TABLE

   ORDER BY COLUMN

   )

WHERE rownum < 2

That means : sort the rows and get the first row.

Permalink No Comments

A Function Based Example

August 10, 2007 at 4:41 pm (Oracle & PL/SQL)

SQL> set autotrace traceonly
SQL> SET serveroutput ON

–let’s create a table that contains country names and codes from apex environment
SQL> CREATE TABLE t_func AS select * from flows_020100.wwv_flow_countries t;

Table created.

–then write a query that searches brazil :)
SQL> SELECT * FROM t_func WHERE upper(country_name) LIKE ‘BRAZIL’;
Execution Plan
———————————————————-                     
Plan hash value: 175459743                                                     
                                                                               
—————————————————————————-   
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |   
—————————————————————————-   
|   0 | SELECT STATEMENT  |        |     1 |   146 |     2   (0)| 00:00:01 |   
|*  1 |  TABLE ACCESS FULL| T_FUNC |     1 |   146 |     2   (0)| 00:00:01 |   
—————————————————————————-   
                                                                               
Predicate Information (identified by operation id):                            
—————————————————                            
                                                                               
   1 - filter(UPPER(”COUNTRY_NAME”) LIKE ‘BRAZIL’)                             
                                                                               
Note                                                                           
—–                                                                          
   - dynamic sampling used for this statement                                  
Statistics
———————————————————-                     
         48  recursive calls                                                   
          0  db block gets                                                     
         12  consistent gets                                                   
          1  physical reads                                                    
          0  redo size                                                         
        482  bytes sent via SQL*Net to client                                  
        384  bytes received via SQL*Net from client                            
          2  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                    
          0  sorts (disk)                                                      
          1  rows processed  

–then let’s create an function based index on column country_name                                                  

SQL> CREATE INDEX t_func_idx ON t_func(upper(country_name));

Index created.

–write the query again

SQL> SELECT * FROM t_func WHERE upper(country_name) LIKE ‘BRAZIL’;
Execution Plan
———————————————————-                     
Plan hash value: 3533916538                                                    
                                                                               
——————————————————————————————                                                                     
                                                                               
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
Time     |                                                                     
                                                                               
——————————————————————————————                                                                     
                                                                               
|   0 | SELECT STATEMENT         |            |     1 |   146 |     1   (0)| 00:00:01                                                                                                                                            
|   1 |  TABLE ACCESS BY INDEX ROWID| T_FUNC     |     1 |   146 |     1   (0)| 00:00:01 |                                                                     
                                                                               
|*  2 |   INDEX RANGE SCAN          | T_FUNC_IDX |     1 |       |     1   (0)| 00:00:01 |                                                                     
                                                                               
——————————————————————————————                                                                     
                                                                               
                                                                               
Predicate Information (identified by operation id):                            
—————————————————                            
                                                                               
   2 - access(UPPER(”COUNTRY_NAME”) LIKE ‘BRAZIL’)                             
                                                                               
Note                                                                           
—–                                                                          
   - dynamic sampling used for this statement                                  
Statistics
———————————————————-                     
         28  recursive calls                                                   
          0  db block gets                                                     
          9  consistent gets                                                   
          0  physical reads                                                    
          0  redo size                                                         
        482  bytes sent via SQL*Net to client                                  
        384  bytes received via SQL*Net from client                            
          2  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                    
          0  sorts (disk)                                                      
          1  rows processed                                                    

SQL> spool off

In the first example, query made a FULL TABLE SCAN and cost was 2.

In the second example, our index worked and it made an INDEX RANGE SCAN by using T_FUNC_IDX and cost was 1. These tables that we query on are small tables. The difference will be increase in larger tables…

tested in Oracle XE Edition 10g Release 2 (10.2)

And Code :
SET serveroutput ON
set autotrace traceonly
alter session set QUERY_REWRITE_ENABLED=TRUE;
alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
drop table t_func;
drop index t_func_idx;
CREATE TABLE t_func AS select * from flows_020100.wwv_flow_countries t;
SELECT * FROM t_func WHERE upper(country_name) LIKE ‘BRAZIL’;
CREATE INDEX t_func_idx ON t_func(upper(country_name));
SELECT * FROM t_func WHERE upper(country_name) LIKE ‘BRAZIL’;

A advised and different perspective to function based index is :)

http://oracle-unix.blogspot.com/2007/07/performance-tuning-how-to-make-like.html

Permalink No Comments

My Oracle CheetSheet - 1

August 10, 2007 at 4:26 pm (Non-technical)

–flush buffer cache and shared pool
alter system flush buffer_cache;
alter system flush SHARED_POOL;
=======================================================
–increase a column’s type value
CREATE TABLE a (aa VARCHAR(10));
INSERT INTO a VALUES (’sdfsdf’);
INSERT INTO a VALUES (’sdxxxf’);
INSERT INTO a VALUES (’sddsdf’);
ALTER TABLE a modify aa VARCHAR2(50);
=======================================================
–create a sequence
CREATE SEQUENCE my_table_seq
            START WITH 1
            INCREMENT BY 1
            NOMAXVALUE
            NOCYCLE;
=======================================================
–create a table
CREATE TABLE T_E_LOC_UPD_LOGS(
        NLOG_ID          NUMBER(7, 0)     NOT NULL,
        NSTART_LOGID     NUMBER(10, 0)    NOT NULL,
        NFINISH_LOGID    NUMBER(10, 0),
        BSUCCESS         NUMBER(1, 0)     NOT NULL,
        DLOG_DATE        DATE             NOT NULL,
        NERROR_CODE      NUMBER(6, 0),
        VERROR_DESC      VARCHAR2(500),
        CONSTRAINT T_E_LOC_UPD_LOGS_PK PRIMARY KEY (NLOG_ID)
    );
=======================================================
–create a directory
CREATE DIRECTORY oraload AS ‘c:\oraload\’;
GRANT READ,WRITE ON DIRECTORY oraload TO hr;                                
SELECT * FROM all_directories; 
=======================================================
–grant access to any table to a schema
GRANT SELECT, INSERT, UPDATE, DELETE ON my_table TO hr;
=======================================================
–add primary key
ALTER TABLE my_table ADD ( CONSTRAINT my_table_PK PRIMARY KEY (LOG_ID));
=======================================================
–crate database link
– requires “xxxxxxxxx.xxxx” entry in TNSNAMES.ORA
CREATE DATABASE LINK my_link
CONNECT TO <<username>> IDENTIFIED BY <<password>>
USING ‘xxxxxxxxx.xxxx’;

SELECT * FROM all_db_links; 
DROP DATABASE LINK my_link;  
SELECT * FROM my_table@my_link;
=======================================================
–some tablespace options
SELECT * FROM User_Tablespaces;
ALTER TABLESPACE akdora AUTOEXTEND ON NEXT 30M MAXSIZE UNLIMITED;
Alter table FR_TBL_loc_upd_data storage (maxextents unlimited);
CREATE TABLESPACE
  akdora
  DATAFILE
  ‘/akdora.dbf’
  size
  300M
  AUTOEXTEND ON
  next 30M 
  MAXSIZE UNLIMITED;
ALTER TABLESPACE akdora NOLOGGING;
=======================================================
–ANALYZE Statement
http://www.ss64.com/ora/analyze.html
=======================================================

Permalink 2 Comments

A small sequence trick

August 8, 2007 at 8:20 am (Oracle & PL/SQL)

I could not send a sequence next value directly as a parameter in a pl/sql block. Then, :)


begin
pr_insert_mytable
(
mytable_seq.NEXTVAL ,
xx,
yy,
0,
zz,
);
end;

Error: PLS-00357: Table,View Or Sequence reference mytable_seq.NEXTVAL not allowed in this context
Line: 301
Text: pr_insert_log(mytable_seq.NEXTVAL,

Error: PL/SQL: Statement ignored
Line: 301
Text: pr_insert_mytable(mytable_seq.NEXTVAL,

then we can set it the sequence to a variable then insert it :)

declare
ln_id number(10);
begin
select mytable_seq.NEXTVAL into ln_id
from dual;
pr_insert_mytable
(
ln_id,
xx,
yy,
0,
zz,
);
end;

Permalink 1 Comment

Calling a web service by PL/SQL (utl_http)

August 3, 2007 at 1:49 pm (Oracle & PL/SQL)

Last day, a mission is given to me about calling a web services by pl/sql. Firstly, i started to search ; “What is a web service?” The term Web services describes a standardized way of integrating Web-based applications using the XML, SOAP, WSDL and UDDI open standards over an Internet protocol backbone. Actually we can summarize it;

Web Services can convert your applications into Web-applications.
By using Web services, your application can publish its function or message to the rest of the world.

Web Services can be used by other applications.
With Web services your accounting department’s Win 2k servers can connect with your IT supplier’s UNIX server.

The basic Web Services platform is XML + HTTP.
Web services uses XML to code and decode your data and SOAP to transport it.

We have some input parameters and make a request. Then, we get a response from it with output parameters. All this syntax is in XML format. So, we will use XMLtype in our pl/sql package.

 

Let’s start to write a soap access package or we can use this package :) (www.oracle-base.com/dba/miscellaneous/soap_api.sql)

:) Let’s assume we have a function “myFunction” in this page http://www.mywebservice.com/web :) Then,start to write our code

CREATE OR REPLACE PACKAGE pkg_my_webservice IS
FUNCTION call_myfuntion
(
vp_parameter1 VARCHAR2,
vp_parameter2 VARCHAR2
) RETURN VARCHAR2;
END pkg_my_webservice;
/
CREATE OR REPLACE PACKAGE BODY pkg_my_webservice IS


vg_funciton_fnc VARCHAR2(256) := ‘myFunction’;
vg_ws_address VARCHAR2(255) := ‘http://www.mywebservice.com/web’;


FUNCTION call_myfuntion(
–lets assume that it inputs two parameters called string1, string2
vp_parameter1 VARCHAR2,
vp_parameter2 VARCHAR2)

RETURN VARCHAR2 AS
ol_req soap_api.t_request;
ol_resp soap_api.t_response;
BEGIN
– we initilize a new request
ol_req := soap_api.new_request(vg_funciton_fnc,
‘xmlns=”‘ || vg_ws_address || ‘”‘);
– we started to add parameters
soap_api.add_parameter(ol_req,
’string1′,
‘partns:string’,
vp_parameter1);
soap_api.add_parameter(ol_req,
’string2′,
‘partns:string’,
vp_parameter1);
– we call the web service
ol_resp := soap_api.invoke(ol_req,
vg_ws_address,
vg_funciton_fnc);
– we get back the results
RETURN soap_api.get_return_value(ol_resp,
‘result’, — result tag name
‘xmlns:m=”‘ || –can be change as “xmlns:n1″
vg_ws_address || ‘”‘);

END call_myfuntion;

END pkg_my_webservice;
/

pkg_my_webservice_blog.txt

You may need to change some tag attributes. The main basic function syntax is like this. If you have more than one return xml tags, then you need to call the get_return_value function as much as result attributes. You may also store the results in a pl/sql type and return it back.

There are also other ways to do this. You may also use oracle’s “utl_dbws” package, however “utl_http” is more faster than it. You can test it.

In my next post, i am gonna explain soap_api package in more details.

Permalink 1 Comment

July 4, 2007 at 8:26 am (Java)

I started to learn Eclipse, I find this video tutorials for beginning level.
Thanks Dave
Eclipse Video Tutorial by Dave Powell
http://www.bestechvideos.com/2007/01/18/eclipse-video-tutorial-by-dave-powell/

Permalink No Comments

COMPRESSING INDEXES

April 17, 2007 at 11:24 am (Oracle & PL/SQL)

--IN THIS DEMO, WE WILL CALCULATE THE REQUIRED SPACE FOR AN INDEX THAT CONTAINING 3 COLUMNS OF A TABLE
–TOTAL BLOCK SIZE = TOTOL BLOCKS - UNUSED BLOCKS
–I FOUND THIS EXAMPLE FROM “EXPERT ONE-TO-ONE ORACLE” BOOK OF “TOMS KYTE”
–WE CAN SEE THAT TO COMPRESS A INDEX GAINS US MORE FREE SPACE :)
SQL>
SQL> set serverout on
SQL> create or replace procedure show_space
2 (
3 p_segname in varchar2,
4 p_owner in varchar2 default user,
5 p_type in varchar2 default ‘TABLE’,
6 p_partition in varchar2 default NULL
7 )
8 as
9 l_free_blks number;
10 l_total_blocks number;
11 l_total_bytes number;
12 l_unused_blocks number;
13 l_unused_bytes number;
14 l_LastUsedExtFileId number;
15 l_LastUsedExtBlockId number;
16 l_last_used_block number;
17
18 procedure p( p_label in varchar2, p_num in number )
19 is
20 begin
21 dbms_output.put_line( rpad(p_label,40,’.') || p_num );
22 end;
23 begin
24
25 dbms_space.free_blocks
26 ( segment_owner => p_owner,
27 segment_name => p_segname,
28 segment_type => p_type,
29 partition_name => p_partition,
30 freelist_group_id => 0,
31 free_blks => l_free_blks );
32
33 dbms_space.unused_space
34 ( segment_owner => p_owner,
35 segment_name => p_segname,
36 segment_type => p_type,
37 partition_name => p_partition,
38 total_blocks => l_total_blocks,
39 total_bytes => l_total_bytes,
40 unused_blocks => l_unused_blocks,
41 unused_bytes => l_unused_bytes,
42 last_used_extent_file_id => l_LastUsedExtFileId,
43 last_used_extent_block_id => l_LastUsedExtBlockId,
44 last_used_block => l_last_used_block );
45
46 p( ‘Free Blocks’, l_free_blks );
47 p( ‘Total Blocks’, l_total_blocks );
48 p( ‘Total Bytes’, l_total_bytes );
49 p( ‘Unused Blocks’, l_unused_blocks );
50 p( ‘Unused Bytes’, l_unused_bytes );
51 p( ‘Last Used Ext FileId’, l_LastUsedExtFileId );
52 p( ‘Last Used Ext BlockId’, l_LastUsedExtBlockId );
53 p( ‘Last Used Block’, l_last_used_block );
54
55 end;
56 /
Procedure created.

SQL>
SQL>create table table123 as select * from all_objects;

Table created.

SQL>
SQL> create index t_idx on table123(owner,object_type,object_name);

Index created.

SQL> exec show_space(’T_IDX’,user,’INDEX’);
Free Blocks………………………..0
Total Blocks……………………….80
Total Bytes………………………..655360
Unused Blocks………………………7
Unused Bytes……………………….57344
Last Used Ext FileId………………..1
Last Used Ext BlockId……………….43265
Last Used Block…………………….1

PL/SQL procedure successfully completed.

–TOTAL BLOCK SIZE = 80 - 7 = 73

SQL>
SQL> drop index t_idx;

Index dropped.

SQL> create index t_idx on table123(owner,object_type,object_name) compress 1;

Index created.

SQL> exec show_space(’T_IDX’,user,’INDEX’);
Free Blocks………………………..0
Total Blocks……………………….64
Total Bytes………………………..524288
Unused Blocks………………………1
Unused Bytes……………………….8192
Last Used Ext FileId………………..1
Last Used Ext BlockId……………….43249
Last Used Block…………………….7

PL/SQL procedure successfully completed.

–TOTAL BLOCK SIZE = 64 - 1 = 63

SQL>
SQL> drop index t_idx;

Index dropped.

SQL> create index t_idx on table123(owner,object_type,object_name) compress 2;

Index created.

SQL> exec show_space(’T_IDX’,user,’INDEX’);
Free Blocks………………………..0
Total Blocks……………………….56
Total Bytes………………………..458752
Unused Blocks………………………4
Unused Bytes……………………….32768
Last Used Ext FileId………………..1
Last Used Ext BlockId……………….42897
Last Used Block…………………….4

PL/SQL procedure successfully completed.

–TOTAL BLOCK SIZE = 56 - 4 = 52

SQL> spool off

–FINALLY WE CAN OBVIOUSLY SEE THAT WE CAN HOLD THE SAME INDEX DATA IN LESS MEMORY SPACE BY COMPRESSING IT..

Permalink No Comments

« Previous entries