0

I am trying to describe a table without using the DESCRIBE command but I want to combine the query with a substitution variable. Assuming I have the following table:

--DROP TABLE customers       CASCADE CONSTRAINTS PURGE;
CREATE TABLE customers
( customer_id number(10) NOT NULL,
  customer_name varchar2(50) NOT NULL,
  city varchar2(50)
);

Following the posts here and here but adding a substitution variable, I have the following:

ACCEPT myv CHAR PROMPT 'Enter a table name: '

SELECT 
    column_name AS "Name", 
    nullable AS "Null?",
    concat(concat(concat(data_type,'('),data_length),')') AS "Type"
FROM user_tab_columns
WHERE table_name = '&myv';

This returns a blank table with the appropriate column names. It doesn't matter if I entered the table name in the input prompt as CUSTOMERS or customers. However, desc customers yields:

Name          Null     Type         
------------- -------- ------------ 
CUSTOMER_ID   NOT NULL NUMBER(10)   
CUSTOMER_NAME NOT NULL VARCHAR2(50) 
CITY                   VARCHAR2(50)

Any idea how I can get substitution variable to work here? Thanks.

Community
  • 1
  • 1
sedeh
  • 7,083
  • 6
  • 48
  • 65
  • I ask about hidden assumption, just to be sure - do you really run this code sinppet in Oracle SQL*Plus ? Or maybe in another client ? – krokodilko May 23 '15 at 06:47
  • I ran it in sql developer. – sedeh May 23 '15 at 12:51
  • I've tested this query on my Sql Developer, I am using Version 4.1.0.19, it worked fine. You can use `UPPER` function in the WHERE condition to change the case of charachters: `WHERE table_name = UPPER('&myv');`. You can also append: `SELECT UPPER('&myv') FROM DUAL;` for debugging purposes, in order to check whether entered table name is correct or not. – krokodilko May 23 '15 at 13:28
  • Hm, I am stumped. Using `UPPER` doesn't appear to change anything. I am using Version 4.0.3.16. By "append", do you mean doing something like: `ACCEPT myv CHAR PROMPT 'Enter a table name: ' SELECT UPPER('&myv') FROM DUAL;`. – sedeh May 23 '15 at 14:46

1 Answers1

0

I got it to work with a bind variable. Not exactly sure what is going on because @kordirko said the query worked for him as is. Anyway, to get it to work for me (I'm using SQL Developer Version 4.0.3.16), I used bind variable, like so:

SELECT 
    column_name "Name", 
    nullable "Null?",
    concat(concat(concat(data_type,'('),data_length),')') AS "Type"
FROM user_tab_columns
WHERE table_name = :myv;

I then entered CUSTOMERS into the value field of the Enter Binds window and the query executed fine. If anybody knows why substitution variable failed but bind variable did not, that will certainly add to the discussion.

sedeh
  • 7,083
  • 6
  • 48
  • 65