Home » SQL & PL/SQL » SQL & PL/SQL » CREATE PROCEDURE change in 10G.
CREATE PROCEDURE change in 10G. [message #200699] Tue, 31 October 2006 23:54 Go to next message
kkalyankrishna
Messages: 1
Registered: October 2006
Junior Member
IS there any change in CREATE PROCEDUR and CREATE ANY PROCEDURE in ORCL10G.

In ORCL10G Is it needed to have CREATE PROCEDURE to create any procedure in our own schema?
(9i it is working fine without CREATE PROCEDURE)

Steps for 10G and 9I

10G :

SQL> set role none;

Role set

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
ALTER SESSION
UNLIMITED TABLESPACE
CREATE ANY TABLE
ALTER ANY TABLE
DROP ANY TABLE
INSERT ANY TABLE
CREATE ANY INDEX
DROP ANY INDEX
CREATE ANY SYNONYM
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
CREATE ANY SEQUENCE
DROP ANY SEQUENCE
SELECT ANY SEQUENCE
CREATE ANY PROCEDURE
EXECUTE ANY PROCEDURE
CREATE ANY TRIGGER

17 rows selected

SQL>
SQL> create or replace procedure TestProcT Is
2 Begin
3
4 NULL;
5
6 End;
7 /

create or replace procedure TestProcT Is
Begin

NULL;

End;

ORA-01031: insufficient privileges

9i:

SQL> set role none;

Role set

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
ALTER SESSION
UNLIMITED TABLESPACE
CREATE ANY TABLE
ALTER ANY TABLE
DROP ANY TABLE
INSERT ANY TABLE
CREATE ANY INDEX
DROP ANY INDEX
CREATE ANY SYNONYM
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
CREATE ANY SEQUENCE
DROP ANY SEQUENCE
SELECT ANY SEQUENCE
CREATE ANY PROCEDURE
EXECUTE ANY PROCEDURE
CREATE ANY TRIGGER

17 rows selected

SQL>
SQL> create or replace procedure TestProcT Is
2 Begin
3
4 NULL;
5
6 End;
7 /

Procedure created

Please also note that i am creating a Procedure from a Package, so all the ROLES will be deactivated. (SET ROLES NONE).

Thanks in Advance,
Kalyan.
Re: CREATE PROCEDURE change in 10G. [message #200768 is a reply to message #200699] Wed, 01 November 2006 04:42 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
There is a difference in CONNECT role in 10g.
Grant those privileges seperately.
SCOTT >grant create procedure to foobar;

Grant succeeded.

SCOTT >connect foobar/foobar
Connected.
FOOBAR >set role none;

Role set.

FOOBAR >select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE PROCEDURE

FOOBAR >create or replace procedure boo as
  2  begin
  3  null;
  4  end;
  5  /

Procedure created.
Previous Topic: Reg bind variable
Next Topic: SQL output
Goto Forum:
  


Current Time: Thu Dec 08 20:26:55 CST 2016

Total time taken to generate the page: 0.11262 seconds