tool

[RamanaSQL]

I know it sounds different but looking for a tool which speeds up up our process

Is there a tool or software where we give the table name it creates a procedure with create , slect , update and delete stmt in that procudere.

Pls help

[gandolf989]

Originally posted by RamanaSQL
I know it sounds different but looking for a tool which speeds up up our process

The best tools that you can use to speed up your processing are tkprof, sql expert and statspack. There is no replacement for good tuning skills.

Originally posted by RamanaSQL
Is there a tool or software where we give the table name it creates a procedure with create , select , update and delete stmt in that procudere.

Pls help

You can create dynamic sql which will creates package, preferably, or procedure, that would act as an interface to your program, which would handle all DML. If you are programming in Java, many development environments, including WebSphere, have functionality that creates generic code for you. Some have code that will create all of the Enterprise JavaBEans for you once you register the tables in you ide.

There are also sql tools which will do some of what you want. But if you really want to learn Oracle, start writing dynamic sql.

[RamanaSQL]

thank you for the quick reply

we are not tuning any thing.yes i have created lot of other procedures.

Currently we are revisiting lot of code where we need select ,create update,delete for all the tables , which takes lot of time,if start creating.So wondering is there any tool like store procedure wizard.All it does it speeds our time lines.No we are not using Java.

I found many for Sql server but not for Oracle.

[gandolf989]

Sorry I used the wrong table name in the first cursor.

DECLARE
   CURSOR c_user_tables IS
   SELECT table_name
     FROM user_tables
    ORDER BY table_name;
   v_is_column_name       BOOLEAN := TRUE;
   v_is_not_column_name   BOOLEAN := FALSE;

   PROCEDURE output_columns 
    ( p_table_name  IN user_tab_columns.table_name%TYPE,
      p_is_column_name IN BOOLEAN )
   IS
      CURSOR c_user_tab_columns IS
      SELECT column_name 
        FROM user_tab_columns
       WHERE table_name = p_table_name
       ORDER BY column_id;
      v_column_values  VARCHAR2(2000);
      v_current_value  VARCHAR2(2000);
      v_line_length    BINARY_INTEGER;
   BEGIN
      FOR r_row IN c_user_tab_columns
      LOOP
         IF p_is_column_name
         THEN
            v_current_value  := r_row.column_name;
         ELSE
            v_current_value  := 'P_' || p_table_name ||'.'|| r_row.column_name;
         END IF;

         v_line_length := NVL( LENGTH(v_column_values), 0 );
         IF    v_line_length = 0 
         THEN
            v_column_values := v_current_value;
         ELSIF v_line_length > 70
         THEN
            DBMS_OUTPUT.PUT_LINE( v_column_values      || ',' );
            v_column_values := LPAD(' ', 10, ' ')      || v_current_value;
         ELSE
            v_column_values := v_column_values || ', ' || v_current_value;
         END IF;
      END LOOP;
      v_column_values := RTRIM(v_column_values, ',');
      DBMS_OUTPUT.PUT_LINE( v_column_values );
   END; 
BEGIN
   DBMS_OUTPUT.ENABLE(1000000);
   FOR r_ut IN c_user_tables
   LOOP
      DBMS_OUTPUT.PUT_LINE(
         'CREATE OR REPLACE PROCEDURE ' || r_ut.table_name || '_INS' );
      DBMS_OUTPUT.PUT_LINE(
         ' ( p_'            || r_ut.table_name ||
         ' IN OUT NOCOPY '  || r_ut.table_name || '%ROWTYPE )' );
      DBMS_OUTPUT.PUT_LINE('IS');
      DBMS_OUTPUT.PUT_LINE('BEGIN');
      DBMS_OUTPUT.PUT_LINE('INSERT INTO ' || r_ut.table_name || ' ( ');
      output_columns( r_ut.table_name, v_is_column_name );
      DBMS_OUTPUT.PUT_LINE(') VALUES (');
      output_columns( r_ut.table_name, v_is_not_column_name );
      DBMS_OUTPUT.PUT_LINE(');');
      DBMS_OUTPUT.PUT_LINE('END ' || r_ut.table_name || '_INS;');
      DBMS_OUTPUT.PUT_LINE('/');
      DBMS_OUTPUT.PUT_LINE(' ');
   END LOOP;
END;
/

[RamanaSQL]

gandolf989 ..Thank you very much for your time.much appreciated.I will run code you provided

[RamanaSQL]

Hi
I get the following error when i execute

ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes

Is there a way to run this program for one or two tables , so that my buffer will not overflow.I manually put in the table names but still get errors??

[gandolf989]

Originally posted by RamanaSQL
Hi
I get the following error when i execute

ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes

Is there a way for one or two tables , so that my buffer will not overflow

You should either do fewer tables at one time of use utl_file instead.

[RamanaSQL]

ok

also it displays the out put too many times instead of one per table

am i missing some thing here

Thanks for the help

[gandolf989]

Originally posted by RamanaSQL
ok

also it displays the out put too many times instead of one per table

am i missing some thing here

Thanks for the help

Sorry I used the wrong table name in the first cursor.
I made the change above.

[RamanaSQL]

thanks for your time