Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


How to pass parameter values between Oracle Stored Procedures?

Posted on 2004-04-12
Medium Priority
Last Modified: 2013-12-11
I'm writing a C# user interface for an Oracle 9i Database. To perform updates, inserts and deletes I call (already existing) PL/SQL procedures.
I'm using ODP.NET( to communicate between .NET and Oracle.

When I call an Insert stored procedure directly with parameters, everything works fine. But when I call a procedure, and that procedure in turn calls another procedure, nothing happens! It's something wrong with my parameter values, but what?

My code looks like the following:


try { OracleParameter[] prm=new OracleParameter[6];

/*setting parameter values for the first Insert operation. The routine execProcNew inserts a new record and returns an ID value, which will be used in the next Insert operation. This call works fine*/

prm[0]=new OracleParameter(":p_tool_id",OracleDbType.Int32,10,ParameterDirection.InputOutput,false,0,0,"",DataRowVersion.Proposed,0);
prm[1]=new OracleParameter(":p_tool_location",OracleDbType.Varchar2,200,ParameterDirection.Input,true,0,0,"",DataRowVersion.Proposed,dsIns.Tables["ETL$TOOL"].Rows[i]["tool_location"]);
prm[2]=new OracleParameter(":p_tool_schema",OracleDbType.Varchar2,40,ParameterDirection.Input,true,0,0,"",DataRowVersion.Proposed,dsIns.Tables["ETL$TOOL"].Rows[i]["tool_schema"]);
prm[3]=new OracleParameter(":p_tool_name",OracleDbType.Varchar2,40,ParameterDirection.Input,false,0,0,"",DataRowVersion.Proposed,dsIns.Tables["ETL$TOOL"].Rows[i]["tool_name"]);
prm[4]=new OracleParameter(":p_tool_description",OracleDbType.Varchar2,200,ParameterDirection.Input,true,0,0,"",DataRowVersion.Proposed,dsIns.Tables["ETL$TOOL"].Rows[i]["tool_description"]);
prm[5]=new OracleParameter(":p_tool_exec",OracleDbType.Varchar2,200,ParameterDirection.Input,true,0,0,"",DataRowVersion.Proposed,dsIns.Tables["ETL$TOOL"].Rows[i]["tool_exec"]);


/*ProcPath is a string that contains the path to the correct oracle stored procedure*/

OracleParameter[] prm2=new OracleParameter[3];

/*Setting parameter values for the next insert operation. The first parameter value is set to the newly returned NewId, that came from the last insert. The other two parameter values were also sent to the Insert operation above, and they caused no problems there. The routine execProc calls an Oracle Procedure, and doesn't return any values */

prm2[0]=new OracleParameter(":p_tool_id",OracleDbType.Int32,10,ParameterDirection.Input,false,0,0,"",DataRowVersion.Proposed,NewID);
prm2[1]=new OracleParameter(":p_tool_schema",OracleDbType.VarChar2,40,ParameterDirection.Input,false,0,0,"",DataRowVersion.Proposed,dsIns.Tables["ETL$TOOL"].Rows[i]["tool_schema"]);
prm2[2]=new OracleParameter(":p_tool_exec",OracleDbType.VarChar2,200,ParameterDirection.Input,false,0,0,"",DataRowVersion.Proposed,dsIns.Tables["ETL$TOOL"].Rows[i]["tool_exec"]);


And here are the PL/SQL code:

/*This proc is the one being called by the second call above, the one that fails*/
PROCEDURE Update_Tool_Execution
(p_tool_id IN etl$tool.tool_id%TYPE, p_tool_schema IN etl$tool.tool_schema%TYPE, p_tool_exec IN etl$tool.tool_exec%TYPE) IS

/* p_tool_exec is not null here, I have checked*/
IF p_tool_exec IS NOT NULL THEN

/*Another procedure is being called from this one, with the same parameters*/
etl$tool_parameter_p.Insert_Parameters (p_tool_id,p_tool_schema,p_tool_exec);

/*This exception is not being thrown - at least I don't get an error message*/
WHEN etl$tool_parameter_p.tool_in_use THEN
RAISE etl$tool_parameter_p.tool_in_use;
WHEN others THEN
END Update_Tool_Execution;

/*This procedure is being called by the procedure above*/

PROCEDURE Insert_Parameters
(p_tool_id IN etl$tool.tool_id%TYPE, p_tool_schema IN etl$tool.tool_schema%TYPE, p_tool_exec IN etl$tool.tool_exec%TYPE) IS

SELECT aa.position, aa.argument_name, pd.datatype_id, pd.datatype_name,
CASE WHEN aa.data_type = 'OBJECT' THEN aa.type_name
ELSE aa.data_type END AS argument_datatype
FROM all_arguments aa, etl$parameter_datatype pd
WHERE aa.owner = p_tool_schema
AND aa.package_name||'.'||aa.object_name = p_tool_exec
AND pd.datatype_name(+) = CASE WHEN aa.data_type = 'OBJECT' THEN aa.type_name
ELSE aa.data_type END
ORDER BY aa.position;

/*For each row the above SQL statement returns there should be an Insert operation. The problem seems to be that the cursor is empty. If I run this statement and my parameter values are enclosed by ' signs, then it works. But when I run this procedure in debug mode (in TOAD) and encloses the parameter value like this - 'param value' - then the SQL statement returns nothing! And no error message is being displayed... So how do I pass parameter values from .NET to an Oracle stored procedure, and then further on to another Oracle stored procedure? It is here that something goes wrong.*/

v_param_id number(10) := NULL;
v_datatype_id number(10) := NULL;

FOR par_r IN par_c LOOP
-- Insert parameters
(p_param_id => v_param_id
,p_tool_id => p_tool_id
,p_param_name => par_r.argument_name
,p_param_default_value => '<Type default value>'
,p_param_seq => par_r.position
,p_datatype_id => par_r.datatype_id
,p_format_id => NULL

WHEN others THEN

/*And finally the second insert procedure, that should be called by the procedure above*/

(p_param_id IN OUT etl$tool_parameter.param_id%TYPE
,p_tool_id IN etl$tool_parameter.tool_id%TYPE
,p_param_name IN etl$tool_parameter.param_name%TYPE
,p_param_default_value IN etl$tool_parameter.param_default_value%TYPE
,p_param_seq IN etl$tool_parameter.param_seq%TYPE
,p_datatype_id IN etl$tool_parameter.datatype_id%TYPE
,p_format_id IN etl$tool_parameter.format_id%TYPE
) IS
|| INSERT procedure
v_param_name etl$tool_parameter.param_name%TYPE;

SELECT etl$tool_parameter_seq.NEXTVAL
INTO p_param_id
FROM dual;

-- A variable is needed to convert the value to lower case
v_param_name := p_param_name;

INSERT INTO etl$tool_parameter
(param_id, tool_id, param_name, param_default_value, param_seq, datatype_id, format_id)
(p_param_id, p_tool_id, lower(v_param_name), p_param_default_value, p_param_seq, p_datatype_id, p_format_id);
WHEN others THEN

If I call the latest proc - Ins - directly from .NET, then records are being added. Why so? And why doesn't it work if I call  Update_Tool_Excecution with the right values?

My first guess would be that my parameters - although they contain correct values - are not in the correct format. But what is the correct format then? I have so far not been able to run my Oracle stored procedures successfully...

I find it is also a bit strange that if I call an Insert procedure directly from .NET - that is, when I don't try to pass parameter values on to another procedure - then it works!

How do I pass parameter values from .NET to an Oracle stored procedure, and then further on to another Oracle stored procedure? How should I enclose the parameter values?

Question by:mborgheim
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 22

Expert Comment

by:Helena Marková
ID: 10811569
I think that this is a problem:
FOR par_r IN par_c LOOP
-- Insert parameters
(p_param_id => v_param_id
,p_tool_id => p_tool_id
,p_param_name => par_r.argument_name
,p_param_default_value => '<Type default value>'
,p_param_seq => par_r.position
,p_datatype_id => par_r.datatype_id
,p_format_id => NULL
It never loops, there is no
par_r.argument_name ...

I hope this will be a little help for you.

Author Comment

ID: 10811629
I don't think so. If you look closer at the PL/SQL that should populate the cursor, you'll see that each of the cursor's rows has five fields: position, argument_name, datatype_id, datatype_name and argument_datatype.

The strangest thing with this code is that it works today, if you call it from Oracle Forms. It also works if I run it in as 'pure' PL/SQL, as I mentioned above. But it doesn't work when I call it from C#.

LVL 22

Expert Comment

by:Helena Marková
ID: 10811671
Huh, it is an interesting problem, maybe someone else can help you...

Accepted Solution

dbms_chu earned 1200 total points
ID: 10850982
Can you add dbms_application_info.set_client_info(p_tool_id||'   '||p_tool_schema||'   '||p_tool_exec) and dbms_output.put_line(p_tool_id||'   '||p_tool_schema||'   '||p_tool_exec) to help confirm what the sp is receiving from C#?  It may also help to add a dbms_output.put_line() with sqlcode and sqlerr in the exception block of UPDATE_TOOL_EXECUTION.

If you can call UPDATE_TOOL_EXECUTION from sqlplus and get the correct records inserted into etl$tool_parameter then I would focus on the C# code.  

OTN has a article on ODP.net with descriptions using C# on how to setup stored proc calls and parameters.

Regarding your comment on TOAD DEBUG:
As I recall, TOAD debug does not want quotes around your input values unless you want quotes in the string.


Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question