Truncating Within Procedures

I am able to truncate tables within a stored procedure by passing the table name (mytable) to a procedure which performs the truncate (truncate_table), as in

        truncate_table('mytable')

where procedure truncate_table is

        create or replace procedure truncate_table (tablename varchar2) as

        v_crsr := dbms_sql.open_cursor;
        dbms_sql.parse(v_crsr, 'truncate table ' || tablename, DBMS_SQL.V7);
        v_rtn := dbms_sql.execute(v_crsr);
        dbms_sql.close_cursor(v_crsr);
        END truncate_table

I have no problems when the table name is explicitly called out, but run into problems when the table name is a synonym. I've tried to bind the synonym to a variable and pass this to the procedure like this:

        v_crsr := dbms_sql.open_cursor
        dbms_sql.bind_variable(v_crsr, ':v_tbl', 'mysynonym')
        truncate_table(v_tbl)
        dbms_sql.close_cursor(v_crsr);

but am not having much luck, in that I receive the following error:

        ORA-01003: no statement parsed

Any thoughts on how to get this to work? Thanks in advance!

John Flipse

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Oct 09 1998 - 00:41:59 CDT