You first need to create your table first then it will be available in the dynamic SQL. This works: CREATE TABLE #temp3 (id INT) EXEC (‘insert #temp3 values(1)’) SELECT * FROM #temp3 This will not work: EXEC ( ‘create table #temp2 (id int) insert #temp2 values(1)’ ) SELECT * FROM #temp2 In other words: Create … Read more
Please visit this ODP site set up by oracle for Microsoft OracleClient Developers: http://www.oracle.com/technetwork/topics/dotnet/index-085703.html Also below is a sample code that can get you started to call a stored procedure from C# to Oracle. PKG_COLLECTION.CSP_COLLECTION_HDR_SELECT is the stored procedure built on Oracle accepting parameters PUNIT, POFFICE, PRECEIPT_NBR and returning the result in T_CURSOR. using Oracle.DataAccess; … Read more
For SELECTs you can use the FOUND_ROWS construct (documented here): SELECT SQL_CALC_FOUND_ROWS something FROM your_table WHERE whatever; SELECT FOUND_ROWS( ) ; which will return the number of rows in the last SELECT query (or if the first query has a LIMIT clause, it returns the number of rows there would’ve been without the LIMIT). For … Read more
Your stored procedures work as coded. The problem is with the last line, it is unable to invoke either of your stored procedures. Three choices in SQL*Plus are: call, exec, and an anoymous PL/SQL block. call appears to be a SQL keyword, and is documented in the SQL Reference. http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4008.htm#BABDEHHG The syntax diagram indicates that … Read more
A procedure does not have a return value, whereas a function has. Example: CREATE OR REPLACE PROCEDURE my_proc (p_name IN VARCHAR2 := ‘John’) as begin … end CREATE OR REPLACE FUNCTION my_func (p_name IN VARCHAR2 := ‘John’) return varchar2 as begin … end Notice how the function has a return clause between the parameter list … Read more
OK – here’s the step-by-step way of doing this: (1) add your stored procedure to the EDMX file (when you first create it, or later on by using Update model from database and picking that stored procedure) (2) once you have the stored procedure in your model – use the Model Browser to add a … Read more
Take a look at http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html Basically you declare error handler which will call rollback START TRANSACTION; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; EXIT PROCEDURE; END; COMMIT;
I think you’re looking for SELECT select_expressions INTO: select test_table.name into name from test_table where id = x; That will pull the name from test_table where id is your function’s argument and leave it in the name variable. Don’t leave out the table name prefix on test_table.name or you’ll get complaints about an ambiguous reference.
Yeah, it’s a pain, and the way you’re doing it looks similar to what I do: order by case when @SortExpr=”CustomerName” and @SortDir=”ASC” then CustomerName end asc, case when @SortExpr=”CustomerName” and @SortDir=”DESC” then CustomerName end desc, … This, to me, is still much better than building dynamic SQL from code, which turns into a scalability … Read more
Using CSV is probably the simplest way, assuming you can be 100% certain that your elements won’t themselves contain strings. An alternative, and probably more robust, way of doing this is to create a custom type as a table of strings. Supposing your strings were never longer than 100 characters, then you could have: CREATE … Read more