ITKeyword,专注技术干货聚合推荐

注册 | 登录

解决oracle - ORA:00900 - Invalid SQL Statement

itPublisher 分享于

2020腾讯云双十一活动,全年最低!!!(领取3500元代金券),
地址https://cloud.tencent.com/act/cps/redirect?redirect=1073

【阿里云】双十一活动,全年抄底价,限时3天!(老用户也有),
入口地址https://www.aliyun.com/1111/home

推荐:ORA-00900: invalid SQL statement

1、错误描述      SQL> startup;        startup        ORA-00900: invalid SQL statement 2、错误原因 3、解决办法

-1

I am new to the vast world of oracle. What I am trying to do is, creating a stored procedure and retrieve its result. My procedure goes as

Create or Replace Procedure usp_RotaPlateProductie_Select(
afdelingId in varchar2,
productTypeId in varchar2,
productieData out sys_refcursor)
IS 
Begin
Open productieData for
Select Rotaplateproductie.Batchnummer, Cpiplusproductieorder.Productnummer,
    Product.Omschrijving, Productieresultaatrtplrol.Bruto_In_Meters
    From Rotaplateproductie inner join Productieresultaatrtplrol on
    Rotaplateproductie.Batchnummer = Productieresultaatrtplrol.Batchnummer 
    inner join Cpiplusproductieorder on 
    Productieresultaatrtplrol.ProductieNummer =  Cpiplusproductieorder.ProductNummer 
    inner join Product on
    Cpiplusproductieorder.Productnummer = Product.Productnummer
    Where Rotaplateproductie.Afdelingid = '3144' and Rotaplateproductie.producttype = 'PT005'   
END;

And using the below code I am trying to execute it.

var rc REFCURSOR
EXEC usp_RotaPlateProductie_Select('3144','PT005', :rc);

While executing the above lines I am getting Ora:00900 error.

When I run the query part of the procedure, it is running fine but with procedure it gives me error.

oracle plsql
|
  this question
edited Jun 28 '12 at 10:08 Romil Kumar Jain 13.7k 4 32 67 asked Jun 28 '12 at 10:03 Maverick 51 2 2 10      I ASSUME this procedure is valid right ? – 
 ef
Jun 28 '12 at 10:54      The query is valid, but not sure about the procedure –  Maverick Jun 28 '12 at 11:07      Maverick next time dont accept unless you are sure if it does happen please ... please let us know why so any one can get use from this question – 
 ef
Jul 4 '12 at 5:26

 | 

2 Answers
2

解决方法

As Shareef pointed out you're missing a semicolon on the statement inside your procedure, but it doesn't look like you're actually creating it properly. You need a / after the procedure to tell Oracle to execute the code and actually do the creation. I think it's seeing the var and exec statements as part of a single call, which is incorrect. But I'm also not sure how you're running this; if it's in SQL Developer you need to 'run script' (F5) rather than just 'run'.

Create or Replace Procedure usp_RotaPlateProductie_Select(
    p_afdelingId in varchar2,
    p_productTypeId in varchar2,
    p_productieData out sys_refcursor)
IS 
Begin
    Open p_productieData for
        Select Rotaplateproductie.Batchnummer, Cpiplusproductieorder.Productnummer,
            Product.Omschrijving, Productieresultaatrtplrol.Bruto_In_Meters
        From Rotaplateproductie
        inner join Productieresultaatrtplrol on
            Rotaplateproductie.Batchnummer = Productieresultaatrtplrol.Batchnummer 
        inner join Cpiplusproductieorder on 
            Productieresultaatrtplrol.ProductieNummer = Cpiplusproductieorder.ProductNummer 
        inner join Product on
            Cpiplusproductieorder.Productnummer = Product.Productnummer
        Where Rotaplateproductie.Afdelingid = p_afdelingId
        and Rotaplateproductie.producttype = p_productTypeId; 
END;
/
var rc REFCURSOR
EXEC usp_RotaPlateProductie_Select('3144','PT005', :rc);
print :rc

I've switched to use the IN parameters, and I've taken the liberty of changing the names so you can distinguish between parameters and column names; they were the same which can cause confusion.

You might also find it useful to alias your tables; no functional difference but maybe easier to read:

Create or Replace Procedure usp_RotaPlateProductie_Select(
    p_afdelingId in varchar2,
    p_productTypeId in varchar2,
    p_productieData out sys_refcursor)
IS 
Begin
    Open p_productieData for
        Select rp.Batchnummer, cppo.Productnummer,
            p.Omschrijving, pra.Bruto_In_Meters
        From Rotaplateproductie rp
        inner join Productieresultaatrtplrol par
            on rp.Batchnummer = pra.Batchnummer 
        inner join Cpiplusproductieorder cppo
            on pra.ProductieNummer = cppo.ProductNummer 
        inner join Product p
            on cppo.Productnummer = p.Productnummer
        Where rp.Afdelingid = p_afdelingId
        and rp.producttype = p_productTypeId; 
END;
/

If you get a message that the procedure compiled with warnings, do 'show errors' to get the details.


|
  this answer
answered Jun 28 '12 at 11:28 Alex Poole 94.8k 6 64 101

 | 

did you run the code

Select Rotaplateproductie.Batchnummer, Cpiplusproductieorder.Productnummer,
    Product.Omschrijving, Productieresultaatrtplrol.Bruto_In_Meters
    From Rotaplateproductie inner join Productieresultaatrtplrol on
    Rotaplateproductie.Batchnummer = Productieresultaatrtplrol.Batchnummer 
    inner join Cpiplusproductieorder on 
    Productieresultaatrtplrol.ProductieNummer =  Cpiplusproductieorder.ProductNummer 
    inner join Product on
    Cpiplusproductieorder.Productnummer = Product.Productnummer
    Where Rotaplateproductie.Afdelingid = '3144' and Rotaplateproductie.producttype = 'PT005'

i it works please reply i think you miss semicolon in the end

推荐:ORA - 01017 :invalid username/password;logon denied [Oracle SQL Developer 登录]

利用 Oracle SQL Developer 登陆,出现以下错误: Status:ORA - 01017 :invalid username/password;logon denied 如图: 解决: 在命令行中 以DBA身份 进入数据

;

@Mavrik edited

After adding semicolon in the end of the query, the procedure is created properly. thanks but it is still returning the same error code when I tried to execute it using the below code

var rc REFCURSOR EXEC usp_RotaPlateProductie_Select('3144','PT005', :rc);

UPDATE 1: if you have ora 00900 it may be because of

Oracle/PLSQL: ORA-00900 Error


|
  this answer
edited Jun 28 '12 at 11:26 answered Jun 28 '12 at 10:12
 ef
3,328 3 30 53 1   If it's all being run together, it's also possibly missing the / between the end of the procedure and the var. (And presumably the query should be using the IN parameters, not hard-coded values). –  Alex Poole Jun 28 '12 at 10:20      I will use them using IN parameters in future. just for testing I have done this way –  Maverick Jun 28 '12 at 11:05      @Maverick are you using sql plus – 
 ef
Jun 28 '12 at 11:24      @
 ef - I am using PLSQL –  Maverick Jun 29 '12 at 2:52      @
 ef - Hi Shareef, semicolon helped to build the procedure successfully. Thanks –  Maverick Jun 29 '12 at 9:25  |  show more comments

推荐:oracle sql*plus 和PLSQL

用户可以通过在sql plus中输入命令来向数据库发送命令,而数据库也通过sql plus来呈现给用户。 PLSQL:oracle的过程化编程语言。   指定行的长度:show linesize


相关阅读排行


相关内容推荐

最新文章

×

×

请激活账号

为了能正常使用评论、编辑功能及以后陆续为用户提供的其他产品,请激活账号。

您的注册邮箱: 修改

重新发送激活邮件 进入我的邮箱

如果您没有收到激活邮件,请注意检查垃圾箱。