I pass in 2 parameters when calling the script, first the table name and second a name for the temp file on the unix box. It does not fully work if the number or xmltype columns are null but an addition of a decode around these should do the trick. I then run the file by referencing the url + filename. As I'm already spooling to a log file and am only on Oracle 9i the script spools its generated sql statmenet to the unix box to an area which is accessible via a url. This is mainly incase a tester re-runs a script without backing up their data.
#Insert query in pl sql developer install
I've recently being working on a script to be called from the main install script to create insert statements from data within a table before it is dropped. Hi, we have a requirement that install scripts create a spool file of all the activities. V_ref_cur_output := v_insert_list||v_ref_cur_output V_ref_cur_output := replace(v_ref_cur_output,'null,)','null,null)') V_ref_cur_query := 'SELECT '||v_ref_cur_columns||' FROM '||v_table_name V_insert_list := 'INSERT INTO '||v_table_name||' ('||v_column_list||') VALUES ' V_ref_cur_columns := substr(v_ref_cur_columns,8)
V_column_list := ltrim(v_column_list,',') V_column_list := v_column_list||','||i.column_name V_table_name varchar2(30) := 'EMP' - Your TablenameĬursor c1 is select column_name, data_type from user_tab_columns where table_name = v_table_name order by column_id There are number of workarounds which can be implemented to avoid this error.
So, if the length of 'insert into ' exceeds 255, the query will fail. Also note that dbms_output is restricted to 255 characters.
I have used very limited data-types in the solution (number, date and varchar2 only).
#Insert query in pl sql developer code
Following sample code can be used to generate insert statement.