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!

Need help for my sql

Discussion in 'SQL PL/SQL' started by winggins, Sep 14, 2018.

  1. winggins

    winggins Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    Location:
    Steamboat Springs, CO
    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. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,652
    Likes Received:
    375
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Interesting that you demand that someone from this forum complete your work for you; that is NOT how this forum operates.

    You ARE on the right track as you need to loop through all of the tables in the database, but you also need to loop through all of the COLUMNS as well., The dba_tab_columns view is a good place to start.