Re: why 922 error?

Oops, mispoke.
  The procedure executes fine in sqlplus, but not via a jdbc connect. I did some more playing around with this and discovered some weirdness. Instead of the stored proc, I inlined the create user command in my java code and sent that across to the db. It also threw a 922. (This was in my applet). So, I created a little application that just issued the create user statement and of course it worked. The only difference between the test case application and the applet is that the connection is "fresh" in the application. i.e. connection is established and the create user statement is sent. In my applet, the connection is established, other selects/inserts/updates are done and then a create user statement may be issued. I changed the applet to establish a brand new connection, then issued the create user statement, and it worked...go figure. Then using the original connection, I execute other stored procedures that create the db tables for that user. Those procedures work fine. It's only buggy when a create user statement is issued and only if the connection is "old" (relatively speaking...) One final note, the fresh connection didn't help the stored procedure work, it still threw a 922.
So, inlining the command, with a fresh connect is how I'll have to do it...

Thanks for your time and interest!
Cindy

Thomas Kyte wrote:

> A copy of this was sent to "C. Ferguson" <c_ferguson_at_rationalconcepts.com>
> (if that email address didn't require changing)
> On Sun, 25 Jul 1999 23:04:40 -0700, you wrote:
>
> >Ok gang, need some help in figuring out what's going on...
> >I've create a stored procedure via the following script.
> >
> >CREATE OR REPLACE PROCEDURE makeauser (who VARCHAR2) AS
> > v_Cursor integer default dbms_sql.open_cursor;
> > v_s dbms_sql.VARCHAR2S;
> > BEGIN
> > v_s(1) := 'CREATE USER ' || who;
> > v_s(2) := ' IDENTIFIED BY test' || who;
> > v_s(3) := ' DEFAULT TABLESPACE MYUSERS ';
> > v_s(4) := ' TEMPORARY TABLESPACE MYTEMP ';
> > v_s(5) := ' QUOTA UNLIMITED ON MYUSERS ';
> > v_s(6) := ' QUOTA UNLIMITED ON MYTEMP ';
> > v_s(7) := ' QUOTA UNLIMITED ON MYTBS ';
> > v_s(8) := ' QUOTA 5M ON SYSTEM ';
> >
> >
> > DBMS_SQL.PARSE(v_Cursor, v_s, 1, 8, TRUE, DBMS_SQL.NATIVE);
> >
> > v_s(1) := ' GRANT CODE TO ' || who;
> >
> > DBMS_SQL.PARSE(v_Cursor, v_s, 1, 1, TRUE, DBMS_SQL.NATIVE);
> >
> > EXCEPTION
> > WHEN OTHERS THEN
> > if (dbms_sql.is_open(v_Cursor)) then
> > DBMS_SQL.CLOSE_CURSOR(v_Cursor);
> > end if;
> > DBMS_OUTPUT.PUT_LINE(SQLERRM);
> > raise;
> >
> > END makeauser;
> >/
> >
> >GRANT EXECUTE ON makeauser TO PUBLIC;
> >
> >COMMIT;
> >
> >
> >Tablespaces exist and code role exists.
> >So here's the problem. The script runs just fine in sqlplus.
> >When I connect through jdbc, the script throws a 922 error, missing or
> >invalid option.
>
> You say "the script thows a 922 error". JDBC doesn't do 'scripts'. How exactly
> are you submitting this via jdbc?
>
> >So, what's going on here?
> >
> >Any help would be greatly appreciated.
> >Thanks in advance,
> >Cindy
> >
> >P.S. Oracle 8.0.4 on Solaris 2.6 and winNT4.0
> >
> >
>
> --
> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June 21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Mon Jul 26 1999 - 16:03:33 CDT