Home » SQL & PL/SQL » SQL & PL/SQL » regarding all_tab_columns (oracle 9 i, windows xp)
regarding all_tab_columns [message #417515] Mon, 10 August 2009 00:45 Go to next message
karthikeyanc2003
Messages: 33
Registered: August 2009
Location: india
Member
hi folks Razz ,
i have a great doubt that i ever seen in oracle,
consider a senirio , i have a table called employee having six column, while i run the following query;

DECLARE
t1 NUMBER;
table_name VARCHAR2(100);
BEGIN
table_name := 'employee';
SELECT Count(*) INTO t1 FROM all_tab_columns WHERE TABLE_name = upper( table_name );
Dbms_Output.Put_Line(t1);
END ;


i get a output as 14206

rather than 6

let me know what wents wrong in this.
Re: regarding all_tab_columns [message #417516 is a reply to message #417515] Mon, 10 August 2009 00:50 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>let me know what wents wrong in this.
What is input?
How can I produce same output?
You got what you deserved.

Re: regarding all_tab_columns [message #417518 is a reply to message #417515] Mon, 10 August 2009 00:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
When in doubt, strip the extra's and work your way back up from there.
So, first go back to SQL, instead of using PL/SQL. Check how many employee-tables there are. Multiple schemas can each have an employee table.
Secondly, I think you simplified/anonymized your problem. Nothing wrong with that, but could it be that you left out a bit too much?
Re: regarding all_tab_columns [message #417519 is a reply to message #417516] Mon, 10 August 2009 00:58 Go to previous messageGo to next message
karthikeyanc2003
Messages: 33
Registered: August 2009
Location: india
Member
hi blackswan,




for your refrerence i attach the table structure too

CREATE TABLE employee (
emp_id NUMBER(6,0) NOT NULL,
first_name VARCHAR2(20) NULL,
last_name VARCHAR2(50) NULL,
address VARCHAR2(150) NULL,
ssn NUMBER(9,0) NULL,
ph NUMBER(9,0) NULL
)

and while i run the following query
SELECT Count(*) FROM all_tab_columns WHERE table_name = Upper ('employee');

i get the answer 6 but while going for the

DECLARE
t1 NUMBER;
table_name VARCHAR2(100);
BEGIN
table_name := 'employee'; /* this is the input */
SELECT Count(*) INTO t1 FROM all_tab_columns WHERE TABLE_name = upper( table_name );
Dbms_Output.Put_Line(t1);
END ;

i am getting a output as

14206

Re: regarding all_tab_columns [message #417520 is a reply to message #417519] Mon, 10 August 2009 01:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rename your variable
Re: regarding all_tab_columns [message #417522 is a reply to message #417519] Mon, 10 August 2009 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, 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), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: regarding all_tab_columns [message #417523 is a reply to message #417515] Mon, 10 August 2009 01:07 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>i am getting a output as
>14206
so you claim.
provide proof so anyone do the same!
Provide DDl & DML statements.
Re: regarding all_tab_columns [message #417526 is a reply to message #417523] Mon, 10 August 2009 01:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
BlackSwan wrote on Mon, 10 August 2009 08:07
>i am getting a output as
>14206
so you claim.
provide proof so anyone do the same!
Provide DDl & DML statements.

This is nonsense and you know it.
There is no way anyone can create a testcase regarding all_tab_columns.
Re: regarding all_tab_columns [message #417528 is a reply to message #417518] Mon, 10 August 2009 01:26 Go to previous messageGo to next message
karthikeyanc2003
Messages: 33
Registered: August 2009
Location: india
Member
hi blackswam,

here is the ddl .


CREATE TABLE employee (
emp_id NUMBER(6,0) NOT NULL,
first_name VARCHAR2(20) NULL,
last_name VARCHAR2(50) NULL,
address VARCHAR2(150) NULL,
ssn NUMBER(9,0) NULL,
ph NUMBER(9,0) NULL
)

and i go for the pl/sql part as i mention before.


and i like to thank all of u for responding my query at your busy time , i would be really happy when i get the solution...


Re: regarding all_tab_columns [message #417529 is a reply to message #417515] Mon, 10 August 2009 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Frank already gave you the answer:
Quote:
rename your variable

In you query how Oracle can know if "table_name" if the column name or the variable? It chooses the the column name in this case.

Regards
Michel
Re: regarding all_tab_columns [message #417530 is a reply to message #417515] Mon, 10 August 2009 01:30 Go to previous message
karthikeyanc2003
Messages: 33
Registered: August 2009
Location: india
Member
hi thanks to all who response;
Previous Topic: How to retrive it??
Next Topic: Cursor- no data found exception handaling
Goto Forum:
  


Current Time: Sat Dec 03 07:55:14 CST 2016

Total time taken to generate the page: 0.10587 seconds