How to display the script of an object?

[aljubicic]

Hi,

I am developing an application that will need to display the script for such objects as stored procs, triggers, schemas, seqeunces etc. How can I extract the script for a table or stored proc say that will show all in the script including indexes, constraints, grants etc.

I would like it to display a script as below for each of those objects..

CREATE TABLE IWS_COMPANY
(
COMPANY_CD VARCHAR2(3 BYTE) NOT NULL,
COMPANY_NAME VARCHAR2(30 BYTE) NOT NULL,
BARCODE VARCHAR2(10 BYTE),
IS_UNKNOWN VARCHAR2(1 BYTE) NOT NULL,
IS_ACTIVE VARCHAR2(1 BYTE) DEFAULT 'Y' NOT NULL,
INSERT_USER_CD VARCHAR2(15 BYTE) DEFAULT USER NOT NULL,
INSERT_TIME DATE DEFAULT SYSDATE NOT NULL,
UPDATE_USER_CD VARCHAR2(15 BYTE) DEFAULT USER,
UPDATE_TIME DATE DEFAULT SYSDATE
)
TABLESPACE IWR_DATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 256K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;


CREATE OR REPLACE TRIGGER IWS_COMPANY_TBIU
BEFORE INSERT OR UPDATE ON IWS_COMPANY
FOR EACH ROW
/******************************************************************************
NAME: IWS_COMPANY_TBIU
PURPOSE: To assign the insert or update time automatically and enforce
upper case for code
REVISIONS:
Ver Date Author Description
------- --------- --------------- ------------------------------------
1.0 16/09/04 WIC Initial version.
******************************************************************************/
DECLARE d_CUR_DATETIME DATE;
BEGIN
:NEW.COMPANY_CD := UPPERNEW.COMPANY_CD);
d_CUR_DATETIME := SYSDATE;
IF INSERTING THEN
:NEW.INSERT_USER_CD := UPPER(USER);
:NEW.INSERT_TIME := d_CUR_DATETIME;
END IF;
:NEW.UPDATE_USER_CD := UPPER(USER);
:NEW.UPDATE_TIME := d_CUR_DATETIME;

EXCEPTION
WHEN OTHERS THEN
NULL;
END IWS_COMPANY_TBIU;
/
SHOW ERRORS;



CREATE OR REPLACE TRIGGER tU_IWS_COMPANY after UPDATE on IWS_COMPANY for each row
-- ERwin Builtin Mon Mar 07 15:32:53 2005
-- UPDATE trigger on IWS_COMPANY
declare numrows INTEGER;
begin
/* ERwin Builtin Mon Mar 07 15:32:53 2005 */
/* IWS_COMPANY R/146 IWS_PRODUCT ON PARENT UPDATE CASCADE */
if
/* ld.COMPANY_CD <> :new.COMPANY_CD */
ld.COMPANY_CD <> :new.COMPANY_CD
then
update IWS_PRODUCT
set
/* IWS_PRODUCT.COMPANY_CD = :new.COMPANY_CD */
IWS_PRODUCT.COMPANY_CD = :new.COMPANY_CD
where
/* IWS_PRODUCT.COMPANY_CD = ld.COMPANY_CD */
IWS_PRODUCT.COMPANY_CD = ld.COMPANY_CD;
end if;


-- ERwin Builtin Mon Mar 07 15:32:53 2005
end;
/
SHOW ERRORS;



ALTER TABLE IWS_COMPANY ADD (
PRIMARY KEY
(COMPANY_CD)
USING INDEX
TABLESPACE IWR_INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 256K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
));


GRANT SELECT ON IWS_COMPANY TO IWR_READ;

GRANT SELECT ON IWS_COMPANY TO IWS_REPORT_ROLE;


Is there any way of doing this from an sql query or by any other means that can be called from a .NET app?

Thanks
Anthony

 

[carp]

I think you will need to create a stored function that generates the script and returns it as a VARCHAR2 or CLOB. If I were going to write such a thing, I would probably try to use the Oracle-provided package DBMS_METADATA to do the heavy lifting for me. My function would simply make calls to this package's functions, string the results together, and return the resultant script.