SQL & PL/SQL :: ORA-02289 - Sequence Does Not Exist
Aug 27, 2010
I created a stored procedure(A) in user1@db1. This stored procedure will insert data from db1 to tables in user2@db2 using synonyms created in user1@db1 referencing table at user2@db2.
In the stored procedure (A),
insert into synonym select seq_xx.nextval from table@db1;
Then I will execute (A) in user2@db1. Execute right on (A) is given to user2@db1.Select and insert grants on db2 tables are given to user1@db1. Seq is created in db1. Compilation is successful, however when i execute (A) it has an error of
ORA-02289: sequence does not exist
ORA-02063: preceding line from db1
ORA-02063: preceding 2 lines from db2
I created simple custom workflow (Start -> Notification -> End).
I got the following error when I run the workflow.
[WF_ERROR] ERROR_MESSAGE=ORA-02289: sequence does not exist ERROR_STACK= FND_SEARCH_EVENTS.On_Object_Change(oracle.apps.wf.notification.send, 5C84790B967158FFE04400144F23BF3C)
The notification activity returned this error but the notification has been sent to performer.
i wrote a program to that takes tablename as input parameter and returns true or false based on table exist or not
the below code works differently when table has data and when table does not have data ? how to improve the below code to make sure my function always retuns true if table exists and false if does not exist regardless of 0 records or more than one record
CREATE OR REPLACE FUNCTION is_tab_present_g (pi_tab_name IN VARCHAR2) RETURN BOOLEAN IS row_cnt NUMBER := 0; sql_stm VARCHAR2 (4000); l_tab_name VARCHAR2 (4000);
[Code]....
my verification block
BEGIN if is_tab_present_g('chk_pk') then dbms_output.put_line('yessssss'); execute immediate 'drop table chk_pk cascade constraints'; else dbms_output.put_line('nooo'); end if; END; /
I've been trying to write some code to add a column if it does not exist as the code will be run numerous times and will be parameterized in other software to run across multiple tables.
I need to put amount '0' if the row exist but has no amount in my "where " conditions. the orginal commad is :
select t.aaa, count (t.bbb), sum (t.ccc) from nrb t where t.vvv IN ('3','4','5','6','D','E','F') and t.ddd like '50%' and t.eee >= TO_DATE('2012/03/21','YYYY/MM/DD') and t.eee <= TO_DATE('2012/07/21','YYYY/MM/DD') group by t.aaa order by t.aaa
and the result is : "result" tab in excel atached file.i need this result: "result 2" tab in excel atached file.
create table TEST ( COL_DATE DATE ) partition by range (COL_DATE) ( partition TEST2012 values less than (TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
[Code]....
when i execute this i receive the following error:
I am trying to do a impdp using a network link and it fails with the ORA-31626: job does not exist.It worked with a different database on the same server. The network link is there, data pump directory exists, the read and write privileges are granted to the oracle user.There are no other data pump jobs running:
SQL> select JOB_NAME,STATE from DBA_DATAPUMP_JOBS;
no rows selected
My database details: BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Data Mining and Real Application Testing options ORA-31626: job does not exist ORA-00942: table or view does not exist ORA-00942: table or view does not exist
I listed invalid object, but when compile object, say that not exist.
SQL> SELECT OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS WHERE STATUS='INVALID' AND OBJECT_NAME ='PCK_PIR_NFEL_MILLENIUM' AND OWNER='PIRAMIDE' OBJECT_NAME OBJECT_TYPE --------------------------------- -------------------- PCK_PIR_NFEL_MILLENIUM PACKAGE
SQL> ALTER PACKAGE PCK_PIR_NFEL_MILLENIUM COMPILE; ALTER PACKAGE PCK_PIR_NFEL_MILLENIUM COMPILE * ERROR at line 1: ORA-04043: object PCK_PIR_NFEL_MILLENIUM does not exist
I am running Oracle 10g on my machine. I have create an ER diagram in Toad Data Modeler, which includes all the keys, contraints etc.I have generated a DDL script which I want to biuld my tables with in Oracle.I have loaded the DDL script using SQL*Plus Worksheet and there are no problems.then tryed to insert some test data into my tables and I keep getting an error code of:
ORA-00942, table or view does not exist.Now I know the tables have been created and also by verifying this with the data dictionary using
select table_name from user_tables;
TABLE_NAME ========= Table1 Table2 Table3 . . etc
It then displays all 20 of my tables, as above. I am using a Visual Basic front end and I can see all 20 tables listed there also, with attribute names for each table.Also I get the following by typing:
select owner, object_type from all objects where object name = 'Customer';
CREATE MATERIALIZED VIEW LOAD.M_VW_ABN_PROD_921 [color=red][i]REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT (TRUNC(SYSDATE+1) + 9.5/24)[/i][/color]AS SELECT ............... ......................
it creates a DBMS_JOB for me with the required interval.
But now my requirement is that I want to create a job on the existing MV.
Now I tried the code mentioned in the previous post to schedule the job.
This job does not work. When I tried to execute it manually using:
DBMS_IJOB.RUN(12345) as sys user is throws the follwing error.
Error listed in the alert.log file is as below:
ORA-12012: error on auto execute of job 17497 ORA-23404: refresh group "CLARITY"."M_MV_INBASKET_ANALYSIS" does not exist ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.DBMS_REFRESH", line 23 ORA-06512: at "SYS.DBMS_REFRESH", line 195 ORA-06512: at line 1 Sun Jul 10 20:07:51 2011
I am trying to run the following code. The issue i am having is when running it in a function or procedure. (the SELECT statement works on it's own - so why doesn't it would in a procedure?)
SELECT LAST_DDL_TIME from SYS.dba_objects WHERE object_type='TABLE' AND OBJECT_NAME = 'CONT_ASSESSMENT'
i get an error saying "PL/SQL: ORA-00942: table or view does not exist"..I a quite new to oracle / SQL.
insert into TEST (ENT_SIG, EMP_ID, DATE_START, DATE_END) values ('014', '120', TO_DATE('20080101','YYYYMMDD'), TO_DATE('20080131','YYYYMMDD')); insert into TEST (ENT_SIG, EMP_ID, DATE_START, DATE_END) values ('014', '121', TO_DATE('20080201','YYYYMMDD'), TO_DATE('20080228','YYYYMMDD')); [code].....
I'm asking if there's a way to do this:
update test a set (date_Start, date_end) = (select date_start, date_end from test2 b where b.emp_id = a.emp_id [code].......
Without using the WHERE EXISTS. I don't want to make two accesses to table2, I would like instead to do something like "If subquery returns a row, use it, else keep what you have in destination table".
Is there a way to do it without entering test2 twice?
I am creating an index in program and then drop the index at the end of the program. Some times due to some problem if the index could not be dropped and the user again runs the program then we get the error
ORA-01408 Index already exist on the column.
how I can get away with this error or how I can check whether the index with the same columns exits prior to creating an index.
i am trying to update using bulk processing.am facing this ORA-22160: element at index [1] does not exist.
ORA-22160: element at index [string] does not exist
Cause: Collection element at the given index does not exist.
Action: Specify the index of an element which exists.
how to avoid this.
1 create or replace procedure dept_new 2 is 3 type dept_type is table of dept.deptno%type; 4 type tab_type is table of dept%rowtype; 5 --variable declaration 6 dept_t dept_type; 7 tab_t tab_type;
[code]....
my dept and dept1 tables are both of same structure.
why Oracle is saying my sub partition does not exist when I can clearly see it?
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
SQL> SELECT table_name, 2 partition_name, 3 subpartition_name 4 FROM user_tab_subpartitions 5 WHERE table_name = 'DAY_NETWORK_AGG' 6 AND subpartition_name = 'P_USER_1_P_201302010000'; [code].....
ORA-14251: Specified subpartition does not exist
This is what I used to create the table and I have yet to put any data in it, so I am wondering if Oracle doesn't really create the subpartitons from the template until there is actual data.
I'm trying to create a procedure but it's giving me errors:
16/3 PL/SQL: ORA-00942: table or view does not exist 4/2 PL/SQL: SQL Statement ignored 3/9 PLS-00341: declaration of cursor 'S_CUR' is incomplete or malformed 63/3 PL/SQL: Item ignored 69/33 PLS-00597: expression 'S_ARRAY' in the INTO list is of wrong type 69/3 PL/SQL: SQL Statement ignored 72/15 PL/SQL: ORA-00942: table or view does not exist 72/3 PL/SQL: SQL Statement ignored
I don't get why its giving table or view does not exists, because when I just execute the SELECT statement, it returned records. weird...
here's my procedure:
create or replace procedure proc_jrp_d_old_77a as cursor s_cur is select /*+ ALL_ROWS */&vtabdate, cd.cb_cardholder_no, ac.user_id,
Usually we check 1 row of data in table and compare if they exist, i want to compare different set of row all together to comapre and take the decisions
in the above example i have to check for d_key for set of row exist in target or not, so by above example d_key 1 and 3 has save value in 2 different rows so i want to check if the combination of rows
103499 1 99262 2
as a set appear in source then do nothing else insert so first time d_key 1 both records will insert but for d_key has same set in 2 rows will not be inseted. the number of row can be more than 2 also . so for the given key if we have 3 rows in source then i have to compare all the 3 same set of row in target exist of . in the target i don't have any extra column.
