SQL & PL/SQL :: Whether User2 Can Update Emp Through Package Pack
Mar 22, 2010
Consider following scenario
Schema Update Privilege on emp directly
User1 Yes
User2 No
I have created a package named pack in user1 schema which contains one procedure which updates table emp. ow i executed the following in schema user1
Grant execute on pack to user2;
I connected as user2 and executed
Create Synonym pack for user1.pack;
Note that user2 doesn't have update privileges on table emp
Tell me whether user2 can update emp through package pack
View 18 Replies
ADVERTISEMENT
Feb 10, 2011
I have an Index in User1 schema. can i grant access on this Index to User2. if so, what privs i can give..?
for proc's we will give "Grant Execute on proce1 to User2". Like this how on Indexes..
View 3 Replies
View Related
Jun 15, 2013
We have 2 node oracle 11.2.0.3 RAC database and we enabled stats-pack on the database(since its STD edition). However we found that stats pack report coming from 1st node properly but second node it show as below.
Instance DB Name Snap Id Snap Started Level Comment------------ ------------ --------- ----------------- ----- --------------------INST2 PRDDB 2139 15 Jun 2013 04:00 5 2140 15 Jun 2013 04:00 5 2151 15 Jun 2013 04:00 5 2152 15 Jun 2013 04:00 5 2153 15 Jun 2013 04:00 5 2154 15 Jun 2013 04:00 5 2155 15 Jun 2013 04:00 5 2156 15 Jun 2013 05:00 5 2157 15 Jun 2013 05:00 5 2158 15 Jun 2013 05:00
[code][....
View 3 Replies
View Related
Jan 24, 2012
I have created a user "user1" who has dba role.i wanted to create a another user "user2" with same privileges which user1 has. so i granted dba role to user2 but i dont want user2 to have "drop" privilege on any objects except his own objects.
View 1 Replies
View Related
Aug 25, 2011
I was just asked by one of the sysadmins what the typical size of an oracle database install plus service pack be?
View 1 Replies
View Related
Jul 6, 2010
I've experienced an error while installing the Application Management Pack 3 on Linux Machine (OS: Oracle Enterprise Linux 5.3 32-bit) with EM Grid Control 10g Release 5. I want to install the Grid Control Plug-in Oracle E-Business Suite Management Pack 3 to manage our EBS Oracle Apps R12, but it was failed due to error. Below was the logs for error:
Log: /u01/app/oraInventory/logs/installActions2010-07-06_03-00-11PM.log
RepManager was not able to run successfully.
/applications/em/em/WEB-INF/lib/myjar.jar:/u01/app/oracle/OracleHomes/oms10g/sysman/jlib/emCfg.jar:/u01/a
pp/oracle/OracleHomes/oms10g/jlib/ojmisc.jar:/u01/app/oracle/OracleHomes/oms10g/sysman/jlib/emUpgrade.jar
:/u01/app/oracle/OracleHomes/oms10g/sysman/jlib/emagentSDK.jar oracle.apps.oms_extn.util.EBSOUIUtil RepMa
[code].....
View 1 Replies
View Related
Jun 20, 2013
I have 2 users in my oracle DB. They both have very different privileges and they both have too many different privileges. Now I want to grant user 1 the same privilege that user2 has while user1 keeps his existing privilege. How can this be done without manually comparing their difference and manually grant user 1 each privilege that he doesn't have? Or second option, can I override user 1's privilege with user 2's privileges?
View 2 Replies
View Related
May 28, 2013
I need to find out in DB Package where this Package is installed (in which schema). The problem is this DB Package can be installed in various schemas. This means that I can't use select user from dual or system environment SYS_CONTEXT('USERENV', 'OS_USER').
What I would need is something like $$PLSQL_UNIT
View 8 Replies
View Related
Jan 15, 2012
I have a package with several procedures which raise and catch an error if a foreign key constraint has been violated. I put the the following code in my package body:
e_ouder_niet_gevonden EXCEPTION;
PRAGMA EXCEPTION_INIT(e_ouder_niet_gevonden,-2291);
Now all the procedures inside the package which catch this exception in the EXCEPTION block work fine. I would like to be able to use that exception outside of my package as well though, how would I do this?
View 4 Replies
View Related
May 14, 2010
I have a base table (Table A) block with multiple records displayed. I need to track audits to this underlying table in the following way:
If user updates a field in the block I want the pre-changed record's audit fields to be set and I need to create a copy of the record with the changed values. Basically any changes will result in the record being logically deleted, and a copy record created with the newly changed values.
Tried to implement in the block's pre-update trigger which will call a package to directly update Table A then Insert into Table A, then requery the block. Is there a clean and efficient way to do this?
View 4 Replies
View Related
Sep 7, 2010
I have to update 20 and 60 million records of a table. The update statement are
1> 20 million recs
update mycustomer set update_time=add_months(sysdate,240) where seq_num = 1;
commit;
2> 60 million recs
update mycustomer set update_time=sysdate-seq_num where seq_num <> 1;
commit;
Q1> Is there any way to improve performance
Q2> Will parallel dml improve performance
Q2> Would a pl/sql cursor make any difference in speed.
View 1 Replies
View Related
Aug 17, 2010
This is my query:
UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c
[Code]...
The problem I am having is that it is updating all rows even when it is pulling back a null value for b.sak_request. I've tried adding b.sak_request is not null to the select statement like this:
UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c
WHERE b.nam_eds_tracking_id = c.id_dir_track_eds
[Code]...
but it doesn't seem to make a difference. The reason I need to do this is that the difference between where it matches with a valid (non-null) value is 396 rows vs. 12,484 rows which is too time consuming to run on my page.
View 9 Replies
View Related
May 29, 2012
i want to create a trigger that will update a table when there is an insert or update.i can't across this error that i don't even know what it means "table %s.%s is mutating, trigger/function may not see it".
*Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.
CREATE OR REPLACE TRIGGER set_date_end
BEFORE INSERT OR UPDATE OF issued ON shares_amount
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO shares_amount(date_end) VALUES(SYSDATE);
END set_date_end;
/
View 3 Replies
View Related
Mar 6, 2013
create table test1
( ID NUMBER(11)
,MEMBER_NMBR NUMBER(10)
,CODE_NMBR NUMBER(7)
,ROW_EFCTV_DT DATE
,ROW_TRMNTN_DT DATE
[code]....
insert into test1 values (11007,7462,32,'30/sep/2012','31/dec/9999',3,'25/sep/1998','AUTUMN',1,0,344);
insert into test1 values (11007,7462,32,'30/oct/2012','31/dec/9999',3,'25/sep/1998','AUTUMN',1,0,344);
IDMEMBER_NMBRCODE_NMBRROW_EFCTV_DTROW_TRMNTN_DTFLAG_NMBRBRTH_DTNAMECLAIM_CDAMT1AMT2
1100774623209/30/2012 00:0012/31/9999 00:00309/25/1998 00:00AUTUMN10344
1100774623210/30/2012 00:0012/31/9999 00:00309/25/1998 00:00AUTUMN10344
I have to update the row_trmntn_dt of first row to row_efctv_dt of 2nd row which is 30th Oct 2012 - 1 day i.e. 29th Oct 2012
View 10 Replies
View Related
May 30, 2013
i am reading the columns value from different table but i want to update it with single update statement. such as how to update multiple columns (50 columns) of table with single update statement .. is there any sql statement available i know it how to do with pl/sql.
View 5 Replies
View Related
Mar 21, 2011
I have one doubt about update command in sql. How to update the multiple rows with different values using update statment.
Eg:-
SQL> set linesize 500;
SQL> set pagesize 500;
SQL> select * from emp;
SQL> select empno,ename,sal from emp;
SQL> select empno,ename,sal from emp;
EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000
7698 BLAKE 2850
7782 CLARK 2450
7566 JONES 2975
7654 MARTIN 1250
[Code]....
The above table contains 14 records. Now i would like to update the salary column with different values like
EMPNO SAL
===========
7839 18000
7698 20000
7782 5000
...
...
...
7934 25000
How to update above values with single update query.
View 11 Replies
View Related
Jan 25, 2012
After many tests I can't make work and update of the same table inside the same table.
Trying to avoid Mutating Table Error now I have
ORA-00036: maximum number of recursive SQL levels (50) exceeded
Sample Data :
create table test_compound (USERID VARCHAR2(10),APP VARCHAR2(15),LAST_UPDATED_ON TIMESTAMP);
insert into test_compound values ('user1','1',systimestamp);
insert into test_compound values ('user2','2',systimestamp-4);
insert into test_compound values ('user3','3',systimestamp-6);
CREATE OR REPLACE TRIGGER trigger_test
FOR UPDATE ON test_compound
COMPOUND TRIGGER
TYPE t_tab IS TABLE OF VARCHAR2(50);
l_tab t_tab := t_tab();
[code].......
When I execute :
update test_compound
set last_updated_on=systimestamp
where userid='user1' and app='1';
The trigger should update the first row and all the data from test_compound table where userid='user1'. Maybe the problem is that updating the same table inside the trigger is firing in a recursive way the trigger.
View 13 Replies
View Related
Jul 19, 2011
I have a column "empno" in EMP table and "deptno" in DEPT table . I want to update both the columns with single UPDATE statement. With out a creation of stored procedure or view(updating it through view).
View 4 Replies
View Related
Jan 12, 2011
Why Package has to be created as two parts
1) Package spec
2) Package Body ?
Quote:PLS-00304: cannot compile body of '<PACKAGE_NAME>' without its specification
I learned it as a Thumb of rule! But IF there is any rule for it give that URL/link for that.
View 25 Replies
View Related
Mar 31, 2011
is it possible that two different package can contain same procedure?
View 1 Replies
View Related
Oct 20, 2010
Trying to accomplish:
I am trying to calculate pay with a package which consists of four functions for calculations and a procedure that calls the functions to calculate net pay.
DML DDL and package
I the DML and DDL and the package as an attachment.
Problem
Errors below
32/9 PLS-00103: Encountered the symbol "E" when expecting one of the following:
, ; for group having intersect minus order start union where connect The symbol "having" was substituted for "E" to continue.
32/54 PLS-00103: Encountered the symbol ")" when expecting one of the following:
LINE/COL ERROR
-------- ----------------------------------------------------------------
. ( * @ % & - + ; / at for mod remainder rem <an exponent (**)> and or group having intersect minus order start union where connect || multiset
33/9 PLS-00103: Encountered the symbol "INTO" when expecting one of the following:
. ( ) , * @ % & = - + < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || member submultiset
View 39 Replies
View Related
Jul 17, 2012
In package specification, there 3 procedures But in package body, there are 2 procedures...will this execute?
View 14 Replies
View Related
Mar 8, 2012
There are 4 packages got invalid 2 days back. when I analyze the database I came to know that there are 5 tables got truncated and 2 tables got altered during the issue period through the code. Those truncated tables have the indirect relationship with these 4 packages.but there is no any relation between these packages and altered table.
Also during that time I got the below error in my alert log.I am sure the cause this error is the invalid packages.
ORA-00600: internal error code, arguments: [kkxprpic8], [], [], [], [], [], [], []
I know if any alteration happens in a table, the refrence package will be getting as invalid. Apart from this, is there anyother cause to bring the package into invalid status? How to proceed further to find the root cause of thses invalid package?
View 6 Replies
View Related
Nov 22, 2011
We are working on a POC to build Web services from Oracle PL/SQL packages. For this we need to create a PL-SQL package having a stored procedure which will accept one input parameter and sends out data sets with multiple rows.
The type of the output parameter should be compatible with JDBC so as to publish the package as a web service. We tried using an Array and Ref cursor in PL/SQL but it didn't work with JDBC.
what type of PL/SQL output we should use here?
Our Environment : Oracle 10.2.0.3.0, Jdeveloper (to convert PL/SQL package into to Web Service) and Web Logic 11G server to deploy the services.
View 4 Replies
View Related
Apr 27, 2007
I ran the dbms_rectifier_diff.differences package successfully just to know the differences after it i droppped the all tables from the schema but now I have some funny table names exist in my schema like BIN$SHabcAN0slsAAdjhf0/ABQCiA==$0. I want to remove it but dont know how, when I run the following command on sql prompt i get error message says 'sql command not properly ended'.
SQL>drop table BIN$SHabcAN0slsAAdjhf0/ABQCiA==$0;
View 1 Replies
View Related
Apr 20, 2013
I have written a package including 13 procedures to insert data into a temp table but i am getting duplicate rows in dat table how to track from which procedure i am getting duplicate rows.
step 1 : procedures are inserting data using some joins into a temp table.
step 2: from temp table data will get stored into a target table where if the data already exist it will update the target table else will insert the data.(its a procedure where i used merge).
as i have duplicate data on temp table i am not able to update data on target table.
View 6 Replies
View Related
Jun 11, 2012
I have created a PL/SQL package and complied it and I got a meaasge with no errors.But I dont know how do I run the package to check whether the data has been populated in tables or not.I want to know do I need to write any code for execution to see the output.If I need to write how can I do it in TOAD.
View 6 Replies
View Related
Apr 27, 2010
How to use DBMS_SQL package. I tried with the following procedure.
CREATE OR replace PROCEDURE Crt_tab_inst(tab_name VARCHAR2,
col1_name VARCHAR2,
col1_value VARCHAR2)
IS
cur BINARY_INTEGER := dbms_sql.open_cursor;
fdbk BINARY_INTEGER;
[code]........
But when Iam executing the procedure, it is throwing the below error. This is the error Iam getting:
SQL> EXEC crt_tab_inst('MYTAB','MYCOL','NAME1');
BEGIN crt_tab_inst('MYTAB','MYCOL','NAME1'); END;
*
ERROR at line 1:
ORA-00984: column not allowed here
ORA-06512: at "SYS.DBMS_SYS_SQL", line 909
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "SCOTT.CRT_TAB_INST", line 21
ORA-06512: at line 1
View 10 Replies
View Related
Feb 14, 2013
I am unable to compile my package because of a lock being held on it . Let me provide the details for better clarity .
select * from dba_ddl_locks where session_id=111 and owner='RAHUL';
session_id owner name type mode_held mode_requested
111 RAHUL RAHUL_PKG BODY NULL None
111 RAHUL RAHUL_PKG Table/Procedure/Type NULL NoneThe session details of the session id 111 is as below
select sid,serial#,user#,username,command,status,process,sql_exec_start from v$session where sid=122
sid serial# user# username command status process SQL_EXEC_START
111 3558 222 RAHUL 47 KILLED 4420 13-DEC-12 02.00.00 AM
The strange this is that we have tried killing this session and the status above even shows ' 'KILLED', stilll the dba_ddl_locks is showing the lock details on the package RAHUL_PKG and we are unable to compile the package (the locking session is running since 13-Dec-2012 ) and we are unable to kill that . Also , is it like if i have executed a package that is referencing some other package then we wont be able to compile the referenced package until and unless the dependent package has completed its execution ??
View 6 Replies
View Related
Aug 30, 2007
I could execute a package for eg if i had a package with procedures related to statistics and i run them each night, could i just do an exec on the package and it would run all those procedures??
Its not possible but i could call each procedure from ONE procedure
View 2 Replies
View Related
