oracle - ORA:00900 - Invalid SQL Statement -

-

i new vast world of oracle. trying is, creating stored procedure , retrieve result. procedure goes as

create or replace procedure usp_rotaplateproductie_select( afdelingid in varchar2, producttypeid in varchar2, productiedata out sys_refcursor)  begin open productiedata select rotaplateproductie.batchnummer, cpiplusproductieorder.productnummer,     product.omschrijving, productieresultaatrtplrol.bruto_in_meters     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     rotaplateproductie.afdelingid = '3144' , rotaplateproductie.producttype = 'pt005'    end;

and using below code trying execute it.

var rc refcursor exec usp_rotaplateproductie_select('3144','pt005', :rc);

while executing above lines getting ora:00900 error.

when run query part of procedure, running fine procedure gives me error.

as shareef pointed out you're missing semicolon on statement inside procedure, doesn't you're creating properly. need / after procedure tell oracle execute code , creation. think it's seeing var , exec statements part of single call, incorrect. i'm not sure how you're running this; if it's in sql developer need 'run script' (f5) rather 'run'.

create or replace procedure usp_rotaplateproductie_select(     p_afdelingid in varchar2,     p_producttypeid in varchar2,     p_productiedata out sys_refcursor)  begin     open p_productiedata         select rotaplateproductie.batchnummer, cpiplusproductieorder.productnummer,             product.omschrijving, productieresultaatrtplrol.bruto_in_meters         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         rotaplateproductie.afdelingid = p_afdelingid         , rotaplateproductie.producttype = p_producttypeid;  end; / var rc refcursor exec usp_rotaplateproductie_select('3144','pt005', :rc); print :rc

i've switched use in parameters, , i've taken liberty of changing names can distinguish between parameters , column names; same can cause confusion.

you might find useful alias tables; no functional difference maybe easier read:

create or replace procedure usp_rotaplateproductie_select(     p_afdelingid in varchar2,     p_producttypeid in varchar2,     p_productiedata out sys_refcursor)  begin     open p_productiedata         select rp.batchnummer, cppo.productnummer,             p.omschrijving, pra.bruto_in_meters         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         rp.afdelingid = p_afdelingid         , rp.producttype = p_producttypeid;  end; /

if message procedure compiled warnings, 'show errors' details.


Comments

Post a Comment

Popular posts from this blog

c# - SVN Error : "svnadmin: E205000: Too many arguments" -

-
i trying reposotries using c# code process svncommand = null; var psi = new processstartinfo("svnadmin"); psi.redirectstandardoutput = true; psi.redirectstandarderror = true; psi.useshellexecute = false; psi.arguments= @"dump c:\repositories\myrepo > c:\temp\myrepodumpfile.dump"; using (svncommand = process.start(psi)) { var myoutput = svncommand.standardoutput; var myerror = svncommand.standarderror; debug.write("output :" + environment.newline + environment.newline + myoutput.readtoend() + environment.newline + "error :" + environment.newline + environment.newline + myerror.readtoend()); svncommand.close(); } when use dump command commandline svnadmin dump c:\repositories\myrepo > c:\temp\myrepodumpfile.dump it works fine when try ...
Read more

c++ - Using OpenSSL in a multi-threaded application -

-
i have been writing soap client application in c++ on ubuntu using openssl https transport , pthreads threading. have number of threads - 1 central data acquisition thread periodically gets worker threads make soap requests via shared mutex protected queues. reading documentation openssl found is openssl thread-safe? in openssl faq describes mechanisms required ensure thread safety when using openssl. implemented , works fine. the reason question conceptual difficulty really. thinking implementing same functionality application has already, instead of using threads, create 2 seperate applications : 1 worker thread (of multiple copies running) , main data acquisition thread. use tcp sockets communicate between 2 rather mutex protected queues. may bad idea not important - confusing me would have implement same functions required ensure openssl thread safety in second approach or not? my guess not have , treated independent (indeed surely must many applications use openssl) re...
Read more

All overlapping substrings matching a java regex -

-
is there api method returns (possibly overlapping) substrings match regular expression? for example, have text string: string t = 04/31 412-555-1235; , , have pattern: pattern p = new pattern("\\d\\d+"); matches strings of 2 or more characters. the matches are: 04, 31, 412, 555, 1235. how overlapping matches? i want code return: 04, 31, 41, 412, 12, 55, 555, 55, 12, 123, 1235, 23, 235, 35. theoretically should possible -- there obvious o(n^2) algorithm enumerates , checks substrings against pattern. edit rather enumerating substrings, safer use region(int start, int end) method in matcher . checking pattern against separate, extracted substring might change result of match (e.g. if there non-capturing group or word boundary check @ start/end of pattern). edit 2 actually, it's unclear whether region() expect zero-width matches. specification vague, , experiments yield disappointing results. for example: string line = "xx90xx"; strin...
Read more