Solved

Check for existance of tables and views from PL/SQL block

Posted on 2011-02-28
10
573 Views
Last Modified: 2013-12-07
From a PL/SQL block,  I want to check if tables or views required by a cursor exist while the PL/SQL is executing not just at compile time. Does anyone have any suggestions for how to do this?

0
Comment
Question by:weegiraffe
  • 4
  • 3
  • 2
  • +1
10 Comments
 

Author Comment

by:weegiraffe
ID: 34995720
It tables are locked because there is an uncomitted update pending, is there a way of checking this with exception handling to prevent a SELECT executing from a PL/SQL block?
0
 

Author Comment

by:weegiraffe
ID: 34995733
oooops the question below was not intended to go here but into a different question.


" Any of the tables required by the cursor are locked - Because we're only doing a select it returns a value despite a second session having an uncommitted update pending. How do we get round this ?"
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34995749
select statements have nothing to do with the LOCKS or updates. but they will return the data as per the last commit done. i mean if other session has updated something/committed after this session has read the data, then you need to fetch/select it again if you need updated data.

what is your requirement ?
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 16

Accepted Solution

by:
Milleniumaire earned 250 total points
ID: 34995764
You can do this in a couple of ways:

1. Attempt to select from the tables that you want to check exist and trap the "ORA-00942: table or view does not exist" error.

2. Write a query to interrogate the dictionary views (ALL_TABLES, USER_TABLES) to determine if the tables you want to access exist.
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 250 total points
ID: 34995794
To check for views, you need to check DBA_VIEWS or ALL_VIEW or USER_VIEWS accordingly.
0
 

Author Comment

by:weegiraffe
ID: 34995795
Hi Milleniumaire

THanks for your reply. My problem is that when the PL/SQL is compiled that the error "ORA-00942: table or view does not exist" will be thrown if the table does note exist. But I want to catch the errror within the PL/SQL block.
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 250 total points
ID: 34995842
then you need to dynamic sql as tables/views are required for the code to get compiled in first place.
0
 
LVL 16

Assisted Solution

by:Milleniumaire
Milleniumaire earned 250 total points
ID: 34996202
Exactly, as nav_kum_v says, to avoid the compiler throwing errors you need to "hide" the sql by building it up in a string and then executing it at runtime.

It's unusual to not know what objects exist at the time you are building the pl/sql code.  Is this due to the objects being dropped and re-created?
0
 
LVL 32

Expert Comment

by:awking00
ID: 34997437
Can you post your current PL/SQL block?
0
 

Author Closing Comment

by:weegiraffe
ID: 35011638
Code examples would help
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now