-
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!)
-
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.
-
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
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?
-
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...
Cheers!
OraKid.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|