Solved

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

Posted on 2011-02-28
10
572 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
 
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

867 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

20 Experts available now in Live!

Get 1:1 Help Now