| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Help with DBMS_SQL
A copy of this was sent to jon clarke <jonathan.clarke_at_uk.sun.com>
(if that email address didn't require changing)
On Thu, 28 Jan 1999 11:43:54 +0000, you wrote:
>Folks,
>
>I am trying to dynamically drop and create sequences. The dropping is
>working fine, but the creating keeps coming back with errors.
>
>Errors
>sType CREATE sSeqName ACIT_ID_SEQ
>Called CREATE
>sStatement is CREATE SEQUENCE ACIT_ID_SEQ
>Created cursor
>pr_build_dynamic:Something went wrong. closing cursor ORA-01403: no data
>found
>sequence_install:Problem creating sequences ORA-01403: no data found
>
>
its a little known fact that SQLERRM is a function that given an error code, returns the error message. Most people use it just to get the last error message. Your error handler is calling "sqlerrm(100)" which will always give you:
SQL> begin
2 dbms_output.put_line( sqlerrm(100) );
3 end;
4 /
ORA-01403: no data found
PL/SQL procedure successfully completed
So, your exception block will always report "no data found" for all errors regardless. Don't use sqlerrm(100), just use sqlerrm to get the text of the LAST error that occurred.
Ok, once you do that, i believe you will find that the real error is
ORA-01031: insufficient privileges
and that will be because roles are never enabled during the execution of a procedure.
Try this:
SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"
If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure.
You probably have the privelege to do what you are trying to do in the procedure via a role. Grant the privelege directly to the owner of the procedure and it'll work.
grant create sequence to <OWNER>;
>Code is
>
>DBMS_OUTPUT.put_line('Called CREATE');
>
>
> sStatement := 'CREATE SEQUENCE '||sSeqname;
> --START WITH '||TO_CHAR(nStartValue);
> DBMS_OUTPUT.put_line('sStatement is '||sStatement);
>
> cursor_name := DBMS_SQL.open_cursor;
> DBMS_OUTPUT.put_line('Created cursor');
>
> -- Create the statement you want parsed
> DBMS_SQL.parse(cursor_name
> , sStatement
> , DBMS_SQL.v7);
> DBMS_OUTPUT.put_line('Parsed statement');
>
> -- Execute
> rows_processed := DBMS_SQL.execute(cursor_name);
> DBMS_OUTPUT.put_line('Executed');
>
> --Close after successful completion
> DBMS_SQL.close_cursor(cursor_name);
> DBMS_OUTPUT.put_line('Cursor closed normally');
> END IF;
>EXCEPTION
> WHEN OTHERS
> THEN
> DBMS_OUTPUT.put_line('pr_build_dynamic:Something went wrong.
>closing cursor '|| SQLERRM(100));
> DBMS_SQL.close_cursor(cursor_name);
> RAISE;
>
>
>any suggestions
>
>Cheers
>
>Jon
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Jan 29 1999 - 07:06:19 CST
 |
 |