Solved

How to pass parameter values between Oracle Stored Procedures?

Posted on 2004-04-12
5
1,313 Views
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(9.2.0.4) 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:

C#:

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"]);

NewID=execProcNew(prm,ProcPath);

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

ProcPath=ProcPath.Remove(ProcPath.Length-3,3);
ProcPath+="UPDATE_TOOL_EXECUTION";
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"]);

execProc(prm2,ProcPath);
}

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
BEGIN

/* 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);
END IF;

/*This exception is not being thrown - at least I don't get an error message*/
EXCEPTION
WHEN etl$tool_parameter_p.tool_in_use THEN
ROLLBACK;
RAISE etl$tool_parameter_p.tool_in_use;
WHEN others THEN
RAISE;
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

CURSOR par_c 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;

BEGIN
FOR par_r IN par_c LOOP
-- Insert parameters
etl$tool_parameter_p.ins
(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
);
END LOOP;

EXCEPTION
WHEN others THEN
RAISE;
END;

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

PROCEDURE Ins
(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;

BEGIN
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)
VALUES
(p_param_id, p_tool_id, lower(v_param_name), p_param_default_value, p_param_seq, p_datatype_id, p_format_id);
EXCEPTION
WHEN others THEN
raise;
END;

/
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?

/B-heim
0
Comment
Question by:mborgheim
  • 2
5 Comments
 
LVL 22

Expert Comment

by:Helena Marková
Comment Utility
I think that this is a problem:
...
BEGIN
FOR par_r IN par_c LOOP
-- Insert parameters
etl$tool_parameter_p.ins
(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
);
END LOOP;
...
It never loops, there is no
par_r.position
par_r.argument_name ...

I hope this will be a little help for you.
0
 

Author Comment

by:mborgheim
Comment Utility
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#.

/B-heim
0
 
LVL 22

Expert Comment

by:Helena Marková
Comment Utility
Huh, it is an interesting problem, maybe someone else can help you...
0
 
LVL 3

Accepted Solution

by:
dbms_chu earned 400 total points
Comment Utility
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.
http://otn.oracle.com/oramag/oracle/03-mar/o23odp.html

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.

0

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.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now