DECLARE
CUR_EMP_BIR REF CURSOR;
BEGIN
dbname.EMPLOYEE_P.EMPLOYEEBIRTHDAYSEL ( CUR_EMP_BIR );
END;
ORA-06550: line 2, column 19:
PLS-00201: identifier 'CURSOR' must be declared
ORA-06550: line 2, column 15:
PL/SQL: Item ignored
ORA-06550: line 5, column 45:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
[code]...
i am getting this error while fetching select statement in oracle pl/sql proc... ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'SP_CLASS' ORA-06550: line 1, column 7: PL/SQL: Statement ignored My proc is like..PROCEDURE "SP_CLASS" ( iDec IN NUMBER := null, p_ICID in number := null, p_DATA OUT SYS_REFCURSOR) IS BEGIN if(iDec = 1) then open p_DATA for SELECT ICID AS ID, SNAME AS NAME FROM SETUP_CLASS; else if(iDec = 2) then open p_DATA for SELECT ICID AS ID, SNAME AS NAME FROM SETUP_CLASS where ICID = p_ICID ; end if; end if; END;
View 1 RepliesI would like to use a technique for exception handling on our database stored procs whereby, each proc exposes a p_error_code output parameter of type int. 0 indicates no error, and other values indicate application specific exceptions. Now, where a stored proc will return a ref cursor if no exceeptions occur, we would like to check the p_error_code and if not 0, then use the ref cursor. If an exception does occur, then the stored proc may or may not have populated the ref cursor, but the dotnet code will almost definately not read the contents.
OracleCommand command = CommandFactory.CreateCommand(transaction, "RTS.p_get_audit_trail");
I want to have a stored procedure which will accept a Datatable from .NET application and the tablename and will loop thru' each record in the Datatable and insert it into the table. It will insert the bad records into a bad file. Also how to send the datatable to the proc,what kind of parameter to use.
Pls post the code for this proc.
i have two stored procs. when th 1st stored proc runs i want to be able to use this value returned from the stored into another stored proc.
my first stored proc is:
[Code]....
which returns [Transfer_stations_Authority_name] = 'Blackpool'
now i want to use this value into my second stored proc@
[Code]....
[Code]....
I would like to know if there is a way to call Oracle stored procedure from inside code?
View 2 RepliesI have an ASP.NET application that uses an Oracle backend. I want to be able to step into the Oracle packages and stored procedures during debugging.
Is this possible and if so what toolset would be needed?
i want to send mails internally and externally for eg. i want to send mail to my boss in the company and also to the customer which doesnt belong to our domain.
[URL]
and also i want to attach an excel file which is stored in my computer. so how am i going to access that file through the same procedure and then attach the file and send it to both internal as well as external email ids.
I've downloaded OracleHelper.cs & i'm using it for SELECT Statements so far &
Now i'm suppose to INSERT & UPDATE with it....
Will someone give an example for how to insert & update using oracle helper for TEXT & STORED PROCEDURE !
List<OracleParameter> OrclParam = new List<OracleParameter>();
OrclParam.Add(new OracleParameter("@eid",OracleDbType.Int32).Value) = a_type;
OrclParam.Add(new OracleParameter("@ename",OracleDbType.Varchar2).Value) = a_name;
OracleHelper.ExecuteDataset(connectionString, CommandType.Text,
"Insert into Employees (EID,ENAME) values(@eid,@ename)");
The above codes does'nt work
I am looking through a sql stored procedure which I might need to update in the near future. Basically the stored procedure is about 20 lines long. The stored procedure first builds a query
and stores it in a variable named "@Sql". And then for the last two lines of the stored procedure it appears that the big sql statement stored in "@Sql" is executed by using the "EXEC" command. See below. What is confusing though is that the query appears to be exectuted twice? Why was the query written in this way. Don't both lines do the same thing? Why is it being done twice? Could this possibly be a mistake on the
part of the person who wrote the query. Below are the two lines I am talking about?
EXEC sp_executesql @sql
EXEC (@sql)
I'm working on locking down some MySQL user accounts. At the moment I typically grant my user accounts execute privileges over the required stored procedures within a schema.Now I'm looking at the proc table in the mysql schema. The common wisdom that's quoted many times on various websites says "Stored procedures require the presence of the proc table in the mysql schema".By granting select access over the proc table stored procedures will work. But if I removed the above select privilege and grant the user execute privilege over the entire mysql schema the procedure will also work.
Does anyone have any ideas about the security issues by choosing one of the above over another?I would prefer to lock the proc table down all together so the user cannot see it cannot select from it.
My function isn't returning anything - strReturn is empty:
[Code]....
When I execute this stored proc using 'exec GetMerchantLocationZip (3333, 373773)' I get the correct zipcode in SQL. Why don't I get it in Visual Studio?
[Code]....
I am learning, so apologies if it's a obvious error.
I am attempting to execute a stored proc in asp.net in the code behind. The parameter I am trying to pass is strErrorMessage that contains a value of "The transport failed to connect to the server.; ". The error message when the query gets executed is: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 1 ("@errMessage"): Data type 0xE7 has an invalid data length or metadata length. Update with code
try
{
...
...
...
}
catch (Exception ex)
{
ClientScript.RegisterStartupScript(GetType(), "alert", "alert('Email was not sent - " + ex.Message + "');", true);
string strMessage = ex.Message;
string strStackTrace = ex.StackTrace;
strMessage = strMessage.Replace("
", "; ");
strMessage = strMessage.Replace(" ", "");
strStackTrace = strStackTrace.Replace("
", "; ");
strStackTrace = strStackTrace.Replace(" ", "");
AppErrorLog(strMessage, strStackTrace);
return false;
}
protected void AppErrorLog(string strErrorMessage, string strErrorStackTrace)
{
SqlConnection conErrLog = new SqlConnection(strConn);
string sql = "usp_AppErrorLog_AddRecord";
SqlCommand cmdErrLog = new SqlCommand(sql, conErrLog);
conErrLog.Open();
try
{
cmdErrLog.CommandType = CommandType.StoredProcedure;
cmdErrLog.Parameters.Add(new SqlParameter("@errMessage", SqlDbType.NVarChar, 8000));
cmdErrLog.Parameters["@errMessage"].Value = strErrorMessage;
cmdErrLog.Parameters.Add(new SqlParameter("@errStackTrace", SqlDbType.NVarChar, 8000));
cmdErrLog.Parameters["@errStackTrace"].Value = strErrorStackTrace;
cmdErrLog.Parameters.Add(new SqlParameter("@userID", SqlDbType.VarChar, 12));
cmdErrLog.Parameters["@userID"].Value = User.Identity.Name;
SqlDataAdapter ada = new SqlDataAdapter(cmdErrLog);
cmdErrLog.ExecuteNonQuery();
}
catch(Exception e)
{
ClientScript.RegisterStartupScript(GetType(), "alert", "alert('AppErrorLog - " + e.Message + "');", true);
}
finally
{
conErrLog.Close();
}
}
The column datatype in the table is nvarchar(MAX).
i have the following stored proc that inserts data into a database based on a stauts field. the first stored proc inserts into the first table and i am trying to get the identity field out as i need this value for the next stored proc. the first stored proc works fine and inserts the data but the secnd one doesnt, even though there is data there.
[Code]....
I am trying to pass stored procedure from a controller to view. In my Home controller, in Index method, I have this. CustOrderHist is my stored procedure from northwind database.
public ActionResult Index()
{
DataClasses1DataContext dc = new DataClasses1DataContext();
var products = dc.CustOrderHist("ALFKI").GetEnumerator();
return View(products);
[Code]....
I have a stored proc that takes a bit long to run. I tried to do this command.CommandTimeout = 200;still gets time out within 200 secs.How is this done?
View 1 RepliesI have written a stored proc for raise error.Whenever a customer checks for a record in the DB that doesn't exist in the DB he has to get an error displayed.I have written a stored proc help me on it i know it is wrong suggest me the correct one.
[Code]....
I have made a simple stored proc which include Transaction through which I have to delete records from two tables EmpDetail and EmpDept . EmployeeId is PK in EmpDetail and FK in EmpDept. Procedure has been executed but on doing exec DelEmployee @EmployeeID='e07' showing 0 rows affected..
[Code]....
I've been self teaching SQL and asp.net and i'm a little stuck. I have a stored proc for the insert and then in my Insert inside my class it's not returning the ID after the insert as I am receiving an error message that NULL value has cannot be inserted. I want to add default values to a seperate table after the insert. I will provide my code below, if anyone can help me why the value is not going across?
[Code]....
i am trying to write a stored proc that takes data from one table based on a field in that tabke and writes that data out to another table with the eventual idea that this data is deleted from the original table it was read from. my stored proc is as follows:
[Code]....
but i keep getting an error: incorrect syntax near 'BACKUP_TWS_Waste_Colection_Request'.
I hope to one day figure this looping records from a table to update it, but i cannot get this to complie, I guess cause I am not
versed in looping in sql yet :(
[Code]....
I just want to get data from a table per record run the stored procedure(i have nesting on) and update the record's fields with the values that were returned from the Store procedure. The Stored procedure is working fine.
I'm converting Active X code in DTS to Script Task for SSIS and I'm having trouble creating an XML file from my stored proc. Here's the code:
[Code]....
It throws an error on the dataAdapter fill saying:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '<'.
Msg 132, Level 15, State 1, Line 5
The label 'sql' has already been declared. Label names must be unique within a query batch or stored procedure.
I am having a strange problem with using stored proc in CTP5.I am calling a stored proc in my function like this:
private List<Test> GetDatat(Test pTest)
{
using(var staticDB=new StaticDB()) [code]...
But I am getting error that parameters not found. Looking at the sql trace I can see that CTP5 is sending "default" value if any of my parameters is null instead of the null value.
Is it possible to call 1 Stored procedure from another withing a sql query
sp1 :
select ID1,ID2,ID3,ID4 from table1 ;
sp2 :
select name from table2 where ID = ID1;
I want to get corresponding names of ID1,ID2 and ID3,ID4 in sp1 as a query result.Is it somehow possible to call sp2 from sp1?
i have stroPro in mysql db ... i create command like this:
[Code]....
i recive this error:#42000SELECT command denied to user 'MYSUERNAME'@'serverIP for table 'proc'