Re: DBMS_SQL

From: <dejanjovanovic_at_my-deja.com>
Date: 2000/07/13
Message-ID: <8kk0af$65j$1@nnrp2.deja.com>#1/1

James,

Klaus told you what to do - you shouldn't bother with DBMS_SQL when you have EXECUTE IMMEDIATE, but if you still want to know why this wasn't work:

v_passwd := '3';    -- This won't work too:
                    -- Password must start with a
                    -- alphabetic character unless
                    -- it is surrounded by double
                    -- quotation marks: '"3"';

v_user := 'FF1';
v_cursor_id := DBMS_SQL.OPEN_CURSOR;
--v_stmt := 'create user :u identified by :p
--default tablespace USR temporary tablespace
--TEMPORARY;';

• You can't use here bind variables but you can
• use something like this: v_stmt := 'CREATE USER ' || v_user || ' IDENTIFIED BY ' || v_passwd || ' default tablespace USR temporary tablespace TEMPORARY';

DBMS_SQL.PARSE(v_cursor_id, v_stmt,
DBMS_SQL.NATIVE);
-- And you do not need these anymore:

• DBMS_SQL.BIND_VARIABLE(v_cursor_id, ':u',
  --v_user);
  
• DBMS_SQL.BIND_VARIABLE(v_cursor_id, ':p',
  --v_passwd);
  

v_dummy := DBMS_SQL.EXECUTE(v_cursor_id); DBMS_SQL.CLOSE_CURSOR(v_cursor_id);

Regards,
Dejan

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jul 13 2000 - 00:00:00 CDT