Akdora’s Blog

Programming, Oracle, Life, Fun

ORA-14047: ALTER TABLE|INDEX RENAME may not be combined with other operations March 21, 2011

Filed under: Oracle,SQL — Akdora @ 9:40 am
Tags: , , , , ,

When we try to rename  table or index name in Oracle, we use a simple command as following;

alter table [prev_table_name] rename to [last_table_name]

If we put into schema names to this command, we can deal with ORA-14047 error. This error raise when we put schema name in front of the last_table_name.

alter table hr.[prev_table_name] rename to hr.[last_table_name] >>> RAISES ORA-14047 ERROR
alter table hr.[prev_table_name] rename to [last_table_name] >> CORRECT ONE

 

It is same with Index renaming.

 

Search In Oracle Tables :) February 5, 2009

Filed under: PL/SQL — Akdora @ 3:06 pm
Tags: , , , , , , , ,

After I write searching code in PL/SQL blocks. I also  want to add this post.

If you want to find a specific data in Tables, you may check this page:

http://www.adp-gmbh.ch/ora/misc/find_in_tables.html

SQL> set serveroutput on
SQL>
SQL> begin
  2    dbms_output.put_line(find_in_schema(val => ‘147 Spadina Ave’));
  3  end;
  4  /
 
  rowid: AAAC88AAEAAAAAnAAI in LOCATIONS
Success
 
PL/SQL procedure successfully completed
 
SQL>

 

Live Combo Search with ExtJS February 4, 2009

Filed under: Ajax,ExtJS,Java,PL/SQL — Akdora @ 10:39 am
Tags: , , , , , , , , , , , , , , ,

Live Combo Search with ExtJS

 

If you trying to list thousands of data in a combobox, then you have a problem. I faced this problem a while ago. I was developing the presentation layer with ExtJS. ExtJS has a live search solution for this situation. Check the web page: http://extjs.com/deploy/dev/examples/form/forum-search.html

I will explain the whole story from beginning to end about this online dynamic checkbox control. I will use Oracle XE 10g, Java-Struts with JPS pages and ExtJS. We will pass the data with JSON. You may make some changes in the code. It all depends on you. My metod is just a suggestion.

 

Lets create a procedure that returns a cursor. It also takes an input parameter that’s our search string. We have 60000 rows in  table company. It is also recommended to create an Function-Based Index (FBI) on “lower(t. company_name)”.

s

(more…)

 

To Remove All Constraints January 12, 2007

Filed under: Oracle — Akdora @ 10:34 am
Tags: , , , , ,

Last night, while I was coding a new program, I realized that I created wrong constraints. Then I found two methods to fix my mistake.

1- Drop table and create the table again. I think, this is not a desired solution.

DROP TABLE table_name CASCADE CONSTRAINTS;

2- If you have lots of constraints, you cannot write one by one this line.. constraint_names are also so long 🙂

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

So, you can use this procedure;

(more…)