Home » SQL & PL/SQL » SQL & PL/SQL » tablename or alias as in para and see the table avaible in schema (windows oracle9i)
tablename or alias as in para and see the table avaible in schema [message #362420] Tue, 02 December 2008 04:14 Go to next message
saic
Messages: 27
Registered: December 2008
Junior Member
Hi One to All

My requirement is like:

Iam creating a procedure, that input parameter is table name or alias name of the table, and show the msg is that the table is avaible in the schema or not;i have write the follwing proceure
please
but i dont jnow how to send the table alias name.

your reply is help for me

-----------------------

create or replace procedure Proc(tablename in varchar2)is
v_number number;
begin

select count(*) into v_number from all_tables where table_name=tablename;

if v_number is null then
 dbms_output.put_line('no table in the schema');
else
dbms_output.put_line(the table is avaible in the schema');
end if;
end;


thanks
saic
Re: tablename or alias as in para and see the table avaible in schema [message #362427 is a reply to message #362420] Tue, 02 December 2008 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This has been asked several times the last month.
Many solutions have been posted.
Please search BEFORE posting.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: tablename or alias as in para and see the table avaible in schema [message #362429 is a reply to message #362427] Tue, 02 December 2008 04:30 Go to previous messageGo to next message
saic
Messages: 27
Registered: December 2008
Junior Member
If You dont mind please Provide me the links

thanks
saic
Re: tablename or alias as in para and see the table avaible in schema [message #362432 is a reply to message #362429] Tue, 02 December 2008 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To find them I'd have to search, thing you can do by yourself.
Search for "execute immediate", "dynamic query", "table name in variable" or the like.

But clearly I don't see what is the problem with your procedure apart from the fact that "count(*)" NEVER returns NULL.

Regards
Michel

[Updated on: Tue, 02 December 2008 04:47]

Report message to a moderator

Re: tablename or alias as in para and see the table avaible in schema [message #362433 is a reply to message #362420] Tue, 02 December 2008 04:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your basic approach looks ok to me.
I'd add an OUT parameter to the procedure to pass back whether or not you found the table, and I'd select from USER_TABLES rather than ALL_TABLES if I wanted to just look for the named table in the current schema, plus you're missing a ' in the second Dbms_output.

What exactly is the problem you're having?
Re: tablename or alias as in para and see the table avaible in schema [message #362437 is a reply to message #362420] Tue, 02 December 2008 05:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Remember that count(*) does not return NULL if no rows were found...
Re: tablename or alias as in para and see the table avaible in schema [message #362447 is a reply to message #362437] Tue, 02 December 2008 05:55 Go to previous messageGo to next message
saic
Messages: 27
Registered: December 2008
Junior Member
Hi All

Thanks for Giving this much Response

My Requirement is

i need to send table_name or alias of table_name as in parameter
and from the procedure i want see wheather the table is exits or not

please provide me the link or procedure.

Thanks
SAIC
Re: tablename or alias as in para and see the table avaible in schema [message #362450 is a reply to message #362447] Tue, 02 December 2008 06:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As we said your procedure should work with small modifications.
Fix it and come back (with a formatted one).

Regards
Michel
Re: tablename or alias as in para and see the table avaible in schema [message #362454 is a reply to message #362447] Tue, 02 December 2008 06:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What do you mean by alias? A synonym? If so, query all_synonyms to get the tablename and then use the query you already have to see if the table exists.
Re: tablename or alias as in para and see the table avaible in schema [message #362455 is a reply to message #362450] Tue, 02 December 2008 06:19 Go to previous messageGo to next message
saic
Messages: 27
Registered: December 2008
Junior Member
Hi ,

Thanks your reply,
the code is like
CREATE OR REPLACE PROCEDURE Proc
     (TableName  IN VARCHAR2)
IS
  v_Number  NUMBER;
BEGIN
  SELECT COUNT(* )
  INTO   v_Number
  FROM   User_Tables
  WHERE  Table_Name = TableName;
  
  IF v_Number = 0 THEN
    dbms_Output.Put_Line('no table in the schema');
  ELSE
    dbms_Output.Put_Line('the table is avaible in the schema');
  END IF;
END;

But it allso possible that the in parameter can be alis of the table , in this scenario what we can do.

[Mod-Edit: Frank added [code] and [/code]-tags around your code to improve readability.
Please do so yourself in the future.

[Updated on: Tue, 02 December 2008 06:22] by Moderator

Report message to a moderator

Re: tablename or alias as in para and see the table avaible in schema [message #362459 is a reply to message #362455] Tue, 02 December 2008 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you mean "alias of the table"?
Explain how you want to use your function.

Regards
Michel
Re: tablename or alias as in para and see the table avaible in schema [message #362462 is a reply to message #362459] Tue, 02 December 2008 06:31 Go to previous messageGo to next message
saic
Messages: 27
Registered: December 2008
Junior Member
Hi Michel

Alias of table---->A synonym

but we dont know what they sending it may be table name or it may be alis but the proceudre should show the out to the corresponding input i.e table exits or not but i have formated some , please tell me it ok for both scenario.

CREATE OR REPLACE PROCEDURE Proc
     (TableName  IN VARCHAR2)
IS
  v_Number  NUMBER;
BEGIN
  SELECT COUNT(* )
  INTO   v_Number
  FROM   User_Tables
  WHERE  Table_Name = TableName;
  
  SELECT COUNT(* )
  INTO   v_Number
  FROM   All_Synonyms
  WHERE  Synonym_Name = TableName;
  
  IF v_Number = 0 THEN
    dbms_Output.Put_Line('no table in the schema');
  ELSE
    dbms_Output.Put_Line('the table is avaible in the schema');
  END IF;
END;


please give reply

thanks
Saic
Re: tablename or alias as in para and see the table avaible in schema [message #362465 is a reply to message #362462] Tue, 02 December 2008 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check in the following order:
1/ user_tables
2/ user_synonyms
3/ all_synonyms

You may have to handle synonym of synonym.

Regards
Michel
Re: tablename or alias as in para and see the table avaible in schema [message #362466 is a reply to message #362462] Tue, 02 December 2008 06:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Problem might be that there can be multiple synonyms pointing to different object, plus, on top of that, the table itself.
Which would you then need?
Re: tablename or alias as in para and see the table avaible in schema [message #362471 is a reply to message #362466] Tue, 02 December 2008 06:58 Go to previous messageGo to next message
saic
Messages: 27
Registered: December 2008
Junior Member
Hi Michel,

Pleae give commets on below code

CREATE OR REPLACE PROCEDURE Proc
     (TableName  IN VARCHAR2)
IS
  v_Number  NUMBER;
BEGIN
  SELECT COUNT(* )
  INTO   v_Number
  FROM   User_Tables
  WHERE  Table_Name = TableName;
  
  SELECT COUNT(* )
  INTO   v_Number
  FROM   User_Synonyms
  WHERE  Synonym_Name = TableName;
  
  SELECT COUNT(* )
  INTO   v_Number
  FROM   All_Synonyms
  WHERE  Synonym_Name = TableName;
  
  IF v_Number = 0 THEN
    dbms_Output.Put_Line('no table in the schema');
  ELSE
    dbms_Output.Put_Line('the table is avaible in the schema');
  END IF;
END;


it is enough for to search the table in schema
any other tink should taken care?

thanks
Saic
Re: tablename or alias as in para and see the table avaible in schema [message #362472 is a reply to message #362471] Tue, 02 December 2008 07:00 Go to previous messageGo to next message
saic
Messages: 27
Registered: December 2008
Junior Member
Hi Frank

Thanks giving for your Valuble Information
please see the code and give your comments

thank
SAIC
Re: tablename or alias as in para and see the table avaible in schema [message #362473 is a reply to message #362471] Tue, 02 December 2008 07:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Hi Michel,

Pleae give commets on below code

It is not formatted.

Regards
Michel
Re: tablename or alias as in para and see the table avaible in schema [message #362476 is a reply to message #362471] Tue, 02 December 2008 07:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Pretend you are the database.
Start from line one and think what will happen. Forget what your goal is, just look at the code and interpret it verbatim.
Do this several times; each with a different scenario in your head (e.g. table exists, second time synonym exists, third time nothing exists, etc)
What do you see? What happens?

THIS is how you write/read/check code.
Re: tablename or alias as in para and see the table avaible in schema [message #362479 is a reply to message #362473] Tue, 02 December 2008 07:19 Go to previous messageGo to next message
saic
Messages: 27
Registered: December 2008
Junior Member
Hi Frank

thanks for giving your comments , iam not a oracle dev, iam in java
if i done an mistake please forgive me here in mysystem i dont have oracle database,just iam frameing the code,

thanks
SAic
Re: tablename or alias as in para and see the table avaible in schema [message #362482 is a reply to message #362479] Tue, 02 December 2008 07:23 Go to previous messageGo to next message
saic
Messages: 27
Registered: December 2008
Junior Member
Hi Michel,

CREATE OR REPLACE PROCEDURE Proc
     (TableName  IN VARCHAR2)
IS
  v_Number  NUMBER;
BEGIN
  SELECT COUNT(* )
  INTO   v_Number
  FROM   User_Tables
  WHERE  Table_Name = TableName;
  
  SELECT COUNT(* )
  INTO   v_Number
  FROM   User_Synonyms
  WHERE  Synonym_Name = TableName;
  
  SELECT COUNT(* )
  INTO   v_Number
  FROM   All_Synonyms
  WHERE  Synonym_Name = TableName;
  
  IF v_Number = 0 THEN
    dbms_Output.Put_Line('THERE IS NO SUCH TABLE AVIABLE IN THE SCHEMA');
  ELSE
    dbms_Output.Put_Line('THE TABLE IS AVIABLE IN THE SCHEMA ');
  END IF;
END;


in the above code if the first select is executes no need to second select sts,how can avoid this scanrio

thanks
SAIC
Re: tablename or alias as in para and see the table avaible in schema [message #362483 is a reply to message #362479] Tue, 02 December 2008 07:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
It's no different in Oracle then it is in Java..
What happens in Java if you would do
String s
s = firstMethod();
s = secondMethod();
s = thirdMethod();

if (s == null) {
    System.out.println("None of the methods returned value");
} else {
    System.out.println("One method returned value");
}

Does this seem ok to you?
Re: tablename or alias as in para and see the table avaible in schema [message #362485 is a reply to message #362483] Tue, 02 December 2008 07:34 Go to previous messageGo to next message
saic
Messages: 27
Registered: December 2008
Junior Member
Hi Michel

Please give response for this below code.

if the first select sts is returning 1 then how can avoid the second select sts and same as third select sts.

CREATE OR REPLACE PROCEDURE Proc
     (TableName  IN VARCHAR2)
IS
  v_Number  NUMBER;
BEGIN
  SELECT COUNT(* )
  INTO   v_Number
  FROM   User_Tables
  WHERE  Table_Name = TableName;
  
  SELECT COUNT(* )
  INTO   v_Number
  FROM   User_Synonyms
  WHERE  Synonym_Name = TableName;
  
  SELECT COUNT(* )
  INTO   v_Number
  FROM   All_Synonyms
  WHERE  Synonym_Name = TableName;
  
  IF v_Number = 0 THEN
    dbms_Output.Put_Line('THERE IS NO SUCH TABLE AVIABLE IN THE SCHEMA');
  ELSE
    dbms_Output.Put_Line('THE TABLE IS AVIABLE IN THE SCHEMA ');
  END IF;
END;


thanks
Saic
Re: tablename or alias as in para and see the table avaible in schema [message #362487 is a reply to message #362485] Tue, 02 December 2008 07:38 Go to previous message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Quote:
if the first select sts is returning 1 then how can avoid the second select sts and same as third select sts.


How about using normal IF statements. For example:

SELECT COUNT(* )
  INTO   v_Number
  FROM   User_Tables
  WHERE  Table_Name = TableName;

IF v_Number != 1 THEN
   -- Second select...
Previous Topic: SPACE IN PADDING
Next Topic: how to query table that only have a value....
Goto Forum:
  


Current Time: Sun Dec 04 16:40:39 CST 2016

Total time taken to generate the page: 0.06252 seconds