What's the problem here for dbms_sql
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: What's the problem here for dbms_sql

  1. #1
    Join Date
    Oct 2000
    Posts
    449

    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

  2. #2
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    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."

    Amar's Blog  Get Firefox!

  3. #3
    Join Date
    Oct 2000
    Posts
    449
    This is done, with set serveroutput on ...

    Can't see anything else, but posted..

    Thx, ST2000

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968

    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;
    /

  5. #5
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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

  6. #6
    Join Date
    Oct 2000
    Posts
    449
    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

  7. #7
    Join Date
    May 2002
    Posts
    2,645
    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)

  8. #8
    Join Date
    May 2002
    Posts
    2,645
    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.

  9. #9
    Join Date
    May 2002
    Posts
    2,645
    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)

  10. #10
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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 04: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
  •  



Click Here to Expand Forum to Full Width