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

View A Stored Procedure Without Granting Execute Permissions?

Jul 13, 2012

Is there a way to grant rights to view a stored procedure without granting execute permissions?

View 2 Replies View Related

Ora-06502 - Create Generate Procedure That Create Dynamic Procedure Through DBMS_SQL

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

SQL & PL/SQL :: Granting Privilege To A Role

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

SQL & PL/SQL :: How To Execute Type Variable Procedure

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

SQL & PL/SQL :: Granting Permission On All The Tables To Another User?

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

PL/SQL :: Granting Object Level Privileges

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

Create Procedure To Create User In Oracle

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

SQL & PL/SQL :: Dynamic Procedure Name With Record Type I/p Parameter?

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

SQL & PL/SQL :: Executing Dynamic Procedure With Record Type

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

Security :: New Procedures Not Working Even After Granting EXECUTE

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

Overload PL / SQL Procedure Based On Different Record Type With Same Structure

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

SQL & PL/SQL :: How To Pass Type Object As A Parameter To Stored Procedure

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

SQL & PL/SQL :: Create Record Type With Rowtype And One Field

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

SQL & PL/SQL :: Create Object Then Use As Data Type Of New Table

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

SQL & PL/SQL :: Can Create Table Type Object By Using %ROWTYPE

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

WebService - How To Create Record Type And PL/SQL Table

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

SQL Create Table Doesn't Support %TYPE / But PL/SQL Does

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

PL/SQL :: Create Function To Return Table Type

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

Client Tools :: Execute A Procedure With Parameter Of Object Type?

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

Cannot Create BLOB Data Type For A Column In Tavle

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

PL/SQL :: Create A Procedure Inside A Procedure

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

Grant Permissions On Oracle 11.2.0.3?

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

Opening Permissions To Trace Files?

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

SQL & PL/SQL :: Grant Table Permissions To Another User

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

PL/SQL :: How To Give A User Administrative Permissions

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

Security Role And Auto Permissions

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

RMAN :: Output File Permissions With ASM / Grid Infrastructure

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

Export/Import/SQL Loader :: Grant Permissions Not Importing From 10g To 11g?

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

SQL & PL/SQL :: Create DBMS-SQL Procedure?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved