What SQL would I need to use to list all the stored procedures on an Oracle database? If possible I'd like two queries: list all stored procedures by name list the code of a stored procedure, given a name ...
Apparently in mysql, you cannot use RETURN in a stored proc which is ridiculous because you can do it in mssql. ...
With this PROCEDURE "ADD_BOOKMARK_GROUP" ( "NAME" IN VARCHAR2, "BOOKMARK_GROUP_ID" IN NUMBER, "STAFF_ID" IN VARCHAR2, "MAX_NO" IN INT, "NUMFOUND" OUT INT, "NEW_ID" OUT NUMBER) IS BEGIN NEW_ID := -1; SELECT COUNT(*) INTO NUMFOUND FROM BOOKMARK_GROUP_TABLE WHERE STAFF_ID = STAFF_ID; IF NUMFOUND < MAX_NO THEN INSERT...
Hi, Can anyone suggest how to debug stored procedure from a remote sql server using visual studio.net 2005? I am able to debug my stored procedure from local server, but I'm unable to do so if the sql server is remote. I will often get an error that "Unable to start T-SQL Debugging.Could not attach to SQL Server process on [Remote S...
Is there a way to get a value of a local variable specified by its name dynamically in SQL Server SP? declare @foo int declare @bar int declare @variable_name varchar(10) set @variable_name = '@foo' print -- magic happens here - how to print the value of the variable -- which name is stored in @variable_name, in this case @foo ...
Hi Folks, Simply querying running jobs using something like select * from dba_jobs_running; works fine when executed in my sqldevelopers SQL console. However, it does not work, when having exactly the same statement within a procedure. Compilation fails with PL/SQL: ORA-00942: table or view does not exist Any ideas? Is there so...
I have added a bounty to this as I have, as yet been able to figure this out and time is out. The below stored procedure will not allow me to add it to Modify it. When attempting to modify it I get the following error --> Msg 213, Level 16, State 1, Procedure spPersonRelationshipAddOpposing, Line 51 Insert Error: Column name or n...
I am using LINQ to SQL to call a stored procedure with a single result set (I have found a lot of information about handling multiple result sets, but that is not what I am doing). The result set is actually all columns from 3 tables joined together, and I have LINQ to SQL entities for those 3 tables. The schema is something like this: ...
I've been struggling all day calling a Stored Procedure from a classic ASP page. I have a few basic noobie questions. First, is this the best way to add a parameter to my command: cmd.Parameters.Append cmd.CreateParameter("@SubmissionDate", adDBTimeStamp, adParamInput, , txtDate) Second, is adDbTimeStamp the proper type to use when ...
I'm writing a second interface to a database application to get around some shortcomings of the original interface. Unfortunately, if I create new records, expected audit trail records will not be generated. It sure would have been nice if the database design had worked such details into table triggers, or else provided a stored proced...
I want to run a query like this: SELECT * FROM Studio WHERE Id IN (134, 144, 132, 138, 7432, 7543, 2566) but the amount of Id's passed to the IN clause is only determined at runtime. Do I have to use dynamic SQL or can this be done with a stored procedure? UPDATE: If either option is available, which one is better? Thanks. ...
Hi, I want to INSERT various users into a Oracle db with a stored procedure. A user (table "user") has, say, name, surname and date of birth: CREATE TABLE "USER" ( "Name" VARCHAR2(50), "Surname" VARCHAR2(50), "Dt_Birth" DATE, ) A stored procedure to create a user is pretty simple: CREATE PROCEDURE Insert_User(p_user, ...
With reference to http://stackoverflow.com/questions/980324/oracle-variable-number-of-parameters-to-a-stored-procedure I have s stored procedure to insert multiple Users into a User table. The table is defined like: CREATE TABLE "USER" ( "Name" VARCHAR2(50), "Surname" VARCHAR2(50), "Dt_Birth" DATE, ) The stored proced...
I have a table with two fields in an sql server database and my asp.net application calls a stored procedure with a '@SearchString' parameter and the stored procedure finds all records where the @Searchstring value is found in the concatenation of two fields in the table, call them 'Field1' and 'Field2' So the logic looks like this(I ha...
I am looking for good resources on Oracle Stored Procedure geared towards beginners. I tried the Dev Shed Article and one from Oracle Documentation site but they did not meet my needs. The one form Oracle Documentation site had the overhead of Java example. I tried the Dev Shed one but I keep getting invalid SQL error when I try their ex...
I have googled this and keep coming up with "No it is not possible" but these posts were dated 2005-2007 so I'm wondering if this has been changed. A code example: CREATE PROCEDURE `blah` ( myDefaultParam int = 0 -- This breaks the code for some reason ) BEGIN -- Do something here END One of the solutions has been to pass null and...
Is it possible to use Linq-to-Sql to execute a stored procedure that does not return an output? ...
Any Ideas? I asked the question "Is it possible to have a default parameter for a mysql stored procedure?" today and the answer was a very strong "no this is not possible." So my next question then is how do you as a php/mysql developer handle this problem? Do you pass null and in the SP have an IF block that sets the variable if its nu...
Hey I'm working with a SQL Server stored procedure that returns error codes; here is a very simple snippet of the SP. DECLARE @ret int BEGIN SET @ret = 1 RETURN @ret END I can get the return value with the mssql extension using: mssql_bind($proc, "RETVAL", &$return, SQLINT2); However, I can't figure out how to access the return va...
I have the following very basic stored procedure: CREATE PROCEDURE [dbo].[GetNumberToProcess] AS RETURN 999 I then have some code using Enterprise Library to run and get the return value: Dim cmd As DbCommand Dim ResultValue as String Dim lDBCommand as String = "dbo.GetNumberToProcess" Dim actionDB As...