Stored Procedure parameters

Stored Procedure parameters

• Eric Knox MSFT

  I tested against 10g and 9i with your fire_employee procedure, and I was able to get the parameter successfully both in code and in the Visual Studio Server Explorer.

  Here is the code I'm using:

  using (oraConn = new OracleConnection(@"Data Source=myServer;User Id=scott;Password=Tiger;"))
  {
  oraConn.Open();

  DataTable schemaTable = oraConn.GetSchema(schemaName, new string[] { "SCOTT" });
  printDataTable(schemaTable); //this is my own function that just dumps the DataTable contents
  }

  This returns one row with all of the info for the EMP_ID argument. However, since even VS isn't showing you the parameters, I don't think the problem is in your code. OracleClient is just executing a query against one of the Oracle system catalogs, so you should verify that the query works properly in a tool like SQL*PLUS, outside of .NET.

  The actual query being executed for the Arguments schema looks like this:

  SELECT OWNER, PACKAGE_NAME, OBJECT_NAME, ARGUMENT_NAME, POSITION, SEQUENCE, DEFAULT_VALUE, DEFAULT_LENGTH, IN_OUT, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, DATA_TYPE FROM ALL_ARGUMENTS WHERE (OWNER= :OWNER OR :OWNER is null) AND (PACKAGE_NAME = :PACKAGENAME OR :PACKAGENAME is null) AND (OBJECT_NAME = :OBJECTNAME OR :OBJECTNAME is null) AND (ARGUMENT_NAME = :ARGUMENTNAME OR :ARGUMENTNAME is null)

  (NOTE: You can get the above using the tracing that is built into System.Data.OracleClient in .NET 2.0. See http://msdn.microsoft.com/library/default.asp url=/library/en-us/dnadonet/html/tracingdataaccess.asp for instructions)

  The parameters :OWNER, :PACKAGENAME, :OBJECTNAME, and :ARGUMENTNAME are created and bound internally in the provider, based on the restrictions that you pass in the GetSchema call. If you are using just the owner restriction like I am using in my example above, you can simplify the query and remove the parameters like this:

  SELECT OWNER, PACKAGE_NAME, OBJECT_NAME, ARGUMENT_NAME, POSITION, SEQUENCE, DEFAULT_VALUE, DEFAULT_LENGTH, IN_OUT, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, DATA_TYPE FROM ALL_ARGUMENTS WHERE (OWNER= 'SCOTT')

  When I run this in SQL*PLUS, I get the same data that I get in Server Explorer and when I run the GetSchema method against the Arguments schema.

  You can also run a trace while trying to retrieve the value from Server Explorer, and you will see it uses a slightly different query that looks like this:

  SELECT * FROM ALL_ARGUMENTS WHERE DATA_LEVEL = 0 AND (OWNER = :OWNER OR :OWNER is null) AND (OBJECT_NAME = :OBJECTNAME OR :OBJECTNAME is null)

  Again there are parameters that are created and bound internally, but again you could simplify this and just specify hard-coded values based on your procedure's owner and name, like this:

  SELECT * FROM ALL_ARGUMENTS WHERE DATA_LEVEL = 0 AND (OWNER = 'SCOTT') AND (OBJECT_NAME = 'FIRE_EMPLOYEE');

  Again, this gives me the data for the one argument.

  Try these queries and see if they give the right data or not, then we'll go from there.

  Thanks,
  Sarah

  
  
  

• slight

  Hello Sarah,

  Back once again with oracle procedure problem.

  I have even tried to see the list of oracle procedure parameters in Server Explorer window in Visual Studio 2005. Even Visual Studio.Net is not showing any parameter list or any info regarding any parameter from any oracle procedure.

  I have established connection to oracle database in server explorer. Its showing all tables, their fields, and other objects. Its even showing all procedure names in the tree but not showing any parameters for any procedure.

  What could be the reason. Please help. Thanks.

  
  
  

• sburton

  Hello Sarah,

  I have tested all the sql queries which you have given me in ur last reply in Sql*Plus

  but its showing "no rows selected". Is there something wrong in my oracle procedure code or what

  I can see those stored procedures which I have provided you as example in my Oracle Enterprise Manager under the user account "Scott". But when I am using sql queries which you have provided, I am not getting any result and its showing "no rows selected". What could be the issue. I m not able to guess. Please help.

  Please provide me an example oracle procedure with some parameters so that I can compile them into my oracle database and then try to retrieve its parameter information by using sql queries which you have provided me in ur last reply.

  Thanks again for being so patient.

  
  
  

• sbadams

  Thanks Kalpa. I know this but I want to retrieve information about parameters of any procedure in Oracle database. For example if there is procedure like abc with some parameters, I want to get info like data type, size, direction ,etc for each and every parameter in this abc procedure.

  If u know or find any information, please help me.

  Thanks.

  
  
  

• Galford

  Are you actually using a query, or are you using the schema functionality in ADO.NET 2.0 If you are using schemas, you can use the Procedures schema. However, there is a restriction in that it won't return any procedures that are defined inside of a package.

  Thanks,
  Sarah

  
  
  

• Cathyjack

  Hi Hasmi,

  Can you tell me the query of how to extract the Procedure Names from Oracle. I have written the query to get the Table and Views but I am not getting to query to get the Stored Procedures Names.

  Thanks,

  amit

  
  
  

• cgerull

  Hello Sarah,

  First of all thanks a lot for such a nice and prompt reply. I liked it. I hope I got a very good and knowledgeable lady to learn many things.

  Now the thing is I have tried both GetSchema with Arguments with proper restrictions or no restrictions as well as DeriveParameters but failed to retireve parameter information.

  I am using System.Data.OracleClient data provider.

  Here are the oracle procedure which I am using:

  CREATE PROCEDURE fire_employee (emp_id in NUMBER) is
  BEGIN
  DELETE FROM emp WHERE empno = emp_id;
  END;

  And... another one for example is:

  PROCEDURE calc_bonus (emp_id IN INTEGER, bonus OUT REAL) IS
  hire_date DATE;
  BEGIN
  SELECT sal * 0.10, hiredate INTO bonus, hire_date FROM emp
  WHERE empno = emp_id;
  IF MONTHS_BETWEEN(SYSDATE, hire_date) > 60 THEN
  bonus := bonus + 500; -- causes syntax error
  END IF;
  End;

  Is there something wrong in this procedure codes or

  If possible can u provide me a sample oracle procedure with some parameters and also code which you have used to test DeriveParameters and GetSchema with Arguments so that I can go through it plz.

  If you wish to send me the requested code, then u can send to hashsoft@gmail.com.

  Thanks a lot again.

  
  
  

• Bekas

  I think you must have a permissions problem or some other configuration issue on your Oracle server that is not allowing you to select rows from that catalog table. If the SELECTs don't even work when run directly outside of .NET, you should work with Oracle to figure out why. I don't know enough about the Oracle server side to know why that wouldn't work, I just know that is the query we are using to populate the schema in the .NET provider. The ALL_ARGUMENTS table is not something that Microsoft is creating -- it's an Oracle system table.

  It's not your procedure code -- I used exactly what you have provided (specifically the fire_employee procedure) and it works fine for me. I didn't modify anything, just copied and pasted exactly what you posted.

  If you just do "SELECT * FROM ALL_ARGUMENTS", with no WHERE clause, do you still get no rows

  Thanks,
  Sarah

  
  
  

• skhan22

  I have a 10g database that I query against. I have a piece of code that builds the command object dynmically and executes procedures

  I have tow databases both 10g 2.0.2

  the following query works on one and does not work on the other database when I call it though my code in VB.NEt 1.1

  select * from ALL_ARGUMENTS

  where object_id=(select object_id from all_objects where owner=SCHEMANAME and upper(object_type)=upper(ObjectType) and upper(object_name)=Upper(ObjectName))

  and owner=SCHEMANAME

  order by sequence asc;

  I get the following error

  "Failed to initialize distributed transaction. Please see KB article Q322343"

  I am using System.Data.OracleClient 1.0 in both cases.

  Any help is highly appreciated

  
  

• JudyinFL

  select * from user_object where object_name=PROCEDURE_NAME and object_type='PROCEDURE'
  
  

• Kroeze

  Which .NET data provider are you using With System.Data.OracleClient, you should be able to get schema information and use DeriveParameters. If you can post more information about what is not working for you, I can help more. Is that an exact error message that you posted in bold Which method is throwing it Can you provide a call stack

  In .NET 2.0, we added the OracleConnection.GetSchema method that allows you to gather metadata from a variety of objects. To get stored procedure parameter information, you should be able to use the Arguments schema.

  Also, I just tested DeriveParameters with a procedure and was able to get the parameters.

  Please post an example stored procedure that is not working for you, along with a code snippet that fails, the error message, and call stack.

  See the following reference for more information on GetSchema and the available schemas for System.Data.OracleClient:

  Understanding the Provider-Specific Schema Collections
  http://msdn2.microsoft.com/en-us/library/ms254969(d=ide).aspx

  Thanks,
  Sarah

  
  
  

• Kobe_London

  Hello Sarah,

  I have tried to use both DeriveParameters as well as GetSchema("Arguments") with and without restrictions also for this simple oracle procedure but both methods failed. I am using Oracle 9i Relese 1.

  Please help .

  create or replace procedure Authors_Array(pEmail VARCHAR2, pName VARCHAR2) is
  begin
  INSERT INTO aspallianceauthors (authoremail, authorfullname) VALUES (pEmail, pName);
  Commit;
  end Authors_Array;

  
  
  

• Marco.Casamento

  Moved SqlParameter caching question to a new thread -- http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=468584&SiteID=1
  
  

• nCognito

  Hashmi,

  I have the same problem you have. I want to get the complete definition of a stored procedure, i.e. procedure name, parameters, etc. Did you ever get a correct answer

  If so, please share with me.

  David

  
  
• Stored Procedure parameters