A view represents a virtual table. You can join multiple tables in a view and use the view to present the data as if the data were coming from a single table. A stored procedure uses parameters to do a function… whether it is updating and inserting data, or returning single values or data sets. … Read more
You say you are new to Oracle. I’m guessing you are used to SQL Server, where it is quite common to use temporary tables. Oracle works differently so it is less common, because it is less necessary. Bear in mind that using a temporary table imposes the following overheads: read data to populate temporary table … Read more
You want something like this (untested) Dim cmd, rs, ars, conn Set cmd = Server.CreateObject(“ADODB.Command”) With cmd ‘Assuming passing connection string if passing ADODB.Connection object ‘make sure you use Set .ActiveConnection = conn also conn.Open should ‘have been already called. .ActiveConnection = conn ‘adCmdStoredProc is Constant value for 4 (include adovbs or ‘set typelib in … Read more
I suggest you split your stored procedure into two procedures that each respectively return a separate table and have those called to different worksheets. There are a variety of ways to return data to Excel using SQL Here is a favourite of mine from code by Joshua (you don’t have to use the parameters): Select … Read more
You can’t reference the column alias in the WHERE clause – your options are: replicate the CASE statement in the WHERE clause use a subquery: PROCEDURE P_LOAD_EXPIRED_ACCOUNT(pDayDiff NUMBER, ExpiredCur OUT MEGAGREEN_CUR) IS BEGIN OPEN ExpiredCur FOR SELECT x.account_name, x.service_type, x.expired_date FROM (SELECT s.account_name, s.service_type, CASE WHEN s.service_type = 1 THEN ADD_MONTHS(ACTIVATED_DATE,3) WHEN s.service_type = 2 … Read more
In phpMyAdmin you can create the stored procedure in the SQL window. You may have to set the delimieter to something like “$$” instead of the default “;”. You can change this from the bottom of the SQL window.
There’s a lot of plumbing you have to get right in order to get this to work. Try this guide. It includes this sample. It assumes you’ve made a reference to ADO, you’ve downloaded the OleDB provider for Oracle, and you’ve set up the TNSNames.ora file. If you don’t want to use TNSNames.ora you can … Read more
At the top of your .vb file: Imports System.data.sqlclient Within your code: ‘Setup SQL Command Dim CMD as new sqlCommand(“StoredProcedureName”) CMD.parameters(“@Parameter1”, sqlDBType.Int).value = Param_1_value Dim connection As New SqlConnection(connectionString) CMD.Connection = connection CMD.CommandType = CommandType.StoredProcedure Dim adapter As New SqlDataAdapter(CMD) adapter.SelectCommand.CommandTimeout = 300 ‘Fill the dataset Dim DS as DataSet adapter.Fill(ds) connection.Close() ‘Now, read through … Read more
It is possible to send an int list to your stored procedure using XML parameters. This way you don’t have to tackle this problem anymore and it is a better and more clean solution. have a look at this question: Passing an array of parameters to a stored procedure or check this code project: http://www.codeproject.com/Articles/20847/Passing-Arrays-in-SQL-Parameters-using-XML-Data-Ty … Read more
Most likely, the problem is that the grant was done via a role. Privileges granted to a user are not available in a definer’s rights stored procedure (the default). In SQL Developer, it is relatively easy to verify that this is the problem. If you run the command SET ROLE none and then run the … Read more