[ASK TOM]PLSQL参数out copy and out nocopy

razvan -- Thanks for the question regarding "in and out variables - whats the difference", version 7.3.2

Submitted on 19-Jun-2000 9:36 Central time zone

Tom's latest followup | Bookmark | Bottom

Last updated 4-Mar-2008 7:31

You Asked

hello tom,

can you explain me what is the diference between variables in and out in pl/sql?

thanks a lot
razvan 

and we said...

An IN parameter can be read but not written to in plsql.  If I attempt to modify an IN 
parameter -- it will fail at compile time.  For example:

ops$tkyte@8i> create or replace procedure p( x in number )
  2  as
  3  begin
  4          dbms_output.put_line( 'x = ' || x );
  5          x := 55;
  6  end;
  7  /

Warning: Procedure created with compilation errors.
ops$tkyte@8i> show err
Errors for PROCEDURE P:

LINE/COL ERROR
-------- -----------------------------------------------------
5/2      PLS-00363: expression 'X' cannot be used as an 
         assignment target
5/2      PL/SQL: Statement ignored


An IN OUT parameter can not only be READ but it can be WRITTEN to as well.  It retains 
whatever value it had before coming into the subroutine.  Consider:


ops$tkyte@8i> create or replace procedure p( x in OUT number )
  2  as
  3  begin
  4          dbms_output.put_line( 'x = ' || x );
  5          x := 55;
  6  end;
  7  /

Procedure created.

ops$tkyte@8i> declare
  2          y       number default 10;
  3  begin
  4          p(y);
  5          dbms_output.put_line( 'y = ' || y );
  6  end;
  7  /
x = 10
y = 55

PL/SQL procedure successfully completed.


So, the value 10 was passed in and the value 55 was written to it on the way out.  

An OUT parameter can be read and written however an OUT only parameter is always 
assigned NULL on the way into the routine. Consider:


ops$tkyte@8i> create or replace procedure p( x OUT number )
  2  as
  3  begin
  4          dbms_output.put_line( 'x = ' || x );
  5          x := 55;
  6  end;
  7  /

Procedure created.

ops$tkyte@8i> declare
  2          y       number default 10;
  3  begin
  4          p(y);
  5          dbms_output.put_line( 'y = ' || y );
  6  end;
  7  /
x =
y = 55

PL/SQL procedure successfully completed.
on the way in, NULL -- not 10 -- was passed.  OUT parameters always default to NULL.


Here is another example showing that an OUT parameter is *always* modified -- even if we 
don't directly modify it in the routine:

ops$tkyte@8i> create or replace procedure p2( x OUT number )
  2  as
  3  begin
  4          dbms_output.put_line( 'x = ' || x );
  5          -- x := 55;  we do not assign 55
  6  end;
  7  /

Procedure created.

ops$tkyte@8i> declare
  2          y       number default 10;
  3  begin
  4          p2(y);
  5          dbms_output.put_line( 'y = ' || y );
  6  end;
  7  /
x =
y =

PL/SQL procedure successfully completed.

Notice how y is set to NULL, even though we made no assignments to it


It is interesting to note that the ability to READ an OUT parameter is new with 7.3.  In 
prior releases you would have gotten the error:

LINE/COL ERROR
-------- ----------------------------------------------------
4/2      PL/SQL: Statement ignored
4/34     PLS-00365: 'X' is an OUT parameter and cannot be read


 

Reviews

examples using copy and nocopy August 19, 2001 - 5pm Central time zone Bookmark | Bottom | Top Reviewer: Debby from USA Please give more examples using copy and nocopy. and also the example for inout. Thx Followup August 19, 2001 - 9pm Central time zone: Good point, COPY and NOCOPY (new with 8i) modify this behaviour somewhat. Normally paramters are copied to the OUT values AFTER the succesful execution of a procedure, so for example when we compare the behaviour of a COPY and NOCOPY routine: ops$tkyte@ORA8I.WORLD> create or replace procedure p1( x OUT number, y IN OUT number ) 2 as 3 begin 4 x := 55; 5 y := 55; 6 raise program_error; 7 end; 8 / Procedure created. ops$tkyte@ORA8I.WORLD> create or replace procedure p2( x OUT nocopy number, y IN OUT nocopy number ) 2 as 3 begin 4 x := 55; 5 y := 55; 6 raise program_error; 7 end; 8 / Procedure created. ops$tkyte@ORA8I.WORLD> ops$tkyte@ORA8I.WORLD> declare 2 l_x number default 0; 3 l_y number default 0; 4 begin 5 p1( l_x, l_y ); 6 exception 7 when others then 8 dbms_output.put_line( 'x = ' || l_x ); 9 dbms_output.put_line( 'y = ' || l_y ); 10 end; 11 / x = 0 y = 0 PL/SQL procedure successfully completed. ops$tkyte@ORA8I.WORLD> ops$tkyte@ORA8I.WORLD> declare 2 l_x number default 0; 3 l_y number default 0; 4 begin 5 p2( l_x, l_y ); 6 exception 7 when others then 8 dbms_output.put_line( 'x = ' || l_x ); 9 dbms_output.put_line( 'y = ' || l_y ); 10 end; 11 / x = 55 y = 55 PL/SQL procedure successfully completed. we see that x and y's values are different. In the COPY routine -- p1 -- the values are COPIED to the out parameters upon successful completion. In the nocopy routine, PLSQL is in effect sending a pointer to X and Y -- as soon as we modify them in the subroutine, their values are changed in the calling routine. So, that begs the question, why the heck would you want to do this? The side effect seems to be not nice, whats the benefit? Performance: ops$tkyte@ORA8I.WORLD> create or replace procedure p3( x OUT dbms_sql.varchar2s ) 2 as 3 begin 4 for i in 1 .. 20000 loop 5 x(i) := rpad( '*', 255, '*' ); 6 end loop; 7 end; 8 / Procedure created. ops$tkyte@ORA8I.WORLD> create or replace procedure p4( x OUT NOCOPY dbms_sql.varchar2s ) 2 as 3 begin 4 for i in 1 .. 20000 loop 5 x(i) := rpad( '*', 255, '*' ); 6 end loop; 7 end; 8 / Procedure created. ops$tkyte@ORA8I.WORLD> ops$tkyte@ORA8I.WORLD> set timing on ops$tkyte@ORA8I.WORLD> declare 2 l_x dbms_sql.varchar2s; 3 begin 4 p3(l_x); 5 end; 6 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.47 ops$tkyte@ORA8I.WORLD> declare 2 l_x dbms_sql.varchar2s; 3 begin 4 p4(l_x); 5 end; 6 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.35 as you can see, avoiding the copy of that much data can shave some runtime off our execution. You should consider NOCOPY for all large variables (tables of anything) if it doesn't hurt your logic to do so. in what scenarios we need to use out and inout April 23, 2002 - 4pm Central time zone Bookmark | Bottom | Top Reviewer: Schesser Tom, can you generalize as to when specifically we have to use out and inout parameter modes. 1.Actually instead of using an out parameter I can use a function.Isnt it? So what is the utility of out mode. 2.When do we have to or make use of inout mode. Are there any specific situations where we need to use this mode. And lastly, and most interestingly, we use functions from within our sql as it returns a value. Now using out and inout parameter modes we can also return values from procedures, but why cant we use them in sql statements. ( thinking this way there is no difference between functions and procedures except that that the former can be used in sql , where as latter cannot be used) Can you explain. Thank you Followup April 23, 2002 - 7pm Central time zone: 1) some people just like to use procedures. a function can return a single value, a procedure -- many. You can have more then one OUT in a procedure. 2) there are never any cases where you HAVE to. You would use it when you a) want to supply a value that b) the procedure can modify and c) you will recieve the modifications 3) because there is no way for you to declare a variable in a SQL query and in order to call an IN OUT -- you need a local variable (to supply a value to be read from and to supply a place to put the out value into) NOCOPY and collection April 24, 2002 - 3am Central time zone Bookmark | Bottom | Top Reviewer: kiro from Bulgaria Hi Tom NOCOPY is very useful for performance when we are working with collection. What do you mean? Followup April 24, 2002 - 7am Central time zone: re-read the comments/followups. Look for "performance". NOCOPY causes the collection (big data) to be passed by REFERENCE (by a pointer) not by COPYING the data to a temp variable, working on the temp variable, and copying back out again. Legal to select into an OUT parameter?? April 24, 2002 - 5am Central time zone Bookmark | Bottom | Top Reviewer: Pascal from HH,Germany Hi Tom Thanks very much for a Great Discussion on this...but all i know is that NOCOPY is great when there's an IN OUT/OUT pl/sql Table type parameter... I have one doubt though, although i know it's working , but can we safely select into / (Bulk select) INTO an OUT/ IN OUT parameters I was thinking may be only the Assignment is allowed when working with OUT/ IN OUT parameters , but couldn't find Documents to prove this.. Thanks Followup April 24, 2002 - 7am Central time zone: NOCOPY is only useful with IN/OUT and OUT parameters actually. (not available with IN) IN parameters -- already passed by reference. You can only assign to OUT or IN/OUT parameters. Yes you can "safely" select into them. You didnt get my poing April 24, 2002 - 11am Central time zone Bookmark | Bottom | Top Reviewer: Schesser The below as you said "2) there are never any cases where you HAVE to. You would use it when you a) want to supply a value that b) the procedure can modify and c) you will recieve the modifications " Can be done even using a function( except that only one value will be returned using a function, and many using a procedure). Why would I use a procedure , I will use a function. Further, even when I want to return multiple values out from a function, I CAN USE A REF CURSOR variable, and return multiple values out isnt it. Kindly clarify. Followup April 24, 2002 - 3pm Central time zone: As i always say -- there are an INIFINITE number of ways to accomplish most anything. Yes, you can use a function (however, the function would have to allocate a temporary variable, copy the input parameter so it can change it and then return the temporary. Might be more efficient to use a procedure with nocopy) Why would you use a procedure? Because you want to -- it is your choice. A ref cursor is to return a result set. It would not be an appropriate technique for returning formal parameters. April 24, 2002 - 12pm Central time zone Bookmark | Bottom | Top Reviewer: Tianhua Wu from Atlanta, GA Everyday I can learn something new here. I use in and out for quite q while, but I never understand them btter than now, Thankk you! Constant/Static variables April 24, 2002 - 12pm Central time zone Bookmark | Bottom | Top Reviewer: A reader Hi, Tom, Could you clarify and give us an example on constant / static variables like in C/C++ please? Many thanks, Followup April 24, 2002 - 4pm Central time zone: easy ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace package demo_pkg 2 as 3 x CONSTANT number := 5; 4 y number; 5 end; 6 / Package created. ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace package body demo_pkg 2 as 3 a CONSTANT number := 5; 4 b number; 5 end; 6 / Package body created. ops$tkyte@ORA817DEV.US.ORACLE.COM> X is like "#define X 5" in a .h file Y is like int y; defined outside of a function (global variable, avaiable in all code). A is like #define A 5 in the .c file B is like static int b; OUT parameters August 31, 2002 - 10am Central time zone Bookmark | Bottom | Top Reviewer: Mo from Reston, USA Tom: 1. I do understand that IN parameters are a must for a procedure that you want to pass values into. However, Why do I need to define OUT parameters to write to. Can I just declare variables in my PL/SQL program, and write values to those variables and then output the values using dbms_ouput.put_line. Is there a benefit of using an OUT paraemter. 2. Also, in a web application you pass the values that you want to submit by incorporating it in the tags. How do you do this in a client/server program. My thinking is that when you want output of procedure A read into procedure B, you make a call to A within B procedure code. I think in this case you MUST use OUT parameters in A otherwise if you use variables they can not be accessed in B code. AM I correct? Thank you, Followup August 31, 2002 - 10am Central time zone: 1) umm, say your client is a VB program and needs to get the results. dbms_output doesn't do anything in VB (and in fact, dbms_output just contains a procedure get_line(s) that has an OUT parameter that allows the client to get the output and display it - that is what sqlplus does) OUT parameters are necessary for the language PLSQL to be a real programming language. Just like every other language in the world (Java, C, C++, etc) 2) Yes, you are accurate in your depiction -- although you could use globals instead of formal parameters hence the word "must" is a little strong. nocopy - a hint ? September 9, 2002 - 5am Central time zone Bookmark | Bottom | Top Reviewer: ram from India but i read in a book that nocopy is a 'hint' rather than a 'directive' and so the compiler is not obliged to make use of it everytime. the book also mentioned that if the compiler chooses to ignore the hint we will NOT get an error. in your example the values of x and y were different for a procedure with/without nocopy. however if we are not really sure if nocopy is being used then we cant really say that the value of x will be some_value, can we ? or, is it that if the compiler uses it once we can be sure it will be used in all the future calls ? pls clarify. Followup September 9, 2002 - 8am Central time zone: The only thing you can say is: if you use NOCOPY, beware that the behaviour (eg: value) of your in out or out parameters may be different then when NOT using NOCOPY. A Way to get Procedure Parameters ? October 23, 2002 - 6am Central time zone Bookmark | Bottom | Top Reviewer: Christophe Parmentier from Sophia-Antipolis, France Is there a Way to get procedure parameters from another procedure ? E.G. Calling a Trace Procedure that gets the : 1) The Calling Procedure Name? 2) Its parameters Names? to UTL_FILE them... Thanks ! Followup October 23, 2002 - 7am Central time zone: no, there is not. search for DBMS_TRACE on this site or http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76936/dbms_tra.htm#1000263 to see the sort of debug stuff you can capture. OUT and NOCOPY April 24, 2003 - 6am Central time zone Bookmark | Bottom | Top Reviewer: J. Smits from Springfield, MA If an OUT parameter is not assigned to any value during a procedure, its value goes out as NULL (or an empty table) when the procedure completes. However, what is the behavior for an OUT NOCOPY parameter that is not assigned during the procedure? Will it retain the value it had before the procedure call, or will it be nulled out? In addition, although I don't expect you to explain how to predict whether the compiler will actually choose to implement NOCOPY, is there any way we can know after compilation that the compiler chose it? Knowing this would help to quantify the performance improvement that NOCOPY has on a particular process. Followup April 24, 2003 - 8am Central time zone: the semantics of an OUT parameter is a) set it to null b) call procedure c) upon success, copy the value back out NOCOPY only changes the behaviour of C ops$tkyte@ORA920> create or replace procedure nocopy_demo( p1 out NOCOPY number, p2 out NOCOPY number ) 2 as 3 begin 4 p1 := 55; 5 raise program_error; 6 end; 7 / Procedure created. ops$tkyte@ORA920> ops$tkyte@ORA920> declare 2 l1 number := 1; 3 l2 number := 2; 4 begin 5 nocopy_demo( l1, l2 ); 6 exception 7 when others then 8 dbms_output.put_line( 'l1 = ' || l1 ); 9 dbms_output.put_line( 'l2 = ' || l2 ); 10 end; 11 / l1 = 55 l2 = PL/SQL procedure successfully completed. The variable is nulled out..... The only way to quantify the performance increase/decrease in any case (even if you knew that it did the nocopy) would be to benchmark the procedure before and after. Thanks for the clarification April 24, 2003 - 1pm Central time zone Bookmark | Bottom | Top Reviewer: J. Smits Yes, when doing performance testing we always do a before and after to see if a particular change made a difference. However, when a test focusing on NOCOPY does not show a significant difference in performance, we don't know if it was because NOCOPY itself did not have much of an effect, or because the compiler chose not to implement NOCOPY for the particular code base. out parameter April 24, 2003 - 6pm Central time zone Bookmark | Bottom | Top Reviewer: A reader Hi otm, I have procedure create or replace p1(x out char, y out varchar) as begin select 'xyz'||myseq.nextval into x from dual; y := success; end; create or replace p2(x in char, y out varchar) as begin insert into t values(x); y := success; end; create or replace proceudre p( p_1 out char, p_2 in varchar, p_3 out varchar, p_4 out char) is begin /**** calling another procedure p1 that will generate a code(using seq.nexval) and return in p_1 parameter */ p1(p_1,p_3); /******* also use this generated code in the another proceudres... /**----&gt***/ dbms_output.put_lin(p_1); p2(p_1,p_4); end; /*******************************************************/ this procedure works fine if I use sqlplus but, if i use java/jsp --(app - tomcat ) it complains about can not bind LONG value to parameter and p_1 parameter give problems it is some how get LONG ?? values, if called from java but if I do that from sqlplus it is fine they (java developer) uses registerparameter ?? can you please comment on that ? Followup April 24, 2003 - 6pm Central time zone: without actually seeing the smallest possible test case that reproduces the error, I cannot really say what the java programmer is doing wrong in their code. Call to procedure having more than 1 OUT variables... April 25, 2003 - 6am Central time zone Bookmark | Bottom | Top Reviewer: Riaz Shahid from PRAL, Islamabad, Pakistan Hello Tom! How can we call a procedure having more than 1 OUT variables ? create or replace procedure myproc (a OUt number, b OUT number) as begin a:=10; b:=15; end; Now how we will make call to this procedure ? Like declare a number; b number; begin a,b):=myproc; end; But this is not valid. So how we'll do that? Riaz Followup April 25, 2003 - 7am Central time zone: you need to read the PLSQL programmers guide -- this is like "basic programming 101" stuff. Just like you would in ANY 3gl language begin myproc( a, b ); end; / C April 25, 2003 - 10am Central time zone Bookmark | Bottom | Top Reviewer: A reader In sum, nocopy works like passing parameters by reference, like in C. Shouldn't there be an "IN OUT NOCOPY" -- which is how it works in C ? log April 25, 2003 - 12pm Central time zone Bookmark | Bottom | Top Reviewer: A reader hi tom, here is the log from tomcat40 and debug stmts. Apr 22 15:47:47 [DEBUG]: Executing :{call p_mypkg.p (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) } - com.com.oracle.dataaccess.p.processInsertUpdate(Unknown Source) Apr 22 15:47:48 [INFO]: p1 Message Code: DB-SUCCESS - com.oracle.dataaccess.p.processInsertUpdate(Unknown Source) Apr 22 15:47:48 [ERROR]: p1 SQL Error Code: null - com.oracle.dataaccess.p.processInsertUpdate(Unknown Source) /************************/ step 1: p calls p1 and get success and generates a code in out parameter p_1 setp 2 : p calls p2 using new value in p_1 and passes in to p2 as **IN** parametner /*************************/ Apr 22 15:47:48 [INFO]: p2 Message Code: DB-GEN_FAIL_SYSTEM - com.oracle.dataaccess.p(Unknown Source) Apr 22 15:47:48 [ERROR]: p2 SQL Error Code: ORA-01461: can bind a LONG value only for insert into a LONG column - com.oracle.dataaccess.p.processInsertUpdate(Unknown Source) /*************************************/ setp 3: p2 gets fali system setp 4: action as a whole fails... /*************************************/ Followup April 25, 2003 - 3pm Central time zone: that is not java code... test case -- small, concise, yet complete... why nt default nocopy June 2, 2003 - 10am Central time zone Bookmark | Bottom | Top Reviewer: Tanmoy Datta Choudhury from Bangalore,India tom , as u said that nocopy and copy is for performance that why oracle is not using that as a default.. Followup June 2, 2003 - 10am Central time zone: because a) it was not that way in the beginning (so making it the default would affect tons of existing code) b) it has undesirable side effects if applied arbitrarily (it would not be good to be the default) so, to be backwards compatible AND to promote proper behavior of variables -- it is something you have to explicitly (and hopefull knowledgably) choose to use. thanks tom June 4, 2003 - 2am Central time zone Bookmark | Bottom | Top Reviewer: Tanmoy Datta Choudhury from Bangalore,India thanks for your reply ...i understood .. IN OUT in Functions? September 9, 2003 - 10am Central time zone Bookmark | Bottom | Top Reviewer: A reader from Canada Hi Tom! I was wondering, can we also use IN OUT parameters in a function? I know we can use several IN OUT parameters in a procedure, but since a function already returns one value by default, is it ok to add another value it can return by using the IN OUT parameters? Thanks and more power! Followup September 9, 2003 - 11am Central time zone: yes, you can. C External Routines September 9, 2003 - 3pm Central time zone Bookmark | Bottom | Top Reviewer: A reader Hi Tom, Can the NOCOPY be used for returing values from C External Routines? Do we have the same performance benifit? Thanks Passing Ref Cursor Result Set from one stored program to another December 8, 2003 - 10am Central time zone Bookmark | Bottom | Top Reviewer: Justin from PA Tom, We are converting from SQL Server to Oracle. In SQL Server, we have a procedure that calls a function. Here is the SQL Server function: ALTER FUNCTION dbo.fn_CEPrescriptionGetFamilyT071Ids (@intFamilyId int) RETURNS @tblChild TABLE (t071_id int) AS BEGIN INSERT @tblChild SELECT DISTINCT t071_id FROM t071_prescription t071 WITH (NOLOCK) INNER JOIN t067_participant t067 WITH (NOLOCK) INNER JOIN t033_fam_members t033 WITH (NOLOCK) INNER JOIN t014_benefit_fam t014 WITH (NOLOCK) ON t033.t014_id = t014.t014_id ON t067.t069_id = t033.t069_id ON t071.t067_id = t067.t067_id WHERE family_id = @intFamilyId AND fam_mbrship_typ_cd = 'P' RETURN END Here is the SQL Server procedure that is calling the function: ALTER PROCEDURE dbo.sp_CEPrescriptionGetPrescriptionAndContents_Child @intFamilyId int, @intT071Id int AS SET NOCOUNT ON DECLARE @T table ( t071_id int ) IF @intFamilyId > 0 BEGIN INSERT @T SELECT t071_id FROM dbo.fn_CEPrescriptionGetFamilyT071Ids(@intFamilyId) END ELSE BEGIN INSERT @T ( t071_id ) VALUES ( @intT071Id) END SELECT T040_ID ,ABS(T040_ID) AS ID40 ,t040.T071_ID ,FI_SEQ_NR ,T040_STATUS_CD ,t040.T093_ID ,NEWID() AS Id , '' AS WEB_STATUS FROM t040_FI_Spec t040 WITH (NOLOCK) WHERE t040.t071_id IN (SELECT t071_id FROM @T) ORDER BY t040.FI_SEQ_NR GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO I didn't write the SQL Server stuff, but I am trying to duplicate it in Oracle utilizing Ref Cursors. The application is a VB app and we have gotten this to work utilizing "sql types" (myscalartype & myscalartabletype), but can't seem to get Ref Cursors to work. We keep getting inconsistent datatypes errors. (Which I can understand why, but don't know how to remedy.) Here is our Oracle translation attempt at ref cursors which fails from VB with the ORA-00932. *Oracle Function:* CREATE OR REPLACE FUNCTION FN_CEPRESCRIPTIONGETFAMILYT071(intFamilyId IN NUMBER DEFAULT NULL) RETURN types.RC_NUM AS l_rc types.RC_NUM; intFamilyId_ NUMBER(10,0) := intFamilyId; BEGIN OPEN l_rc FOR SELECT DISTINCT t071_id FROM wic_owner8.t071_prescription t071 ,wic_owner8.t067_participant t067 ,wic_owner8.t033_fam_members t033 ,wic_owner8.t014_benefit_fam t014 WHERE (t033.t014_id = t014.t014_id) AND (t067.t069_id = t033.t069_id) AND (t071.t067_id = t067.t067_id) AND (family_id = FN_CEPRESCRIPTIONGETFAMILYT071.intFamilyId_ AND fam_mbrship_typ_cd = 'P'); RETURN l_rc; END FN_CEPRESCRIPTIONGETFAMILYT071; / CREATE OR REPLACE PROCEDURE SP_CEPRESCRIPTIONGETPRESCRIPTI ( intFamilyId int, intT071Id int DEFAULT NULL, l_rc OUT sys_refcursor ) AS BEGIN IF intFamilyId > 0 THEN OPEN l_rc FOR SELECT T040_ID ,ABS(T040_ID) AS ID40 ,t040.T071_ID ,FI_SEQ_NR ,T040_STATUS_CD ,t040.T093_ID ,NEWID() AS Id ,'' AS WEB_STATUS FROM t040_FI_Spec t040 WHERE t040.t071_id IN (select FN_CEPRESCRIPTIONGETFAMILYT071(intFamilyId) from dual ) ORDER BY t040.FI_SEQ_NR; ELSE OPEN l_rc FOR SELECT T040_ID ,ABS(T040_ID) AS ID40 ,t040.T071_ID ,FI_SEQ_NR ,T040_STATUS_CD ,t040.T093_ID ,NEWID() AS Id ,'' AS WEB_STATUS FROM t040_FI_Spec t040 WHERE t040.t071_id IN (intT071Id) ORDER BY t040.FI_SEQ_NR; END IF; END; / I have copied both the function code and the procedure code directly from this window into sqlplus, and both compile fine, but I know the procedure is *wrong*. This: select FN_CEPRESCRIPTIONGETFAMILYT071(1001) from dual; Results in this: FN_CEPRESCRIPTIONGET ==================== CURSOR STATEMENT : 1 CURSOR STATEMENT : 1 T071_ID ================= 1001 1002 486562 11189949 4 rows selected. 1 row selected. This: exec SP_CEPRESCRIPTIONGETPRESCRIPTI (1001,NULL); Results in this: PLS-00306: wrong number or types of arguments in call to 'SP_CEPRESCRIPTIONGETPRESCRIPTI' So if you could go through - step by step - passing the result set of a stored program in the form of a Ref Cursor, from one stored subprogram to another. I am working on a more simplistic test case right now... Thanks much for looking at this! Followup December 9, 2003 - 5am Central time zone: looks like it worked to me - given the select f(x) from dual worked. You just want to (in sqlplus) declare a cursor variable to return the value into and then print it: variable x refcursor exec sp_.....( 1001, null, :x ) print x thats all -- you, you just needed to "get" the last parameter and print it. follow up goof December 8, 2003 - 10am Central time zone Bookmark | Bottom | Top Reviewer: Justin from PA By the way, in case you want to reference page numbers, I have all 3 of your books: Beginning Oracle Programming Effective Oracle by Design Expert One on One... I am looking at pg 572-575 in EOBD. Goof: The Oracle procedure listing in my previous post should have been: CREATE OR REPLACE PROCEDURE SP_CEPRESCRIPTIONGETPRESCRIPTI ( intFamilyId int, intT071Id int DEFAULT NULL ) --, l_rc OUT sys_refcursor ) Without the refcursor parameter declaration. I know we need to have a ref cursor in the parameter list to pump the final result set out to VB, but when I have the ref cursor commented out, then I get the: exec SP_CEPRESCRIPTIONGETPRESCRIPTI(1001); ORA-00932: inconsistent datatypes: expected - got CURSER I was talking about. PS. Why the "got "CURSER""? Is CURSOR simply misspelled CURSER?? I have found a link on your site which may help me December 8, 2003 - 12pm Central time zone Bookmark | Bottom | Top Reviewer: Justin from PA Just wanted to let you know, I found (interestingly enough) by typing in simply "ref cursor" the very first note that comes up is "Returning a refcursor and use it in IN clause" which made me sick, as I've tried so many ways to find it, and it was staring me in the face... I'll try it, and see if I can figure it out with a YAF. Sorry for the thread continuations... Internal Implementation March 8, 2004 - 9am Central time zone Bookmark | Bottom | Top Reviewer: Dhrubo from Kolkata,INDIA Hi Tom I have some basic questions regarding IN OUT implementation My understanding says -- OUT --- "Inside the program, an OUT parameter acts like a variable that has not been initialized. In fact, the OUT parameter has no value at all until the program terminates successfully (without raising an exception, that is). During the execution of the program, any assignments to an OUT parameter are actually made to an internal copy of the OUT parameter. When the program terminates successfully and returns control to the calling block, the value in that local copy is then transferred to the actual OUT parameter. That value is then available in the calling PL/SQL block. " IN -- "IN Parameter is basically a protected pointer." So why is this kind of implementation?Because in case of OUT I feel that has memory overhead and is a complex way of doing thing.In case of IN we could have just passed by value.Please explain Followup March 8, 2004 - 9am Central time zone: in -> pointer passed, data is not copied in out -> value is copied to a temporary, temporary is available read write in the procedure, temporary copied to value upon successful return out -> temporary is assigned NULL, temporary is used in routine, upon success temporary is copied to output. ops$tkyte@ORA9IR2> create or replace procedure p( p_in in number, 2 p_in_out in out number, 3 p_out out number, 4 p_fail in boolean ) 5 is 6 begin 7 dbms_output.put_line 8 ( 'on the way in ' || p_in || ',' || p_in_out || ',' || p_out ); 9 p_in_out := p_in; 10 p_out := p_in; 11 if ( p_fail ) 12 then 13 raise program_error; 14 end if; 15 dbms_output.put_line 16 ( 'on the way out ' || p_in || ',' || p_in_out || ',' || p_out ); 17 end; 18 / Procedure created. ops$tkyte@ORA9IR2> ops$tkyte@ORA9IR2> declare 2 l_in number := 55; 3 l_in_out number := 100; 4 l_out number := 200; 5 begin 6 dbms_output.put_line 7 ( 'before ' || l_in || ',' || l_in_out || ',' || l_out ); 8 begin 9 p( l_in, l_in_out, l_out, TRUE ); 10 exception 11 when others then null; 12 end; 13 dbms_output.put_line 14 ( 'after call 1 ' || l_in || ',' || l_in_out || ',' || l_out ); 15 p( l_in, l_in_out, l_out, FALSE ); 16 dbms_output.put_line 17 ( 'after call 2 ' || l_in || ',' || l_in_out || ',' || l_out ); 18 end; 19 / before 55,100,200 on the way in 55,100, after call 1 55,100,200 on the way in 55,100, on the way out 55,55,55 after call 2 55,55,55 PL/SQL procedure successfully completed. so, IN is passed by reference (no copy, fast). OUT is returned by VALUE (copy upon success) IN OUT is passed by VALUE (copy on way in, copy on way out) and OUT/IN OUT can be modified sometimes with the NOCOPY hint Why OUT is implemented in that way? March 8, 2004 - 11pm Central time zone Bookmark | Bottom | Top Reviewer: Dhrubo from Kolkata,India Hi TOM, I now understand why we implemented IN as a protected pointer but you gave no explanation about why OUT is implemented in that fashion.Isn't copying slow and that method entails higher memory overhead ( though memory is cheap these days) am i too curious? Will be highly enlightened by your explanations Followup March 9, 2004 - 11am Central time zone: it is the very defintion of the langage (borrowed from Ada). your OUT parameter is done conceptually like this: set out_parameter to null call procedure and get temporary back if success then out_parameter := temporary; it makes for predicable code, if procedure "fails", your data is in a known state. see this example: ops$tkyte@ORA9IR2> create or replace package demo_pkg 2 as 3 procedure p1( x out number, fail in boolean, fail2 in boolean ); 4 procedure p2( x out NOCOPY number, fail in boolean, fail2 in boolean ); 5 end; 6 / Package created. ops$tkyte@ORA9IR2> ops$tkyte@ORA9IR2> create or replace package body demo_pkg 2 as 3 4 procedure p1( x out number, fail in boolean, fail2 in boolean ) 5 is 6 begin 7 x := 1; 8 if (fail) then raise program_error; end if; 9 x := 2; 10 if (fail2) then raise program_error; end if; 11 x := 3; 12 end; 13 14 procedure p2( x out number, fail in boolean, fail2 in boolean ) 15 is 16 begin 17 x := 1; 18 if (fail) then raise program_error; end if; 19 x := 2; 20 if (fail2) then raise program_error; end if; 21 x := 3; 22 end; 23 24 end; 25 / Package body created. ops$tkyte@ORA9IR2> ops$tkyte@ORA9IR2> declare 2 x number := 55; 3 begin 4 begin demo_pkg.p1( x, false, false ); exception when others then null; end; 5 dbms_output.put_line( 'p1 f,f ' || x ); 6 x:=55; 7 begin demo_pkg.p1( x, true, false ); exception when others then null; end; 8 dbms_output.put_line( 'p1 t,f ' || x ); 9 x:=55; 10 begin demo_pkg.p1( x, false, true ); exception when others then null; end; 11 dbms_output.put_line( 'p1 f,t ' || x ); 12 x:=55; 13 14 begin demo_pkg.p2( x, false, false ); exception when others then null; end; 15 dbms_output.put_line( 'p2 f,f ' || x ); 16 x:=55; 17 begin demo_pkg.p2( x, true, false ); exception when others then null; end; 18 dbms_output.put_line( 'p2 t,f ' || x ); 19 x:=55; 20 begin demo_pkg.p2( x, false, true ); exception when others then null; end; 21 dbms_output.put_line( 'p2 f,t ' || x ); 22 x:=55; 23 end; 24 / p1 f,f 3 p1 t,f 55 p1 f,t 55 p2 f,f 3 p2 t,f 1 p2 f,t 2 PL/SQL procedure successfully completed. Now, p2 (which uses the "unsafe" nocopy -- pass by reference) returns "unpredicable" results where as the p1 procedure returns consistent, expected results. nocopy??? May 30, 2004 - 10pm Central time zone Bookmark | Bottom | Top Reviewer: NOTNA from Phil. Hi Tom, I was playing with the NOCOPY option with my small db (9204). and here is my code create or replace package pkg1 as type g_dflt_val_tbls is table of varchar2(400) index by varchar2(400); g_dflt_vals g_dflt_val_tbls; procedure test1; procedure test2; end pkg1; / create or replace package body pkg1 as procedure test1 is begin g_dflt_vals('VALUE1') := 'String Val1'; g_dflt_vals('VALUE2') := 1; g_dflt_vals('VALUE3') := null; g_dflt_vals('VALUE4') := 'String Val2'; g_dflt_vals('VALUE5') := 3; g_dflt_vals('VALUE6') := 'String Val3'; g_dflt_vals('VALUE7') := 'String Val4'; g_dflt_vals('VALUE8') := null; pkg2.tst1(g_dflt_vals); test2; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('exception INSIDE PKG1 VALUE 8 =>'||g_dflt_vals('VALUE8')); end test1; procedure test2 is begin dbms_output.put_line('INSIDE PKG1 VALUE 1 =>'||g_dflt_vals('VALUE1')); dbms_output.put_line('INSIDE PKG1 VALUE 2 =>'||g_dflt_vals('VALUE2')); dbms_output.put_line('INSIDE PKG1 VALUE 3 =>'||g_dflt_vals('VALUE3')); dbms_output.put_line('INSIDE PKG1 VALUE 4 =>'||g_dflt_vals('VALUE4')); dbms_output.put_line('INSIDE PKG1 VALUE 5 =>'||g_dflt_vals('VALUE5')); dbms_output.put_line('INSIDE PKG1 VALUE 6 =>'||g_dflt_vals('VALUE6')); dbms_output.put_line('INSIDE PKG1 VALUE 7 =>'||g_dflt_vals('VALUE7')); dbms_output.put_line('INSIDE PKG1 VALUE 8 =>'||g_dflt_vals('VALUE8')); pkg2.tst1(g_dflt_vals); g_dflt_vals('VALUE8') := 'I MODIFIED VALUE 8 pkg1.test2 here'; pkg2.tst2(g_dflt_vals); dbms_output.put_line('INSIDE PKG1 VALUE 8 (AFTER MODIFICATION)=>'||g_dflt_vals('VALUE8')); end test2; end pkg1; / create or replace package pkg2 as procedure tst1(g_dflt_vals in out nocopy pkg1.g_dflt_val_tbls); procedure tst2(g_dflt_vals in out nocopy pkg1.g_dflt_val_tbls); end pkg2; / create or replace package body pkg2 as procedure tst1(g_dflt_vals in out nocopy pkg1.g_dflt_val_tbls) is begin g_dflt_vals('VALUE1') := g_dflt_vals('VALUE1')||'New String Val1'; g_dflt_vals('VALUE2') := g_dflt_vals('VALUE2')+1; g_dflt_vals('VALUE3') := null; g_dflt_vals('VALUE4') := g_dflt_vals('VALUE4')||'New String Val2'; g_dflt_vals('VALUE5') := g_dflt_vals('VALUE5')+1; g_dflt_vals('VALUE6') := g_dflt_vals('VALUE6')||'New String Val3'; g_dflt_vals('VALUE7') := g_dflt_vals('VALUE7') ||'New String Val4'; g_dflt_vals('VALUE8') := g_dflt_vals('VALUE8')||'MODIFIED'; tst2(g_dflt_vals); --raise_application_error(-20001,'SAMPLE ERROR'); end tst1; procedure tst2(g_dflt_vals in out pkg1.g_dflt_val_tbls) is begin dbms_output.put_line('INSIDE PKG2 VALUE 1 =>'||g_dflt_vals('VALUE1')); dbms_output.put_line('INSIDE PKG2 VALUE 2 =>'||g_dflt_vals('VALUE2')); dbms_output.put_line('INSIDE PKG2 VALUE 3 =>'||g_dflt_vals('VALUE3')); dbms_output.put_line('INSIDE PKG2 VALUE 4 =>'||g_dflt_vals('VALUE4')); dbms_output.put_line('INSIDE PKG2 VALUE 5 =>'||g_dflt_vals('VALUE5')); dbms_output.put_line('INSIDE PKG2 VALUE 6 =>'||g_dflt_vals('VALUE6')); dbms_output.put_line('INSIDE PKG2 VALUE 7 =>'||g_dflt_vals('VALUE7')); dbms_output.put_line('INSIDE PKG2 VALUE 8 =>'||g_dflt_vals('VALUE8')); g_dflt_vals('VALUE8') := g_dflt_vals('VALUE8') ||' ASSIGNED A VALUE AT TEST2'; RAISE_APPLICATION_ERROR(-20001,'SAMPLE ERROR'); end tst2; end pkg2; / When I declared pkg2.tst2 with NOCOPY the output is the following : SQL> exec pkg1.test1 INSIDE PKG2 VALUE 1 =>String Val1New String Val1 INSIDE PKG2 VALUE 2 =>2 INSIDE PKG2 VALUE 3 => INSIDE PKG2 VALUE 4 =>String Val2New String Val2 INSIDE PKG2 VALUE 5 =>4 INSIDE PKG2 VALUE 6 =>String Val3New String Val3 INSIDE PKG2 VALUE 7 =>String Val4New String Val4 INSIDE PKG2 VALUE 8 =>MODIFIED exception INSIDE PKG1 VALUE 8 =>MODIFIED ASSIGNED A VALUE AT TEST2 But when I modified the pkg2.tst2 to remove the NOCOPY option, i get this : SQL> set serverout on SQL> exec pkg1.test1 INSIDE PKG2 VALUE 1 =>String Val1New String Val1 INSIDE PKG2 VALUE 2 =>2 INSIDE PKG2 VALUE 3 => INSIDE PKG2 VALUE 4 =>String Val2New String Val2 INSIDE PKG2 VALUE 5 =>4 INSIDE PKG2 VALUE 6 =>String Val3New String Val3 INSIDE PKG2 VALUE 7 =>String Val4New String Val4 INSIDE PKG2 VALUE 8 =>MODIFIED exception INSIDE PKG1 VALUE 8 =>MODIFIED ASSIGNED A VALUE AT TEST2 look at the "exception INSIDE PKG1 VALUE 8" the value is still the same as without the NOCOPY option. what I am expecting is : "exception INSIDE PKG1 VALUE 8 =>MODIFIED", since I did not specify the NOCOPY option. Can you enlighten me with this one? Cheers, NOTNA Followup May 31, 2004 - 12pm Central time zone: really hard time following this as there appears to be pkg1/pkg2/test1/test2, ugh confusing. small, concise, clear, yet 100% complete (the ingredients for a good test case). look at your spec for pkg2. I see nocopy in there? ops$tkyte@ORA9IR2> create or replace package pkg2 2 as 3 procedure tst1(g_dflt_vals in out nocopy pkg1.g_dflt_val_tbls); 4 procedure tst2(g_dflt_vals in out nocopy pkg1.g_dflt_val_tbls); 5 end pkg2; 6 / Package created. ops$tkyte@ORA9IR2> exec pkg1.test1 INSIDE PKG2 VALUE 1 =>String Val1New String Val1 INSIDE PKG2 VALUE 2 =>2 INSIDE PKG2 VALUE 3 => INSIDE PKG2 VALUE 4 =>String Val2New String Val2 INSIDE PKG2 VALUE 5 =>4 INSIDE PKG2 VALUE 6 =>String Val3New String Val3 INSIDE PKG2 VALUE 7 =>String Val4New String Val4 INSIDE PKG2 VALUE 8 =>MODIFIED exception INSIDE PKG1 VALUE 8 =>MODIFIED ASSIGNED A VALUE AT TEST2 PL/SQL procedure successfully completed. ops$tkyte@ORA9IR2> create or replace package pkg2 2 as 3 procedure tst1(g_dflt_vals in out nocopy pkg1.g_dflt_val_tbls); 4 procedure tst2(g_dflt_vals in out pkg1.g_dflt_val_tbls); 5 end pkg2; 6 / Package created. ops$tkyte@ORA9IR2> exec pkg1.test1 INSIDE PKG2 VALUE 1 =>String Val1New String Val1 INSIDE PKG2 VALUE 2 =>2 INSIDE PKG2 VALUE 3 => INSIDE PKG2 VALUE 4 =>String Val2New String Val2 INSIDE PKG2 VALUE 5 =>4 INSIDE PKG2 VALUE 6 =>String Val3New String Val3 INSIDE PKG2 VALUE 7 =>String Val4New String Val4 INSIDE PKG2 VALUE 8 =>MODIFIED exception INSIDE PKG1 VALUE 8 =>MODIFIED PL/SQL procedure successfully completed. Where to put the NOCOPY in a Package. July 15, 2004 - 11am Central time zone Bookmark | Bottom | Top Reviewer: Bob Lyon from Houston, TX By running the following test, it appears that in Packages, the NOCOPY must be in the package spec. Putting a NOCOPY solely in the package body shows no performance improvement. I couldn't find anything in Oracle's documentation really covering this, so I ran the following tests... CREATE OR REPLACE PACKAGE pkgNO_NOCOPY AS procedure p3( x OUT dbms_sql.varchar2s ); END; / CREATE OR REPLACE PACKAGE BODY pkgNO_NOCOPY AS procedure p3( x OUT dbms_sql.varchar2s ) as begin for i in 1 .. 20000 loop x(i) := rpad( '*', 255, '*' ); end loop; end; END; / CREATE OR REPLACE PACKAGE pkgSpec_NOCOPY AS procedure p3( x OUT NOCOPY dbms_sql.varchar2s ); END; / CREATE OR REPLACE PACKAGE BODY pkgSpec_NOCOPY AS procedure p3( x OUT dbms_sql.varchar2s ) as begin for i in 1 .. 20000 loop x(i) := rpad( '*', 255, '*' ); end loop; end; END; / CREATE OR REPLACE PACKAGE pkgBody_NOCOPY AS procedure p3( x OUT dbms_sql.varchar2s ); END; / CREATE OR REPLACE PACKAGE BODY pkgBody_NOCOPY AS procedure p3( x OUT NOCOPY dbms_sql.varchar2s ) as begin for i in 1 .. 20000 loop x(i) := rpad( '*', 255, '*' ); end loop; end; END; / CREATE OR REPLACE PACKAGE pkgBoth_NOCOPY AS procedure p3( x OUT NOCOPY dbms_sql.varchar2s ); END; / CREATE OR REPLACE PACKAGE BODY pkgBoth_NOCOPY AS procedure p3( x OUT NOCOPY dbms_sql.varchar2s ) as begin for i in 1 .. 20000 loop x(i) := rpad( '*', 255, '*' ); end loop; end; END; / set timing on SQL> declare 2 l_x dbms_sql.varchar2s; 3 begin 4 for i in 1 .. 1000 loop 5 pkgNo_NOCOPY.p3(l_x); 6 l_x.DELETE; 7 end loop; 8 9 end; 10 / Elapsed: 00:05:18.02 SQL> SQL> declare 2 l_x dbms_sql.varchar2s; 3 begin 4 for i in 1 .. 1000 loop 5 pkgSpec_NOCOPY.p3(l_x); 6 l_x.DELETE; 7 end loop; 8 end; 9 / Elapsed: 00:03:51.05 SQL> SQL> declare 2 l_x dbms_sql.varchar2s; 3 begin 4 for i in 1 .. 1000 loop 5 pkgBody_NOCOPY.p3(l_x); 6 l_x.DELETE; 7 end loop; 8 end; 9 / Elapsed: 00:05:07.09 SQL> SQL> declare 2 l_x dbms_sql.varchar2s; 3 begin 4 for i in 1 .. 1000 loop 5 pkgBoth_NOCOPY.p3(l_x); 6 l_x.DELETE; 7 end loop; 8 end; 9 / Elapsed: 00:03:51.06 Followup July 15, 2004 - 1pm Central time zone: the spec should match the body. the SPEC is what is visible. pass in variable July 26, 2004 - 10pm Central time zone Bookmark | Bottom | Top Reviewer: A reader Tom, a newbie question. I have a procedure inside the P2PA_EXPENDITURES_IMPORT_PK package: PROCEDURE expenditures_import_main ( errbuf OUT VARCHAR2, retcode OUT NUMBER, p_cefms_foa_code IN VARCHAR2, p_project_number IN VARCHAR2 ); I want to run it for p_cefms_foa_code='M2' and all the p_project_number. I did as : APPS@S0P2DBD5> set serveroutput on size 1000000 APPS@S0P2DBD5> declare 2 v_errbuf varchar2(2000); 3 v_retcode number; 4 v_project_number varchar2(30) := ' '; 5 begin 6 P2PA_EXPENDITURES_IMPORT_PK.expenditures_import_main ( 7 errbuf=> v_errbuf 8 ,retcode=> v_retcode 9 , p_cefms_foa_code => 'M2' 10 , p_project_number => v_project_number 11 ); 12 dbms_output.put_line(v_errbuf); 13 dbms_output.put_line(to_char(v_retcode)); 14 end; 15 / PL/SQL procedure successfully completed. APPS@S0P2DBD5> It finished in a couple of seconds. I am not sure it ran for all the project number or not. Followup July 27, 2004 - 7am Central time zone: and there is no way we could tell either from the above? I mean, you passed in ' ' for the project number, does your code process all project numbers if it is passed that? if so, it did, else it did not. C like pass by copy or reference March 9, 2005 - 5pm Central time zone Bookmark | Bottom | Top Reviewer: A reader Hi I am a beginner C Programmer starting to do some PL/SQL tasks. In C we can pass parameters by value or reference, to pass by reference we use pointers. Char for example are always passed by reference AFIAK. I am reading about this IN - IN/OUT - OUT - NOCOPY theory. IN as you said is passed by reference since no writing is needed IN/OUT is passed by value OUT by value IN/OUT with NOCOPY is passed by reference correct Can OUT passed by reference as well? If so how does it work? Does the procedure simply return the memory address? In the documentation it says this: Because NOCOPY is a hint, not a directive, the compiler can pass NOCOPY parameters to a subprogram by value or by reference. So, if the subprogram exits with an unhandled exception, you cannot rely on the values of the NOCOPY actual parameters It says that if the program exits with an unhandled exception then I cannot rely the values of parameters but how do you handle an exception for this situation? Followup March 9, 2005 - 6pm Central time zone: out can be done with nocopy. basically, if you use NOCOPY - you are saying "if the procedure I call raised an exception, the values of the OUT or IN OUT nocopy parameters are garbage, unreliable and I won't use them" Just like in C if you called a routine and passed an address and the routine longjump()'ed back to you - the contents of the pointers you passed -- what they point to, would be in an unknown state and you just wouldn't use them. As a C programmer myself, you will hate plsql for a little while but come to really like it over time ;) how to call a function with out parameter March 10, 2005 - 2am Central time zone 来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9225895/viewspace-1034763/，如需转载，请注明出处，否则将追究法律责任。 0 0 分享到： 上一篇： RAC Debug开关修改工具 下一篇： [摘选]Choosing Columns and Expressions to Index 请登录后发表评论 登录 全部评论 <%=items[i].createtime%> <%=items[i].content%> <%if(items[i].items.items.length) { %> <%for(var j=0;j <%=items[i].items.items[j].createtime%> 回复 <%=items[i].items.items[j].username%>   回复   <%=items[i].items.items[j].tousername%>： <%=items[i].items.items[j].content%> <%}%> <%if(items[i].items.total > 5) { %> 还有<%=items[i].items.total-5%>条评论 ) data-count=1 data-flag=true>点击查看 <%}%> <%}%> <%}%> cyzhang1983 注册时间：2008-05-04 博文量 67 访问量 163304 最新文章 英语招聘 错误操作触发诊断日志 [METALINK]How To Identify a Hot Block Within The Database Buffer Cache[ID 163424.1] [转]PLSQL AND Bind variables [ASK TOM ]JAVA AND BIND VARIABLE [摘选]Choosing Columns and Expressions to Index RAC Debug开关修改工具 RAC: Frequently Asked Questions [ID 220970.1] ORA-1632: max # extents 4096 reached in index PERFSTAT.STATS$PARAMETER_PK How To Troubleshoot TNS: 12500 errors on Windows Platforms [ID 271072.1] 支持我们 作者招募 用户协议 FAQ Contact Us 北京盛拓优讯信息技术有限公司. 版权所有  京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号：11010802021510 广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

转载于:http://blog.itpub.net/9225895/viewspace-1034763/