PLS-00905 with remote stored procedure

Oracle 7.3.3 - 7.3.4
I was working on the problem when user called remote stored procedure (user - Oracle 7.3.3, procedure -- Oracle 7.3.4) using db_link. The code is like the next:
v_emply NUMBER(5) := 11111;
v_var1 number(2) := 111
v_sqlcode number(8);
v_sqlerrm varchar2(256);


The return error code was:PLS-00905: object is invalid;
DB_LINK connected to user VIEWUSER. This user did not have direct EXECUTE privilege on the called procedure. EXECUTE privilege was given thru the role. I gave direct EXECUTE privilege. It did not help. So I gave direct execute privilege to all procedures that were called inside of CHECK_MSG. It also did not help -- the same error code. So I recompiled all of those stored procs. The same result. Then I gave direct privilege on all tables that are accessed thru the stored procedure and after that procedure started to work. I can understand everything except of the last step. Does anybody can explain to me why I should perform that last step

Posted On: Friday 16th of November 2012 02:08:45 AM Total Views:  289
View Complete with Replies

Related Messages:

question on trigger with FK constraint on delete cascade ??   (94 Views)
Hi Everyone. I have a table 'bbb' with a FOREIGN KEY constraint with on delete CASCADE (please see code below); then I have to create a trigger on table 'bbb'; whenever a row is deleted from bbb, insert the deleted row to bbb_del; The problem I have now is when a row is deleted from table aaa, the corresponding row in bbb is deleted but the trigger does not run; any input I tried to put the trigger on table aaa, and just insert the rows into bbb_del from table bbb with the criteria; but getting a "ORA-04091 : mutating, trigger/function may not see it" error. Thx ================================================= create table aaa ( col1 number, aaa_col2 number) / alter table aaa add constraint aaa_PK primary key ( col1) / create table bbb ( col1 number, bbb_col2 number) / alter table bbb add constraint bbb_aaa_fk foreign key (col1) references aaa on delete CASCADE / create table bbb_del ( col1 number, bbb_col2 number) / ============================================= create or replace trigger bbb_AFTER_DELETE after DELETE on bbb FOR EACH ROW declare v_counter number; begin INSERT INTO bbb_del values ( ld.col1, ld.bbb_col2 ); end; / ==============================================
Problems with snapshot replication   (74 Views)
I have a table DA_TEST_TABLE_NOPK (this table is without primary keys), and I want to replicate this table into another db using snapshot replication, so i create logs on master side: CREATE MATERIALIZED VIEW LOG ON DA_TEST_TABLE_NOPK TABLESPACE DA_LOGS NOLOGGING WITH ROWID EXCLUDING NEW VALUES Then I create a table DA_TEST_TABLE_NOPK on the replicated side(with the same structure as in master side), and use matview with prebuilt option. But I cant create matview(because of error ORA-12014: table 'DA_TEST_TABLE_NOPK' does not contain a primary key constraint) CREATE MATERIALIZED VIEW DA_TEST_TABLE_NOPK ON PREBUILT TABLE REFRESH FAST ON DEMAND AS SELECT * FROM DA_TEST_TABLE_NOPK@master_side; (master_side -db link to master side ) When I try to create matview 'with rowid', I recieved another error (ORA-12058: materialized view cannot use prebuilt table). So what can you suggest to solve this problem (replicate table without primary key with prebuilt table). Thank you for your's attention
Problems with Oracle on a IBM Storage area network (SAN)   (89 Views)
Hi I am trying to find out any information about Oracle database's on IBM's Storage area network (SAN). Oracle Metalink has very little information on the subject. My Story !! A month ago i migrated our Data warehouse from locally attached disks to the SAN, the disks are all RAW devices. The migration went well and inital tests looked good. The tests comprised of some sql query and building of BITMAP indexes. The query times were the same but there was a slight improvment on the index builds. looked good..... Until our data warehouse team started to load data into the warehouse. Jobs that took 10 minutes before migrating to the SAN are now taking 30 minutes. So it looks like Inserts and updates are taking longer. why I have looked at the obvious area's like stats and reorging the tables but nothing has helped. Has anyone else out there had any experience of SAN's and oracle running slow on data loads. I know this is all a bit general but any info would be good.
Problems with MTS and Oracle   (77 Views)
Hi all, who know problems with MTS (Microsoft Transaction Server) and Oracle, specialy with Update statements
Problems with french caracters   (72 Views)
problems with db_writer_processes parameter   (321 Views)
Hallo, my DB is running with two CPUs. For more efective writing data to RAID 5 discs I try to start 4 db_writer_processes. I can start 2 but not 4 of them. Is there any rule I do not now Please help me! Christian Oracle9i Release - Production PL/SQL Release - Production CORE Production TNS for 32-bit Windows: Version - Production NLSRTL Version - Production
problems with 10g R2 install on Linux   (156 Views)
I've installed 10g Database R2 (and de/re-installed it) on my RHEL4 Linux system several times now, and I have the same errors come up each time that I had with a fresh install. For example, when I try to start the EM console: Code: $ $ORACLE_HOME/bin/emctl start dbconsole OC4J Configuration issue. /usr/local/oracle/product/10.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_[hostnme]_[sid] not found. When I try to configure the EM agent: Code: $ $ORACLE_HOME/bin/emctl config agent EM Configuration issue. /usr/local/oracle/product/10.2.0/db_1/[hostnme]_[sid] not found. When I try to recreate the EM repository: Code: $ $ORACLE_HOME/bin/emca -repos recreate ... Jul 9, 2007 4:02:59 PM oracle.sysman.emcp.EMReposConfig invoke SEVERE: Error dropping the repository Jul 9, 2007 4:02:59 PM oracle.sysman.emcp.EMReposConfig invoke ... When I try to configure the dbcontrol web port (or any other port): Code: $ $ORACLE_HOME/bin/emca -reconfig ports -DBCONTROL_HTTP_PORT 1820 ... Jul 9, 2007 3:57:23 PM oracle.sysman.emcp.EMConfig perform SEVERE: Database Control is not configured for this database. ... And another: Code: $ $ORACLE_HOME/bin/emca -config dbcontrol db -repos recreate STARTED EMCA at Jul 9, 2007 4:04:23 PM EM Configuration Assistant, Version Production Copyright (c) 2003, 2005, Oracle. rights reserved. Enter the following information: Database SID: oracledb Listener port number: 1521 Password for SYS user: Password for DBSNMP user: Invalid username/password. I've found and tried to follow steps to alleviate each of these errors, but nothing works. There seems to be something essential wrong, either with the version (I verified the sum of the archive before installing) or with my system with regard to Oracle.
problem with size during database creation..   (145 Views)
I guess by default, oracle is creating the database using DBCA method with size 2 GB. If I want the database size to be 300 GB, what should I do
problem with sequence generation   (206 Views)
HI, I have a sequence DBS_REQ_ID as below create sequence DBS_REQ_ID minvalue 1 maxvalue 99999999 start with 1562448 increment by 1 cache 10 cycle; using this sequence we insert values into a table and the max values inserted into the table is it has not reached the max value. my problem is when i do an insert it starts with 1562505. but it has to start with 91128746. anyway to reset the sequence.
Problem with Scheduled Jobs in Management Server   (65 Views)
We have a lot scheduled jobs in our management server and by default they working fine. But sometimes some of the jobs have a long delay and never are completed. For example, one job which is starting at 01:45 A.M. in the morning is still with the status "Started" and never will be completed. In this case we stop the job and start it again. Then everything is okay. We decide to troubleshooting with the turn on logging in the management server but these logs are very unuseful. So, we wondering, is this bug in managements server or there is another way to debug started job and to discover what is going on.
Problem with partitioning   (144 Views)
I see this code executes perfectly fine CREATE TABLE main_table ( id number NOT NULL, BIRTH_date date NOT NULL) PARTITION BY RANGE (BIRTH_date) (PARTITION PARTITION_jan VALUES LESS THAN (to_date('2005-07-01','yyyy-mm-dd')) TABLESPACE ts1 )ENABLE ROW MOVEMENT; But when I do this I get an error: CREATE TABLE main_table ( id number NOT NULL, BIRTH_date date NOT NULL) PARTITION BY RANGE (BIRTH_date) (PARTITION PARTITION_jan VALUES LESS THAN (to_date('2005-07'||'-01','yyyy-mm-dd')) TABLESPACE ts1 )ENABLE ROW MOVEMENT; ERROR at line 7: ORA-14019: partition bound element must be one of: string, datetime or interval literal, number
Problem with listener (ORA-12514) after shut down and re-start   (57 Views)
I have installed Oracle XE version 10.2.0 in Windows XP professional. After installing the database, in the listener I could see the service XE registered with it. "Service "XE" has 1 instance(s). Instance "XE", status READY, has 1 handler(s) for this service... Service "XEXDB" has 1 instance(s). Instance "XE", status READY, has 1 handler(s) for this service... Service "XE_XPT" has 1 instance(s). Instance "XE", status READY, has 1 handler(s) for this service..." But once I shutdown the window machine and restart it, the problem status. When I try to connect the system, ORA-12514 error message is thrown. SQL> connect username/password@XE ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor (a) The database service (OracleServiceXE) has started automatically and is running. (b) The listener.ora [C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN] has the correct setup. ============ SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server) (PROGRAM = extproc) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) (ADDRESS = (PROTOCOL = TCP)(HOST = HCL)(PORT = 1521)) ) ) DEFAULT_SERVICE_LISTENER = (XE) ============ (b) If I check the listener status, XE service is not registered. ============= LSNRCTL> status Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for 32-bit Windows: Version - Beta Start Date 27-APR-2008 18:34:23 Uptime 0 days 0 hr. 12 min. 22 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Default Service XE Listener Parameter File C:\oraclexe\app\oracle\product\10.2.0\server\network\a dmin\listener.ora Listener Log File C:\oraclexe\app\oracle\product\10.2.0\server\network\l og\listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=HCL)(PORT=1521))) Services Summary... Service "CLRExtProc" has 1 instance(s). Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully ============= The strange thing is the service XE was registered before shuting down the system. Other point to be noted is, once the error message comes, if I un-install Orace XE and re-install it, then while checking the listener status, service XE is registered. Again, after shut down and re-start, it is gone. Can somebody let me know where the problem lies.
problem with fragmentation   (155 Views)
Hi Some of my tablespaces are very badly fragmented. I did 'alter tablespace ts_name coalese', but this seems to be making only a small improvement. I had table partitions and their index partitions on these tables. When the structure needed to be changed, I dropeed the table, took offline the datafiles, dropped the tablespaces and then removed the datafiles at os level. I again created symbolic links between raw partitions and the datafiles and then recreated the entire tablespace/tables/indexes with the new structure. But, for some reason the newly created tablespaces are all fragmented. Does anyone know why and help to get over with this fragmentation my INITIAL and NEXT are sized equally and are 12M
Problem with Enterprise Management Console - Urgent   (59 Views)
Hi! I have installed Oracle Enterprise 8.17 (Normal Installation) on Win 2000 Server and the Installation was successful. I wanted to start the Management Console on the WIN 2000 Server and when I want to login with the following details: Managment Server VI01W35, Administrator SYSMAN, Password: change_on_install I got the following error: VTK-1000: Unable to connect to the managment server VI01W35. I have checked all services in WIN 2000. Oracle Services are all running without problems. Can you please help me! Thank you from Austria Markus
Problem with DBMS_STATS   (72 Views)
Hi Oracle , Whenever I run dbms_stats.gather_table_stats('',''); for a particular table in my database I get the following error. ERROR at line 1: ORA-01114: IO error writing block to file %s (block # %s) and get disconnected from oracle. I have checked DBA_TABLES and other details about the table but I am not finding anything unsual about this particular table. The same works fine with the other tables in the same schema. The oracle version is on sun 2.8. If anybody has come across the following problem please help me regarding this.
Problem with cron job   (86 Views)
I created a cron job that executes a script and sends out the output to a few people everyday. The emails are being sent but with no output in them. When I execute the script in sql*plus, I get rows back. Those rows are not in the emails. What am I doing wrong
Problem with a simple imp/exp   (217 Views)
Hi all, I'm trying to do an export/import database to another computer. I'm running into a problem. Export works fine, and I am using this command: exp userA/userA owner=userA file=exp.dat log=exp.log I get the resulting files exp.dat and exp.log. I copy them to the other computer, and try to run the command as follows: imp userB/userB fromuser=userA touser=userB file=exp.dat log =exp.log And then I get this error repeatedly on all CREATE TABLE statements: IMP-00003: ORACLE error 1950 encountered ORA-01950: no privileges on tablespace 'SYSTEM' Why is it trying to create the tables on SYSTEM I created userB using the following commands: create userB identified by test; grant create session to userB; Nothing special to userB at all...
Problem with 8.1.7 installation   (99 Views)
Hi We have Oracle 8.1.6(64 bit) running on HP UX 11.0 server. The LISTENER is running on default port 1521. Now i installed Oracle 8.1.7 server in a different $ORACLE_HOME. The installation was clean without any errors. The problem is when i am trying to run SVRMGRL, i am getting the following error. ------------------------------------------------------------------- $ svrmgrl Oracle Server Manager Release - Production Copyright (c) 1997, 1999, Oracle Corporation. Rights Reserved. sh: /u06/oracle/product/8.1.7/bin/oracle: Execute permission denied. sh: /u06/oracle/product/8.1.7/bin/oracle: Execute permission denied. ORA-12547: TNS:lost contact SVRMGR> ----------------------------------------------------------------- I am also not able to create a new LISTENER for 8.1.7. Could anyone please explain why the prblem is occuring.
Problem and mystery with public synonyms   (100 Views)
DBA's: Today, our users experienced a problem with ORA-00980. Losing reference on Synonyms. I checked for dba_synonyms for that schema_owner but could find none of these tables. This owner has about 2300 tables. Further, I checked dba_synonyms to see synonms owned by users, and I was amazed to see a table_owner having about 4300 count. Reason, I was amazed is that the table_owner, I assumed always is a second name for schema owner, which proved false, since that user does not exist in dba_users.. Below are the findings: SQL> select table_type, count(*) from cat group by table_type; TABLE_TYPE COUNT(*) ----------- ---------- SEQUENCE 65 SYNONYM 116 TABLE 2492 VIEW 163 SQL> select table_owner, count(*) from dba_synonyms group by table_owner; TABLE_OWNER COUNT(*) ------------------------------ ---------- COTDEV2 4066 COTPROD 16 SYS 1018 SYSTEM 10 TOAD 95 WCCPROD 9 36 7 rows selected. SQL> select username from dba_users; USERNAME ------------------------------ SYS SYSTEM OUTLN DBSNMP COTPROD TOAD COTVIEW COTSUPER
Possible Bug with Oracle9i   (78 Views)
OS: Windows 2000 advanced server. Database version: When I try to connect to the database as connect sys@oracle as sysdba it allows me to connect to the database as SYS user even without supplying the password. It only prompts for the password. I checked in Metalink and did not find any related bug. Any clarification will be highly appreciated.