gkaindia.com

Home > > HELP!! Stored Procedures - Adding and Inserting Records

HELP!! Stored Procedures - Adding and Inserting Records

Advertisement
Can ANYONE tell what I'm doing wrong? I'm learning PL/SQL, and I don't know what I'm missing here.
For starters, I'm trying to write a stored procedure to:
-- insert data into a NULL column added using ALTER TABLE
-- there's about +/- 45 records where this data needes to be added.
-- the value for each null field in the column is a random value (that is, generated using dbms_random.random
I've tried several ways to write the procedure, but have run against a wall to determine what I'm missing.
Here's the SQL statements and the error messages:
SQL> CREATE OR REPLACE PROCEDURE update_proj_number (p_proj4_id IN VARCHAR2)
2 AS
3 BEGIN
4 DECLARE
5 v_counter NUMBER(5) := 0;
6 END;
7 BEGIN
8 v_counter := 0;
9 dbms.random.initialize(32768);
10 v_counter := v_counter + 1;
11 p_proj4_id := dbms.random.random
12 UPDATE sample_trades
13 SET proj4_id = RANDOM(p_proj4_id);
14 exit when NOTFOUND%;
15 END;
16 /
Warning: Procedure created with compilation errors.
SQL>
SQL> SHOW ERRORS
Errors for PROCEDURE UPDATE_PROJ_NUMBER:
LINE/COL ERROR
6/2 PLS-00103: Encountered the symbol "END" when expecting one of the following:
begin function package pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> cursor form current
12/3 PLS-00103: Encountered the symbol "UPDATE" when expecting one of the following:
. ( * @ % & = - + ; < / > in mod not rem an exponent (**)
<> or != or ~= >= <= <> and or like between is null is not &#0124; &#0124; is dangling
The symbol ";" was substituted for "UPDATE" to continue.
14/22 PLS-00103: Encountered the symbol ";" when expecting one of the following:
( type <an identifier> <a double-quoted delimited-identifier>
SQL>
Would it be more efficient to use a cursor to fetch the rows containing the null value or is a stored procedure OK for this?
Also, I would like to have a second example of a stored procedure that will add random rows to a table (that is, create duplicates of information already in the table). This stored procedure will also use dbms_random.
For example,
IF p_value1 = 'FIRST_VALUE' then
add this record (incl. random_val for key);
ELSIF p_value = 'SECOND_VALUE' then
add that record (incl. random_val for key);
and on and on.
Much thanks to anyone who can help.
For example,
null
Advertisement
Try this
CREATE OR REPLACE PROCEDURE update_proj_number (p_proj4_id IN VARCHAR2)
AS
v_counter NUMBER(5) := 0;
BEGIN
v_counter := 0;
dbms.random.initialize(32768);
v_counter := v_counter + 1;
p_proj4_id := dbms.random.random ;
UPDATE sample_trades
SET proj4_id = RANDOM(p_proj4_id);
END;
/
Read the other 3 answers
I am using stored procedures for a form. In DB, I have select and update procedures work without problems. However, for the insert procedure, it does not seem work right. Here is how things work for the insert procedure: 1. On the form, I have an add
Can I call a Stored Procedure in After Insert Trigger ? Please send a sample code (good example) of After Insert Trigger. Thanks.Kishore, I have two table WLCS_ORDER, WLCS_ORDER_LINE WLCS_ORDER - It holds order header information like ORDER_ID CUSTOM
I have two synchronous scenarios XI<->PI<->JDBC, where JDBC is receiver adapter. Each scenario runs a different stored procedure in Oracle database. The first stored procedure returns only one record, the second stored procedure returns many r
Hallo, I┬┤ m trying calling a stored-procedure with two input-parameter; one of typ record (oracle) and one of type tabel of records. Is this possible (I think there are only types like string, integer etc. possible)? When not is there another possibi
I am working a form and block A is based on a stored procedure. Assuming it contains customer information and in a tab form contains 20 customer's info. When the mouse cursor pointing to any one of the record, I'd like to pass the record, just one in
Hi there, I'm trying to use the type is record to setup all my output columns; However, these columns are joined from 2 or more tables. i.e., TYPE my_rec IS RECORD ( myColumn1 varchar2(50), myColumn2 varchar2(20) where myColumn1 is slected from a joi
hi all, i've created stored procedures for when inserting records into my tables. when using it in my master, i created a pl/sql process and just call out my procedure passing all the item values... how do i do this on the detail report? thanks allen
I need to create a stored procedure which will insert data in two tables. The procedure will get its inputs from an Oracle Developer Form which will be inserted into the tables. The 2 tables structure: 1.FEES_MASTER Name Null? Type FEES_ID NOT NULL N
Hi. I'm Oscar and I'm new for here. I have a problem that i can't find the correct answer to solve it. Well. I've created a form with 2 blocks. Both of them have the Query Data Source Type Property to "Procedure", and both procedures on database
I have the following table... CREATE TABLE I_TIPOLOGIA ( CODICE NUMBER(6), DESCRIZIONE VARCHAR2(100), CONSTRAINT I_TIPOLOGIA_PK PRIMARY KEY (CODICE) ); and the following package to connect a datablock to previous table with a stored procedure... CREA
Hi Experts, Currently in my application we have view objects and also we have some stored procedure calls to insert and update data (in the procedure we don't commit it. Due to the volume and complexity we couldn't migrate all the procedures). After
Hi, I am working on a form with data block based on stored procedures. Here is the question: On a data block based on simple query, where insert a new record is taking care of by oracle form. That is create_record will do the job. When the data block
Dear All, I have a requirement, where i have to call a stored procedure through reciever JDBC adapter by providing multiple records at a time as input to the stored procedure, and also recieve multiple records at a time. Can any one tell me, how the
I have a JSP/Struts application that has a master table and several subordinate tables. Each table has inserts, updates and deletes handled by a stored procedure. When a record is inserted into any of the subordinate tables, the stored procedure gets
A stored procedure exists that inserts a new record and returns the newly generated record ID. This stored procedure is called from a WinForms application but at the ExecuteNonQuery time an exception is thrown indicating that the wrong number of para
Dear Experts, Please help / guide on below statement which i want to execute in stored procedure, EXECUTE IMMEDIATE 'INSERT INTO TABLE SELECT COUNT(AGENTID) FROM TABLE_1 A, TABLE_2 B, TABLE_3 C WHERE A.COL = B.COL AND B.COL = C.COL AND DT BETWEEN TRU
i'm oracle express edition newbie :) i have this stored procedure. it not belong to a package. create or replace procedure "LIST_MEMBERS" ("MEMBER_CUR" OUT SYS_REFCURSOR) is begin OPEN MEMBER_CUR FOR SELECT * FROM members; end; now, in
Hi, I'm new to the Oracle SOA Suite and have been creating very simple SCA WebServices (async and sync) prototypes to INSERT, UPDATE and Poll Oracle 9i and 11g databases. So far, everything works and passes the tests from EM. I cannot get the Stored
Hi All, I am facing a problem in the stored procedure. In the stored procedure I am picking records from the table and displaying them in the screen using dbms_output.put_line statement. This works fine if my query fetches few records but in case of