Thursday, March 22, 2012
a view in a package for oracle
BEGIN
...
EXECUTE IMMEDIATE 'create view v_emp as select * from emp';
END;
But why would you want to do that?|||I have to create install scripts for these, because they need to go into various schemas. along with functions and procedures. So I wanted to bundle them all up. That way a package can stay in the schema for future reference. Or can we store scripts on the schema.
Why, is this in-efficient?|||No, I thought maybe you were creating views dynamically from your application. Normally packages are used for code that will be run many times. What you are doing is unusual, but harmless.|||Hi, I am unable to figure out how to invoke this view from the package specification. here is what I have
CREATE OR REPLACE PACKAGE ABC IS
end;
/
I know something has to identify the view in the header but I cant figure out how to do that.
CREATE OR REPLACE PACKAGE BODY ABC AS
BEGIN
EXECUTE IMMEDIATE
'CREATE OR REPLACE VIEW ABCD
(LISTNAME, CODE, DESCRIPTION)
AS
SELECT
translate(CODELKUP.LISTNAME,"^`"""," ") LISTNAME,
translate(CODELKUP.CODE,"^`"""," ") CODE,
translate(CODELKUP.DESCRIPTION,"^`"""," ") DESCRIPTION
FROM
CODELKUP';
END;
/|||You need to create a procedure in the package like this:
CREATE OR REPLACE PACKAGE ABC IS
procedure create_view;
end;
/
CREATE OR REPLACE PACKAGE BODY ABC AS
procedure create_view is
begin
EXECUTE IMMEDIATE
'CREATE OR REPLACE VIEW ABCD
(LISTNAME, CODE, DESCRIPTION)
AS
SELECT
translate(CODELKUP.LISTNAME,"^`"""," ") LISTNAME,
translate(CODELKUP.CODE,"^`"""," ") CODE,
translate(CODELKUP.DESCRIPTION,"^`"""," ") DESCRIPTION
FROM
CODELKUP';
end;
END;
/
You will have problems with those quotes though. Oracle uses only single quotes (') to delimit text strings.
Thursday, March 8, 2012
A Severe error occured ...
i am using the HTML Textbox from activeup.com. I get a strange error when i put to much text inside the HTML textbox and insert it into my SQL Server with an SP. Myquestion is: is there a limit on a field in SQL of the type Text or what could it else be (error in HTML Textbox?)can you post some code.
Thursday, February 9, 2012
a generic error occurred in gdi+
Other reports don't have error. The report will not render any chart although thousands of record will be need to output.
Hope anyone can help. Thanks!
Is there any solution to this problem?
Other threads suggest that this type of error may be due to security permissions.
|||Might be an issue with an older version of GDI+. Maybe try this one: http://www.microsoft.com/downloads/details.aspx?FamilyID=6a63ab9c-df12-4d41-933c-be590feaa05a&DisplayLang=en.|||Brian,
Thanks for your response.
In the redist.txt it tells you that you cannot update the gdiplus.dll in Windows XP because it is a protected file; however, we replaced it and after a reboot, it fixed the problem.
If anyone has trouble replacing the gdiplus.dll in the "windows\system32" directory, it may be a good idea to reboot in safe mode, replace the gdiplus.dll and then reboot into Windows normally.
|||When Drawing an Image from a Stream, make sure that the Stream has been re-positioned back to the beginning if it has been accessed before.
GDI+ is very powerful; too bad it doesn't have a more robust error message interface. Are there even any error codes available? I couldn't even find an InnerException.
HTH someone else
Cheers,
Robert
a generic error occurred in gdi+
Other reports don't have error. The report will not render any chart although thousands of record will be need to output.
Hope anyone can help. Thanks!
Is there any solution to this problem?
Other threads suggest that this type of error may be due to security permissions.
|||Might be an issue with an older version of GDI+. Maybe try this one: http://www.microsoft.com/downloads/details.aspx?FamilyID=6a63ab9c-df12-4d41-933c-be590feaa05a&DisplayLang=en.|||Brian,
Thanks for your response.
In the redist.txt it tells you that you cannot update the gdiplus.dll in Windows XP because it is a protected file; however, we replaced it and after a reboot, it fixed the problem.
If anyone has trouble replacing the gdiplus.dll in the "windows\system32" directory, it may be a good idea to reboot in safe mode, replace the gdiplus.dll and then reboot into Windows normally.
a function inside function in a query
Code Snippet
create view missEeCutsAS
select e.idevent
from events e, isolatedLeptons as l, isolatedLeptons as l2
where dbo.module(e.PxMiss,e.PyMiss)>=40
and dbo.effectiveMass(e.PxMiss,e.PyMiss,sum(l.px)
,sum(l2.py))<= 150.0 and l2.eventid=e.idevent and l.eventid = e.idevent and l.idap <> l2.idap;
We need you to describe the problem or error in more detail please. I imagine you are getting a syntax or Group By -type error, but need to know more info.
Thanks,
Bryan
Code Snippet
create view missEeCuts
AS
select e.idevent
from events as e, isolatedLeptons as l, isolatedLeptons as l2
where dbo.module(e.PxMiss,e.PyMiss)>=40
and l2.eventid=e.idevent and l.eventid = e.idevent and l.idap <> l2.idap
group by e.idevent
having dbo.effectiveMass(e.PxMiss,e.PyMiss,sum(l.px)
,sum(l2.py))<= 150.0;
and its trowing me this error
Msg 8121, Level 16, State 1, Procedure missEeCuts, Line 3
Column 'events.PxMiss' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8121, Level 16, State 1, Procedure missEeCuts, Line 3
Column 'events.PyMiss' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
the thing is that i dont want to group by it by PxMiss, PyMiss
|||
I broke the code down a bit. Looking at hte logic, you might end up with a list of repeated idevent values so you may want to use a SELECT DISTINCT at the top. Still, this should take care of the GROUP BY issue
Code Snippet
create view missEeCuts
as
select
e.idevent
from events e
inner join (
select
eventid,
idap,
sum(px) as sum_px
from isolatedLeptons
group by
eventid,
idap
) l
on e.idevent=l.eventid
inner join (
select
eventid,
idap,
sum(py) as sum_py
from isolatedLeptsons
group by
eventid,
idap
) l2
on e.idevent=l2.eventid AND
l.idap <> l2.idap
where
dbo.module(e.PxMiss,e.PyMiss)>=40 AND
dbo.effectiveMass(e.PxMiss,e.PyMiss,l.sum_px),l2.sum_py))<= 150.0
Msg 3623, Level 16, State 1, Line 1
A domain error occurred.
the original one was this function and I need to change it to a view, returning a table with all the events that fullfil the condition
Code Snippet
create function missEeCuts(@.idevent INT)
Returns bit
AS
BEGIN
declare @.pt31x Real;
declare @.pt31y Real;
set @.pt31x = (select sum(l.px)
from isolatedLeptons(@.idevent) as l);
set @.pt31y = (select sum(l2.py)
from isolatedLeptons(@.idevent) as l2);
return ( select distinct 1
from events e
where @.idevent= e.idevent and dbo.module(e.PxMiss,e.PyMiss)>=40
and dbo.effectiveMass(e.PxMiss,e.PyMiss,@.pt31x,@.pt31y)<= 150.0
)
END
GO
|||
There is most likely a mathematical error in there. Try running each of the subqueries independently to verify they work without errors. Also, verify the data type of the px and py fields are inline with the data types expected by the effectivemass function.
B.
|||youre right, the problem is in effectivemass