SQL & PL/SQL :: Granting Permissions For Create Type / Procedure?
May 18, 2011
I have a question regarding how to grant create type and create procedure roles correctly.Currently if I do this:
create user vackar_temp identified by "123456789";
grant create type to vackar_temp ;
Then as vackar_temp:
create type temp_col as table of number;
I get the following error:
ORA-01031: insufficient privileges
I've also tried:
grant resource to vackar_temp ;
But that doesn't work either If I run this:
select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
select
null grantee,
[code]...
I can see that the user does have the create type role:
User, his roles and privileges
------------------------------
VACKAR_TEMP
CREATE TYPE
RESOURCE
CREATE CLUSTER
CREATE INDEXTYPE
[code]...
View 8 Replies
ADVERTISEMENT
Jul 13, 2012
Is there a way to grant rights to view a stored procedure without granting execute permissions?
View 2 Replies
View Related
Mar 31, 2004
ORA-06502...I have database on oracle 9i on Solaris 9. I create a generate procedure that create dynamic procedure through DBMS_SQL. On this database I got the ORA-06502 error. When I tried to run the same procedure on the same database on oracle 8i on NT this work fine.
View 3 Replies
View Related
Aug 18, 2010
I have created a role, when i try to grant privilege to that role, it give me insufficient privilege error. After granting privileges to this role, i have to grant this role to other. what type of privileges should i have.
Create Role cb_select;
Grant select on atable to cb_select; (Got error)
View 7 Replies
View Related
Apr 26, 2013
I have created the following procedure. Since I am using this first time I don't know how to execute this.
CREATE OR REPLACE PACKAGE GAFT_PROG_DIT.ram_package
IS
TYPE type_ots IS TABLE OF ORDER_TREND_SCORE%ROWTYPE INDEX BY PLS_INTEGER;
PROCEDURE InsertTrend( P_TYPE_OTS_REC IN type_ots );
END;
/
[Code]...
View 2 Replies
View Related
Jan 19, 2011
I have 3 users in one schema. One user is having all the tables, packages,triggers etc and this is the super user.
I want to access all the tables, packages etc without prefix the username in other two users.
View 15 Replies
View Related
Aug 28, 2012
I want grant object level privileges to some user so that he can view(select) any object that resides under another user.Not select any table option.
View 5 Replies
View Related
Jan 19, 2012
I need to create PROCEDURE to create user in oracle
CREATE OR REPLACE PROCEDURE "CREATE_USER_ORACLE8"
(
USER_ID in VARCHAR2,
PASSWORD in VARCHAR2,
ROLES in VARCHAR2,
nReturnCode OUT NUMBER
)
BEGIN
[code].......
Compilation errors for PROCEDURE NOG.CREATE_USER_ORACLE8
Error: PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:
; is with authid deterministic parallel_enable as
Line: 9
Text: BEGIN
i want that the customer execute PROCEDURE (user_id,password,PROCEDURE )
View 5 Replies
View Related
Jun 22, 2012
I have a problem with passing procedure name dynamically with record type i/p parameter..I'm not attaching any insert/create table statements, as I'm unsure of forming the sql statement dynamically..
CREATE OR REPLACE PACKAGE med_order_pkg AS
TYPE deid_med_order_typ IS RECORD(....)
L_deid_med_order_typ deid_med_order_typ;
PROCEDURE RULE_MASTER_PRC (P_IN_RULE IN deid_med_order_typ);
END;
[code]....
From the above, I need to execute a procedure with record type as i/p parameter..V_SQL should form the statement & execute another procedure which comes into the variable V_MSG_PROC .I'm having difficuly in forming the statement...(I did it by hard-coding the procedure with parameter in the next line which is commented out & it works...So how can I modify V_SQL in the above statement?
View 3 Replies
View Related
Jul 26, 2012
I'm not attaching any tables / data..etc...I just want to know how to pass the record type to a procedure (which are actually obtained from a table) -- see ** below where I'm getting an error..Need to pass the whole record type "l_shl_order_msg"
CREATE OR REPLACE PROCEDURE CM_BUILD_MSG_PRC (P_IN_BLD_MSG_CURSOR IN SYS_REFCURSOR,
P_OUT_BLD_MSG_CURSOR OUT SYS_REFCURSOR)
IS
l_shl_order_msg CRAE_INTERFACE.GLB_VAR_PKG.deid_SHELL_order_typ;
V_MSG_SHELL_NAME VARCHAR2(1000);
V_MESG_TEXT_SEGMENT VARCHAR2(1000);
V_TEXT VARCHAR2(1000);
V_MSG_TEXT VARCHAR2(4000);
V_MSG_FINAL_TEXT VARCHAR2(4000);
V_MSG_PROC VARCHAR2(1000);
V_SQL VARCHAR2(4000);
V_CNT NUMBER;
L_STATUS VARCHAR2(100);
L_REASON VARCHAR2(1000);
[code]...
I get an error saying that "wrong number or types of arguments in call to ||"..Not sure how to pass record type dynamically...
View 10 Replies
View Related
Sep 2, 2010
oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
I have created two new procedures. When I log in as ADMIN, I can execute these procedures.. but when i log in as WC_GUEST or WC_TECH, I cannot.. One of the procedure name is WCL_RPRTD_PARTS_REP_SEARCH, to which, I have given this: grant execute on WCL_RPRTD_PARTS_REP_SEARCH to WC_GUEST,WC_TECH;
Also, I have changed the ROLE of ADMIN, to the same role as these two users and tried.. it works fine.
View 5 Replies
View Related
Apr 8, 2004
I have two cursors like
cursor_A IS select * from table_a where condition_A;
cursor_B IS select * from table_a where condition_B;
record_A is a recorded of cursor_A%ROWTYPE
record_B is a recorded of cursor_B%ROWTYPE
I define a procedure like pro_A(record_in cursor_A%ROWTYPE) can I overload this procedure by defining pro_B(record_in cursor_B%ROWTYPE)?
If I can't, Can I call pro_A by passing record_B as the parameter to it?
View 3 Replies
View Related
May 9, 2012
I am fairly new to oracle, here's what I am doing.
Create or replace type csc_info as object( source_code varchar2(10),
Docno varchar2(10),
Key_value_1 varchar2(10),
Key_value_2 varchar2(10));
[Code]....
I need to test the procedure how do I pass the inputs for the type object csc_info?
View 2 Replies
View Related
Oct 11, 2012
using as template this table:
create table t1 (c1 number,c2 number);
CREATE OR REPLACE
TYPE REC IS RECORD (
R1 T1%ROWTYPE,
R2 NUMBER
);
I'm trying to create one RECORD type with all the columns from table T1 + one new field R2. But gives me an error.
The point to use T1%ROWTYPE and not to hardcode the columns from T1, is due to if we add a new column to T1, is created when is executed again RECORD definition and not to add the column.
View 3 Replies
View Related
Nov 11, 2012
I have existing table which needs to be copy to new table with object using the cursor. The exist table has a rank column which has some duplicate rank, which need to be remove and provide a series of numbers, like 1,2,3,4,5,...
create type UNIVERSITY as object (
U_RANK number(2),
U_SCHOOL varchar2(150),
U_COUNTRY varchar2(150),
U_SCORE number(3)
)
[code].....
DBMS_OUTPUT:
------------
Warnings: --->
W (1): Warning: execution completed with warning
<---
0 record(s) affected
[Executed: 11/11/2012 9:12:19 PM] [Execution: 31ms]
View 4 Replies
View Related
Apr 20, 2012
Can we create TABLE type object by using %ROWTYPE in SQL.
I am bale to create PL/SQL table type object .But i am unable to create SQL type
SQL> declare
2 type table_emp is table of scott.emp%rowtype index by binary_integer;
3 employees table_emp;
4 begin
5 select * bulk collect into employees from scott.emp;
6 end;
7 /
PL/SQL procedure successfully completed
SQL> create or replace type table_emp is table of scott.emp%rowtype index by binary_integer;
2 /
Warning: Type created with compilation errors
SQL> show errors
Errors for TYPE DBO.TABLE_EMP:
LINE/COL ERROR
-------- ----------------------------------------------------------
1/19 PLS-00355: use of pl/sql table not allowed in this context
0/0 PL/SQL: Compilation unit analysis terminated
SQL>
How can I create global table type object with %rowtype
View 3 Replies
View Related
Oct 7, 2010
I am working on a webservice call from ORacle.I have a button on my form application called verify.Wheni click on verify button , a pl.sql procedure should be invoked and that procedure will call .net webserive to validate the address , the result from the webserivce will be in xml.I have to extract the xml into some variables and return these varibles to Forms application..I am plannig to use pl/sql table to store the result from web service call.
here are the output values:
Customer_Id varchar2(20),
ErrorCode varchar2(30),
ErrorDesc varchar2(3000),
Fcount number,
FErrorCode -- this is array,-- if fcount >1 then these values will be repeted.
FErrorDesc -- this is array,
FStatusCode -- this is array,
FStatusDesc -- this is array,
Street varchar2(3000),
Street2 varchar2(3000),
Suite varchar2(20),
City varchar2(20),
State varchar2(2),
Zip_Code varchar2(10)
create record type and pl/sql table for these.I want to return pl/sql table as a out parameter to the form.
View 1 Replies
View Related
Oct 12, 2010
oracle 10G who knows why Oracle SQL doesn't support %TYPE when create a table. But it supports it when we use it in PL/SQL.
SQL> create table wg_1
(account_category JNL_WORK_LIST.ACCOUNT_CATEGORY%TYPE)
;
2 3 (account_category JNL_WORK_LIST.ACCOUNT_CATEGORY%TYPE)
*
ERROR at line 2: ORA-00911: invalid character
SQL> desc JNL_WORK_LIST
Name Null? Type
----------------------------------------- -------- ----------------------------
ACCOUNT_NO NOT NULL NUMBER(10)
BILL_REF_NO NOT NULL NUMBER(10)
BILL_REF_RESETS NOT NULL NUMBER(3)
JNL_EARNED_THRU_DT DATE
[code]...
PL/SQL procedure successfully completed.
View 3 Replies
View Related
Jul 25, 2012
I am trying to create a function which would return a nested table with 3 columns of a table as a type.
my query is like
select col1,col2,col3 from table_1;
View 4 Replies
View Related
Mar 29, 2010
I am trying to execute the procedure from toad, where the type is like an object.How can I do that?
CREATE OR REPLACE TYPE list_t AS TABLE OF list_objtype;
CREATE OR REPLACE TYPE list_objtype AS OBJECT
( Emp_ID NUMBER(5),
EMp_Name VARCHAR2(32 BYTE)
);
In the following procedure I am calling list_t, but how would i pass the values to it?
PROCEDURE modify_settings
( p_List in list_t,
p_info out varchar2)
DECLARE
p_List TABLE;
[code]...
View 1 Replies
View Related
Jul 6, 2010
user@tank> create table filetable(id number(6), file_content BLOB);
0 rows affected (0.02 seconds)
vlanke@tank_db> desc filetable;
ID NULL NUMBER(6,0)
FILE_CONTENT NULL
I am new to Database field.
- I created a table with 2 columns id (datatype as number) and file_content datatype as BLOB.
- My question is under desc filetable; query why does it not show me a data type as BLOB.
- create table query does not return any error to me. How do I verify that FILE_CONTENT column has datatype as blob.
View 1 Replies
View Related
Jun 25, 2012
DB version:11g
can we create a procedure inside a procedure...if not is there any alternative??
in db2 it is allowed so do oracle support this????
View 5 Replies
View Related
Apr 20, 2012
wanted to grant dbms_scheduler permission to the system user in oracle 11.2.0.3,but it is showing insufficient privileges, but my System has Sysdba rights.
SQL> SELECT * FROM v$pwfile_users WHERE sysdba='TRUE';
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
[code]....
Revoke succeeded.
SQL> conn system
Enter password:
Connected.
[code]...
while i ran this query
"
SQL> select * from session_privs;"
for system user i have 202 rows of different privileges,but the same query for sysdba has 208 rows...
View 6 Replies
View Related
Nov 15, 2010
un-documented parameter _trace_files_public / I want to set this to true so my app team can review trace files. better way to proceed to open read permissions for non oracle users.
View 2 Replies
View Related
Jul 11, 2012
I have two user a and b. I have to Grant select update insert Delete Permission to all tables of user b To User a. how can it possible?
View 5 Replies
View Related
Oct 15, 2012
Suppose I using "scott" user, I need to give administrative permissions to scott. How can I give??
View 6 Replies
View Related
May 13, 2013
I have created and role in my database and assign privileges as per following query.
Select 'Grant select on'||' user.'||object_name||' '||'to MyRole ;' from all_objects
where object_type in ('TABLE','VIEW')
and owner='username';
After granting role to new user everything work fine.I want to know a way to sync role with any newly created object.
Should I create a job that may execute above sql store results in a file and then execute to ensure all privileges are up to date for role or there any other optimal way exist ?
View 4 Replies
View Related
Jul 10, 2013
I've noticed that the backup files produced by RMAN (Database is 11.2.0.2.0 Enterprise Edition) )on a RHEL5.5. server which is running ASM have group permissions assigned to ASMADMIN (so the file permissions are assigned to ORACLE:ASMADMIN) as opposed to OINSTALL or any other group.
While this is not an issue (it's not currently impacting us in any way) I'm curious as to how and why this happens. I suspect it is related to the fact that the oracle executable has these permissions also but I can't find any material to confirm my suspicion.
View 2 Replies
View Related
Jun 6, 2012
i am using datapump to import database from 10g to 11g . all the tables and users everything got transferred but some grant permissions (create session) on users ,not importing to 11g. but same process imports grant if if do datapump to another 10g db .
do i need to import grants separately for 11g .
View 18 Replies
View Related
Oct 7, 2010
I am creating the following dbms_sql procedure...
CREATE or replace PROCEDURE table_demo
(tabname IN varchar2)
IS
cursor_name INTEGER;
rows_processed INTEGER;
[code]...
There are no compilation errors.I call this code from the following anonymous block...
DECLARE
X CHAR:='T';
BEGIN
TABLE_DEMO(X);
END;
This also compiles successfully and without any errors. It runs properly as wellHowever when I run 'select * from T'. Then system throws up the error of table or view does not exist.
View 12 Replies
View Related