Re: Newbie PL/SQL Question - Inserting into a table from a stored procedure

From: Kris <Kris_at_ms.com>
Date: Mon, 07 Jun 2004 19:51:57 GMT
Message-ID: <xH3xc.1$Wg.0_at_news04.bloor.is.net.cable.rogers.com>

try running from a simple sql*plus window to test: I just rana quick test with your code and it worked:

SQL> create table PERSONS
  2 (

  3     prsn_uid NUMBER,
  4     prsn_first_nm VARCHAR2(20),
  5     prsn_middle_nm VARCHAR2(20),
  6     prsn_last_nm VARCHAR2(20)

  7 );

Table created.

SQL> CREATE OR REPLACE PROCEDURE insert_person(uid IN NUMBER,

  2                                 first_nm IN VARCHAR,
  3         middle_nm IN VARCHAR,
  4         last_nm IN VARCHAR) IS

  5 BEGIN
  6 INSERT INTO PERSONS (prsn_uid, prsn_first_nm, prsn_middle_nm,

  7        prsn_last_nm) values (uid, first_nm, middle_nm,
  8        last_nm);
  9  END insert_person;

 10 /

Procedure created.

SQL> set serveroutput on
SQL> exec insert_person(1,'first', 'middle', 'last');

PL/SQL procedure successfully completed.

SQL> COMMIT; Commit complete.

SQL> select * from persons;

  PRSN_UID PRSN_FIRST_NM PRSN_MIDDLE_NM PRSN_LAST_NM

---------- -------------------- -------------------- --------------------
         1 first                middle               last

SQL>

.................

I created a simple table based on your procedure, but it might be different.

--you should also look at adding exception handling in your procedure, such as below:
CREATE OR REPLACE PROCEDURE insert_person (

   uid IN NUMBER,
   first_nm IN VARCHAR,
   middle_nm IN VARCHAR,
   last_nm IN VARCHAR) IS
BEGIN
  INSERT INTO PERSONS (prsn_uid, prsn_first_nm, prsn_middle_nm,

      prsn_last_nm) values (uid, first_nm, middle_nm,
      last_nm);
      COMMIT;

EXCEPTION
   WHEN OTHERS THEN

      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE ('Errors found: '||SQLERRM);

END insert_person;
/

who owns the table, who's calling the procedure: do you have privs? are there public/private synonymns?

easiet way to debug is go to the basics: sqlplus. try inserting into the table with the same user you are running from the java, then try the procedure, etc....

"Eraser" <eraser_at_nospam.com> wrote in message news:pan.2004.05.30.09.57.25.494180_at_nospam.com...
> Hello,
>
> I'm just starting to learn PL/SQL. To get my feet wet,
> I'm trying to write a simple stored procedure that takes some
> values as parameters, and inserts those values into a table.
> For some reason my simple procedure is not working, I'm
> probably missing something simple. Here is how I'm trying to
> create this procedure:
>
> CREATE OR REPLACE PROCEDURE insert_person(uid IN NUMBER,
> first_nm IN VARCHAR,
> middle_nm IN VARCHAR,
> last_nm IN VARCHAR) IS
> BEGIN
> INSERT INTO PERSONS (prsn_uid, prsn_first_nm, prsn_middle_nm,
> prsn_last_nm) values (uid, first_nm, middle_nm,
> last_nm);
> END insert_person;
>
> I'm not sure this is relevant, but I'm typing the above
> declaration into an Java based SQL client called SquirrelSQL
> (http://sourceforge.net/projects/squirrel-sql/), the
> output I get after executing the above is:
>
> Warning: Warning: execution completed with warning
> SQLState: null
> ErrorCode: 17110
> 0 Rows Updated
> Query 1 elapsed time (seconds) - Total: 0.053, SQL query: 0.053,
> Building output: 0
> Error: java.sql.SQLException: ORA-00900: invalid SQL statement
>
> Can anyone please point out what is wrong with the above
> procedure?
>
> Thanks,
> Eraser
Received on Mon Jun 07 2004 - 21:51:57 CEST