oracle ORA:00900 - 无效的 SQL 语句

ORA:00900 - Invalid SQL Statement

提问by Maverick

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.

在执行上述行时，我收到 Ora:00900 错误。

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

当我运行程序的查询部分时，它运行良好，但使用程序时它给了我错误。

回答by Alex Poole

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 varand execstatements 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'.

正如 Shareef 指出的那样，您在程序中的语句中缺少分号，但看起来您实际上并未正确创建它。您需要一个/after 过程来告诉 Oracle 执行代码并实际进行创建。我认为它将varandexec语句视为单个调用的一部分，这是不正确的。但我也不确定你是如何运行它的；如果它在 SQL Developer 中，您需要“运行脚本”(F5) 而不仅仅是“运行”。

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.

我已经切换到使用 IN 参数，并且我随意更改了名称，以便您可以区分参数和列名称；它们是相同的，这可能会引起混淆。

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.

如果您收到一条消息，该程序编译时带有警告，请执行“显示错误”以获取详细信息。

回答by shareef

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

我它有效请回复我想你最后错过了分号

;

@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);

;

@Mavrik 编辑

在查询的末尾添加分号后，程序创建正确。谢谢，但是当我尝试使用下面的代码执行它时它仍然返回相同的错误代码

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

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

更新 1：如果你有 ora 00900 可能是因为

Oracle/PLSQL: ORA-00900 Error

Oracle/PLSQL：ORA-00900 错误