Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2010
    Posts
    5

    Unanswered: Layman working in Oracle. Need Help.

    1 CREATE OR REPLACE PROCEDURE P2 (C IN EMP.EMPNO%TYPE,D OUT EMP%ROWTYPE)
    2 IS
    3 BEGIN
    4 SELECT ENAME,SAL,DEPTNO into D.ENAME,D.SAL,D.DEPTNO FROM EMP WHERE EMPNO=C;
    5 DBMS_OUTPUT.PUT_LINE(D.ENAME);
    6 DBMS_OUTPUT.PUT_LINE(D.SAL);
    7 DBMS_OUTPUT.PUT_LINE(D.DEPTNO);
    8* END;
    SQL> /

    Procedure created.

    SQL> SET SERVEROUTPUT ON;
    SQL> EXEC P2;
    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'P2'
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    I am unable to execute this procedure. Next I had tried with the below code:
    SQL> EXEC P2(7788,EMP);
    However this also did not work. Please could you provide with a solution to execute the above procedure.

    Thanks in advance..

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >However this also did not work.
    I tried to make my car go.
    It did not work.
    Tell me how to make my car go.
    >SQL> EXEC P2(7788,EMP);
    in line above what EXACTLY is "EMP"?

    When you invoke a PL/SQL procedure, the number & datatype of arguments must match the procedure
    In neither of your attempts was this requirement met & therefore errors resulted.

    When all else fails, Read The Fine Manual
    http://download.oracle.com/docs/cd/B...b14261/toc.htm
    http://tahiti.oracle.com
    Last edited by anacedent; 10-01-10 at 22:40.
    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.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Instead of EXEC, as you are using OUT parameter, you need to declare a variable which will accept OUT parameter's value. It also means that you'll skip a simple EXEC statement and use the whole PL/SQL block, along with its DECLARE section and call the procedure from within the BEGIN-END block.

    Something like this:
    Code:
    SQL> CREATE OR REPLACE PROCEDURE P2
      2    (C IN EMP.EMPNO%TYPE,
      3     D OUT EMP%ROWTYPE
      4    )
      5  IS
      6  BEGIN
      7    SELECT ENAME,SAL,DEPTNO
      8      INTO D.ENAME,D.SAL,D.DEPTNO
      9      FROM EMP WHERE EMPNO=C;
     10    DBMS_OUTPUT.PUT_LINE(D.ENAME);
     11    DBMS_OUTPUT.PUT_LINE(D.SAL);
     12    DBMS_OUTPUT.PUT_LINE(D.DEPTNO);
     13  END;
     14  /
    
    Procedure created.
    
    SQL> DECLARE
      2    l_d emp%rowtype;
      3  BEGIN
      4    p2 (7934, l_d);
      5  END;
      6  /
    MILLER
    1300
    10
    
    PL/SQL procedure successfully completed.
    
    SQL>

  4. #4
    Join Date
    Oct 2010
    Posts
    5

    Reply

    Thanks for the reply Littlefoot. I have tried the code prior posting this query which you have replied to me and succeeded to obtain the result. However, I understood from your reply that I could not directly use the simple execution statement exec(parameter list) when one of the parameter is an out parameter of rowtype.

    But, is there any such option to use the simple execution statement to execute the Procedure, or is it that we have to mandatorily use a PL/SQL block to the call the Procedure while using rowtype as an out parameter.

    Once again thanks for the reply.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SQL is totally different from PL/SQL.
    a datatype that is valid in PL/SQL may not be valid in SQL
    In SQL you can only use datatypes valid in SQL & "rowtype" is not valid in SQL
    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.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's another example of what Anacedent already said:
    Code:
    SQL> create or replace procedure p3
      2    (c in emp.empno%type,
      3     d out emp.ename%type
      4    )
      5  is
      6  begin
      7    select ename
      8      into d
      9      from emp
     10      where empno = c;
     11  end;
     12  /
    
    Procedure created.
    
    SQL> -- this won't work:
    SQL> var l_d emp.ename%type;
    Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
                        VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
                        NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR |
                        BINARY_FLOAT | BINARY_DOUBLE ] ]
    SQL> var l_d varchar2(20);
    SQL>
    SQL> exec p3 (7934, :l_d);
    
    PL/SQL procedure successfully completed.
    
    SQL> print l_d;
    
    L_D
    -------------------------------------------------------------------------
    
    MILLER
    
    SQL>
    More reading about EXEC(UTE); link to VARIABLE command is at the bottom of the page.

  7. #7
    Join Date
    Oct 2010
    Posts
    5

    Thanks Message

    Hi, thanks for the reply which you have given. Sorry for the query which I had posted. I am a starter working on Oracle and so there were some confusions in my mind which I had posted. And also I am not even an average guy who would think that he is an above average guy.. However thanks for the reply and the link which you have provided...

Posting Permissions

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