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
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
November 19th, 2011 6:47pm
Yes I understand. Thank you.
November 19th, 2011 6:54pm