Results 1 to 6 of 6
  1. #1
    Join Date
    May 2012
    Posts
    9

    Unanswered: Stored Procedure Syntax

    Firstly, apologize if I'm reposting. I tried general search but that did not help in resolving my issue. It is a simple query with union statement. Works fine if I run the query by itself. Same query when wrapped in a sp, I'm getting following errors.

    PL/SQL: SQL Statement ignored
    PL/SQL: ORA-00933: SQL command not properly ended
    PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe


    Here is the sample code and I'm getting error when trying to compile the same. Oracle Version: 10.2

    Create or Replace Procedure A_SAMPLE (start in date, finish in date)
    IS
    BEGIN
    SELECT A1,B1,C1 FROM XYZ
    WHERE (C1 > = start AND C1<= Finish)

    UNION

    SELECT A1,B1,C1 FROM ABC
    WHERE (C1 > = start AND C1<= Finish)

    END A_SAMPLE;


    Can someone please let me know what the error is about? Thanks for your help in advance.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SELECT statements within PL/SQL procedure require INTO clause
    Code:
    CREATE OR replace PROCEDURE A_sample (START  IN DATE, 
                                          finish IN DATE) 
    IS 
    BEGIN 
        SELECT a1, 
               b1, 
               c1 
        FROM   xyz 
        WHERE  ( c1 > = start 
                 AND c1 <= finish ) 
        UNION 
        SELECT a1, 
               b1, 
               c1 
        FROM   abc 
        WHERE  ( c1 > = start 
                 AND c1 <= finish ); 
    END a_sample;
    Last edited by anacedent; 10-21-13 at 19:39. Reason: fixed typo
    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
    May 2012
    Posts
    9
    Thanks anacedent for your reply.

    Does this mean I have create a temp table to store final recordset?

    [QUOTE=anacedent;6605076]SELECT statements with PL/SQL procedure require INTO clause

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It is foolish to start coding in any new language without knowing the basics.
    PL/SQL is an entirely different language from plain SQL.

    You should SERIOUSLY consider to first Read The Fine Manual; Concepts Guilde
    Contents
    &
    Contents

    URL below contains many fine coding examples
    Ask Tom Home
    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
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You don't need any "temp table". One option is to select into a variable, such as
    Code:
    SQL> create or replace procedure prc_test (par_empno in emp.empno%type)
      2  as
      3    l_ename emp.ename%type;
      4  begin
      5    select e.ename
      6      into l_ename       --> this!
      7      from emp e
      8      where e.empno = par_empno;
      9
     10    dbms_output.put_line('Emp. name is ' || l_ename);
     11  end;
     12  /
    
    Procedure created.
    
    SQL> exec prc_test(7369);
    Emp. name is SMITH
    
    PL/SQL procedure successfully completed.
    
    SQL>
    On the other hand, SELECT could have returned more than a single record. In that case, the above code would have returned an error. For example:
    Code:
    SQL> create or replace procedure prc_test (par_deptno in emp.deptno%type)
      2  as
      3    l_ename emp.ename%type;
      4  begin
      5    select e.ename
      6      into l_ename       --> this!
      7      from emp e
      8      where e.deptno = par_deptno;
      9
     10    dbms_output.put_line('Emp. name is ' || l_ename);
     11  end;
     12  /
    
    Procedure created.
    
    SQL> exec prc_test(10);
    BEGIN prc_test(10); END;
    
    *
    ERROR at line 1:
    ORA-01422: exact fetch returns more than requested number of rows
    ORA-06512: at "SCOTT.PRC_TEST", line 5
    ORA-06512: at line 1
    In that case, you'd have to use bulk processing or a loop (or any other technique you find appropriate). For example:
    Code:
    SQL> create or replace procedure prc_test (par_deptno in emp.deptno%type)
      2  as
      3    type emp_names_t is table of emp.ename%type index by pls_integer;
      4    l_enames emp_names_t;
      5  begin
      6    select e.ename
      7      bulk collect into l_enames   --> this!
      8      from emp e
      9      where e.deptno = par_deptno;
     10
     11    for i in 1 .. l_enames.count loop
     12      dbms_output.put_line('Emp. name is ' || l_enames(i));
     13    end loop;
     14  end;
     15  /
    
    Procedure created.
    
    SQL> exec prc_test(10);
    Emp. name is CLARK
    Emp. name is KING
    Emp. name is MILLER
    
    PL/SQL procedure successfully completed.
    
    SQL>
    Code:
    SQL> create or replace procedure prc_test (par_deptno in emp.deptno%type)
      2  as
      3  begin
      4    for cur_r in (select e.ename
      5                  from emp e
      6                  where e.deptno = par_deptno
      7                 )
      8    loop
      9      dbms_output.put_line('Emp. name is ' || cur_r.ename);
     10    end loop;
     11  end;
     12  /
    
    Procedure created.
    
    SQL> exec prc_test(10);
    Emp. name is CLARK
    Emp. name is KING
    Emp. name is MILLER
    
    PL/SQL procedure successfully completed.
    
    SQL>

  6. #6
    Join Date
    May 2012
    Posts
    9
    Thanks Littlefoot for the reply. I used refcursor instead and it worked. Here is the sample code.

    Create or Replace Procedure A_SAMPLE (param1 in datatype, param2 in datatype, a_OUT out SYS_REFCURSOR)
    IS
    BEGIN
    OPEN a_OUT FOR
    SELECT A1,B1,C1 FROM XYZ
    WHERE (C1 > = param1 AND C1<= param2)

    UNION

    SELECT A1,B1,C1 FROM ABC
    WHERE (C1 > = param1 AND C1<= param2)
    ;
    END;
    /
    Last edited by rookie19; 11-07-13 at 22:20.

Posting Permissions

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