Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to order IP address

cubeguyAug 20 2010 — edited Aug 23 2010
Version 10g

For sample data
	CREATE TABLE IP_ADDRESS
(
  IP_ADDR          VARCHAR2(15)                 PRIMARY KEY,
  IP_STATUS        VARCHAR2(5)                  NOT NULL,
  EQUIPMENT_TYPE   VARCHAR2(20)                 NOT NULL,
  EQUIPMENT_ID     VARCHAR2(20)
  )


Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.154.195', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.154.210', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.154.22', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.154.226', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.154.23', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.154.24', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.154.25', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.154.26', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.154.27', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.154.29', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.154.48', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.154.56', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.154.61', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.154.64', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.154.73', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.155.108', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.155.109', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.155.110', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.155.16', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.155.34', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.155.39', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.155.55', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.155.68', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.155.71', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.155.88', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.158.133', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.171.113', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.182.224', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.187.249', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.187.250', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.187.251', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.187.252', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.187.253', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.187.254', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.191.141', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.191.195', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.191.246', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.191.250', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.10', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.100', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.101', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.102', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.103', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.104', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.105', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.106', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.107', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.108', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.109', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.11', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.110', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.111', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.112', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.113', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.114', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.115', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.116', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.117', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.118', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.119', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.12', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.120', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.121', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.122', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.123', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.124', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.125', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.126', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.127', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.128', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.129', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.13', 'S', 'DSLAM_FTEL', NULL);
Insert into IP_ADDRESS
   (IP_ADDR, IP_STATUS, EQUIPMENT_TYPE, EQUIPMENT_ID)
 Values
   ('172.20.193.130', 'S', 'DSLAM_FTEL', NULL);
and i fire 
SELECT IP_ADDR
	FROM Ip_Address
	WHERE Equipment_Type = 'DSLAM_FTEL'
	AND Ip_Status = 'S'
	ORDER BY Ip_Addr ASC;
when i fire the above query the ip_addr are not in order even i apply order by clause.

Could some help me please
This post has been answered by Frank Kulash on Aug 20 2010
Jump to Answer

Comments

gdarling - oracle
Hi,

I'm not sure if you're asking how you can execute 3 statements at once, or how to execute 3 statements in the same transaction.

To execute 3 at once, you'd need to wrap them in an anonymous block.

To execute 3 statements in a transaction, you'd use an OracleTransaction object to control the commit.

In your case though, you can't execute those 3 statements in a single transaction, as two of them are DDL so will force a commit.

Hope it helps,
Greg
926357
Thanks for the reply. I want to execute 3 statement at once in a sequential manner. I put all the statements inside a BEGIN and END like
BEGIN
Create temp table;
Select query;
Drop table;
END;
but it is throwing error that


Error report:
ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

( begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

Divya
gdarling - oracle
You can't issue ddl directly in a procedure, you'd need to use execute immediate for that:
310955
Sorry that didn't jump out at me initially.

I'm really not sure why you'd want to issue those three inside a block in the first place though. Where is the data going to "go" when you select it? You can't return it as a ref cursor because the table is already dropped by the time the data will be fetched from the cursor.

You may want to look into use Global Temporary Tables instead. http://docs.oracle.com/cd/E11882_01/server.112/e10595/tables003.htm#i1006400

Greg
926357
I am not using stored procedure, I am directly executing the query. I tried with Global temp table and execute immediate also, it is not working. This was working in sybase and now I need to change to oracle.

This is what I tried now and I got the error saying table/view does not exists.

BEGIN
EXECUTE IMMEDIATE 'Create GLOBAL TEMPORARY TABLE temp_table ON COMMIT DELETE ROWS AS
Select
a as x,
b as y,
c as a,
d as y
from
tablea
Where
z= 1
and q=''22-SEP-12''
group by
a';
SELECT * FROM im_temp_table d
EXECUTE IMMEDIATE 'drop table temp_table';
END;
SigCle
Dear qdarling,

Could you mind give an example on plsql procedure "To execute 3 at once, you'd need to wrap them in an anonymous block."

Best Regards,
LinXianHan
gdarling - oracle
SigCle ,
Here's an example that executes 3 statements;
begin insert into foo values(1); insert into foo values(2); insert into foo values(3); end;

923354,
The block doesn't compile because temp_table doesn't exist at the point you're trying to compile the anonymous block. I'd recommend re-reading the doc link and forum link provided to get a better understanding of how temp tables work, as it's simply different with Oracle. You don't create Oracle temporary tables on the fly; you create them ahead of time and then just use them. The data itself is already specific to a particular session; you don't create and drop the table each time.
Also, you can't just "select * from table" in plsql. The results have to GO SOMEWHERE. Usually you'd either open a cursor and process it in the block, or send out a ref cursor if you want to send the data to a client side app. The ref cursor data wouldn't actually be fetched until the block completes though, so you'd need to use ON COMMIT PRESERVE ROWS, which would also mean you'd need to clean up the data yourself (delete the data from the table when you're done with it).

Corrections/comments welcome.
Greg
926357
Thanks.
1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 20 2010
Added on Aug 20 2010
11 comments
2,887 views