Work with SQL Server more, Oracle can be said to be small white, recently want to use stored procedures to complete the document copy function, the results encountered a variety of problems, in fact, is very simple problem, but for me still spent a lot of time to solve, waste these time is very not worth, It also makes me more and more fond of Microsoft's products, whether it is Visual Studio, SQL Server Management Studio or office, the ease of use is very good.
The stored procedure I'm trying to create is not complicated, that is, the fields that are inserted into the table are so much more TMD, the code is:
Create or Replace procedureProcpushmeasure (p_pk_measurevarchar2) asv_pk_measureChar( -); begin --the primary key for the new document is replaced with ' PUSH ' in the 第5-8位 keyV_pk_measure:=SUBSTR (P_pk_measure,1,4)||'PUSH'||SUBSTR (P_pk_measure,9); Dbms_output.put_line (P_pk_measure||'='||v_pk_measure); Insert into Jgpm_cm_measure (Pk_measure, Vbillno, Vbillstatus, Biscollect, Bissplit, Bisupload, Dapprovedate, Dbilldate, Dmakedate, Dmeapprodate, Dr, Dreportdate, Napplybasemny, Napplyorigmny, Napplyrate, Nbaserate, Ncanpayb Asemny, Ncanpayorigmny, Ncanpayrate, Ncontbasemny, Ncontorigmny, Ncurrpaybasemny, Ncurrpayorigmny, Ncurrprodbasemny, Ncurrprodorigmny, Nexecbasemny, Nexecorigmny, Nmeaapplybasemny, Nmeaapplyorigmny, Nmeaapprbasemny , Nmeaapprorigmny, Nmeasurbasemny, Nmeasurorigmny, Npaybasemny, Npayorigmny, Npayrate, Nprev Paybasemny, Nprevpayorigmny, Nprevprodbasemny, Nprevprodorigmny, Nsumapplybasemny, Nsumapplyorigmny, Nsummeabasemny, Nsummeaorigmny, Pk_basetype, Pk_billtype, Pk_cont, Pk_corp, Pk_file, Pk_origintype, Pk_project, p K_source, Sourcets, sourcetype, TS, Vapproveid, Vapprovenote, Vauditor, Vconmanager, Vdealer ID, VDEF1, VDEF10, Vdef2,VDEF3, VDEF5, Vdef6, Vdef7, Vdef8, Vdef9, Vdeptid, Vmemo, Voperatorid, vprocess, Vrealmeasno , Vreserve1, Vreserve10, Vreserve2, Vreserve3, Vreserve4, Vreserve5, Vreserve6, Vreserve7, Vreserve8, Vreserve9, Vsuperma Nager, Vyearprojpoint, Dreceiptdate, Vmanagerpinion, Vapprvstatus, vrealprocess, Nmeaapprori Gmnypart1, Nmeaapprbasemnypart1, Nmeaapprorigmnypart2, Nmeaapprbasemnypart2, Nmeaapprorigmnypart3, Nmeaapprbasemnypart3, Itermno, Vyearmonth, Dtermbegindate, Dtermenddate, Nmeaapplyedorigmny, Nmeaapplyedbasemny, PK _valstat, Nmeaapplyedbasemny, Nmeaapplyedorigmny)SELECTV_pk_measure,'t85l'||SUBSTR (Vbillno,5),8, Biscollect, Bissplit, Bisupload, Dapprovedate, Dbilldate, Dmakedate, Dmeapprodate, Dr, Dreportdate, Napplybasemny, Napplyorigmny, Napplyrate, Nbaserate, Ncanpaybasemny, Ncanpayorigmny, Ncanpayrate, Ncontbasemny, n Contorigmny, Ncurrpaybasemny, Ncurrpayorigmny, Ncurrprodbasemny, Ncurrprodorigmny, nexecbase Mny, Nexecorigmny, Nmeaapplybasemny, Nmeaapplyorigmny, Nmeaapprbasemny, Nmeaapprorigmny, Nmeasurbasemny, Nmeasurorigmny, Npaybasemny, Npayorigmny, Npayrate, Nprevpaybasemny, Nprevpayorigmny, Nprevprodbasemny, Nprev Prodorigmny, Nsumapplybasemny, Nsumapplyorigmny, Nsummeabasemny, Nsummeaorigmny, Pk_basetype , Pk_billtype, Pk_cont, Pk_corp, Pk_file, Pk_origintype, Pk_project, Pk_source, Sourcets, sourcetype, TS, Vapproveid, Vapprovenote, Vauditor, Vconmanager, Vdealerid, Vdef1, VDEF10, Vdef2, VDEF3, VDEF5, Vdef6, Vdef7 , Vdef8, Vdef9, Vdeptid, Vmemo, Voperatorid, vprocess, Vrealmeasno, vreserve1, Vreserve10, Vreserve2, Vreserve3, VRE Serve4, Vreserve5, Vreserve6, Vreserve7, Vreserve8, Vreserve9, Vsupermanager, Vyearprojpoint, Dreceiptdate, Vmanagerpinion, Vapprvstatus, vrealprocess, Nmeaapprorigmnypart1, Nmeaapprbasemnypart1, Nmeaapprorigmnypart2, Nmeaapprbasemnypart2, Nmeaapprorigmnypart3, NMEAAPPRBASEMNYPART3, ITER MNO, Vyearmonth, Dtermbegindate, Dtermenddate, Nmeaapplyedorigmny, Nmeaapplyedbasemny, Pk_valstat, Nmeaapplyedbasemny , Nmeaapplyedorigmny fromjgpm_cm_measureWHEREPk_measure=p_pk_measure;End;
After you execute a script that creates a stored procedure after execution in PL/SQL developer, you cannot invoke the stored procedure with the following scripts to report an error: Invalid object
In the list of objects on the left, you can see that there is a red fork in front of the stored procedure, which indicates that the stored procedure was created with a problem and the number of fields was reduced to only two or three necessary fields, the stored procedure was created successfully, but because there are too many fields, a field is added, It is a bit foolish to test the creation of a successful method.
After a long time and finally found a way, on the stored procedure, right-click, select View
It's going to be a good thing back there.
Oracle stored procedure creation failed, how to view its cause