Home » SQL & PL/SQL » SQL & PL/SQL » generate DDL
icon5.gif  generate DDL [message #304327] Tue, 04 March 2008 21:31 Go to next message
nagrcm
Messages: 3
Registered: March 2008
Junior Member
Hi Guys,

Can you please give me some ideas in writing a PL/SQL code in generating a DDL for the indexes on a table where table name is passed as an input parameter and get the DDL generated to a file using a naming convention.

Thanks
Nag
Re: generate DDL [message #304329 is a reply to message #304327] Tue, 04 March 2008 21:46 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
just search the forum for dbms_metadata.get_ddl
Re: generate DDL [message #304466 is a reply to message #304327] Wed, 05 March 2008 05:45 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
create or replace procedure proc1(tname in varchar2,cname in varchar2)
is
v varchar2(60);
begin
v:='create index emp_ind on '|| tname ||' ( ' || cname || ' )';
execute immediate v;
end;
Re: generate DDL [message #304470 is a reply to message #304466] Wed, 05 March 2008 06:01 Go to previous messageGo to next message
nagrcm
Messages: 3
Registered: March 2008
Junior Member
Hi,

I did not understand what cname is doing in that query.

Thanks
Nag
Re: generate DDL [message #304471 is a reply to message #304327] Wed, 05 March 2008 06:02 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
tname - table name
cname - column name
Re: generate DDL [message #304474 is a reply to message #304471] Wed, 05 March 2008 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you know what is SQL INJECTION?

Regards
Michel
Re: generate DDL [message #304478 is a reply to message #304474] Wed, 05 March 2008 06:19 Go to previous messageGo to next message
nagrcm
Messages: 3
Registered: March 2008
Junior Member
No I don't know what is an SQL INJECTION is
Re: generate DDL [message #304481 is a reply to message #304478] Wed, 05 March 2008 06:30 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
Then you better read this :
SQL INJECTION

regards,
Re: generate DDL [message #304506 is a reply to message #304466] Wed, 05 March 2008 07:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
spmano1983 wrote on Wed, 05 March 2008 12:45
create or replace procedure proc1(tname in varchar2,cname in varchar2)
is
v varchar2(60);
begin
v:='create index emp_ind on '|| tname ||' ( ' || cname || ' )';
execute immediate v;
end;

How exactly does this answer the original question?
Quote:
generating a DDL for the indexes on a table where table name is passed as an input parameter and get the DDL generated to a file using a naming convention

Re: generate DDL [message #304845 is a reply to message #304506] Thu, 06 March 2008 22:25 Go to previous messageGo to next message
wy-ak47
Messages: 2
Registered: March 2008
Junior Member
create or replace procedure p_createindex (tname in varchar2,cname in varchar2,idname in varchar2)
is
cursor_handle pls_integer;
fdback pls_integer;
begin
cursor_handle :=dbms_sql.open_cursor;
dbms_sql.parse(cursor_handle,'create index '|| idname ||' on '|| tname ||' ( '|| cname ||' ) ',dbms_sql.native );
fdback:=dbms_sql.execute(cursor_handle);
dbms_sql.close_cursor(cursor_handle);
end p_createindex;

tnam => table name
cname => column name
idname => index name

hope that makes sense !

[Updated on: Thu, 06 March 2008 22:30]

Report message to a moderator

Re: generate DDL [message #304849 is a reply to message #304845] Thu, 06 March 2008 22:46 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> hope that makes sense !
I don't think so.
Paraphrasing Frank's quote
Quote:

generating a DDL for the indexes on a table where table name is passed as an input parameter and get the DDL generated to a file using a naming convention

The OP is looking to EXTRACT the ddl of an existing Index and not trying to CREATE an index.
Previous Topic: UNION SELECT which removes results from the second table if they exist in the first
Next Topic: Add amounts for similar grouped items?
Goto Forum:
  


Current Time: Sun Dec 11 00:25:45 CST 2016

Total time taken to generate the page: 0.09920 seconds