-4

What will happen if i do not define length attribute in my hibernate mapping hbm.xml file?

what is the default length value it will take for that column?

Thanks

Danish
  • 189
  • 2
  • 3
  • 14
  • 2
    What do you mean with "one shot"? A single SQL statement? A single PL/SQL block? A single transaction? –  Aug 28 '18 at 13:05
  • yes, to all your question – Danish Aug 28 '18 at 13:17
  • 2
    Oracle does not support transactional DDL, so that part is impossible. –  Aug 28 '18 at 13:17
  • So i have to run alter command 100 times ? – Danish Aug 28 '18 at 13:18
  • 1
    Yes, either manually in SQL, or with a loop in PL/SQL –  Aug 28 '18 at 13:19
  • could you please give me how to run that through loop for many tables. – Danish Aug 28 '18 at 13:20
  • Use a [Cursor FOR Loop](https://docs.oracle.com/database/121/LNPLS/cursor_for_loop_statement.htm) to step through the set of tables you want to alter, and [`execute immediate`](https://docs.oracle.com/database/121/LNPLS/executeimmediate_statement.htm) to make the changes. Bonus points for making it re-runnable e.g. for use in an automated deployment. – William Robertson Aug 28 '18 at 13:40
  • feel free to edit the question on how to do this specifically in SQL Developer, but i would suggest adding detail on exactly what you're wanting to change for the 100 tables – thatjeffsmith Aug 28 '18 at 15:20

2 Answers2

2

Since you tagged SQL Developer...

You can grab this XML extension from our GitHub repo. In particular, the 'MultiSelectObjectExample'

Once this is added, you can multi-select a ton of tables in the connection tree, right click, and generate your code to run.

enter image description here

Let's say you wan to add a column called STACKOVERFLOW, a VARCHAR2(4000), to a bunch of tables.

Select the tables, do the right click.

Then in the pop-up dialog:

enter image description here

Copy that block of text to a SQL Worksheet.

Run it.

enter image description here

See the button i have in the red box? If you click that, it'll copy the output to a new sql worksheet so you can run it.

This is a lot of steps, but if you want GUI help for dynamically doing something to a lot of objects, it could be very useful.

You could of course alter the code to add that text to a local variable and use EXECUTE IMMEDIATE, but then it would be very easy to mess up 100 tables in one go. I like the DBMS_OUTPUT way as it forces me to look at the code before I execute it.

thatjeffsmith
  • 20,522
  • 6
  • 37
  • 120
0

Loop over the list of tables and execute alter scripts you Need with execute immediate

In this example I rename all tables created under my user

begin

  for rec in (select * from user_tables)
    execute immediate 'ALTER TABLE '|| rec.table_name|| ' RENAME TO DUP_'|| rec.table_name;
  end Loop;

end;
/

You can then dynamically generate the alter command that you need from the data dictionary

hotfix
  • 3,376
  • 20
  • 36