EXECUTE IMMEDIATE question

Hi gurus,

I'd like to create a view in a block using dynamic sql (execute immediate statement, so I can catch error and do other house keeping in case an error occurs. But the definition of the view is so long that it's over 5000 characters -- far more than the 2000 limit for char/varchar data types in PL/SQL. So whenever I tried to create the view using 'execute immediate', it throws out error, but if I simply run the create view statement itself, it worked great.

Is there anyway for me to overcome this problem?

FYI -- when I create the view using dynamic sql, the error message it threw out was 'PLS-00382: expression is of wrong type'. I was thinking it should say something like 'over the limit' etc. So did I miss anything here?

Thanks a bunch,
Elaine

VarChar2 limit in PL/SQL is 32,767 bytes.

Can you post the code? (feel free to trim down that view def'n!)

Quote:

---

Originally posted by slimdave
VarChar2 limit in PL/SQL is 32,767 bytes.

Can you post the code? (feel free to trim down that view def'n!)

---

Thanks a lot for your reply! I revisited the views I have and was able to make all of them work except one (the longest one).

Here is the stored procedure I have (and the dynamic sql is inside the stored procedure) --


CREATE OR REPLACE PROCEDURE crView
(i_vname IN CHAR,
i_vstring IN CHAR,
i_product IN CHAR,
i_status IN CHAR)
IS
v_rcnt INT := 0;

BEGIN
IF (i_vname IS NULL) THEN
RETURN;
END IF;
EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ' || i_vname || ' AS ' || i_vstring; -- dynamic sql

spinin_update_CkSum (i_product, i_status);

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm);
END crView;
/


Here is where I got error --

begin
CRVIEW('View_Name(list_of_column_names)' , 'view_definition_which_is_about_4000_chars',other_Param','other_param');
exception
..
end;
/


Note: In above 'view_definition_which_is_about_4000_chars', there is a UNION operator inside. Could that be the cause? I don't think so, but I can't find other reason that prevent this from working.

Here is the error message I got --

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

Thanks a lot!!!

You're missing a quote before other_Param, but that might just be a forum typo?

Perhaps you could change the CHAR declarations to varchar2, just to see what that does.

Quote:

---

Originally posted by slimdave
You're missing a quote before other_Param, but that might just be a forum typo?

Perhaps you could change the CHAR declarations to varchar2, just to see what that does.

---

Yeah, that missing quote is a typo. And I tried to change that to varchar2, still got the same error:confused:

Any other thoughts?

Millions of thanks!

I'm wondering whether the problem is not with the execute immediate, but with the view definition itself.

Have you tried creating the view directly through SQL*Plus?

Quote:

---

Originally posted by slimdave
Have you tried creating the view directly through SQL*Plus?

---

Yes I did, and that worked just fine.

Create a dummy table and insert the SQL_STMT which u r creating dynamically using execute immediate.

then try executing the generated script from the table...

In case you are still wondering this, I got this one solved by removing all the unnecessary parenthesis from the view definition. Those parenthesis don't seem to be a problem when I run the query from sql+ directly, but it kept giving me that weird error when I run it from the store procedure (crView) I wrote.

Don't know how to explain this, but hey, it's now working like a charm:D :D :D