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

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Eraser
    #1

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

    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(u id 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.SQLExc eption: ORA-00900: invalid SQL statement

    Can anyone please point out what is wrong with the above
    procedure?

    Thanks,
    Eraser
    Tags: errorcode: 17110, sqlstate
    • Alex Ivascu
      #2
      Re: Newbie PL/SQL Question - Inserting into a table from a stored procedure

      There doesn't seem to be nothing wrong with your statement.
      It might be the way SquirrelSQL interprets it.

      Alex Ivascu


      "Eraser" <eraser@nospam. comwrote in message
      news:pan.2004.0 5.30.09.57.25.4 94180@nospam.co m...
      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(u id 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.SQLExc eption: ORA-00900: invalid SQL statement
      >
      Can anyone please point out what is wrong with the above
      procedure?
      >
      Thanks,
      Eraser

        Comment

        • Kris
          #3
          Re: Newbie PL/SQL Question - Inserting into a table from a stored procedure

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

          SQLcreate 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.

          SQLCREATE OR REPLACE PROCEDURE insert_person(u id 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.

          SQLset serveroutput on
          SQLexec insert_person(1 ,'first', 'middle', 'last');

          PL/SQL procedure successfully completed.

          SQLCOMMIT;

          Commit complete.

          SQLselect * 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@nospam. comwrote in message
          news:pan.2004.0 5.30.09.57.25.4 94180@nospam.co m...
          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(u id 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.SQLExc eption: ORA-00900: invalid SQL statement
          >
          Can anyone please point out what is wrong with the above
          procedure?
          >
          Thanks,
          Eraser

            Comment

            Previous template Next
            Working...