OraFAQ Forum: SQL & PL/SQL » Cursor in SQL Plus

Home » SQL & PL/SQL » SQL & PL/SQL » Cursor in SQL Plus

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Cursor in SQL Plus [message #4335]

Thu, 28 November 2002 10:17

jerome
Messages: 13
Registered: May 2000

Junior Member

Hi all,

Sorry for the newbie queetion (I use oracle for 3 days now...) , but here it is:

I wnat to run a describe against all the tables of my database (in all_tables).

I was trying this:
<<<<<<<<<<<
declare
cursor c1 is select table_name from all_tables;
mytable c1%ROWTYPE;
begin
open c1;
loop
fetch c1 into mytable;
exit when c1%notfound;
describe mytable.table_name;
end loop;
end;
>>>>>>>>>>>

but it cry
::::::::::::;
describe mytable.table_name;
*
ERROR at line 10:
ORA-06550: line 10, column 14:
PLS-00103: Encountered the symbol "MYTABLE" when expecting one of the following:
:= . ( @ % ;
The symbol ":=" was substituted for "MYTABLE" to continue.
:::::::::::::
Could someone help me with this?
thanks in advance

Report message to a moderator

Re: Cursor in SQL Plus [message #4337 is a reply to message #4335]

Thu, 28 November 2002 23:22

Deepa
Messages: 269
Registered: November 2000

Senior Member

you cannot use sqlplus command "describe" inside the pl/sql block.

instead you can query the all_tab_columns view to get details about the table & its columns;

Report message to a moderator

Re: Cursor in SQL Plus [message #4341 is a reply to message #4335]

Fri, 29 November 2002 03:26

cormac
Messages: 25
Registered: November 2002

Junior Member

change or query to the following, if you want the results to print to screen:

set serveroutput on size 1000000
declare
cursor c1 is
select table_name
from all_tables;
mytable all_tables.table_name%type;
begin
open c1;
loop
fetch c1 into mytable;
exit when c1%notfound;
dbms_output.put_line('table names:'||' '|| mytable);
end loop;
end;
/

Cormac

Report message to a moderator

Re: Cursor in SQL Plus [message #4362 is a reply to message #4335]

Mon, 02 December 2002 19:01

Barbara Boehmer
Messages: 9094
Registered: November 2002
Location: California, USA

Senior Member

Another way:

SET      ECHO OFF FEEDBACK OFF HEADING OFF PAGESIZE 0 VERIFY   OFF
SPOOL    query.sql

SELECT   'DESC ' || table_name
FROM     all_tables
ORDER BY table_name
/

SPOOL    OFF
SET      ECHO ON HEADING ON
START    query.sql

Report message to a moderator

Previous Topic:	sql join question
Next Topic:	sql join question

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 14 23:47:07 CDT 2024