sp_generate_inserts for oracle

Question

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?

Answer 1

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

Answer 2

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)