Tablename is a variable

[coaty]

Hi,
I am trying to write a generic bit of SQL (Oracle 8i) to run against more than one table - the table being unknown until runtime. I am therfore trying to pass the table name as a variable to the Select statement as follows:

1 DECLARE
2 var1 char(20):= 'ng1.users';
3 sql_stmt varchar2(500):=
4 'select count(*)
5 from :var1
6 BEGIN
8 EXECUTE IMMEDIATE sql_stmt USING var1;
9 end;

the result is
ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at line 7

I am now at the hair tearing out stage. Any help appreciated.

 

[OraCool]

Coaty

After Line 6 (Begin) add the following:
select 'select count(*) from '||var1 into sql_stmt
from dual;

Change the line 3 as:
sql_stmt varchar2(500):=null;

Change Line 8 to:
Execute immediate sql_stmt;

Please inform me if it works !!

Regards

 

[coaty]

OraCool
Thanks for the response. Still having a problem tho'.
Now running:
1 DECLARE
2 var1 char(20):= 'ng1.users';
3 sql_stmt varchar2(500):= null;
4 BEGIN
5 select 'select count(*) from '||var1 into sql_stmt
6 from dual;
7 EXECUTE IMMEDIATE sql_stmt;
8* end;

which gives

ERROR at line 1:
ORA-03113: end-of-file on communication channel

and if run again

ERROR at line 1:
ORA-01041: internal error. hostdef extension doesn't exist

Once again all help appreciated.

 

[OraCool]

Coaty

The Whole block should end with a '/'

I don't see any?

 

[coaty]

Oracool,
My cutting & pasting is lacking - the '/' is there. The buffer definitely contains:

DECLARE
var1 char(20):= 'ng1.users';
sql_stmt varchar2(500):= null;
BEGIN
select 'select count(*) from '||var1 into sql_stmt
from dual;
EXECUTE IMMEDIATE sql_stmt;
end;
/

but still getting the last 2 errors reported.

Any thoughts

Thanks again

 

[OraCool]

Ok Coaty,

I am sure I have seen this error before, I will get back as quickly as possible.

Regards

 

[coaty]

Oracool,
Thought I'd try a different tack to do the same thing but still no luck, Can you see what's wrong eith this?

1 PROCEDURE cnt_tbl (var1 IN VARCHAR2) AS
2 cid INTEGER;
3 BEGIN
4 cid := DBMS_SQL.OPEN_CURSOR;
5 DBMS_SQL.PARSE(cid,'SELECT COUNT(*) FROM '||var1,dbms_sql.v8i);
6 DBMS_SQL.CLOSE_CURSOR(cid);
7 END;
8* cnt_tbl ('ng1.users');

which when run gives:

PROCEDURE cnt_tbl (var1 IN VARCHAR2) AS
*
ERROR at line 1:
ORA-00900: invalid SQL statement

Again all help appreciated

Coaty

 

[OraCool]

Hey Coaty,
Try this one the same old script with a little modification:
I have suffixed a semi-colon at the end.

I have not yet started using this feature, I hope this works

DECLARE
var1 char(20):= 'ng1.users';
sql_stmt varchar2(500):= null;
BEGIN
select 'select count(*) from '||var1||';'
into sql_stmt
from dual;
EXECUTE IMMEDIATE sql_stmt;
end;
/