can I use "desc" within a PL/SQL statement?

[dbalearner]

I am trying to check if a table belonging to a schema exists so I can describe it. The code is as follows:


set serveroutput on
set pagesize 0
declare
V_Owner sys.dba_tables.owner%TYPE := upper('&&owner');
V_Table sys.dba_tables.table_name%TYPE := upper('&&table');
V_NumRows integer := null;
begin
select count(1) into V_NUmRows from dual
where exists (select null from dba_tables where owner = V_Owner and table_name = V_Table);
if V_numRows > 0
then
dbms_output.put_line (' ');
desc V_Owner.V_Table;
else
dbms_output.put_line ('No record!');
end if;
end;
/
exit


Enter value for owner: scott
old 2: V_Owner sys.dba_tables.owner%TYPE := upper('&&owner');
new 2: V_Owner sys.dba_tables.owner%TYPE := upper('scott');
Enter value for table: emp
old 3: V_Table sys.dba_tables.table_name%TYPE := upper('&&table');
new 3: V_Table sys.dba_tables.table_name%TYPE := upper('emp');
desc V_Owner.V_Table;
*
ERROR at line 11:
ORA-06550: line 11, column 3:
PLS-00103: Encountered the symbol "DESC" when expecting one of the following:
begin case declare else elsif end exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << close current delete fetch lock insert
open rollback savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe
<an alternatively-quoted SQL string>
The symbol "DESC" was ignored.

Any idea how I can make it work or I need to use dynamic SQL for describing the table?

Thanks

 

[SantaMufasa]

Learner,

First, "desc(ribe)" is not a SQL or a PL/SQL command...it is SQL*Plus, and as such, PL/SQL has no clue what to do with it. (PL/SQL knows only how to deal with PL/SQL commands and SQL commands.)

You certainly could build a SELECT statement to simulate the results you need from a "DESC". Are you comfortable doing that, or would you like us to help?

If you want help, we would need to know precisely what pieces of data you would like to see about a table and the format you want to see the data.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[dbalearner]

Thanks Santa. My original objective was to check if a table exists and if so to drop it. But this code does not work:

set serveroutput on
set pagesize 0
declare
V_Owner sys.dba_tables.owner%TYPE := upper('&&owner');
V_Table sys.dba_tables.table_name%TYPE := upper('&&table');
V_NumRows integer := null;
begin
select count(1) into V_NUmRows from dual
where exists (select null from dba_tables where owner = V_Owner and table_name = V_Table);
if V_numRows > 0
then
dbms_output.put_line (' ');
dbms_output.put_line ('Dropping table '|| V_Owner||"'"||V_Table');
drop table V_Owner||"."||V_Table
else
dbms_output.put_line ('No record!');
end if;
end;
/
exit


Enter value for owner: scott
old 2: V_Owner sys.dba_tables.owner%TYPE := upper('&&owner');
new 2: V_Owner sys.dba_tables.owner%TYPE := upper('scott');
Enter value for table: mytable
old 3: V_Table sys.dba_tables.table_name%TYPE := upper('&&table');
new 3: V_Table sys.dba_tables.table_name%TYPE := upper('mytable');
Enter value for owner: scott
old 2: V_Owner sys.dba_tables.owner%TYPE := upper('&&owner');
new 2: V_Owner sys.dba_tables.owner%TYPE := upper('scott');
Enter value for table: mytable
old 3: V_Table sys.dba_tables.table_name%TYPE := upper('&&table');
new 3: V_Table sys.dba_tables.table_name%TYPE := upper('mytable');
ERROR:
ORA-01756: quoted string not properly terminated

 

[SantaMufasa]

Learner,

First, your code is highly dangerous since you are dropping tables. Is a DBA the owner/user of this script? If so, then at least the proper user is running it.

Your error(s) BTW, result from Oracle's not recognising any punctuation beside a single quote to delimit a literal string...You have used double quotes in or more places.

Here is how I would modify your script for improved readability and execution:

set verify off
set serveroutput on format wrap
set pagesize 0
accept owner prompt "Enter owner of table to drop: "
accept table prompt "Enter name of table to drop: "
declare
V_SQL_code varchar2(1000);
begin
  execute immediate 'drop table &owner..&table';
  dbms_output.put_line (' ');
  dbms_output.put_line ('Dropped table &owner..&table cascade');
exception
  when others then dbms_output.put_line('Error: Did not drop "&owner..&table".');
end;
/

Section 2 -- Sample invocation and execution of script (that I named "tt_308.sql"):

SQL> @tt_308
Enter owner of table to drop: test
Enter name of table to drop: yada2

Dropped table test.yada2 cascade

PL/SQL procedure successfully completed.

SQL> @tt_308
Enter owner of table to drop: test
Enter name of table to drop: yada
Error: Did not drop "test.yada".

PL/SQL procedure successfully completed.

The latter execution fails since there is no "yada" table.

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[dbalearner]

very nice Santa.

As per my script how can I check if the table actually exists before dropping it? Can I use this code please:

select count(1) into V_NUmRows from dual
where exists (select null from dba_tables where owner = V_Owner and table_name = V_Table);

Or something similar

thanks

 

[SantaMufasa]

Yes, you can use that code to check for existence prior to dropping.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.