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

How to pass parameter values between Oracle Stored Procedures?

Posted on 2004-04-12
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
  • 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 400 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

828 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