| Declaring table names as constants [message #47847] |
Thu, 23 September 2004 10:08  |
Ray Smith
Messages: 2
Registered: September 2004
|
Junior Member |
|
|
I have a packaged procedure that updates several tables as part of a data mart. I would like to use consistent aliases for the tablenames throughout the package.
Here's a test procedure I thought should work:
CREATE OR REPLACE PROCEDURE test_constant_names
IS
my_cal CONSTANT VARCHAR2(30) := MYCALENDAR;
c_cal CONSTANT VARCHAR2(30) := CALENDAR;
BEGIN
INSERT INTO my_cal
SELECT * FROM c_cal;
COMMIT;
END;
Results in "PLS-00357: Table,View Or Sequence reference 'myschema.mytable' not allowed in this context" for rows 3 and 4. Also get ORA-00942 (table does not exist) for line 7.
How can I use and reuse constants for table names?
|
|
|
|
| Re: Declaring table names as constants [message #47849 is a reply to message #47847] |
Thu, 23 September 2004 12:20  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
First, you would need single quotes around those object names. Second, you need to use dynamic SQL anytime you are substituting object names at runtime.
execute immediate 'insert into ' || my_cal || ' select * from ' || c_cal; |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
