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!

Writing and calling PROCEDURES and passing table and column names.

Discussion in 'SQL PL/SQL' started by PapaGeek, Mar 4, 2013.

  1. PapaGeek

    PapaGeek Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Please excuse me for mixing C++ style with a little bit of PL_SQL, but I hope my question comes over correctly and that there is a way to do this in our ORACLE database. My plan is the run the SQL statement as needed to load the necessary procedures onto our daily backup database. They will get deleted each night, and I can’t save them on the production database.

    I am dealing with a fairly complex table constraint structure. I wrote a script to find all the constraints of one table within all the other tables (who-is-referencing-this-item-constraints-t7777 on this website) (they won't let me port the real link yet) and it works great, but it does indicate that there are a lot of places where the “ITEM” is reference “AS” ITEM_SOLD or ITEM_BOUGHT or some other column name.

    Now that I know where everything is referenced, I want to simplify things a bit.

    I want to be able to store a temporary stored procedure for when I need it, and then enter something like:

    CALL WHERE_IS_ITEM_REFERENCED(‘123’);

    And the response should be something like:

    123 is referenced as ITEM in STORE.Customer 5 times
    123 is referenced as ITEM_BOUGHT in BACK_OFFICE.Vendor 3 times

    Nothing is displayed for the tables that do not reference this item ID

    PROCEDURE WHERE_IS_ITEM_REFERENCED (
    pItem IN thisItem )
    BEGIN
    call INNER_PROC(‘STORE.Customer’,’ ITEM’,thisItem);
    call INNER_PROC(‘BACK_OFFICE.Vendor’,’ ITEM_BOUGHT’,thisItem);
    call INNER_PROC(‘ STORE.Sales,’ ITEM,thisItem);
    END

    PROCEDURE INNER_PROC(
    pTable IN tableName,
    pColumn IN columnName,
    pItem IN thisItem )
    BEGIN
    SELECT COUNT(*) FROM pTable WHERE pColumn=thisItem;
    If count > 0
    Output thisItem referenced as pColumn in pTable COUNT times;
    END



    How is this supposed to be written?
     
  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    procedure <name>(<variable> IN/OUT/IN OUT <type>, ... ) IS

    I think you'll need to declare a variable for the count and a sql statement, then BEGIN.

    You then 'could' to build the string holding a SELECT...INTO...FROM stmt and do an EXECUTE_IMMEDIATE on that string.

    <OR>

    Declare a cursor w/ a variable so you can Fetch the value into a variable...test the value and then output the appropriate message.


    HTH

    CJ
     
    PapaGeek likes this.
  3. PapaGeek

    PapaGeek Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Thanks for the reply. Based on that, here is a first attempt, but as a newbie of course it does not work. Can someone comment on the syntax and the order in which things are written?


    PROCEDURE SHOWALL (pItem IN VARCHAR2(8) ) IS
    call SHOWEACH('TABLE1',pItem);
    call SHOWEACH('TABLE2',pItem);
    call SHOWEACH('TABLE3',pItem);
    END

    PROCEDURE SHOWEACH(pTable IN VARCHAR2(32), pItem IN VARCHAR2(8) ) IS
    sqlstmt := 'select count(*) from '||pTable||' where market = '''||pItem||'''';
    BEGIN
    EXECUTE immediate sqlstmt INTO rec_ct;
    if rec_ct > 0 then
    dbms_output.put_line('Item '||pItem||' referenced in '||pTable||' ('||rec_ct||') times');
    end if
    END
    END

    call SHOWALL('ABC');
     
  4. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    You don't CALL in PL/SQL so...off the top of my head

    PROCEDURE SHOWALL (pItem IN VARCHAR2(8) ) IS
    SHOWEACH('TABLE1',pItem);
    SHOWEACH('TABLE2',pItem);
    SHOWEACH('TABLE3',pItem);
    END

    PROCEDURE SHOWEACH(pTable IN VARCHAR2(32), pItem IN VARCHAR2(8) ) IS
    cursor mycntcsr (mytable in varchar2, myitem in varchar2) is
    select count(*) from mytable where market = myitem;
    rec_cnt number;
    BEGIN
    for x in mycntcsr(pTable, pItem) loop
    rec_cnt := rec_cnt +1;
    end loop;
    if rec_cnt > 0 then
    dbms_output.put_line('Item '||pItem||' referenced in '||pTable||' ('||rec_ct||') times');
    end if
    END

    SHOWALL('ABC');

    And that last statement...where are you executing this from ?