Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2009
    Posts
    3

    Angry Unanswered: how to execute a procedure from a different schema

    I created a new package in test1 and this package needs to execute a procedure from schema test2 and I get the following:
    When using execute immediate 'test2.XSA_CURRENT_JCCOST(parm1, parm2)' I get ORA-00900 (invalid SQL statement 'test2.XSA_CURRENT_JCCOST')
    If I code the procedure in my package as test2.XSA_CURRENT_JCCOST(parm1, parm2); it doesn't compile and the error is PLS-00201: identifier 'test2.XSA_CURRENT_JCCOST' must be declared.

    If I run the procedure as
    declare
    parm1 number;
    parm2 number;
    begin
    test2.XSA_CURRENT_JCCOST(parm1, parm2);
    end;
    then it runs okay.

    Why I cannot run it from a package?
    Thanks,
    Byteias

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by byteias
    Why I cannot run it from a package?
    I would help if you showed us the code that does not work instead of the code that works.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Why I cannot run it from a package?
    Keep in mind that privileges acquired via ROLE do not apply with named PL/SQL procedures.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by anacedent
    Keep in mind that privileges acquired via ROLE do not apply with named PL/SQL procedures.
    The error ORA-00900: invalid SQL statement does not seem to imply a privilege problem, but I could be mistaken.

  5. #5
    Join Date
    Apr 2009
    Posts
    3
    CREATE OR REPLACE package body xcr_crpt053_pkg
    as
    function xcr_main_process_f return integer
    is
    cursor c_rpt053 is
    SELECT *
    FROM MANAGER_VALIDATE MV
    , PROJECT PJ
    , PROJECT_STATUS PS
    WHERE PJ.PROJECT_ID = PS.PROJECT_ID
    AND PJ.PROJECT_MANAGER_ID = MV.ID
    AND PJ.REGION_ID = 1100
    ORDER BY PJ.PROJECT_EXEC, PJ.EMAIL_INITIALS, PJ.PROJECT_NAME;

    c_rec c_rpt053%rowtype;

    v_end_date number;
    v_count_jcc number;
    v_include_jcc varchar2(1);
    v_count_jca number;
    v_include_jca varchar2(1);
    v_ret integer;
    v_cnt integer;
    begin
    v_ret :=0;
    v_count_jcc :=0;
    v_include_jcc:=0;
    v_count_jca :=0;
    v_include_jca:=0;

    begin
    execute immediate 'test2.XSA_JCCOST(1100,''RPT053'', v_count_jcc, v_include_jcc)';
    exception
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line(SQLERRM || ' test2.XSA_JCCOST =' || SQLCODE);
    end;
    open c_rpt053;
    loop
    fetch c_rpt053 into c_rec;
    exit when c_rpt053%notfound;
    v_cnt:=v_cnt+1;
    end loop;

    close c_rpt053;

    return v_ret;

    end xcr_main_process_f;
    end xcr_crpt053_pkg;

    declare x integer
    begin
    x:=xcr_crpt053_pkg.xcr_main_process_f;
    end;

    ORA-00900 invalid SQL statement 'test2.XSA_JCCOST' -900

    If I remove the execute immediate I get a compile error. I was granted Admin privileges by the DBA

    Thanks.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    EXECUTE IMMEDIATE invokes SQL statements; not PL/SQL.

    When all else fails, RTFM
    Contents
    Last edited by anacedent; 04-21-09 at 15:51.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    CREATE OR REPLACE PACKAGE BODY xcr_crpt053_pkg 
    AS 
      FUNCTION Xcr_main_process_f 
      RETURN INTEGER 
      IS 
        CURSOR c_rpt053 IS 
          SELECT   * 
          FROM     manager_validate mv, 
                   project pj, 
                   project_status ps 
          WHERE    pj.project_id = ps.project_id 
                   AND pj.project_manager_id = mv.id 
                   AND pj.region_id = 1100 
          ORDER BY pj.project_exec, 
                   pj.email_initials, 
                   pj.project_name; 
        c_rec          c_rpt053%ROWTYPE; 
        v_end_date     NUMBER; 
        v_count_jcc    NUMBER; 
        v_include_jcc  VARCHAR2(1); 
        v_count_jca    NUMBER; 
        v_include_jca  VARCHAR2(1); 
        v_ret          INTEGER; 
        v_cnt          INTEGER; 
      BEGIN 
        v_ret := 0; 
         
        v_count_jcc := 0; 
         
        v_include_jcc := 0; 
         
        v_count_jca := 0; 
         
        v_include_jca := 0; 
         
        BEGIN 
          test2.XSA_JCCOST(1100,'RPT053', v_count_jcc, v_include_jcc) ; 
        EXCEPTION 
          WHEN OTHERS THEN 
            dbms_output.Put_line(SQLERRM 
                                 ||' test2.XSA_JCCOST =' 
                                 ||SQLCODE); 
        END; 
         
        OPEN c_rpt053; 
         
        LOOP 
          FETCH c_rpt053 INTO c_rec; 
           
          EXIT WHEN c_rpt053%NOTFOUND; 
           
          v_cnt := v_cnt + 1; 
        END LOOP; 
         
        CLOSE c_rpt053; 
         
        RETURN v_ret; 
      END xcr_main_process_f; 
    END xcr_crpt053_pkg;
    Run above & use CUT & PASTE so we can see what happens.
    It appears function always returns 0.
    Last edited by anacedent; 04-21-09 at 16:12.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Apr 2009
    Posts
    3
    Thanks, but it came to the privileges (granted to the wrong procedure)...
    Byteias

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by shammat
    The error ORA-00900: invalid SQL statement does not seem to imply a privilege problem, but I could be mistaken.
    Mistakes happen.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by anacedent
    Mistakes happen.
    Very well put

  11. #11
    Join Date
    Jan 2009
    Location
    Dhaka, Bangladesh
    Posts
    51
    login to test2 schema and grant execution permission to test1

    grant exec on test2.XSA_CURRENT_JCCOST to test1;
    Mohammad Hasan Shaharear
    E-mail
    Blog: http://shaharear.blogspot.com

Posting Permissions

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