Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136

    Unanswered: Error calling a procedure

    Hello!

    I have the following problem:

    ################################################## #######################################
    calling this statement:

    BEGIN
    PRC_INS_UPD_HOST('test', 515, 'PC', '31-Dec-2003', 'RBG', 1, 1, 'test', 1223);
    END;
    ################################################## ########################################

    this error occurs:

    ORA-00904: invalid column name
    ORA-06512: at "PRC_INS_UPD_HOST", line 42
    ORA-06512: at line 2
    ################################################## #########################################

    CREATE OR REPLACE PROCEDURE PRC_INS_UPD_HOST
    ( pHostId VIEW_HOST.HOSTID%TYPE,
    pCostCenter VIEW_HOST.COSTCENTER%TYPE,
    pHostCat VIEW_HOST.HOST_CATEGORY%TYPE,
    pEqOn VIEW_HOST.EQUIPPED_ON%TYPE,
    pLoc VIEW_HOST.ID_LOC%TYPE,
    pDesc VIEW_HOST.DESCRIPTION%TYPE,
    pComments VIEW_HOST.COMMENTS%TYPE,
    pHostName VIEW_HOST.HOST_NAME%TYPE,
    pPort VIEW_HOST.PORT%TYPE
    )
    AS
    LIC NUMBER;
    BEGIN
    -- insert/update a (new) row in VIEW_HOST.
    -- check if row already exists
    -- YES:
    -- update VIEW_HOST
    --> update HOST
    --> TRG_HOST_BEF_UPD
    --> SEQ_HOST
    --> update LICENSEE
    --> update TRG_LICENSEE_BEF_UPD
    --> SEQ_LICENSEE
    -- NO:
    -- insert a new row into VIEW_HOST
    --> TRG_VIEW_HOST_INSTOF_INS
    --> insert a new row into LICENSEE
    --> insert a new row into HOST

    UPDATE VIEW_HOST SET COSTCENTER = pCostCenter, HOST_CATEGORY = pHostCat, EQUIPPED_ON = pEqOn,
    ID_LOC = pLoc, DESCRIPTION = pDesc,
    COMMENTS = pComments, HOST_NAME = pHostName, PORT = pPort
    WHERE HOSTID = pHostId;

    IF SQL%ROWCOUNT = 0 THEN
    INSERT INTO VIEW_HOST (HOSTID, COSTCENTER, HOST_CATEGORY, EQUIPPED_ON
    , ID_LOC, DESCRIPTION, COMMENTS, HOST_NAME, PORT)
    VALUES (pHostId, pCostCenter, pHostCat, pEqOn
    , pLoc, pDesc, pComments, pHostName, pPort);
    ELSE
    EXECUTE IMMEDIATE 'SELECT ID_LIC FROM HOST WHERE HOSTID = ' || pHostId INTO LIC; /* line 42 */
    UPDATE LICENSEE SET LIC_CATEGORY = 'HOST' WHERE ID_LIC = LIC;

    END IF;
    END;
    /
    ################################################## ################################################## #################


    If would be great if anybody could help me!
    Thanks in advance!
    Regards,
    Julia

  2. #2
    Join Date
    Feb 2004
    Location
    India
    Posts
    135
    Hi Julia,

    Plz refer the Procedure Parameter is < pHostId VIEW_HOST.HOSTID%TYPE > if this variable not declared properly U may occur error!

    ensure the Column name in that table.

  3. #3
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Thanks.
    Now i know, where is my error, but i don't now how to correct it, namely:


    __________________________________________________ ________

    EXECUTE IMMEDIATE 'SELECT ID_LIC FROM HOST WHERE HOSTID = ' || pHostId INTO LIC;
    __________________________________________________ _________

    ==> pHostId = 'test' ==> VARCHAR2(20),

    so i have to set '', but if i do that:
    _________________________________________

    EXECUTE IMMEDIATE 'SELECT ID_LIC FROM HOST WHERE HOSTID = '' || pHostId || ''' || INTO LIC;
    _______________________________________________

    there is a syntactical error?

    What can i do?
    Regards,
    Julia

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You should use a bind variable:

    EXECUTE IMMEDIATE 'SELECT ID_LIC FROM HOST WHERE HOSTID = :hostid' || INTO LIC USING pHostId;

    But then why use EXECUTE IMMEDIATE at all? Why not just:

    SELECT ID_LIC INTO LIC
    FROM HOST WHERE HOSTID = pHostId;

    ?

  5. #5
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Yes!!
    Thank You!
    Thanks to all!
    Last edited by julla27; 03-29-04 at 07:10.
    Regards,
    Julia

  6. #6
    Join Date
    Feb 2004
    Location
    India
    Posts
    135
    Hi Julia,

    u have given extra single quote for where expression
    plz look at the following.

    EXECUTE IMMEDIATE 'SELECT ID_LIC FROM HOST WHERE HOSTID = ' || pHostId INTO LIC;

    now u try this.

Posting Permissions

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