Package/Procedure In parameter used in Where clause?
Hello,
I have the following code from the body of a package and the inner workings of one of the procedures.
I am having an error:
ORA-00904: "NAMEIN": invalid identifier
ORA-06512: at "SYSTEM.DOMINATION", line 7
ORA-06512: at line 1
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
The error is at the first "EXECUTE IMMEDIATE" View creation in which the "IN" parameter "nameIn" is being used in the WHERE clause.
I don't understand why this is throwing an error. I have seen many examples of parameters being used this way.
Also, the last "EXECUTE IMMEDIATE" with the SELECT statement is a result that I would like shown to the user after execution of the procedure. I am having a hard time understanding how to do this.
create or replace
PACKAGE BODY DOMINATION AS
PROCEDURE GetThem(nameIn IN varchar2, percIn IN number) AS
BEGIN
EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW personTotal AS
SELECT person, COUNT(name) AS total FROM (SELECT DISTINCT name, person FROM sightings WHERE person != nameIn) GROUP BY person';
EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW sharedTotal AS
SELECT p.person, COUNT(DISTINCT name) AS bhave FROM sightings p WHERE p.person != nameIn AND p.name IN (SELECT DISTINCT g.name FROM sightings g WHERE g.person = nameIn) GROUP BY p.person';
EXECUTE IMMEDIATE 'SELECT p.person FROM personTotal p, sharedTotal s WHERE p.person = s.person AND percIn <= s.bhave/p.total ORDER BY person';
END GetThem;
END DOMINATION;
Thank you!!
November 19th, 2011 4:31pm
This appears to be an invalid Oracle PSQL code/procedure, I do not see how it is related to SSIS.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
November 19th, 2011 6:26pm
I saw other posts with similar questions in this area. Would you mind pointing me in the right direction rather than wasting time with a pointless comment? I can understand that it may be frustrating to see a misplaced question but simply commenting
and then leaving no helpful information will do no good. (other than make you feel like more of an MVP)
Please advise...
November 19th, 2011 6:34pm
I am a MVP (Microsoft) not ACE (Oracle).
ORA-00904: means invalid column name
Sorry if I could not assist you any further.
I suggest you go to the Oracle forums to clarify further or resolve your issue.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
November 19th, 2011 6:47pm
Yes I understand.
Thank you.
November 19th, 2011 6:54pm