Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2012
    Posts
    5

    Unanswered: Passing input parameter to SQL procedure

    Hi,
    I am new to SQL.
    This is the procedure requirement i am trying for.

    create or replace PROCEDURE "INVENTORY"
    IS
    BEGIN
    FOR item IN (
    select *
    FROM INVENTORY TD, INVENTORY_BKP TDB
    WHERE TD.HOSTNAME = @HOSTNAME
    and TD.DOMAINNAME=TDB.DOMAINNAME
    ) LOOP
    UPDATE INVENTORY_BKP TDB

    SET .............
    ........;
    commit;
    END LOOP;
    END;

    Where @HOSTNAME value should be passed as parameter from the command line promt like below.
    SQL> exec INVENTORY @HOSTNAME="Hostname";

    But not sure how to declare the @HOSTNAME in the procedure. Can any one help me to resolve this query.

    Regards,
    Hari

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    There is no reason to use a loop to update the rows. That will be much slower than using a single statement and will not scale very well if the table size increases.

    Having said that: you procedure does not declare any parameters, so you cannot pass any. Please refer to the Oracle manual for a description on how to define parameters for a procedure:

    http://docs.oracle.com/cd/E11882_01/...ams.htm#i23202

    It also contains an example on how to call such a procedure.,

  3. #3
    Join Date
    Jun 2012
    Posts
    5
    Thank you for quick response.
    As you said, I tried to update the rows with out using loop (as below code).

    UPDATE INVENTORY_BKP TDB
    SET (COUNT, MINSIZE,MAXSIZE) = ( SELECT TD.COUNT, TD.MINSIZE,
    TD.MAXSIZE
    FROM INVENTORY TD
    WHERE TD.HOSTNAME = TDB.HOSTNAME
    and TD.DOMAINNAME=TDB.DOMAINNAME )
    WHERE EXISTS
    ( SELECT TD.COUNT,TD.MINSIZE,TD.MAXSIZE
    FROM INVENTORY TD, INVENTORY_BKP TDB
    WHERE TD.HOSTNAME = TDB.HOSTNAME
    and TD.DOMAINNAME=TDB.DOMAINNAME and
    (TD.COUNT ! = TDB.COUNT or TD.MINSIZE != TDB.MINSIZE
    or TD.MAXSIZE != TDB.MAXSIZE ) ) ;


    As per my requirement, I need to update some columns (COUNT, MINSIZE,MAXSIZE) from TD into TDB where TD.HOSTNAME = TDB.HOSTNAME and TD.DOMAINNAME=TDB.DOMAINNAME and (TD.COUNT ! = TDB.COUNT or TD.MINSIZE != TDB.MINSIZE or TD.MAXSIZE != TDB.MAXSIZE ) condition satisfies.( here HOSTNAME and DOMAINNAME are the uniq constraint in both tables).

    But this logic is not allowing to insert multiple rows into TDB from TD. Is something wrong in this logic? Please suggest.
    Last edited by hardlad; 06-19-12 at 09:22.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by hardlad View Post
    But this logic is not allowing to insert multiple rows into TDB from TD.
    Do you want to update rows, or do you want to insert new rows? Those are two different things.

    I'm not sure I understand what you are after, but something like this might do what you want:
    Code:
    merge into inventory_bkp tdb 
    using 
    (
        select *
        from inventory td
        where td.hostname = p_hostname
    ) td on (and td.domainname=tdb.domainname and td.hostname = tdb.hostname)
    when matched then update
       SET some_column = td.some_column;
    Where p_hostname would be the parameter to your procedure (in Oracle parameters are not prefixed with @ which is clearly documented in the manual).

    If you do need to insert new rows then you can extend the merge statement with a "when not matched then insert" part. Please see the manual for details.

    Also please use [code] tags to make your SQL readable.
    Details about how to properly format you posting are here: http://www.dbforums.com/misc.php?do=bbcode

  5. #5
    Join Date
    Jun 2012
    Posts
    5
    Hi,
    Apologies for the confusion caused.

    I am really looking for simple query like you have suggested. This code helped me alot to insert the new rows where data(rows) not existing and updating the rows when the data exists.


    Code:
    MERGE INTO INVENTORY_BKP b
    USING (
    SELECT *
    FROM INVENTORY
    WHERE HOSTNAME ='p_hostname' ) e
    ON (b.DOMAINNAME = e.DOMAINNAME and b.HOSTNAME=e.HOSTNAME )
    WHEN MATCHED THEN
    UPDATE SET b.THREADCOUNT = e.THREADCOUNT

    WHEN NOT MATCHED THEN
    INSERT (b.DOMAINNAME,b.HOSTNAME,b.THREADCOUNT)
    VALUES (e.DOMAINNAME,e.HOSTNAME,e.THREADCOUNT);

Posting Permissions

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