Home » SQL & PL/SQL » Client Tools » Displaying Object Name after creating it in sql plus (Oracle 11.2.0.4 ,Solaris)
Displaying Object Name after creating it in sql plus [message #640264] Sun, 26 July 2015 01:25 Go to next message
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 #640269 is a reply to message #640264] Sun, 26 July 2015 02:45 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Do it in PL/SQL and display your message using DBMS_OUTPUT (don't forget to set serveroutput on).

Re: Displaying Object Name after creating it in sql plus [message #640273 is a reply to message #640264] Sun, 26 July 2015 03:22 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3123
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 #640302 is a reply to message #640264] Mon, 27 July 2015 02:49 Go to previous messageGo to next message
gazzag
Messages: 909
Registered: November 2010
Location: Bristol, UK
Senior Member
PROMPT Creating <table_name>
CREATE <table_name>
...
;
Re: Displaying Object Name after creating it in sql plus [message #640304 is a reply to message #640302] Mon, 27 July 2015 02:57 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3123
Registered: May 2013
Location: World Wide on the Web
Senior Member
Well, OP wants to display the message post it's creation. And one could argue how to control the message when table creation fails.
Re: Displaying Object Name after creating it in sql plus [message #640330 is a reply to message #640304] Mon, 27 July 2015 06:38 Go to previous message
EdStevens
Messages: 902
Registered: September 2013
Senior Member
Lalit Kumar B wrote on Mon, 27 July 2015 02:57
Well, 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.
Previous Topic: Insert postgresql BYTEA datatype data into oracle blob column
Next Topic: ERD on views
Goto Forum:
  


Current Time: Sat Feb 24 16:40:50 CST 2018

Total time taken to generate the page: 0.03192 seconds