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
Solved

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

Posted on 2011-02-28
10
574 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

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
'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 …
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…

828 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