CODEWITHSUNDEEP
sqloracle10g
user2197489
user2197489

Reputation: 1

What am I doing wrong in this select statement? I'm using oracle 10g

I am providing SQL that prompts the user for the name of a table. This SQL will provide a report of column names, data types, data lengths, data precision and indicate whether nulls are allowed for the specific table.

Here is the query I am running:

SELECT employee_id, first_name, last_name
FROM employees
GROUP BY employee_id;

select department_id where employee_id = :table_name;


set echo

(select column_name, data_type, data_length,
data_precision, data_scale, nullable
from all_tab_columns
where table_name = 'employees'
order by column_name);

I get a window where a user is supposed to enter the table name but the second select statement fails to run. It comes up with ORA-00933: sql command not properly ended. If I run the second select statement separately I am getting a no data found message.

Upvotes: 0

Views: 1257

Answers (2)

Iswanto San
Iswanto San

Reputation: 18569

First, You're missing the table name in this query:

select department_id where employee_id = :table_name;

It's should be:

select department_id from employees where employee_id = :table_name;

Second

set echo

Will give you an error:

SP2-0265: echo must be set ON or OFF

You must add ON or OFF parameter for that command. For example :

set echo ON;

More: set echo

Third:

select column_name, data_type, data_length,
data_precision, data_scale, nullable
from all_tab_columns
where table_name = 'employees'
order by column_name

Maybe the comparison in where clause is case sensitive.

Try this:

select column_name, data_type, data_length,
data_precision, data_scale, nullable
from all_tab_columns
where lower(table_name) = 'employees'
order by column_name

Upvotes: 1

Piyas De
Piyas De

Reputation: 1764

Your select department_id where employee_id = :table_name;

is wrong.

The table is to be specified in the from clause.

Thanks

Upvotes: 0

Related Questions