Solved

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

Posted on 2011-02-28
10
571 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
To check for views, you need to check DBA_VIEWS or ALL_VIEW or USER_VIEWS accordingly.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:weegiraffe
Comment Utility
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
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
Can you post your current PL/SQL block?
0
 

Author Closing Comment

by:weegiraffe
Comment Utility
Code examples would help
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

772 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

15 Experts available now in Live!

Get 1:1 Help Now