sp_generate_inserts for oracle

Most SQL developers know and 开发者_如何学编程use Narayana Vyas Kondreddi's sp_generate_inserts from http://vyaskn.tripod.com/code/generate_inserts.txt

Is there something similar for Oracle?

With Oracle comes the tool called SQL Developer, and this has the facility to generate insert scripts from table data.

if you wanted an package to do it all for you (while SQL Developer, TOAD, etc can all do it ad-hoc); but the closest thing to the sp_generate_inserts that I have seen is:

"PL/SQL Interface Generator" http://sourceforge.net/projects/plsqlintgen/

after running the two scripts in the download all you have to do it:

CREATE TABLE XYZ(AA VARCHAR2(50) , BB NUMBER(10,2) );

declare 

TPT XTAB_PKG.TAB_PARAMETERS_TYP ;
S   xtab_pkg.SQLDATA_TYP ;
E   XTAB_PKG.xerror_typ ;
BEGIN
        TPT.OWNER := 'ownerName';
        TPT.TABLE_NAME := 'xyz';
        TPT.PACKAGE_NAME := 'xyz_pkg';
        TPT.FILE_PATH := NULL ;
        TPT.FILE_NAME := NULL;
        xtab_pkg.Generate(tpt,s,e);

         for i in s.first..s.last
                 loop
                      DBMS_OUTPUT.PUT_LINE(s(I));
                 end loop;      

END ;

and this will emit the Create SPEC/BODY for the CRUD for the inputted table (this does not create the package, it just stores the package script in the s variable as such:

e.g.

/* PL/SQL Interface for Table xyz*/
CREATE SEQUENCE xyz_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999999999999999 NOCYCLE NOORDER NOCACHE 
/
create or replace package xyz_pkg is 
SUCCESS     constant    varchar2(20) := 'SUCCESS'; ERROR       constant    varchar2(20) := 'ERROR'; WARNING     constant    varchar2(20) := 'WARNING'; 
type xerror_typ is record (status      varchar2(50),message     varchar2(2000));
 type xyz_typ is record 
(
);
type xyz_ref is r.......
Procedure Add (r in out xyz_typ, p_commit in boolean, e   out    xerror_typ);
Procedure Add (t in out xyz_tab, p_commit in boolean, e   out    xerror_typ);
Procedure Del (p_ in xyz.%type, p_commit in boolean, e   out    xerror_typ);
Procedure Del (t in  xyz_tab, p_commit in boolean, e   out    xerror_typ);
Procedure Upd (r in out xyz_typ, p_commit in boolean, e   out    xerror_typ);
Procedure Upd (t in out xyz_tab, p_commit in boolean, e   out    xerror_typ);

Even if this doesn't completely fit your need, you ought to be able to extract the logic to your own means. (just a note, I am not affiliated with this project at all)