Solved

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

Posted on 2011-02-28
10
577 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql case statement to calculate totals 5 51
error in oracle form 11 47
Can a Trigger trigger a Trigger? 4 45
Create a Calendar table 29 36
'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 …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

740 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