| Displaying Object Name after creating it in sql plus [message #640264] |
Sun, 26 July 2015 01:25  |
 |
tigsav
Messages: 49
Registered: April 2012
|
Member |
|
|
Hello Experts,
I am trying to write a simple script which contains table creation ddls for around 10 tables.
I am spooling the sqlplus output to a log file.
The log file contains the sql-plus output .
For eg : it shows "Table Created"
I am trying to know if there is a simple way to know the object name created.
for eg : Employees created instead of table created.
Writing a after create trigger is not an option .
I am looking to know if there a key word in sqlplus which helps us do this.
Thanks & Regards,
tigsav
|
|
|
|
|
|
| Re: Displaying Object Name after creating it in sql plus [message #640273 is a reply to message #640264] |
Sun, 26 July 2015 03:22  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
tigsav wrote on Sun, 26 July 2015 11:55
I am trying to know if there is a simple way to know the object name created.
for eg : Employees created instead of table created.
As Michel suggested you could do it in PL/SQL.
For example,
SQL> set serveroutput on
SQL> begin
2 execute immediate 'create table t(a number)';
3 dbms_output.put_line('Table T created');
4 exception
5 when others then
6 if sqlcode = -955 then
7 dbms_output.put_line('Table T already exists');
8 end if;
9 end;
10 /
Table T created
PL/SQL procedure successfully completed.
SQL> /
Table T already exists
PL/SQL procedure successfully completed.
SQL>
I didn't raise any exception, it was a simple demo. You could handle the exception the way you want.
Something like this:
SQL> ed
Wrote file afiedt.buf
1 begin
2 execute immediate 'crate table t(a number)';
3 dbms_output.put_line('Table T created');
4 exception
5 when others then
6 if sqlcode = -955 then
7 dbms_output.put_line('Table T already exists');
8 else
9 raise;
10 end if;
11* end;
SQL> /
begin
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 9
SQL>
Edit : Added the code to re-raise error when sqlcode other than -955
[Updated on: Sun, 26 July 2015 03:26] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Displaying Object Name after creating it in sql plus [message #640330 is a reply to message #640304] |
Mon, 27 July 2015 06:38 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
Lalit Kumar B wrote on Mon, 27 July 2015 02:57Well, OP wants to display the message post it's creation. And one could argue how to control the message when table creation fails.
I understood that what he gave as an example was not intended to be a hard 'requirement' but I could be wrong.
To the OP: I'd simply adjust my expectations a bit. You have the script, you have the name of the table already embedded in the script as part of the ddl (surely you aren't using dynamic ddl to create a table in pl/sql. That's a very poor practice for many other reasons). So as already said, just at a PROMPT command. Or SET ECHO ON so that the DDL itself is also included in the spool file.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
