SQL & PL/SQL :: ORA-00942 Table Or View Does Not Exit
Jul 7, 2010
In this scenario(within procedure) 1st cursor return data but 2nd cursor showing error like ORA-00942: table or view does not exit.
declare
cursor r1 is
select table_name,table_partition_name
from abc
where table_partition_name like 'NORMAL_P%'
and base_table_name like 'NORMAL'
and date_column_name like 'C_DATE'
and to_date(to_char(from_date,'dd/mon/yyyy'),'dd/mon/yyyy') between '25-jun-2010' and '27-jun-2010'
order by substr(table_partition_name,19,4);
[code].....
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.
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,
I have written a function where I am trying to select data from a table located in a different schema. I have asked my DBA to do two things as I am getting Error(14,23): PL/SQL: ORA-00942: table or view does not exist.
a) Grant privileges. b) Create public synonym.
When I do create the table in the schema where the function is located and compile the function it compiles.
CREATE OR REPLACE PROCEDURE revoke_object_priviliges_user ( p_username IN VARCHAR2) AS l_username VARCHAR2 (30) := upper(p_username); BEGIN FOR rec IN (SELECT OWNER,TABLE_NAME,PRIVILEGE,GRANTABLE FROM dba_tab_privs WHERE GRANTEE=l_username) LOOP IF (rec.GRANTABLE = 'NO')
[code]....
When i execute it, i get the following errors:
6/12 PL/SQL: SQL Statement ignored 6/61 PL/SQL: ORA-00942: table or view does not exist 8/2 PL/SQL: Statement ignored 8/6 PLS-00364: loop index variable 'REC' use is invalid
The form actually consists of one block that refers to a table in DB(Datablock). The functionality of the form is to run some logic and then just insert data in the table .This happens on press of a button.
The problem is when I run the form thru' Admin Id it runs successfully but when I run the form through some other user it gives me the error.
The grants on the table are Public. GRANT DELETE, INSERT, SELECT, UPDATE ON XYZ TO PUBLIC;
I have two schema on the two servers for replication replication is working fine.
i export one schema to another so all the tables exists at both the sites. I am adding objects in the replication group using oracle enterprise manager console.
some of the tables added fine. but some gives me error like.
ORA-23309: object UMESH.PRODUCT_MASTER of type TABLE exists
but with the error in and when generate replication support.
SQL> select status,request,message,oname from dba_repcatlog; STATUS REQUEST -------------- ----------------------------- MESSAGE -------------------------------------------------------------------------------- ONAME ------------------------------ ERROR CREATE_MASTER_REPOBJECT
[code]....
sometimes i got error like
ORA-00942: table or view does not exist
when use CREATE_MASTER_REPOBJECT command to create object at master definition site while the the table exists at the master site.but in the same situation other objects are working fine.
The SELECT at the end of this script fails with "ORA-00942: table or view does not exist". I think I added proper permission via GRANT command.
create table mudd_table ( a number, b varchar2(10) ); create user mudd_user identified by mudd_user; grant select, insert, update, delete on mudd_table to mudd_user; grant create session to mudd_user; conn mudd_user/mudd_user@quadoracle; select * from mudd_table;
connect user1/user1@dbstring CREATE TABLE A12 ( A1 NUMBER, A2 DATE ) / GRANT SELECT,UPDATE,DELETE on A12 to USER2 / DELETE FROM user1.A12 / --throwing error like ORA-00942: table or view does not exist
connect user2/user2@dbstring DELETE FROM user1.A12 / --throwing error like ORA-00942: table or view does not exist SELECT * FROM user1.A12 / --no rows returned
Above scenario has happened only for 2 tables out of 1000 tables in my schema.
Connected to: Oracle8i Release 8.1.6.0.0 - Production JServer Release 8.1.6.0.0 - Production IMP-00003: ORACLE error 942 encountered ORA-00942: table or view does not exist IMP-00023: Import views not installed, please notify your DBA IMP-00000: Import terminated unsuccessfully
Then i run the catexp.sql using SYS user. But i still got that error.
I have a view in schemaA called viewA for example. The view is quite complex, with multiple joins.I've granted ALL to schemaB to the view, and to all the referenced tables within the view.However, when I run execute the view from schemaB, I get a ORA-00942 table or view does not exist.
SQL> select * from schemaA.viewA; select * from schemaA.viewA * ERROR at line 1: ORA-00942: table or view does not exist
I suspect that Oracle is creating to temporary tables/views internally (or using something that is already there), and that I need to grant access to these temporary objects.
I have received a service ticket from QA regarding one of the application forms does not close out even after the exit button is pressed. Instead, it goes to the previous record and continues to do so if there are multiple applications created and then exits at that point.
The end users are not liking that feature and I am unable to detect this error.. What kind of trigger should i apply and what do i write in this trigger.
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';
I am running 3 for loops in continuation. These loops retrieve me 63 values. I need to insert 1 single row in a table and then exit from the same. Moreover when i run the same loop again I need to insert 2nd value fo the second row in the table. The table is having a unique key which does not allow me to load redundant data.
I have all my tables populated but for one table in particular everytime i shut down the command prompt the rows i created erase themselves. Even when i try to bring up that same table in TOAD while the command prompt is still open all the other tables besides that one in particular wont show my rows. What is going on? I was previously messing with some functions and triggers for that table but i can't remember what exactly I was trying to do. Could that have something to do with it?
I have a materialized view "pro_mview",I am trying to refresh the MVIEW by using the following statement.
EXEC DBMS_MVIEW.REFRESH('pro_mview','C');
But I am getting the below error.
* Error at line 1: ORA-12008: error in materialized view refresh path ORA-00942: table or view does not exist ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2256 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2462 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2431 ORA-06512: at line 1
I am able to fetch the data from that materialized view(pro_mview).
Suppose have the following condition in my procedure :
IF var_count = 0 then 'stop processing' --EXIT PROCEDURE ELSE 'continue processing' END IF;
How do I stop futher processing if var_count= 0 . I think about raising an exception...but is there another way out..like a command 'exit' to stop further processing ?
There is a requirement to make a table data in a database (eg: HR database) available in another database (eg: EMP database), instead of accessing it using database link. In EMP database(where data needs to be cloned), data will only be queried and no write operation will be done. Data in remote database (eg: HR DATABASE) will be occassionally fully truncated and reinserted. The plan is to do a similar truncate and reinsert of data (from HR database) into EMP database monthly once using dbms scheduler job. So basically data in just one table needs to be cloned in another database.
Question: For this situation, is a regular table or Materialized view the right choice to clone the table in EMP database and why? The table in HR database (remote database) is not very big.
I had used a When-Validate-Item trigger against an text item.
Also i had a push button (EXIT) for Exiting from the Form with code : Exit_Form(no_validation);
But if an invalid text is entered in the Text-Item and after that if i use the EXIT push Button then it doesnot exit from the Form till a valid text is entered inside the Text-Item.
how to Exit from Form even if the Text-Item has Invalid Value.
This Is My Code ------------------- Declare obutton number; begin
obutton := Show_alert('ALT_EXIT'); clear_block(no_validate); If obutton = ALERT_BUTTON1 Then -- commit; exit_form(no_validate); else exit_form(no_validate); end if; end; -------------------- But When I pressed Yes Or No the database is not update. Why ? I have save My data with save button already no problem but why i close this form The data is'nt save?
So i have to put "summit;" in it when ALT_EXIT pass another warning* appear how do i exit program without the second warning
*it say about"2 record i already updated" about this but i can't remember exactly.
I have problem writing condition to exit the for loop..How can I exit after finish printing
PROCEDURE find_stu_age_given_id(s_sid number) is cursor c1 is select * from student where sid=s_sid; age number; begin For i in c1 LOOP age :=to_char(SYSDATE -i.dob); dbms_output.put_line(i.sid ||' age is '|| age/365); END LOOP; end find_stu_age_given_id;
I have created one master detail form where on master i have one field with name status and if the status is changed to A it will prompt me to another detail block where i will fill the details and in case after i entered the details and then i decide it not to save and come back to main form without saving...how i can do it . I used go_blok In detail to go to master it goes to master but when i am saving the master the details get saved along with it.I dont want to save the detail and go to master block.
My scenario is I need to insert into History table when a record is been updated into a tabular form(insert the updated record along with the additional columns Action_by,Action_type(Like Update or delete) and Action Date Into History table i.e History table contains all the records as the main table which is been visible in tabular form along with these additional columns ...Action_by,action_type and action_date.
So now i dont want to create a befor/after update trigger on base table rather i would like to create a generic procedure which will insert the updated record into history table taking the page alias and pade ID as the parameters(GENERIC procedure is nothing but whcih applies to all the tabular forms(Tables) contained int he application ).
I have a series of sql files that I need to execute via sqlplus. What I do need is to immediately exit the sqlplus instance upon completion of the script. Now, I am not using a batch file, nor a file containing script files where I can add the EXIT command.
I have to effectively run each script from the command line as such:
Now this works, but once it runs the SQLPlus window is still open. Now I have an event handler (in a c# wrapper) that is supposed to fire once the process terminates...but of course, this even never fires since the sqlplus process doesn't terminate after running the script.
Is there any any any any way to get sqlplus to run this script and then self-exit??
I have no control of the sql script and cannot edit them to include an EXIT command. I need to run them as is.
I would like to exit from a cursor loop based on certain conditional checking. I am checking for a lot of different parameters and if they fail, I want to bypass it and fetch the next record in the cursor. I tried just putting an 'Exit' statement in the logic, but it fails. An example of my code is below:
For Row1 in cursor1 Loop If amount < 0 then balance := 0; Else
