StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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 <code>/</code> after the procedure to tell Oracle to execute the code and actually do the creation. I think it's seeing the <code>var</code> and <code>exec</code> 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'.</p> <pre><code>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 </code></pre> <p>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.</p> <p>You might also find it useful to alias your tables; no functional difference but maybe easier to read:</p> <pre><code>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; / </code></pre> <p>If you get a message that the procedure compiled with warnings, do 'show errors' to get the details.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload