|
If you want to restrict users from modifying the procedure or executing it, it's done by using privileges on users or roles (which then are granted to users). Have a look at GRANT[^] statement. If you don't grant sufficient privileges to a person, he won't be able to execute the procedure.
Note that user can have execute privileges even if he hasn't been granted them to the procedure directly. For example database owner has privileges to do anything in the database and so on.
|
|
|
|
|
I want to write a store procedure which accept anothor store procedure as parameter and then retrun the datatypes of input variables of given store procedure. Plz help if any one knows n how to do it.
|
|
|
|
|
You can pick up the definition of the stored proc from syscomments -
select text from syscomments where id = object_id('procname')
and concat all the comments together, then parse for the parameters.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thank you I have got solution by a simple query:
select * from information_schema.Parameters where specific_Name = 'ProceName'
|
|
|
|
|
You learn something every day. I'll keep that view in mind. 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I have two tables, TABLE_OLD and TABLE_NEW, like so:
TABLE_OLD
PART_NUM Flag1 Flag2 Flag3
===============================
223 1 0 0
344 0 0 1
877 0 1 1
878 1 0 0
TABLE_NEW
PART_NO Flag1 Flag2 Flag3
===============================
223 NULL NULL NULL
344 NULL NULL NULL
877 NULL NULL NULL
878 NULL NULL NULL
I want to insert the data from the three columns in the old table, Flag1, Flag2, and Flag3, into their corresponding columns in the new table for each matching part number. The new table has matching columns of the same data type, the cells are just empty.
Most if teh INSERT examples I have seen so far deal with simple inserts of hard-coded data, not this sort of situation. Is the query I want to run something like this?:
INSERT INTO
TABLE_NEW.Flag1
TABLE_NEW.Flag2
TABLE_NEW.Flag3
FROM
TABLE_OLD.Flag1
TABLE_OLD.Flag2
TABLE_OLD.Flag3
WHERE TABLE_OLD.PART_NUM=TABLE_NEW.PART_NO;
Thanks for any help.
|
|
|
|
|
Nostrom0 wrote: I want to insert the data from the three columns in the old table, Flag1, Flag2, and Flag3, into their corresponding columns in the new table for each matching part number. The new table has matching columns of the same data type, the cells are just empty
If you want to insert new rows to the TABLE_NEW, it would be something like:
INSERT INTO TABLE_NEW (Flag1, Flag2, Flag3...)
SELECT Flag1, Flag2, Flag3...
FROM TABLE_OLD
If you want to restrict the rows that are inserted, add WHERE clause
However, I got the impression that you don't actually want to insert new rows, but to update already existing rows. If that's the case, then it would be like:
UPDATE TABLE_NEW
SET (Flag1, Flag2, Flag3...) = (SELECT Flag1, Flag2, Flag3...
FROM TABLE_OLD
WHERE TABLE_OLD.PART_NUM=TABLE_NEW.PART_NO)
By the way, did you get the materialized view fixed?
|
|
|
|
|
Hi Mika, thank you for the answer.
I believe you are right - I do want to do an UPDATE statement, not an INSERT. Both the source and destination tables already have populate dpart number columns, and both have columns set for teh six flags. I just want to "pour" the data from the source to the destination by part number.
I have tried:
UPDATE WEB_PRODUCT_TEST_COPY p
SET (
CONTAINS_MARBLE_FLG,
IS_MARBLE_FLG,
CONTAINS_SMALL_BALL_FLG,
IS_SMALL_BALL_FLG,
SMALL_PARTS_FLG,
BALLOON_FLG
) =
(SELECT
CONTAINS_MARBLE_FLG,
IS_MARBLE_FLG,
CONTAINS_SMALL_BALL_FLG,
IS_SMALL_BALL_FLG,
SMALL_PARTS_FLG,
BALLOON_FLG
FROM TEST_CPSC_BOOL
WHERE PART_NUM = p.PART_NO
);
...but I got an "ORA-01427: single-row subquery returns more than one row". I'm looking this up to see what I did wrong.
WEB_PRODUCT_TEST_COPY is the destination table, TEST_CPSC_BOOL is the source table, and the six columns ending in _FLG are Number fields set to 0 or 1. TEST_CPSC_BOOL uses PART_NUM for the part numbers, and WEB_PRODUCT_TEST_COPY uses PART_NO. If it makes any difference, I am using TOAD on an old 8i database...
Oh yes, I rolled back the changes I made to the MV table and the database worked again. I believe I need to just insert the info methodically step by step - i.e. if either the Proc or the MV try to reference columns that don't exist, they won't work.
|
|
|
|
|
Nostrom0 wrote: thank you for the answer
No problem.
Nostrom0 wrote: I got an "ORA-01427: single-row subquery returns more than one row".
The reason is that the subquery that selects the data to be updated on WEB_PRODUCT_TEST_COPY returns several rows, meaning that you have several PART_NUM for a single p.PART_NO. Since the update is done for each row on a row basis, there must be one exact match in TEST_CPSC_BOOL (or no matches at all)
Nostrom0 wrote: If it makes any difference, I am using TOAD on an old 8i database
That doesn't have any effect.
Nostrom0 wrote: I rolled back the changes I made to the MV table and the database worked again. I believe I need to just insert the info methodically step by step
That sounds like a good plan.
|
|
|
|
|
Aha, you are right - there are some duplicate part numbers in TEST_CPSC_BOOL. I'll have to figure out away to find and kill them, or more likely find them in the original excel sheet the data was imported form and then re-import.
|
|
|
|
|
Nostrom0 wrote: Aha, you are right - there are some duplicate part numbers in TEST_CPSC_BOOL
If they are complete duplicates, meaning all the values are the same, you could try something like this:
UPDATE WEB_PRODUCT_TEST_COPY p
SET (
CONTAINS_MARBLE_FLG,
IS_MARBLE_FLG,
CONTAINS_SMALL_BALL_FLG,
IS_SMALL_BALL_FLG,
SMALL_PARTS_FLG,
BALLOON_FLG
)
=(SELECT
CONTAINS_MARBLE_FLG,
IS_MARBLE_FLG,
CONTAINS_SMALL_BALL_FLG,
IS_SMALL_BALL_FLG,
SMALL_PARTS_FLG,
BALLOON_FLG
FROM TEST_CPSC_BOOL a1
WHERE PART_NUM = p.PART_NO
AND NOT EXISTS (SELECT 1
FROM TEST_CPSC_BOOL a2
WHERE a1.ROWID < a2.ROWID
AND a1.PART_NUM = a2.PART_NUM));
That should select only one of the duplicates, impossible to say which one, but if all of the values are the same, it wouldn't matter.
|
|
|
|
|
Wicked! That worked perfectly. Thank you for preventing me from spending the next three hours of my life screwing around with Conditional Formatting in Excel.
WEB_PRODUCT_TEST_COPY contains more rows/products than the list represented by TEST_CPSC_BOOL. Therefore, the products that don't need to be flagged, and were not included on the TEST_CPSC_BOOL list have a row of six NULLs instead of six 0's. I am hoping this won't make a difference - that when I complete the Proc that checks these new flag columns, my webapp will just treat the NULLs as 0's, not raise the corresponding flag, and not flip out. I suppose this is shading into more of an ASP.NET question than a PL_SQL question... I'll just try it out and see what happens. If it goes wrong I'll be back tomorrow asking how to turn NULLs into 0's...
Thanks again, Mika!
|
|
|
|
|
Nostrom0 wrote: Wicked! That worked perfectly
It's a kind of magic  When you have time, have a look at ROWID column definition on SQL documentation for Oracle.
Nostrom0 wrote: If it goes wrong I'll be back tomorrow asking how to turn NULLs into 0's
Have a look at NVL function. You can use it in the update statement (in the select portion).
Nostrom0 wrote: Thanks again, Mika!
You're welcome
|
|
|
|
|
Off the top of my head
update table_new t1
set t1.flag1 = ( select t2.flag1 from table_old t2 where t2.part_num = t1.part_num )
,t1.flag2 = ( select t2.flag2 from table_old t2 where t2.part_num = t1.part_num )
,t1.flag3 = ( select t2.flag3 from table_old t2 where t2.part_num = t1.part_num )
;
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
I am attempt to lock a record and at the same time retrieve that record.
I am doing this from C# and the string is created using string.format, and while I'm certain the first part of this will work with out a problem. I need to select the record that I'm updating here and I wanted to do it in one statement.
So far I have
WITH GetOneRecord AS
(
SELECT TOP (1) RecordID
FROM [{0}]
WHERE (Completed = 'false') AND (CheckedOut = 'false')
)
UPDATE [{0}]
SET CheckedOut = 'true', CheckedOutBy = '{1}', CheckedOutDateTime = {2}
WHERE (RecordID = GetOneRecord.RecordID)
{0} is a dynamic table name.
{1} is the user who is checking out the record.
{2} is the time the record was checked out.
Can I just add the following after the UPDATE clause and get just the SELECT record
below returned. I mean is there a way to hide the fact that the UPDATE record is going to try and return integer so that only the SELECT clause below is going to return data.
SELECT *
FROM [{0}]
WHERE CheckedOutBy = '{1}'
I'm wanting it to possibly return multiple rows incase something happened and a program has to be restarted or the server goes down. Then I can have the program release the older lock in another command.
|
|
|
|
|
After doing some testing, I keep coming up with an error.
WITH GetTopRecord AS (
SELECT TOP (1) RecordID
FROM [16500Record]
WHERE (Completed = 'false') AND (CheckedOut = 'false')
)
SELECT * FROM [16500Record]
WHERE [16500Record].RecordID = GetTopRecord.RecordID
This produces
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "GetTopRecord.RecordID" could not be bound.
I've done some searching, but as of yet can not figure out what other people are doing to solve the problem.
|
|
|
|
|
Sunset Towers wrote:
WITH GetTopRecord AS (
SELECT TOP (1) RecordID
FROM [16500Record]
WHERE (Completed = 'false') AND (CheckedOut = 'false')
)
SELECT * FROM [16500Record]
WHERE [16500Record].RecordID = GetTopRecord.RecordID
I think there are several problems in your query:
- GetTopRecords acts like a table so you should specify it's columns, like:
WITH GetTopRecord (Column1, Column2...) AS ...
- SELECT TOP (1) RecordID unless RecordID is a column this won't select anything
- unless you order your query, what would be top 1
- I think you cannot use the cte table as a column
What you could do is something like:
SELECT *
FROM [16500Record]
WHERE [16500Record].RecordID = (
SELECT TOP (1) RecordID
FROM [16500Record]
WHERE (Completed = 'false')
AND (CheckedOut = 'false')
ORDER BY ???)
Added:
If your cte returns one row (as it is in your case since you're fetching top 1), another approach to uncorrelated subquery I suggested earlier would be joining the cte result. This would be usable in select statement, but the subquery version I wrote earlier can be used in update. However, the joined version could look like:
WITH GetTopRecord (id) AS (
SELECT TOP (1) RecordID
FROM [16500Record]
WHERE (Completed = 'false')
AND (CheckedOut = 'false')
ORDER BY RecordID
)
SELECT * FROM [16500Record], GetTopRecord
WHERE [16500Record].RecordID = GetTopRecord.id
modified on Tuesday, December 16, 2008 2:29 PM
|
|
|
|
|
Let me add more to supplement Mika's response:
Looks like you are expecting the CTE to behave like a single scalar value, however, your defined CTE actually returns a result set with one row and one column. A correct approach would be to use a variable instead of CTE like this:
Declare @RecordIDToUpdate int
Set @RecordIDToUpdate =
(
SELECT TOP (1) RecordID
FROM [{0}]
WHERE (Completed = 'false') AND (CheckedOut = 'false')
)
UPDATE [{0}]
SET CheckedOut = 'true', CheckedOutBy = '{1}', CheckedOutDateTime = {2}
WHERE (RecordID = @RecordIDToUpdate)
Hope that helps.
Regards,
Syed Mehroz Alam
My Blog
My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
modified on Wednesday, December 17, 2008 12:06 AM
|
|
|
|
|
Syed Mehroz Alam wrote: Looks like you are expecting the CTE to behave like a single scalar value, however, your defined CTE actually returns a result set with one row and one column
That's more clearly stated than what I wrote.
Syed Mehroz Alam wrote: A correct approach would be to use a variable instead
Why is that necessary?
|
|
|
|
|
Mika Wendelius wrote: Why is that necessary?
In no way it is necessary. I just wanted to present a solution very similar to his original query so I replaced CTE with a scalar variable.
My Blog
My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
|
|
|
|
|
Ok, I thought that you saw some problems in the non-correlated subquery I suggested and used variable for this reason.
|
|
|
|
|
hi all;
I want to extract only numeric values from a column which have alphanumeric values like ABC12342M , AE2213Jk.
Thanks
Snehasish
|
|
|
|
|
You should create function which loops every character in string and check if is number or not.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
hi;
Many thanks for reply.It must work.But It will be lengthy process as per my application.So if you give any other soln,that will be helpful.
Thanks
snehasish
|
|
|
|
|
Well here is function which return only numbers from given string parameter.
CREATE FUNCTION [dbo].[ReturnNumber](@Parametri VARCHAR(8000))<br />
returns varchar(8000)<br />
as begin<br />
<br />
declare @i as int<br />
set @i=0<br />
<br />
declare @resultNumber as varchar(8000)<br />
<br />
while(@i<=len(@Parametri))<br />
begin<br />
set @i=@i+1<br />
<br />
if(select isnumeric(substring(@Parametri,@i,1)))=1<br />
begin<br />
set @resultNumber =@resultNumber+substring(@Parametri,@i,1) <br />
end<br />
<br />
end<br />
<br />
return @resultNumer<br />
END
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
