Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Oct 2003
    Posts
    18

    Red face Unanswered: Need Database Expert Help!!!

    Hi,

    I need to write an Oracle SQL script to delete records from the selected tables (condition is where the TABLE_TYPE column value of MAP_TABLE table is equal to 'A', i.e. EMPLOYEE and ACCOUNT). But the table names are stored in TABLE_NAME column of MAP_TABLE.

    So, need advice from expert here on how to write the delete statement for EMPLOYEE and ACCOUNT tables by using the TABLE_NAME column values that retrieved from MAP_TABLE.

    Also how to use the TABLE_NAME column values to select a particular field, eg. EMPLOYEE.EMP_NO.

    Thanks in advance.

    SCHEMA OF MAP_TABLE:
    TABLE_NUMBER TABLE_NAME TABLE_TYPE
    1 EMPLOYEE A
    2 ACCOUNT A
    3 STOCK B
    4 RAW C
    ... ... ...
    ... ... ...
    ... ... ...

  2. #2
    Join Date
    Sep 2003
    Posts
    27
    If you run a query like this:

    set heading off

    select 'Delete from '||table_name||' where x=x;'
    from MAP_TABLE
    where table_type = 'A';


    This will create the statements you need to delete the rows from the tables. Simply run the generated commands. Obviously, you will need to put in your own condition in place of "x=x".

  3. #3
    Join Date
    Oct 2003
    Posts
    18

    Red face

    All,

    Appreciate your help on my posted SQL script question. As need it urgent, hope to get your reply soonest. Thanks a million.


    tdrevans,

    I have tested the SQL that you mentioned. But, that is just a select statement which return a DELETE sql string. So it doesn't perform deletion of records. Any way, thanks so much for your help.

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    You might try reviewing and/or executing the output of tdrevans query. He's a better man than me for understanding your requirements :-)

    You might need it a single call to a procdure which dynamically creates/executes SQL as required, but I'm not sure.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  5. #5
    Join Date
    Oct 2003
    Posts
    18
    billm,

    I don know to get the table names (EMPLOYEE and ACCOUNT) from variable and perform deletion of records on the tables in script. Appreciate your advise. Thanks.

    By the way, in this degree assignment question, I need to write a script without using any function and procedure. I have tested the previous answer, but it jus return an SQL string.

  6. #6
    Join Date
    Dec 2003
    Posts
    18

    Re: Need Database Expert Help!!!

    Hi,

    pass the table type to this procedure like 'A','B' etc.
    it will delete the records.


    create or replace procedure del_rec (tab_type char)
    as
    stat varchar2(1000):='';
    cursor tab is
    select table_name,table_type from map_table;
    begin
    for del_tab in tab loop
    if del_tab.table_type = tab_type then
    stat:='delete from '||del_tab.table_name;
    else
    null;
    end if;
    execute immediate stat;
    end loop;
    exception when others then
    dbms_output.put_line('Errors:-'||sqlerrm);
    end;






    Originally posted by dbnm
    Hi,

    I need to write an Oracle SQL script to delete records from the selected tables (condition is where the TABLE_TYPE column value of MAP_TABLE table is equal to 'A', i.e. EMPLOYEE and ACCOUNT). But the table names are stored in TABLE_NAME column of MAP_TABLE.

    So, need advice from expert here on how to write the delete statement for EMPLOYEE and ACCOUNT tables by using the TABLE_NAME column values that retrieved from MAP_TABLE.

    Also how to use the TABLE_NAME column values to select a particular field, eg. EMPLOYEE.EMP_NO.

    Thanks in advance.

    SCHEMA OF MAP_TABLE:
    TABLE_NUMBER TABLE_NAME TABLE_TYPE
    1 EMPLOYEE A
    2 ACCOUNT A
    3 STOCK B
    4 RAW C
    ... ... ...
    ... ... ...
    ... ... ...

  7. #7
    Join Date
    Oct 2003
    Posts
    18
    rishikant_tiwar ,

    Thanks a lot for your help.

    appreciate if you could advise on how to display the multiple records of the del_tab.table_name table to an output file before the deletion?

    Many Thanks.

  8. #8
    Join Date
    Sep 2003
    Posts
    156

    Re: Need Database Expert Help!!!

    Originally posted by dbnm
    Hi,

    I need to write an Oracle SQL script to delete records from the selected tables (condition is where the TABLE_TYPE column value of MAP_TABLE table is equal to 'A', i.e. EMPLOYEE and ACCOUNT). But the table names are stored in TABLE_NAME column of MAP_TABLE.

    So, need advice from expert here on how to write the delete statement for EMPLOYEE and ACCOUNT tables by using the TABLE_NAME column values that retrieved from MAP_TABLE.

    Also how to use the TABLE_NAME column values to select a particular field, eg. EMPLOYEE.EMP_NO.

    Thanks in advance.

    SCHEMA OF MAP_TABLE:
    TABLE_NUMBER TABLE_NAME TABLE_TYPE
    1 EMPLOYEE A
    2 ACCOUNT A
    3 STOCK B
    4 RAW C
    ... ... ...
    ... ... ...
    ... ... ...
    --------------------------------------------------------------------------------
    nuke.txt

    this procedure will drop all tables and sequences, including those that were
    not set up by the create script!!!

    create new package spec called 'a' - import spec text...
    create new package body called 'a' - import body text...

    ...compile...


    SQL> execute a.b
    say goodbey to tables and sequences..

    --------------------------------------------------------------------------------

    Spec...

    PACKAGE A
    IS

    PROCEDURE B;

    END;

    --------------------------------------------------------------------------------

    Body...

    --nuke tables...

    PACKAGE BODY A IS

    PROCEDURE B
    IS
    v_cursor INTEGER;
    v_return INTEGER;
    v_stmt VARCHAR(100);
    v_tname VARCHAR(30);
    v_ttype VARCHAR(30);
    v_count NUMBER := 0;

    CURSOR c_name IS
    SELECT TABLE_NAME, TABLE_TYPE
    FROM cat;

    BEGIN
    FOR cur_rec IN c_name LOOP

    v_count := v_count + 1;
    v_stmt := 'drop '|| cur_rec.TABLE_TYPE ||' ' ||cur_rec.TABLE_NAME;

    IF cur_rec.TABLE_TYPE = 'TABLE' THEN
    v_stmt := v_stmt ||' cascade constraints';
    END IF;

    v_cursor := DBMS_SQL.OPEN_CURSOR;

    DBMS_SQL.PARSE(v_cursor, v_stmt, DBMS_SQL.NATIVE);
    v_return := DBMS_SQL.EXECUTE(v_cursor);
    DBMS_SQL.CLOSE_CURSOR(v_cursor);

    END LOOP;

    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR OCCURRED'||CHR(10)||v_count||CHR(10)||SQLCODE||' - '||SQLERRM);
    END;
    END;

    --------------------------------------------------------------------------------

    This script is intended for reading and modifying, since it's for an exam question - run it at your own peril... it will nuke all your tables and sequences...
    rgs,

    Ghostman

  9. #9
    Join Date
    Oct 2003
    Posts
    18

    Red face

    I have done the SQL script using the method that mentioned by rishikant_tiwar as shown below,
    but need to display the multiple records (i.e. to include all fields of the records) to an Output file before deletion.
    Hope someone can help here, as need it urgent. Thanks.

    create or replace procedure del_rec (tab_type char)
    as
    stat varchar2(1000):='';
    cursor tab is
    select table_name,table_type from map_table;
    begin
    for del_tab in tab loop
    if del_tab.table_type = tab_type then
    stat:='delete from '||del_tab.table_name;
    else
    null;
    end if;
    execute immediate stat;
    end loop;
    exception when others then
    dbms_output.put_line('Errors:-'||sqlerrm);
    end;
    Last edited by dbnm; 12-10-03 at 21:32.

  10. #10
    Join Date
    Dec 2003
    Posts
    18
    hi,


    I think u want to make a log kind of file for deleted records, am i right ? if yes than
    my advise is create one directory in your required place and create a text file which
    will contain all deleted records.

    For doing this follow the steps:-

    create the folder in anywhere for example c:\oracle\del_log.
    here del_log is a folder which i menullay created in c:\oracle directory.

    than start sqlplus in ur user;
    create directory DEL_LOG as 'C:\oracle\del_log';




    create or replace procedure del_rec (tab_type char)
    as

    log_file utl_file.file_type; -- File in your c:\oracle\del_log.
    log_record varchar2(1000):='';-- This will create the records in log_file.

    stat varchar2(1000):='';

    cursor tab is
    select table_name,table_type from map_table;
    begin

    log_file:=utl_file.fopen('DEL_LOG','l_file.txt','a ');
    for del_tab in tab loop
    if del_tab.table_type = tab_type then

    log_record:=del_tab.table_name||' , '||del_tab.table_type;
    utl_file.putf(log_file,log_record);
    utl_file.new_line(log_file,1);
    stat:='delete from map_table where table_name='||''''||del_tab.table_name||'''';
    execute immediate stat;

    end if;
    end loop;
    utl_file.fclose(log_file);
    exception when others then
    dbms_output.put_line('Errors:-'||sqlerrm);
    end ;










    Originally posted by dbnm
    I have done the SQL script using the method that mentioned by rishikant_tiwar as shown below,
    but need to display the multiple records (i.e. to include all fields of the records) to an Output file before deletion.
    Hope someone can help here, as need it urgent. Thanks.

    create or replace procedure del_rec (tab_type char)
    as
    stat varchar2(1000):='';
    cursor tab is
    select table_name,table_type from map_table;
    begin
    for del_tab in tab loop
    if del_tab.table_type = tab_type then
    stat:='delete from '||del_tab.table_name;
    else
    null;
    end if;
    execute immediate stat;
    end loop;
    exception when others then
    dbms_output.put_line('Errors:-'||sqlerrm);
    end;

  11. #11
    Join Date
    Oct 2003
    Posts
    18
    rishikant_tiwar,

    Yes, u are right that I want to create a log to store deleted records. Which your reply have help me a lot.

    I am still finding on how to display the records in log file WITHOUT specifying the list of field names. Wondering whether tat is possible?

    Thank so much for your many help.

  12. #12
    Join Date
    Dec 2003
    Posts
    18
    hi,

    Can u give me a example how the records should display in log file than i can help u out.



    Originally posted by dbnm
    rishikant_tiwar,

    Yes, u are right that I want to create a log to store deleted records. Which your reply have help me a lot.

    I am still finding on how to display the records in log file WITHOUT specifying the list of field names. Wondering whether tat is possible?

    Thank so much for your many help.

  13. #13
    Join Date
    Oct 2003
    Posts
    18
    rishikant_tiwar,

    There is No specific format required, i.e. can be displayed in any format.

    I am still finding whether can display the records of variable tables without specifying the list of column names in SQL statement script, eg,"log_record:=del_tab.*",
    and not "log_record:=del_tab.table_name||' , '||del_tab.table_type". So that adding of new table columns in future will not have changes on the sql script to add in addtional column name.

    So, need advice from you on how to the display the records WITHOUT specifying list of column names in SQL statement script?

    Warmest Thanks.
    Last edited by dbnm; 12-15-03 at 04:34.

  14. #14
    Join Date
    Dec 2003
    Posts
    18
    Hi,

    now i am giving u full procedure which is using dynamic column name u need not to worry about no of column it will fetch all the column and write in file. Here tab_type parameter is the type like 'A','B' etc which type of table u want to delete.
    ---------------------------------------------------------------

    create or replace procedure del_rec (tab_type char)
    as
    log_file utl_file.file_type;

    tmp_log_record varchar2(2000):='';
    log_record varchar2(2000):='';
    stat varchar2(1000):='';
    col_name varchar2(20):='';


    cursor tab
    is
    select * from map_table;

    cursor colnm
    is
    select column_name from user_tab_columns where table_name='MAP_TABLE';
    begin
    log_file:=utl_file.fopen('SCOTT','l_file.txt','a') ;
    for del_tab in tab loop
    if del_tab.table_type = tab_type then
    BEGIN
    for col_nm in colnm loop
    col_name:=col_nm.column_name;
    stat:=' select '||col_name||' from MAP_TABLE where table_name='||''''||del_tab.table_name||'''';
    execute immediate stat into tmp_log_record;
    log_record:=log_record||','||tmp_log_record;

    end loop;

    exception when others then
    dbms_output.put_line('YOURS Errors:-'||sqlerrm);
    END;
    utl_file.putf(log_file,log_record);
    utl_file.new_line(log_file,1);
    stat:='delete from map_table where table_name='||''''||del_tab.table_name||'''';
    execute immediate stat;
    tmp_log_record:='';
    log_record:='';
    end if;
    end loop;
    utl_file.fclose(log_file);
    exception when others then
    dbms_output.put_line('YOUR Errors:-'||sqlerrm);
    end ;


    It will definietly work.








    ---------------------------------------------------------
    Originally posted by dbnm
    rishikant_tiwar,

    There is No specific format required, i.e. can be displayed in any format.

    I am still finding whether can display the records of variable tables without specifying the list of column names in SQL statement script, eg,"log_record:=del_tab.*",
    and not "log_record:=del_tab.table_name||' , '||del_tab.table_type". So that adding of new table columns in future will not have changes on the sql script to add in addtional column name.

    So, need advice from you on how to the display the records WITHOUT specifying list of column names in SQL statement script?

    Warmest Thanks.

  15. #15
    Join Date
    Oct 2003
    Posts
    18
    rishikant_tiwar,

    Yes, the SQL that you mentioned able to display records using variable table and column name. You are really expert in oracle and PLSQL, because even my DBA also suprise when seen your SQL. Appreciate your help so much. By the way, is that possible to display the record with 37716 characters or even more, because VARCHAR2 only allow up to 32767 character right?

    Before using the above output file method that you mentioned,
    actually I have try to output the dynamic sql result in SQL plus using spool method, i.e. spool C:\mylogfile.log and spool off, but I only can see the "PL/SQL procedure successfully completed" message displayed in file and with NO output record. So, wondering why there is No output record. Until now, I still can't find the reason. Do you have any idea? Appreciate your expertise on this.

    Warmest Thanks.
    Last edited by dbnm; 12-16-03 at 00:24.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •