Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Location
    Valley City, OH
    Posts
    18

    Unanswered: Qualifying a Procedure with Schema

    Good Afternoon

    Shopping for help qualifying a procedure that I am trying to create under a different schema than where I am logged in at.

    I tried ECM.CRM_INSERTFEATUREACC for the first and last name. I tried ECM. before the tablename but still having issues.

    Does anyone have ideas what I am doign wrong?

    Thanks,
    Ed

    CREATE OR REPLACE PROCEDURE CRM_INSERTFEATUREACC
    (FeatACCID IN INT,
    FeatACCDescription IN VARCHAR2,
    FeatACCGroupID IN VARCHAR2,
    FeatACCActFlag IN VARCHAR2) IS
    /************************************************** ****************************
    NAME: CRM_INSERTFEATUREACC
    PURPOSE: Insert New Feature

    REVISIONS:
    Ver Date Author Description
    --------- ---------- --------------- ------------------------------------
    1.0 02262004 edobias 1. Created this procedure.
    ************************************************** ****************************/

    --n_length NUMBER DEFAULT 11;
    --v_prodtype VARCHAR2(1);
    --v_modeltype VARCHAR2(1);
    --v_salesdesign VARCHAR2(1);
    --v_basicmodel VARCHAR2(5);
    --v_custsuffix VARCHAR2(3);
    --n_recs NUMBER DEFAULT 0;
    --n_recordcount NUMBER DEFAULT 50;
    --n_exists NUMBER DEFAULT 0;


    BEGIN

    INSERT INTO ECM.FEATUREACC
    VALUES(FeatACCID,
    FeatACCDescription,
    FeatACCGroupID,
    FeatACCActFlag);

    END CRM_INSERTFEATUREACC;

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    do you have the appropriate privs assigned?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Oct 2003
    Location
    Valley City, OH
    Posts
    18
    Hey There -

    I had the dba check out my rights. Man I just don't understand...
    Here is his response:

    You have all the rights and privileges to do what you want. The problem your having here is that the table is in someone else's schema and the procedure is looking for that table in YOUR schema. So, what you want to do is to qualify the table name with the schema name like...ECM.table_name instead of just table_name.

    Ri



    Thanks,
    Ed

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Your DBA suggests you should qualify the table name with the table owner, from your original post it appears that you are already doing that.....

    PHP Code:
     INSERT INTO ECM.FEATUREACC 
    Can you insert into ECM.FEATUREACC using the same syntax but as a command when logged in via SqlPlus (a direct command, not an anonymous block or package, function etc)? If you can then you're into a typical privileges through role rather directly assigned problem.

    See this link....

    http://www.dbforums.com/showthread.php?threadid=978115

    However, if you're trying to create this procedure in the other schema, that's a different issue. I would imagine you would just qualify the procedure name instead - although I haven't tried it.

    Hth
    Bill
    Last edited by billm; 03-04-04 at 20:18.
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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