Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2012
    Posts
    10

    Unanswered: Error(53,3): PL/SQL: ORA-00928: missing SELECT keyword

    Hi ,

    I am unable to resolve the mentioned error in the below SP.
    Can any one please help in sorting the issue.

    create or replace
    procedure "SP_IMPDATAUNDEFINED"
    as
    dataobjectname varchar2(255);
    description varchar2(255);
    SPECNAME VARCHAR2(255);
    aggregate1 varchar2(20) ;
    classification varchar2(10);
    ERROR_ID VARCHAR2(275);
    BEGIN
    WITH tmp_errors AS
    (SELECT do_name FROM tmp_dataobject WHERE int_id =2
    /* all inputs */
    MINUS
    SELECT do_name FROM tmp_dataobject WHERE int_id = 1
    ),
    tmp_warnings AS
    (SELECT do_name
    FROM tmp_errors
    WHERE do_name IN
    ( SELECT do_name FROM tmp_dataobject WHERE int_id = 3
    )
    ),
    real_errors AS
    (SELECT do_name FROM tmp_errors
    MINUS
    select do_name from tmp_warnings
    ),
    Out_put as (
    select
    re.do_name as doname,
    'Undefined data declared in input table' as descrp,
    do.module_name as module,
    DO.AGGR AS AGGREG,
    'ERROR' as class1,
    'IMPDATAUNDEF-' ||re.do_name as errorid
    FROM real_errors re,
    tmp_dataobject DO
    where re.do_name = do.do_name
    AND DO.INT_ID = 2
    /* only display an error if do_name is in input table */
    UNION
    select
    rw.do_name as doname,
    'Undefined data declared in input table' as descrp,
    do.module_name as module,
    DO.AGGR AS AGGREG,
    'WARNING' as class1,
    'IMPDATAUNDEF-' ||rw.do_name as errorid
    FROM tmp_warnings rw,
    TMP_DATAOBJECT DO
    where rw.do_name = do.do_name
    AND DO.INT_ID = 2
    )
    declare c1 Cursor for
    SELECT
    DONAME,
    DESCRP ,
    MODULE,
    AGGREG,
    CLASS1,
    ERRORID
    FROM Out_put;
    open c1;
    LOOP
    fetch c1 into dataobjectname,
    description,
    SPECNAME,
    aggregate1,
    CLASSIFICATION,
    ERROR_ID;
    end loop;
    close c1;
    COMMIT;
    end;

    Thanks In Advance

    Shiva

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Your code is an unreadable nightmare. How do you expect anyone to read it?

    Anyway: it seems that you are using WITH factoring clause. It expects you to do *something* with it (which is SELECT you are missing). You can't just DECLARE a cursor because you think it is nice to do that in the middle of code. There are certain rules, and you need to follow them.

    Here's an example of how you might do that.
    Code:
    SQL> declare
      2    l_dname varchar2(200);
      3
      4    cursor c1 is
      5      with test as
      6        (select dname from dept)
      7      select dname
      8      from test;
      9  begin
     10    for cur_r in c1 loop
     11      l_dname := cur_r.dname;
     12      dbms_output.put_line(l_dname);
     13    end loop;
     14  end;
     15  /
    ACCOUNTING
    RESEARCH
    SALES
    OPERATIONS
    SOME DEPT.
    
    PL/SQL procedure successfully completed.
    
    SQL>
    Try to rewrite your code, watching this example. If you still have questions, please, PLEASE!, format your code and enclose it into [code] tags before posting it here. I might not read such a mess once again.

  3. #3
    Join Date
    Feb 2012
    Posts
    10
    Thanks Littlefoot.

    Apologies for not using [code]; actually i am new to this forum, i will read the guidelines for the next post and will follow them .

    Secondly, I will try to minimise the code.

    That sample code works, Thanks again dude

    Regards,
    Shiva

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about to replace "declare c1 Cursor for" to the line before "WITH tmp_errors AS"?

  5. #5
    Join Date
    Feb 2012
    Posts
    10
    Hi...

    can any one help in sorting the issue.

    [code]
    CREATE OR REPLACE
    PROCEDURE SP_TEST(C1 OUT SYS_REFCURSOR)
    AS
    dname varchar2(255);
    description varchar2(255);
    BEGIN
    OPEN C1 FOR

    SELECT SCN_NAME,
    SCN_DESCRIPTION
    FROM dataobject WHERE SC_ID ='283649';
    loop
    fetch c1 into dname,
    description;
    end loop;
    CLOSE C1;
    END SP_TEST;


    actually it should return 160 rows.

    but, when i execute via below commands, there is no response .
    VAR D REFCURSOR
    EXEC SP_TEST()
    print r


    Thanks in Advance.

    Regards,
    Shiva

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Code:
    SQL> create or replace procedure sp_test (c1 out sys_refcursor) is
      2  begin
      3    open c1 for select deptno, dname, loc from dept;
      4  end;
      5  /
    
    Procedure created.
    
    SQL> var d refcursor;
    SQL> exec sp_test(:d);
    
    PL/SQL procedure successfully completed.
    
    SQL> print d;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    
    SQL>
    By the way, you failed to properly use CODE tags.

Posting Permissions

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