Home » SQL & PL/SQL » SQL & PL/SQL » generate Primary key dynamically
generate Primary key dynamically [message #232372] Fri, 20 April 2007 05:48 Go to next message
guenni_ac
Messages: 3
Registered: April 2007
Location: Aachen - Germany
Junior Member
Hi,

I want to generate a primary key containing a string pk_ + the OBJECT_ID of the table. This is to avoid problems due to 30 char long tablenames.

Here my PL/SQL construct:

DECLARE
 tabid NUMBER;
BEGIN
 select OBJECT_ID into tabid from user_objects where OBJECT_NAME='<tablename>';
 EXECUTE IMMEDIATE 'alter table stsmanager.<tablename>
 add CONSTRAINT pk_' || TO_CHAR(tabid) PRIMARY KEY (col);
END;


and this is the message:

SQL> DECLARE
  2   tabid NUMBER;
  3  BEGIN
  4   select OBJECT_ID into tabid from user_objects where OBJECT_NAME='Q_XDM_VC4_EC42';
  5   EXECUTE IMMEDIATE 'alter table stsmanager.Q_XDM_VC4_EC42
  6   add CONSTRAINT pk_' || TO_CHAR(tabid) PRIMARY KEY (STIME,NE_ID,OBJ_VC4);
  7  END;
  8  /
 add CONSTRAINT pk_' || TO_CHAR(tabid) PRIMARY KEY (STIME,NE_ID,OBJ_VC4);
                                       *
ERROR at line 6:
ORA-06550: line 6, column 40:
PLS-00103: Encountered the symbol "PRIMARY" when expecting one of the following:
. ( * % & = - + ; < / > at in is mod remainder not rem return
returning <an exponent (**)> <> or != or ~= >= <= <> and or
like LIKE2_ LIKE4_ LIKEC_ between into using || bulk member
SUBMULTISET_
ORA-06550: line 7, column 1:
PLS-00103: Encountered the symbol "END" when expecting one of the following:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor

Can anybody help me out?

Regards

[Updated on: Fri, 20 April 2007 06:03] by Moderator

Report message to a moderator

Re: generate Primary key dynamically [message #232374 is a reply to message #232372] Fri, 20 April 2007 06:08 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
try this


 DECLARE
 tabid NUMBER;
 BEGIN
 select OBJECT_ID into tabid from user_objects where OBJECT_NAME='Q_XDM_VC4_EC42';
 EXECUTE IMMEDIATE 'alter table stsmanager.Q_XDM_VC4_EC42 add CONSTRAINT pk_' || TO_CHAR(tabid)||' PRIMARY KEY(STIME,NE_ID,OBJ_VC4) ';
 END;



i have try this it is working fine

DECLARE
 tabid NUMBER;
 BEGIN
 select OBJECT_ID into tabid from user_objects where OBJECT_NAME='EMP';
 EXECUTE IMMEDIATE 'alter table EMP add CONSTRAINT pk_' || TO_CHAR(tabid)||' PRIMARY KEY (EMPNO,ENAME) ';
 END;
 



--Yash
Re: generate Primary key dynamically [message #232375 is a reply to message #232372] Fri, 20 April 2007 06:09 Go to previous messageGo to next message
kssarayu
Messages: 18
Registered: March 2007
Junior Member

DECLARE
tabid NUMBER;
BEGIN
select OBJECT_ID into tabid from user_objects where object_NAME='TAB1';
EXECUTE IMMEDIATE 'alter table TAB add CONSTRAINT pk_' || TO_CHAR(tabid) ||' PRIMARY KEY (TABLE_NAME)';
END;


I think you missing single quote for PRIMARY KEY clause.

Re: generate Primary key dynamically [message #232380 is a reply to message #232375] Fri, 20 April 2007 06:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I think you are missing the point completely.
You cannot just go ahead and generate primary keys at will. You have to decide on which columns you want them, on a per table basis.
Not something to use execute immediate for.
Re: generate Primary key dynamically [message #232394 is a reply to message #232372] Fri, 20 April 2007 06:57 Go to previous message
guenni_ac
Messages: 3
Registered: April 2007
Location: Aachen - Germany
Junior Member
Thanks to all,
this works fine for me too.
Regards
Previous Topic: Bulk Collect Query
Next Topic: differnce between views n materialized views
Goto Forum:
  


Current Time: Fri Dec 09 04:13:15 CST 2016

Total time taken to generate the page: 0.22395 seconds