LATEST TOPICS

DESCRIBE table with variable substitute and the alternative

Recently one of my friend was designing a script, where the requirement was to DESCRIBE a table structure based on the dynamic input (schema and table name) values provided to the script.

DESC command worked fine, when the inputs were provided in UPPER case and enclosed with double quotes (” “) .

SQL:labpa> desc "&user"."&table"
Enter value for user: SCOTT
Enter value for table: EMP
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

However, if the inputs are provided in lower case; DESC command was not working. We tried different methods to make the DESCRIBE command work with lower case dynamic input as show below

--- double quotes (" ") doesn't work with lower case for DESC command as
--- Oracle stores Object names in UPPER case internally
---
SQL:labpa> desc "&user"."&table"
Enter value for user: scott
Enter value for table: emp
ERROR:
ORA-04043: object "scott"."emp" does not exist

Then, we tried to use the DESC command without double quotes (” “) for the dynamic input. We thought if DESC can work for both lower and UPPER case static input (without any quotes), we can use something like &user.&table to make it work. However, it was ignoring the dot (.) between the user and table name.

SQL:labpa> desc &user.&table
Enter value for user: scott
Enter value for table: emp
ERROR:
ORA-04043: object scottemp does not exist
----
---- as can be seen, the . (dot) was skipped and
---- the values from &user and &table were joined together
---- as scottemp rather than scott.emp

Out of curiosity, the thought came in to use an additional dot (.) between the &user and &table and give it a try.

So, here we are !!!

SQL:labpa> desc &user..&table
Enter value for user: scott
Enter value for table: emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

Wow !!! it worked …

Now, the obvious question. why it did not work with a single dot (.)?

 

Explanation:

We had ignored the basic syntax PL/SQL coding. A dot (.) when used with input variables (&var), appends the characters following the input variable immediately after input substitution. This was the reason it was joining together the values from &user and &table rather than making itself appear between &user and &table.

In other words, a dot (.) is used to terminate a PL/SQL variable as show below


SQL:labpa> select &1. from dual;
Enter value for 1: 100

100
----------
100

SQL:labpa> select &1 from dual;
Enter value for 1: 100

100
----------
100

 

 

Alternative to DESC command:

Apart from the workaround mentioned above, the following SELECT query can also be used as an alternative to the DESC command.  Here, we are querying the table structure from DBA_TAB_COLUMNS view, which is the dictionary view containing the column structure of database tables.

-----
col "Name" for a15
col "Null?" for a7
col "Type" for a15
-----
-----
select COLUMN_NAME as "Name",
NULLABLE as "Null?" ,
data_type||'('||data_length||')' as "Type"
from dba_tab_columns where
OWNER=upper('&1') and TABLE_NAME=upper('&2');

 

Conclusion:

It is always a good choice to regularly brush up the basics to avoid silly mistakes :)

%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed