-
What's the problem here for dbms_sql
SQL > create or replace procedure cr_tbl (string varchar2) as
cursor_name integer;
stmnt_proc integer;
begin
cursor_name := dbms_sql.open_cursor;
dbms_sql.parse(cursor_name, string, dbms_sql.native);
dbms_sql.bind_variable (cursor_name, 'string', string);
dbms_output.put_line('String passed in is: '||string);
stmnt_proc := dbms_sql.execute (cursor_name);
dbms_sql.close_cursor(cursor_name);
exception
when others then
dbms_sql.close_cursor(cursor_name);
end;
/
Procedure created.
SQL> exec cr_tbl ('create table t1 (name varchar2)');
PL/SQL procedure successfully completed.
SQL> desc t1
ERROR:
ORA-04043: object t1 does not exist
Thx, ST2000
-
What is the output if you set serverout on and then execute the procedure
Amar
"There is a difference between knowing the path and walking the path."
-
This is done, with set serveroutput on ...
Can't see anything else, but posted..
Thx, ST2000
-
Re: What's the problem here for dbms_sql
Originally posted by st2000
exception
when others then
dbms_sql.close_cursor(cursor_name);
end;
/
If you are getting an error then your exceptions section will stop it from being raised. If you are using 8i or above you don't need dbms_sql.
Try this instead:
Code:
create or replace procedure cr_tbl (p_string varchar2) as
v_cr_table VARCHAR2(2000);
begin
EXECUTE IMMEDITE p_string;
end;
/
Otherwise you need to do this:
Code:
exception
when others then
dbms_sql.close_cursor(cursor_name);
raise;
end;
/
-
As you do not define a bind variable in the query, you don't need to bind it. You pass the SQL text in the "string" variable, not a value for a bind variable.
You forgot to define length for the "name" column, should be:
exec cr_tbl ('create table t1(name varchar2(100))');
Remove "dbms_sql.bind_variable (cursor_name, 'string', string);", adjust the query and try again.
Ales The whole difference between a little boy and an adult man is the price of toys
-
Please look at my code.. I am not getting any errors in compiling and executing the procedure.. The error is only when I am trying to retrieve what I did and it is obvious that table was not created.. I tried the code that you sent me and still I get errors..
SQL> create or replace procedure cr_tbl (p_string varchar2) as
2 v_cr_table VARCHAR2(2000);
3 begin
4 EXECUTE IMMEDIATE p_string;
5 end;
6 /
Procedure created.
SQL> exec cr_tbl('create table t1 (name varchar2)');
BEGIN cr_tbl('create table t1 (name varchar2)'); END;
*
ERROR at line 1:
ORA-00906: missing left parenthesis
ORA-06512: at "SCOTT.CR_TBL", line 4
ORA-06512: at line 1
**************
SQL> create or replace procedure cr_tbl (string varchar2) as
2 cursor_name integer;
3 stmnt_proc integer;
4 begin
5 cursor_name := dbms_sql.open_cursor;
6 dbms_sql.parse(cursor_name, string, dbms_sql.native);
7 dbms_sql.bind_variable (cursor_name, 'string', string);
8 dbms_output.put_line('String passed in is: '||string);
9 stmnt_proc := dbms_sql.execute (cursor_name);
10 dbms_sql.close_cursor(cursor_name);
11 exception
12 when others then
13 dbms_sql.close_cursor(cursor_name);
14 raise;
15 end;
16 /
Procedure created.
SQL> exec cr_tbl('create table t1 (name varchar2)');
BEGIN cr_tbl('create table t1 (name varchar2)'); END;
*
ERROR at line 1:
ORA-00906: missing left parenthesis
ORA-06512: at "SCOTT.CR_TBL", line 14
ORA-06512: at line 1
**************
But, comment out the raise statement and atleast it executes, but still the table is not created though, where I initiated this.. I am using version 817 on Win NT
SQL> create or replace procedure cr_tbl (string varchar2) as
2 cursor_name integer;
3 stmnt_proc integer;
4 begin
5 cursor_name := dbms_sql.open_cursor;
6 dbms_sql.parse(cursor_name, string, dbms_sql.native);
7 dbms_sql.bind_variable (cursor_name, 'string', string);
8 dbms_output.put_line('String passed in is: '||string);
9 stmnt_proc := dbms_sql.execute (cursor_name);
10 dbms_sql.close_cursor(cursor_name);
11 exception
12 when others then
13 dbms_sql.close_cursor(cursor_name);
14 --raise;
15 end;
16 /
Procedure created.
SQL> exec cr_tbl('create table t1 (name varchar2)');
PL/SQL procedure successfully completed.
SQL> desc t1
ERROR:
ORA-04043: object t1 does not exist
Thx, ST2000
-
Code:
SQL> CREATE OR REPLACE PROCEDURE ddlproc
2 (tablename varchar2, cols varchar2)
3 AS
4 cursor1 INTEGER;
5 BEGIN
6 cursor1 := dbms_sql.open_cursor;
7 dbms_sql.parse(cursor1, 'CREATE TABLE ' || tablename ||
8 ' ( ' || cols || ' )', dbms_sql.v7);
9 dbms_sql.close_cursor(cursor1);
10 END;
11 /
Procedure created.
SQL> execute ddlproc ('T1','NAME VARCHAR2(10)');
PL/SQL procedure successfully completed.
SQL> desc t1
Name Null? Type
----------------------------------------- -------- ----------------
NAME VARCHAR2(10)
-
Code:
SQL> exec cr_tbl('create table t2 (name varchar2(10))');
PL/SQL procedure successfully completed.
SQL> desc t2
Name Null? Type
----------------------------------------- -------- -------------
NAME VARCHAR2(10)
Works for me.
-
Also works this way:
Code:
SQL> CREATE OR REPLACE PROCEDURE CR_TBL
2 (string_in VARCHAR2)
3 IS
4 SQL_STMT VARCHAR2(500);
5 BEGIN
6 SQL_STMT := string_in;
7 EXECUTE IMMEDIATE SQL_STMT;
8 END;
9 /
Procedure created.
SQL> exec cr_tbl('create table t2 (name varchar2(10))');
PL/SQL procedure successfully completed.
SQL> desc t2
Name Null? Type
----------------------------------------- -------- ------------
NAME VARCHAR2(10)
-
I gess u have problem with priviligies.
because :
create table has to grant directly, not thru role.
Code:
create or replace
procedure Do_Sql(p_txt_sql varchar2) is
temp_sql integer;
r number ;
begin
temp_sql := dbms_sql.open_cursor;
dbms_sql.parse(temp_sql,p_txt_sql,DBMS_SQL.NATIVE);
r := dbms_sql.execute(temp_sql);
dbms_sql.close_cursor(temp_sql);
return;
exception
when INVALID_CURSOR then raise_application_error(-20100, 'Invalid sql :'||p_txt_sql);
when OTHERS then raise_application_error(-20100, 'Undefined error in do_sql() with '||p_txt_sql);
end;
/
---------------------------------------------
SQL> grant create table to train;
Grant succeeded.
-- second session;
---------------------------------------------
SQL> begin
Do_Sql('create table test_01(a char(1), b number)');
end;
/
SQL> desc test_01
Name Null? Type
---------------- -------- ----------------------------
A CHAR(1)
B NUMBER
PL/SQL procedure successfully completed.
---------------------------------------------
SQL> revoke create table from train;
Revoke succeeded.
-- second session;
---------------------------------------------
SQL> begin
Do_Sql('create table test_01(a char(1), b number)');
end;
/
begin
*
ERROR at line 1:
ORA-20100: Undefined error in do_sql() with create table test_01(a char(1), b number)
ORA-06512: at "TRAIN.DO_SQL", line 12
ORA-06512: at line 2
-- BUT !!! (because user has resource role)
SQL> create table test_01(a char(1));
Table created.
Just check it.
Last edited by Shestakov; 03-03-2003 at 05:56 PM.
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
|