| Procedures [message #488] |
Fri, 15 February 2002 00:26  |
Tina
Messages: 38
Registered: November 2001
|
Member |
|
|
Hello there,
I am fairly new to Oracle, but have good experience with SQL Server. I'm having trouble putting a few simply commands into a Procedure. Please can you provide me with the best way to put a create table, drop table if exists, into a Procedure, using the following statements:
DROP TABLE ABC.CURR_BAL; --Required if exist command?
CREATE TABLE ABC.CURR_BAL
AS
(select distinct a.ACCOUNT_NUMBER,
b.BAL,
b.BRANCH
from ABC.OPENACC a,
ABC.NEWINV b
where a.ACCOUNT_NUMBER = b.ACCOUNT_NUMBER);
Also, the ABC.CURR_BAL table is not particularly huge, would you recommend that I use TRUNCATE TABLE instead of DROP TABLE.
As I am creating a table, I need to grant select permissions to a couple of users. Can I just use the following after the CREATE TABLE command?
GRANT SELECT ON ABC.CURR_BAL TO BLACKB, BLOGGSF;
Thank you very much for your help.
|
|
|
|
| Re: Procedures [message #489 is a reply to message #488] |
Fri, 15 February 2002 01:28  |
pratap kumar tripathy
Messages: 660
Registered: January 2002
|
Senior Member |
|
|
Hi,
you can not create procedure from these commands because thes are no DML command . u can create sql file for it.
yes it is better to use truncate table.
cheers
pratap
|
|
|
|
| Re: Procedures [message #533 is a reply to message #488] |
Mon, 18 February 2002 17:59 |
seng
Messages: 191
Registered: February 2002
|
Senior Member |
|
|
PL/SQL can't use DDL(Data Definition Language). (as pratap kumar tripathy mention)
Below is defference between DELETE,TRUNCATE and DROP TABLE.
1.Delete
- suitable for remove selected or whole record from small table. slower for huge table.
- can rollback before commited becuase redo log is generated for DELETE.
- No space is freeout. Free space is done by other Oracle Process.
- No effect on schema structure e.g trigger,permission,constraints and other
2. Truncate
- suitable for remove whole record only from huge table. faster for huge table.
- can't rollback before commited becuase no redo log is generated for TRUCATE.
- Certain free space is free.
- No effect on schema structure e.g trigger,permission,constraints, storage clause and other
3. Drop table
- suitable for remove whole record and structure of table. Should be faster than delete ..
- can't rollback before commited becuase no redo log is generated for DROP TABLE.
- all space is free.
- Effect on schema structure e.g trigger,permission,constraints, storage clause and other. This should be done by DBA(consistent in structure).
Two method to control permission.
1. Using grand. Similar to you GRAND command
GRANT SELECT,INSERT,DELETE ON ABC.CURR_BAL TO BLACKB, BLOGGSF ;
2. Using roles. (more easy to manage)
- create role first
CREATE RULE <rule name> IDENTIFIED BY <password>;
- grand permission to
GRANT SELECT,INSERT,DELETE ON ABC.CURR_BAL TO <rule>;
- grand rule ro users
GRANT <rule> TO <user>;
Note: DROP TABLE will revoke the permission which is granted to user. and you need to regrand again.
Hope this will help you. Thanks
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
