Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Mar 2011
    Posts
    9

    Unanswered: Error at line 1 ORA-06550: line 1, column 7: PLS-00306:

    I trying to create an procedure using the below code...

    create or replace NEW_PROC (cnt out number) is

    declare cnt integer;

    begin

    select count(*) into cnt from table;

    end;


    ------
    But I am getting the below error...

    Error(1): PL/SQL: Compilation unit analysis terminated
    Error(2,1): PLS-00410: duplicate fields in RECORD, TABLE or argument list are not permitted

    ------
    please help me in this as i am quite new to PLSQL...
    Note: i am using sqldeveloper tool for creating the procedure.

  2. #2
    Join Date
    Sep 2003
    Location
    NE Florida w/ view of co-workers
    Posts
    32
    Take out the "declare cnt integer;" line. The variable cnt is already defined in your parameter list.

    Try this:
    Code:
    create or replace procedure NEW_PROC (cnt out number) is
    
    begin
    
    select count(*) into cnt from table;
    
    end;

  3. #3
    Join Date
    Mar 2011
    Posts
    9
    hey... thank buddy... it worked...

    but there's one more problem i am facing now...i tried executing the procedure like...

    exec package1.new_proc;

    its giving me an error as
    PLS-00306: wrong number or types of arguments in call to 'new_proc'

    can you please help me in this?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    exec package1.new_proc(0);
    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.

  5. #5
    Join Date
    Mar 2011
    Posts
    9
    Hi..i tried with

    exec package1.new_proc(0);

    but its giving me an error as...

    Error starting at line 1 in command:
    exec lr_automation_package.a1(0);
    Error report:
    ORA-06550: line 1, column 32:
    PLS-00363: expression '0' cannot be used as an assignment target
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    can you please sort this out?? I am using sql developer tool for running this... then i have to call this procedure from unix shel script... this is for your info..

    thanks in advance!

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, it should have been a function instead of a procedure.

    But, if that's for exercising, no problem. As you chose to use a procedure with an OUT parameter, you have to declare it and use while calling your procedure.

    Here's an example:
    Code:
    SQL> create or replace procedure new_proc (cnt out number)
      2    is
      3  begin
      4    select count(*)
      5      into cnt
      6      from emp;
      7  end;
      8  /
    
    Procedure created.
    
    SQL> var l_cnt number;
    SQL> exec new_proc(:l_cnt);
    
    PL/SQL procedure successfully completed.
    
    SQL> print l_cnt
    
         L_CNT
    ----------
            14
    
    SQL>
    Or, in PL/SQL:
    Code:
    SQL> declare
      2    l_cnt_2 number;
      3  begin
      4    new_proc (l_cnt_2);
      5
      6    dbms_output.put_line(l_cnt_2);
      7  end;
      8  /
    14
    
    PL/SQL procedure successfully completed.
    
    SQL>

  7. #7
    Join Date
    Mar 2011
    Posts
    9
    i have a package in which this proc is declared...and as you said i declared the variable in the package...

    create or replace
    PACKAGE Package1 IS

    declare cnt NUMBER;
    PROCEDURE a1(:cnt);

    END Package1;
    --------------------------------------

    but i am getting the below 3 errors...

    Error(6,1): PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: end function package pragma private procedure subtype type use form current cursor
    Error(7,14): PLS-00103: Encountered the symbol "" when expecting one of the following: current
    Error(7,14): PLS-00049: bad bind variable 'CNT'

    ----------------------

  8. #8
    Join Date
    Mar 2011
    Posts
    9
    can anyone please help me in this??

    thanks in advance...

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    when all else fails, Read The Fine Manual.
    Contents

    shows valid syntax for PL/SQL language
    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
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You define the out parameter in the declare section in the routine that will be calling your procedure, not in the procedure or package.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Mar 2011
    Posts
    9
    hi.. thanks for the info...
    i tried it as below as well....
    --------------------
    create or replace PACKAGE Package1 IS

    PROCEDURE a1(:cnt);

    END Package1;
    ------------------------------

    variable cnt_1 number;
    exec package1.a1(:cnt_1);
    --------------------------------
    i am getting the below error

    Error starting at line 2 in command:

    exec lr_automation_package.a1(:cnt_1);
    Error report:

    ORA-06550: line 1, column 7:

    PLS-00306: wrong number or types of arguments in call to 'A1'

    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    06550. 00000 - "line %s, column %s:\n%s"

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> @sp1
    SQL> create or replace procedure NEW_PROC (OUTNUM out number) is
      2  begin
      3  select count(*) into OUTNUM from user_objects;
      4  end NEW_PROC;
      5  /
    
    Procedure created.
    
    SQL> VAR OUTNUM number;
    SQL> exec new_proc(:OUTNUM);
    
    PL/SQL procedure successfully completed.
    
    SQL> print OUTNUM;
    
        OUTNUM
    ----------
           123
    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.

  13. #13
    Join Date
    Mar 2011
    Posts
    9
    hi...i tried with it..but somehow its not working for me... so i have written a function in a package body instead as below:

    create or replace PACKAGE package1 AS

    FUNCTION new1 RETURN NUMBER;
    END package1;

    ----------------
    create or replace PACKAGE BODY package1 AS

    FUNCTION new1 RETURN NUMBER IS
    cnt NUMBER;
    BEGIN

    select count(*) into cnt from table1;
    RETURN cnt;
    END new1;

    END package1;
    ----------------------

    and i ran this using below command:
    select package1.new1 from table1;

    but it is returning a value as many times the count is. Like say count is coming out to be 5 then its returning as
    Output:

    NEW1 ------
    5
    5
    5
    5
    5

    but i want the output to be as just '5' because i am using this value in some unix script....

    can you please help me resolving this...

    i really appreciate your help....

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    select package1.new1 from dual;
    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.

  15. #15
    Join Date
    Mar 2011
    Posts
    9
    thanks buddy... it worked its showing an output only once... but again its giving output as

    NEW1 ---------- 5

    here i don't want this string "NEW1 --------".
    i want the output to be just 5...... can this be acheived as i am nearing to my purpose.... i am going to use this value in the shell script...
    Last edited by swap21783; 03-18-11 at 17:33.

Posting Permissions

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