|
Hello friends,
I want to create 1 stored procedure in Oracle. I have written it & when trying to execute, its giving some error message...i am using Ora 8i.
While creating SP, Oracle gave me error msg :- "Warning: Procedure created with compilation errors."
Will anybody please help me in solving the issue??? Here I am giving that SP...
Looking forword to see your reply..  Thanks in advance...
Regards,
Rohan
CREATE OR REPLACE PROCEDURE usp_Security_IsAuthorisedUser
(strUserName IN VARCHAR2(40),
strPassword IN VARCHAR2(16),
blnIsValidUser OUT BOOLEAN,
strInfo OUT VARCHAR(10))
AS
BEGIN
DECLARE intCount NUMBER;
intCount := 0;
SELECT COUNT(*) INTO intCount
FROM USERS
WHERE UserID = strUserName AND Password = strPassword;
--If count = 1 then only user information is correct
IF intCount = 1 THEN
BEGIN
blnIsValidUser := TRUE;
intCount := 0;
strInfo := 'SP';
SELECT COUNT(*) INTO intCount
FROM UnitRights
WHERE HoU = strUserName;
IF intCount = 1 THEN
strInfo := 'UH';
EXIT;
ELSE
SELECT COUNT(*) INTO intCount
FROM GENCODES
WHERE FLD_NAME = 'MARKET_REG' AND UDF_ST3 = strUserName;
--If count = 1 then this is Region Manager
IF intCount = 1 THEN
strInfo := 'RM';
EXIT;
END IF
END IF
END;
ELSE
BEGIN
blnIsValidUser := FALSE;
SELECT COUNT(*) INTO intCount
FROM USERS
WHERE CODE = strUserName;
IF intCount = 1 THEN
strInfo := 'PI';
ELSE
strInfo := 'UU';
END IF
END;
END IF
END;
|
|
|
|
|
Here is the corrected code. Obviously, I cannot check whether it is doing what it is supposed to do. I just corrected obvious syntax errors. One more thing, when you get "Warning: Procedure created with compilation errors." , it means there are some errors in the stored procedure and you need to correct it before start using it.
CREATE OR REPLACE PROCEDURE usp_Security_IsAuthorisedUser
(
strUserName IN VARCHAR2,
strPassword IN VARCHAR2,
blnIsValidUser OUT BOOLEAN,
strInfo OUT VARCHAR2)
AS
intCount NUMBER;
BEGIN
intCount := 0;
SELECT COUNT(*)
INTO intCount
FROM USERS
WHERE UserID = strUserName
AND Password = strPassword;
--If count = 1 then only user information is correct
IF intCount = 1 THEN
BEGIN
blnIsValidUser := TRUE;
intCount := 0;
strInfo := 'SP';
SELECT COUNT(*)
INTO intCount
FROM UnitRights
WHERE HoU = strUserName;
IF intCount = 1 THEN
strInfo := 'UH';
--EXIT; Invalid. Should be used on in the loop
RETURN;
ELSE
SELECT COUNT(*)
INTO intCount
FROM GENCODES
WHERE FLD_NAME = 'MARKET_REG'
AND UDF_ST3 = strUserName;
--If count = 1 then this is Region Manager
IF intCount = 1 THEN
strInfo := 'RM';
--EXIT; Invalid. Should be used on in the loop
RETURN;
END IF;
END IF;
END;
ELSE
BEGIN
blnIsValidUser := FALSE;
SELECT COUNT(*)
INTO intCount
FROM USERS
WHERE CODE = strUserName;
IF intCount = 1 THEN
strInfo := 'PI';
ELSE
strInfo := 'UU';
END IF;
END;
END IF;
END;
regards
|
|
|
|
|
I am looking for a sofware package which is developed uisng VB.NET technology. Something like ebay clone.
if you know of any please post or send me a email
functions will include
1. User logon
2. User will be able to enter ads (text & graphic)
3. To be able to setup multiple categories (Auto, personals, Garden, Boats, ...)
4. search functons
5. Admin functions
6. to be able to Accept credit card payments
7. Take banner ads
8 .....
Thanks
|
|
|
|
|
system stored procedure/coding in vb.net
|
|
|
|
|
Do u mean that u want to know a sql store procedure to list all SQL servers?
<< >>
|
|
|
|
|
list all regestered database servers on a LAN
priyank
|
|
|
|
|
Which database servers?
if SQL-Server, you can use following code in .NET 2.0:
<br />
DataTable servers = SqlDataSourceEnumerator.Instance.GetDataSources();<br />
foreach (DataRow src in servers.Rows){<br />
foo();<br />
}<br />
The object returned has the following fields:
* ServerName
* InstanceName
* IsClustered
* Version
André
'A programmer ist just a tool which converts caffeine into code'
|
|
|
|
|
ok i continue here my questions:
1)if i want to use an existing mysql database for my application how can install it on the final user machine?
2)if the user has not mysql server installed on its pc, i've to install it before?
3)Have i to configure something too?
4)If my application uses microsoft access database instead, i can create it during the installation on its pc, the have i to configure something like DSN or others?
|
|
|
|
|
I data hadata set with multiple table and i want to bind that to single data set
I tried
DataGrid1.DataSource = Mydataset; // with 3 tables .. THIS SHOWS ! TABLE ONLY
// This sdoesn't work also shows 1st table
DataGrid1.DataSource = Mydataset.Tables["table1"].DefaultView ;
DataGrid1.DataSource = Mydataset.Tables["table2"].DefaultView;
DataGrid1.DataSource = Mydataset.Tables["table3"].DefaultView ;
DataGrid1.DataBind();
// when i do individual work fine but not all three
Thanks
|
|
|
|
|
I have some questions about licensing :
- is there any patent for OleDb or Odbc?
- is there any patent for dBase V?
- using an database server for commercial applications involves the paying of fees to the company/developpers; but, givving the application maximum flexibility by leting the user to set it's own odbc connection string (like in j2ee), let's you escape the patents fees?
Thanks in advance...
I hope I understand...because is a rough world out there...
|
|
|
|
|
AFAIK - there is no fees for ODBC.
By forcing the end user to select or enter a valid ODBC datasource name or connection string - you have placed the licensing legalities on them. And really - they will be unable to create a valid Oracle, SQL Server - etc - odbc datasource if they don't have the RDBMS installed and licensed.
-J
|
|
|
|
|
Currently I have a huge insert method and from running OSQL it takes a few seconds to execute and inserts the many number of entries I require.
However, having to use that query on C#, it times out and nothing gets inserted. This also happens when I try to use the Stored Procedure as well for the insertion (the procedure works on OSQL as well).
Has anyone got ideas of how best to handle this because it shouldnt be taking longer from C# than from running OSQL.....
|
|
|
|
|
You need to change the CommandTimeOut. That might work.
1.Public Function ExecuteSQLCommand(ByVal strSql As String) As Integer
2. InitializeConnection()
3. If con.State = ConnectionState.Open Then con.Close()
4. con.Open()
5. cmd = New SqlCommand()
6. cmd.Connection = con
7. cmd.CommandTimeout = 60
8. cmd.CommandType = CommandType.Text
9. cmd.CommandText = "delete from d where id > 10"
10. Return cmd.ExecuteNonQuery()
11. End Function
|
|
|
|
|
Thats not what I wanted, I know it will run but its extremely slow of doing an Insertion by selects with nested inner joins.
Doing it on OSQL seems to be in 5 seconds, doing it in C# is for 2 minutes 10 seconds, thats like 2 minutes and 5 seconds of something going on that I cant see. Is ExecuteNonQuery on SQL Command doing something to the text or the string, besides executing it? Cause its taking quite a long time to do it....
|
|
|
|
|
Can you show us C# code that execute the huge insert.
If you use the same stored procedure to insert big number of rows, you can use sqlCommand.Prepare(), where
sqlCommand = new SqlCommand("MyStoredProcedure", myConnection)
|
|
|
|
|
I figured it out why it was slow, it was due to SQL Transactions....
Now gotta work out how to speed it up......
Basically what I did that slowed it down was an INSERT SELECT TO Statement, and no I didnt use stored procedures since I also tried that with the same outcome.
Basically in PSEUDO form was more of a
INSERT INTO table1
SELECT [blah blah blah]
FROM #a INNER JOIN #b ON 1 = 1
INNER JOIN table2 ON a.[field] = table2.[field]
AND [etc].
WHERE something = something
|
|
|
|
|
This is my multi word search SQL, but I also need to find the selling price of each item according to what market ID they have and then lookup their price for that market id . The first SELECT finds the Market ID which is then used by the 2nd SELECT to find the market_price.
Can these 2 Select statements be placed within the large SQL in order to find the market price? If so, can someone tell me how to do it?
strSQL = "Select Market_ID from CUSTOMER where ID = @CustID"
strSQL = "Select * from MARKET_PRICE where PART_ID = @PartID and MARKET_ID = @MarketID"
strSQL = "SELECT DISTINCT dbo.PART.ID as ID,
'strSQL = "SELECT dbo.PART.ID as ID,
strSQL = strSQL & "dbo.PART.DESCRIPTION, "
strSQL = strSQL & "dbo.Part.PRODUCT_CODE, "
strSQL = strSQL & "UNIT_PRICE AS Price, "
strSQL = strSQL & "dbo.PART.COMMODITY_CODE, "
''strSQL = strSQL & "dbo.part_model.model_number, "
strSQL = strSQL & "dbo.PART.STOCK_UM "
''strSQL = strSQL & "dbo.PART_CROSSREF.CrossrefID "
strSQL = strSQL & "FROM dbo.PART "
strSQL = strSQL & "INNER JOIN "
strSQL = strSQL & "dbo.PART_CROSSREF ON dbo.PART.ID = dbo.PART_CROSSREF.ID "
strSQL = strSQL & "INNER Join "
strSQL = strSQL & "dbo.PART_MODEL ON dbo.PART.PRODUCT_CODE = dbo.PART_MODEL.PRODUCT_CODE "
strSQL = strSQL & " WHERE ((PART.ID like '%" & KeywordsForSearch(CountLoopsForSQL) & "%')"
For CountLoopsForSQL = 0 To UBound(KeywordsForSearch)
strKeyWord = KeywordsForSearch(CountLoopsForSQL)
If strKeyWord <> "" Then
' Search for the words in the "ID" field
strSQL = strSQL & " OR (dbo.PART_CROSSREF.crossrefID LIKE '%" & KeywordsForSearch(CountLoopsForSQL) & "%')"
strSQL = strSQL & " OR (dbo.PART_CROSSREF.ID LIKE '%" & KeywordsForSearch(CountLoopsForSQL) & "%')"
' Search for the words in the "Product_code" field
strSQL = strSQL & " OR (dbo.Part.Product_code LIKE '%" & KeywordsForSearch(CountLoopsForSQL) & "%')"
' Search for the words in the "Product_code" field
strSQL = strSQL & " OR (dbo.Part.description LIKE '%" & KeywordsForSearch(CountLoopsForSQL) & "%')"
' Search for the words in the "Commodity_code" field
strSQL = strSQL & " OR (dbo.Part.Commodity_code LIKE '%" & KeywordsForSearch(CountLoopsForSQL) & "%')"
End If
Next
strSQL = strSQL & " and (UNIT_PRICE is not null) and (Part.User_9 = 'Y'))"
Thanks
|
|
|
|
|
Select * from MARKET_PRICE where PART_ID = @PartID and MARKET_ID =(Select Market_ID from CUSTOMER where ID = @CustID) ????
|
|
|
|
|
I appreciate the feedback but I had never used a Select within a Select and so not only did I not know what syntax was acceptable, I also didn't know where I could put the Selects. I have since found that I can place them almost anywhere I want.
However, your example did show me a way to use a Select in a way I didn't know it could be done. That will be useful info for the future.
|
|
|
|
|
Morning all you hungover ghouls, goblins and ghosts.
Does anybody know what SQL Server 2005 Developer Edition includes to justify its 2.8GB download? The other editions, Enterprise included, are a good deal smaller. I know the licensing terms of Developer but am wondering what extra packages come inside it.
regards,
Paul Watson
South Africa
Colib and ilikecameras.
K(arl) wrote:
oh, and BTW, CHRISTIAN ISN'T A PARADOX, HE IS A TASMANIAN!
|
|
|
|
|
Paul Watson wrote: Does anybody know what SQL Server 2005 Developer Edition includes to justify its 2.8GB download?
Dunno... It is still downloading. 
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
The disc contains the x86, x64 and Itanium versions of the software. If you sum the three DVD images for the separate Enterprise Edition DVDs they're larger than the single Developer Edition DVD.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
I have a store procedure WriteToDatabase with 2 input parameters inVal1 (type int) and strVal2 (type varchar(80)).
How do I call this store procedure in VC++ 6.0 code???
Please help! Thanks a lot for your time.
|
|
|
|
|
Hi,
i need help in using ADO 2.5 objects with VB 6.0 (i know there're a lot newer version but i need to use this one...)
i'm not an expert in DB programming but i almost go crazy, maybe i'm just too stupid...
1st)
why does Recordset.RecordCount always return -1, even if it DOES contain several records?
i read somewhere that it's neccessary to first call Recordset.MoveLast and set Recordset.CursorLocation
to adUseClient, but this makes no difference at all
2nd)
<br />
Dim uDB as Connection<br />
Dim rs as Recordset<br />
<br />
Set uDB = new Connection<br />
Set rs = new Recordset<br />
<br />
uDB.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source = " & db_directory<br />
If uDB.state <> adStateOpen Then<br />
MsgBox "Opening database failed."<br />
Exit Sub<br />
End If<br />
<br />
For i = 0 to X<br />
rs.Open "SELECT * FROM " & TableName & " WHERE ID = " & CStr(id), uDB, adOpenDynamic, adLockOptimistic<br />
<br />
'update some fields<br />
rs.Update<br />
rs.Close<br />
Next i<br />
this works well for the first loop pass, but raises an "sql syntax error in select statement" up from the second pass. why?
3rd)
<br />
On Error Goto ErrHandler<br />
rs.Filter = "ID = " & CStr(id)<br />
immediately exits my sub without jumping to the error handler, but
<br />
On Error Resume Next<br />
rs.Filter = "ID = " & CStr(id)<br />
works fine and also selects the correct records. why ?
4th)
<br />
rs.Open "SELECT * FROM " & TableName, uDB, adOpenDynamic, adLockBatchOptimistic<br />
For i = 0 To X<br />
rs.AddNew<br />
'update new field<br />
rs.Update 'also tried without calling Update...<br />
Next i<br />
rs.UpdateBatch<br />
shows now error but simply does nothing  ... why?
(while opening the recordset with adLockOptimistic and only calling Update works fine)
5th) are there any restrictions in general on adding / altering data with recordsets that were
opened with a select statement?
6th)
<br />
For i = 0 To X<br />
'do something<br />
sqlCmd = "UPDATE " & TableName & " SET field1 = '" & strField1 & "', field2 = '" & strField2 & "' WHERE ID = " & CStr(id) & " AND LANGUAGE_ID = " & CStr(langId)<br />
uDB.Execute sqlCmd<br />
Next i<br />
this works also fine in the first pass but returns a "sql syntax error in update statement" up from the second pass...!??
thx a lot for any answers.
daniel.
|
|
|
|
|
I cant answer all your questions but i can give you a few ideas.
1. Yes this is correct. ADO recordcount is not like it was in the older DAO. It basically returns the recordcount to the current cursor location so yes you need to call recordset.movelast before you can get a count. It should work for you like this.
Just as a side issue which may help i prefer to set all the recordset properties before opening the recordset. Its more readable eg
set rs = new recordset
rs.cursorlocation = adUseCLient
rs.cursortype = adOpenDynamic
rs.locktype = adLockOptimistic
rs.open sql,connection
2 & 6. try to look at it logically. Define a string variable to hold the SQL eg
dim sSQL as string
sSQL = "SELECT * FROM " & TableName & " WHERE ID = " & CStr(id)
rs.open ssql,connection
you can then set a breakpoint and see what the sql actually contains. Also i would probably set the recordset to a new recordset then set it to nothing within the loop since you are actually creating a new recordset.
3. Do you have any code in the errorhandler to report the Error? What is the error? It could be more of a structure problem if it works without the error handling.
4. OK. You seem to be a bit confused about the use of batch uptates etc.
I am pretty sure you need a client side cursor to use the batch updates. If you use updatebatch you need to use adlockbatchoptimistic as the lock type. I suggest you find some examples on the web but in general the flow should be.
set rs = new recordset
rs.cursorlocation = adUseCLient
rs.cursortype = adOpenDynamic
rs.locktype = adLockBatchOptimistic
rs.open sql,connection
for i = 0 to 9
rs.add
rs.fields(0) = Value
rs.fields(1) = value
next
rs.marshaloptions = admarshallall ' or admarshallmodifiedonly
rs.updatebatch
5. None specific to using SQL unless you have joins
Its been a while since i used ADO but i hope i have given you some ideas.
Jon
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
