Re: Please HELP !!!(It makes me crazy)

From: Mikhail <stiger_at_chat.ru>
Date: 1998/01/14
Message-ID: <34bd29d0.1446840@news.gncom.com>#1/1

On 14 Jan 1998 20:17:31 GMT, l120bj_at_aol.com (L120bj) wrote:

>
>>Subject: Please HELP !!!(It makes me crazy)
>>From: stiger_at_chat.ru (Mikhail)
>>Date: 1/14/98 5:59PM GMT
>>Message-id: <34bcf4dc.12256143_at_news.gncom.com>
>>
>>Hello, everyone,
>>I try create a storage procedure that produces a table_name and
>>creates a table with this table_name in run time.
>>I use
>>…
>>table_name VARCHAR2(6);
>>command_run VARCHAR2(200);
>>BEGIN
>>…
>>cursor_name:= DBMS_SQL.OPEN_CURSOR;
>>command_run:=’CREATE TABLE ‘ || table_name || ‘ AS SELECT * FROM emp’;
>>DBMS_SQL.PARSE(cursor_name, command_run, DBMS_SQL.V7);
>>row_processed:= DBMS_SQL.EXECUTE(cursor_name);
>>DBMS_SQL.CLOSE_CURSOR(cursor_name);
>>^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>>It does not work.
>>But, if I use
>>command_run=’DROP TABLE ‘|| table_name ||;
>>-- where table_name is an existing table, it works!.
>>If you have had a similar problem, please drop me mail.
>>Thank you for your time.
>>Mike.
>>
>>
>>
>>
>>
>>
>>
>
>Is it reporting an error message, could you let us know. It is quite possible
>that it is permissions related, since code executed in stored procedures relies
>on specifically granted privileges (e.g. CREATE TABLE) and not those available
>through a role (e.g. DBA)

Thank you for your interest in this problem. Compiler doesn’t produce any errors and I have the permission for CREATE TABLE.
And my code is:
( It should produce an SQL statement like

---

CREATE TABLE dec_97 AS SELECT * FROM all_year WHERE TO_CHAR(current_date,’MON’) = ‘DEC’

---

CREATE OR REPLACE PROCEDURE backup_table_name AS cursor_name INTEGER;
row_processed INTEGER;
table_name VARCHAR2(6);
command_run VARCHAR2(200);

BEGIN
cursor_name:= DBMS_SQL.OPEN_CURSOR;
SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-1), 'MON_YY') INTO table_name FROM dual;
command_run:='DROP TABLE ' || table_name; DBMS_OUTPUT.PUT_LINE('Table name is: ' || table_name); DBMS_SQL.PARSE(cursor_name, command_run, DBMS_SQL.V7); row_processed:= DBMS_SQL.EXECUTE(cursor_name); DBMS_SQL.CLOSE_CURSOR(cursor_name);
EXCEPTION

	WHEN OTHERS THEN
	DBMS_SQL.CLOSE_CURSOR(cursor_name);

END; I think the variable command_run should be something like command_run:=‘CREATE TABLE ‘ ||table_name|| ‘ AS SELECT * FROM all_year WHERE TO_CHAR(current_date, ‘MON’)= TO_CHAR(ADD_MONTHS(SYSDATE,-1), 'MON’)’

 Thanks for any help.

Regards, Mike. Received on Wed Jan 14 1998 - 00:00:00 CST