Home » SQL & PL/SQL » SQL & PL/SQL » Where is a problem? :)
Where is a problem? :) [message #289102] Wed, 19 December 2007 13:14 Go to next message
jrocknxo
Messages: 8
Registered: December 2007
Junior Member
Hi, I have a problem with dynamic change of table name in EXECUTE IMMEDIATE string.
Result is
ORA-00942: TABLE OR VIEW NOT EXIST
ORA-06512: na "DM.SP_NXO_CCI_AGG_FILTER", line 17
ORA-06512: na line 2


CREATE OR REPLACE PROCEDURE DM.SP_NXO_CCI_AGG_FILTER IS
EXE_PROC1 VARCHAR2(2000);

BEGIN

FOR rec IN (SELECT NXO_CCI_FILTERS.FILTER_FIELD_NAME, NXO_CCI_FILTERS.FILTER_ACTIVE,
NXO_CCI_FILTERS.LAYOUT_SUFFIX FROM NXO_CCI_FILTERS)

LOOP

EXE_PROC1 := '
INSERT INTO R_A_CCI_FILTER_DAY
SELECT TIME_KEY,OBJECT_ID,'''|| SUBSTR(rec.FILTER_FIELD_NAME,5,2) ||''',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL FROM R_A_F_'|| rec.FILTER_FIELD_NAME ||'A_DAY
WHERE
'''|| rec.FILTER_ACTIVE ||''' = ''Y'' AND '''|| SUBSTR(rec.FILTER_FIELD_NAME,6,1) ||''' = ''7''
';
EXECUTE IMMEDIATE EXE_PROC1;

END LOOP;

END SP_NXO_CCI_AGG_FILTER;
/
Re: Where is a problem? :) [message #289104 is a reply to message #289102] Wed, 19 December 2007 13:18 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Find out what you are actually populating the variable exe_proc1 with.

Then execute that statement in sql*plus. I'm sure the error will be very clear.

Re: Where is a problem? :) [message #289105 is a reply to message #289102] Wed, 19 December 2007 13:19 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
It is a good practice to completely form the SQL statement as a VARCHAR2 variable & print it out before EXECUTE IMMEDIATE.
You can then CUT & PASTE into SQL*Plus as a sanity test or debugging aid.
Re: Where is a problem? :) [message #289107 is a reply to message #289104] Wed, 19 December 2007 13:28 Go to previous messageGo to next message
jrocknxo
Messages: 8
Registered: December 2007
Junior Member
you mean this (for example)?

SELECT TIME_KEY,OBJECT_ID FROM R_A_F_|| NXO_CCI_FILTERS.FILTER_FIELD_NAME ||A_DAY;
Re: Where is a problem? :) [message #289108 is a reply to message #289105] Wed, 19 December 2007 13:32 Go to previous messageGo to next message
jrocknxo
Messages: 8
Registered: December 2007
Junior Member
When I put just...(in EXECUTE IMMEDIATE)

FROM '|| rec.FILTER_FIELD_NAME || ...script is OK, but
FROM R_A_F_'|| rec.FILTER_FIELD_NAME ||'A_DAY ...doesnt work.

Thanks for comments.
Re: Where is a problem? :) [message #289109 is a reply to message #289108] Wed, 19 December 2007 13:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And "ORA-00942: TABLE OR VIEW NOT EXIST" does not mean anything for you?

Regards
Michel
Re: Where is a problem? :) [message #289110 is a reply to message #289109] Wed, 19 December 2007 13:48 Go to previous messageGo to next message
jrocknxo
Messages: 8
Registered: December 2007
Junior Member
YES, all table and content is OK.
Re: Where is a problem? :) [message #289111 is a reply to message #289110] Wed, 19 December 2007 13:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But do you have access to them WITHOUT role?

Regards
Michel
Re: Where is a problem? :) [message #289114 is a reply to message #289111] Wed, 19 December 2007 14:00 Go to previous messageGo to next message
jrocknxo
Messages: 8
Registered: December 2007
Junior Member
Yes, it's free Oracle10g...

SELECT TIME_KEY,OBJECT_ID,'''|| SUBSTR(rec.FILTER_FIELD_NAME,5,2) ||''' FROM R_A_F_07A_DAY

SUBSTR(rec.FILTER_FIELD_NAME,5,2) return: "07"

SELECT TIME_KEY,OBJECT_ID,'''|| SUBSTR(rec.FILTER_FIELD_NAME,5,2) ||''' FROM R_A_F_'''|| SUBSTR(rec.FILTER_FIELD_NAME,5,2) ||'''A_DAY

or

SELECT TIME_KEY,OBJECT_ID,'''|| SUBSTR(rec.FILTER_FIELD_NAME,5,2) ||''' FROM R_A_F_'|| SUBSTR(rec.FILTER_FIELD_NAME,5,2) ||'A_DAY

...etc.
Re: Where is a problem? :) [message #289115 is a reply to message #289102] Wed, 19 December 2007 14:02 Go to previous messageGo to next message
jrocknxo
Messages: 8
Registered: December 2007
Junior Member
I think that just syntax problem or problem with automatic generated table name (from FOR and LOOP)...
And I'm not able to test it in SQL...Sad
Re: Where is a problem? :) [message #289116 is a reply to message #289102] Wed, 19 December 2007 14:07 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I think that just syntax problem or problem with automatic generated table name (from FOR and LOOP)...
I agree
>And I'm not able to test it in SQL...
why not?

>SELECT TIME_KEY,OBJECT_ID FROM R_A_F_|| NXO_CCI_FILTERS.FILTER_FIELD_NAME ||A_DAY;
Above is NOT valid SQL syntax & I suspect Oracle is misreporting the error condition
Re: Where is a problem? :) [message #289118 is a reply to message #289102] Wed, 19 December 2007 14:11 Go to previous messageGo to next message
jrocknxo
Messages: 8
Registered: December 2007
Junior Member
Result is:

ORA-00933: příkaz SQL není řádně ukončen

in English Smile

ORA-00933: SQL command not ended
Re: Where is a problem? :) [message #289119 is a reply to message #289102] Wed, 19 December 2007 14:20 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Too bad for you that you got an error.
Too bad for us since we have NO idea what you did to actually produce this error.
CUT & PASTE the whole session should be seriously considered.
Re: Where is a problem? :) [message #289121 is a reply to message #289119] Wed, 19 December 2007 14:37 Go to previous message
jrocknxo
Messages: 8
Registered: December 2007
Junior Member
It's not theme for Newbies! Smile
Thanks...when I find the solution ...a put it here.
Previous Topic: Procedure undefined-ORA-06550 error
Next Topic: Weighted sum in group by
Goto Forum:
  


Current Time: Sat Dec 10 22:37:51 CST 2016

Total time taken to generate the page: 0.05752 seconds