1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Please help me i want to make sql

Discussion in 'SQL PL/SQL' started by tomoaki, Aug 8, 2015.

  1. tomoaki

    tomoaki Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Japan
    i want to check data in oracle .

    procedure:
    CREATE OR REPLACE FUNCTION fun_str(str IN VARCHAR2)
    RETURN VARCHAR2 IS
    str_moji VARCHAR2(4000);

    BEGIN
    str_moji := '';

    FOR i IN 1..LENGTH(str) LOOP
    IF ASCII(SUBSTR(str, i, 1)) >= TO_NUMBER('F040','XXXX') THEN
    str_moji := str_moji || SUBSTR(str, i, 1);
    END IF;
    END LOOP;

    RETURN str_moji;
    END;
    /

    i want to make sql for check all data in all table ... plese complete sql file below


    declare
    tablename varchar2(20);
    columnname varchar2(20);
    sql1 varchar2(2000);

    v_columnname varchar(20);

    type c_cur_type is ref cursor;
    c c_cur_type;

    cursor tbl_list is
    select table_name from dba_ta bles where
    owner='&1' order by table_name;

    begin
    for rec in tbl_list loop

    for vrec in (
    select data_type,column_name,from dba_tab_columns where table_name=rec.table_name
    and (data_type = 'VARCHAR2' or data_type = 'CHAR')

    ) loop
    tablename := rec.table_name;
    columnname := vrec.column_name;
    sql1 := 'select ' || 'fun_str(' || columnname || ') from ' || tablename
    || ' where fun_str(' || columnname || ') is not null';


    ..................................................

    end loop;
    end loop;
    end;
    /
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    It looks like you are attempting to look through all character columns in a given schema to locate columns than contain a subset of ASCII characters. A better/faster option than your FUN_STR function is probably the TRANSLATE function -- if you can come up with a list of both the characters to include and the characters to exclude. For example:

    Code (Text):

    TRANSLATE('Fred',
              'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
              'ABCDEFGHIJKLMNOPQRSTUVWXYZ');
    would return 'F'. The first parameter is the string to be processed, the second is the template for possible characters to be processed, and the third is what the first string should be translated to. Any characters that are missing from the 'translate to' list that were in the template are removed. The end result will work much like your FUN_STR function in that if the string to be processed does not contain any of the characters you are looking for, it will return NULL.

    Your current proposed process is likely to be REALLY slow unless you have a fairly small set of tables and data to search.