Inserting a record for every value from a query

[cavbeek]

Hi all,

First of all let me admit that I have hardly any SQL knowledge, so I might be making very silly mistakes ;-)

I've configured some triggers (with the help of some templates etc.), which will insert a record in a table with events, as soon as a record is inserted/updated. The event-table will be used by an external process to synchronise data.
This works and I've tested it extensively.

Now the only thing I require is a statement that will be used only once, to start the initial synchronisation . I want to select all records in a table, and for each record, I want to insert a record into my event-table. If I can run it from Toad, it'll be more than sufficient.

This is what I have come up with:
[code=oracle]

CURSOR c is SELECT * FROM prig_own.dpic30 0;

BEGIN
FOR r IN c LOOP
INSERT INTO idmdirect.event _process
(
record_id,
table_key,
event_type,
event_time,
table_name
)
VALUES
(
idmdirect.seq_l og_record_id.ne xtval,
('pk_Dienstverb andID=' || REPLACE(r.objec t_ID,' ', '')),
6,
sysdate,
'view_dienstver band'
);

END LOOP;
COMMIT;

END;

[/code]



I'm getting bombarded with "invalid syntax" and errors stating that a different character is expect instead of c (my cursor)

This is oracle 9i, which makes using FORALL and BULK COLLECTION impossible (I think?!)

Thx in advance!

Kind regards,
Kees

[amitpatel66]

Hi all,

First of all let me admit that I have hardly any SQL knowledge, so I might be making very silly mistakes ;-)

I've configured some triggers (with the help of some templates etc.), which will insert a record in a table with events, as soon as a record is inserted/updated. The event-table will be used by an external process to synchronise data.
This works and I've tested it extensively.

Now the only thing I require is a statement that will be used only once, to start the initial synchronisation . I want to select all records in a table, and for each record, I want to insert a record into my event-table. If I can run it from Toad, it'll be more than sufficient.

This is what I have come up with:
[code=oracle]

CURSOR c is SELECT * FROM prig_own.dpic30 0;

BEGIN
FOR r IN c LOOP
INSERT INTO idmdirect.event _process
(
record_id,
table_key,
event_type,
event_time,
table_name
)
VALUES
(
idmdirect.seq_l og_record_id.ne xtval,
('pk_Dienstverb andID=' || REPLACE(r.objec t_ID,' ', '')),
6,
sysdate,
'view_dienstver band'
);

END LOOP;
COMMIT;

END;

[/code]



I'm getting bombarded with "invalid syntax" and errors stating that a different character is expect instead of c (my cursor)

This is oracle 9i, which makes using FORALL and BULK COLLECTION impossible (I think?!)

Thx in advance!

Kind regards,
Kees

Use DECLARE keyword before your cursor.
There are three sections in plsql block:

Declare - Optional (If you have any variable,cursor the be declared, then use this section)
Begin - Mandatory
EXCEPTION - Optional (To Handle Exceptions)

Why not use FORALL BULK INSERT in Oracle 9i? Oracle 9i very well supports this concepts.

[cavbeek]

Use DECLARE keyword before your cursor.
There are three sections in plsql block:

Declare - Optional (If you have any variable,cursor the be declared, then use this section)
Begin - Mandatory
EXCEPTION - Optional (To Handle Exceptions)

Why not use FORALL BULK INSERT in Oracle 9i? Oracle 9i very well supports this concepts.

I'm a bit at a loss... I thought I was using declare to declare the cursor? If I try and create a "normal" stored procedure, like one I have working, I just get a ORA-00900, invalid SQL Syntax.

I realise that me lacking basic knowledge is probably the problem here, but could you point a bit further in the right direction?

[code=oracle]
CREATE OR REPLACE PROCEDURE
"IDMDIRECT"."PR OC_FULLSYNC_DIE NSTVERBAND" AS
CURSOR c is SELECT * FROM prig_own.dpic30 0;

BEGIN
FOR r IN c LOOP
INSERT INTO idmdirect.event _process
(
record_id,
table_key,
event_type,
event_time,
table_name
)
VALUES
(
idmdirect.seq_l og_record_id.ne xtval,
('pk_Dienstverb andID=' || REPLACE(r.objec t_ID,' ', '')),
6,
sysdate,
'view_dienstver band'
);

END LOOP;
COMMIT;

END;
[/code]

Regarding the FORALL BULK INSERT, I googled across some codesamples, which didn't seem to work for me. Another poster had the same problems, which (in the messageboard's opinion) was the result of using Oracle <10.

In my case it doesn't really matter, since I just need to run it once, to create an initial starting point :D

[amitpatel66]

I'm a bit at a loss... I thought I was using declare to declare the cursor? If I try and create a "normal" stored procedure, like one I have working, I just get a ORA-00900, invalid SQL Syntax.

I realise that me lacking basic knowledge is probably the problem here, but could you point a bit further in the right direction?

[code=oracle]
CREATE OR REPLACE PROCEDURE
"IDMDIRECT"."PR OC_FULLSYNC_DIE NSTVERBAND" AS
CURSOR c is SELECT * FROM prig_own.dpic30 0;

BEGIN
FOR r IN c LOOP
INSERT INTO idmdirect.event _process
(
record_id,
table_key,
event_type,
event_time,
table_name
)
VALUES
(
idmdirect.seq_l og_record_id.ne xtval,
('pk_Dienstverb andID=' || REPLACE(r.objec t_ID,' ', '')),
6,
sysdate,
'view_dienstver band'
);

END LOOP;
COMMIT;

END;
[/code]

Regarding the FORALL BULK INSERT, I googled across some codesamples, which didn't seem to work for me. Another poster had the same problems, which (in the messageboard's opinion) was the result of using Oracle <10.

In my case it doesn't really matter, since I just need to run it once, to create an initial starting point :D

Do you have SELECT privilege on the table that you are trying to select from?

Run your sql query of your cursor in TOAD/sql plus and check if it fetches data for you or display error?

[cavbeek]

Do you have SELECT privilege on the table that you are trying to select from?

Run your sql query of your cursor in TOAD/sql plus and check if it fetches data for you or display error?

I explicitly granted it to the user-object, and running the query through toad gives me fine results.

This script is provided by Novell, the manifacturer of the synchronisation software IDM.
[code=oracle]
CREATE PROCEDURE idmdirect.proc_ eventlog
AS
CURSOR c is SELECT * FROM idmdirect.event _process
WHERE status != 'N' FOR UPDATE;
BEGIN
FOR r IN c LOOP
INSERT INTO idmdirect.event _processed
VALUES
(
r.record_id,
r.table_key,
r.status,
r.event_type,
r.event_time,
r.perpetrator,
r.table_name,
r.column_name,
r.old_value,
r.new_value
);
DELETE FROM idmdirect.event _process
WHERE CURRENT OF c;
END LOOP;
COMMIT;

-- EXECUTE IMMEDIATE 'ALTER INDEX indirect.idx_in directlog_1 REBUILD ONLINE';
-- EXECUTE IMMEDIATE 'ALTER INDEX indirect.idx_in directlog_2 REBUILD ONLINE';
-- EXECUTE IMMEDIATE 'ALTER INDEX indirect.idx_in directlog_3 REBUILD ONLINE';

END proc_eventlog;
[/code]

I am certain I used this before in other implementations , but running an EXECUTE idmdirect.proc_ eventlog, now gives me the same ORA-00900, invalid syntax. I'm starting to wonder if anything else might be wrong.

[amitpatel66]

I explicitly granted it to the user-object, and running the query through toad gives me fine results.

This script is provided by Novell, the manifacturer of the synchronisation software IDM.
[code=oracle]
CREATE PROCEDURE idmdirect.proc_ eventlog
AS
CURSOR c is SELECT * FROM idmdirect.event _process
WHERE status != 'N' FOR UPDATE;
BEGIN
FOR r IN c LOOP
INSERT INTO idmdirect.event _processed
VALUES
(
r.record_id,
r.table_key,
r.status,
r.event_type,
r.event_time,
r.perpetrator,
r.table_name,
r.column_name,
r.old_value,
r.new_value
);
DELETE FROM idmdirect.event _process
WHERE CURRENT OF c;
END LOOP;
COMMIT;

-- EXECUTE IMMEDIATE 'ALTER INDEX indirect.idx_in directlog_1 REBUILD ONLINE';
-- EXECUTE IMMEDIATE 'ALTER INDEX indirect.idx_in directlog_2 REBUILD ONLINE';
-- EXECUTE IMMEDIATE 'ALTER INDEX indirect.idx_in directlog_3 REBUILD ONLINE';

END proc_eventlog;
[/code]

I am certain I used this before in other implementations , but running an EXECUTE idmdirect.proc_ eventlog, now gives me the same ORA-00900, invalid syntax. I'm starting to wonder if anything else might be wrong.

Quite Surprising to why that happened?
Your procedure looks fine.
Anyways, invalid syntax at line no ??

[cavbeek]

Quite Surprising to why that happened?
Your procedure looks fine.
Anyways, invalid syntax at line no ??

Very awkward.... it really is the CURSOR-line...

[code=oracle]
CURSOR cursor1 IS SELECT * FROM prig_own.dpic30 0;
[/code]

When I run this in toad, I still get a ORA-00900, invalid syntax. Just the SELECT-statement works fine :(

[amitpatel66]

Very awkward.... it really is the CURSOR-line...

[code=oracle]
CURSOR cursor1 IS SELECT * FROM prig_own.dpic30 0;
[/code]

When I run this in toad, I still get a ORA-00900, invalid syntax. Just the SELECT-statement works fine :(

Which version of oracle yo uare running?

[cavbeek]

Which version of oracle yo uare running?

SELECT * FROM v$version
WHERE banner LIKE 'Oracle%';

Oracle9i Release 9.2.0.1.0

[cavbeek]

SELECT * FROM v$version
WHERE banner LIKE 'Oracle%';

Oracle9i Release 9.2.0.1.0

OK, I think I found my problem.

1) lack of knowledge, I tried to just execute the procedure, while I should have used something like:
[code=oracle]
BEGIN
IDMDIRECT.PROC_ FULLSYNC;
COMMIT;
END;
[/code]

2) And my user didn't have permissions on the tablespace
[code=oracle]
ALTER USER username QUOTA UNLIMITED on tablespace;
[/code]

My eventtable is full and waiting full-sync! I'm happy :-)
Big thx for the help!

[amitpatel66]

Both are same.:

1. EXECUTE proc_name
2. BEGIN proc_name; END;

Check This:

[code=oracle]

SQL> select * from emp;
ENAME EMPNO
-------------------- ----------
a 1
am 2

SQL> create or replace procedure a IS
2 BEGIN
3 INSERT INTO emp as SELECT * FROM EMP;
4 COMMIT;
5 END;
6 /


Procedure created.

SQL> EXECUTE a

PL/SQL procedure successfully completed.

SQL> select * from emp;
ENAME EMPNO
-------------------- ----------
a 1
am 2
a 1
am 2

SQL> BEGIN a; END;
2 /

PL/SQL procedure successfully completed.

SQL> select * from emp;

ENAME EMPNO
-------------------- ----------
a 1
am 2
a 1
am 2
a 1
am 2
a 1
am 2

8 rows selected.

SQL>

[/code]

If you see in above example, I have used both the ways of calling procedure and it works fine.

[cavbeek]

Both are same.:

1. EXECUTE proc_name
2. BEGIN proc_name; END;

Check This:

[code=oracle]

SQL> select * from emp;
ENAME EMPNO
-------------------- ----------
a 1
am 2

SQL> create or replace procedure a IS
2 BEGIN
3 INSERT INTO emp as SELECT * FROM EMP;
4 COMMIT;
5 END;
6 /


Procedure created.

SQL> EXECUTE a

PL/SQL procedure successfully completed.

SQL> select * from emp;
ENAME EMPNO
-------------------- ----------
a 1
am 2
a 1
am 2

SQL> BEGIN a; END;
2 /

PL/SQL procedure successfully completed.

SQL> select * from emp;

ENAME EMPNO
-------------------- ----------
a 1
am 2
a 1
am 2
a 1
am 2
a 1
am 2

8 rows selected.

SQL>

[/code]

If you see in above example, I have used both the ways of calling procedure and it works fine.

Using EXECUTE, I just got those general ORA-00900 errors. But when I used the Toad-UI (which does the begin procedure; end;), I got some more errors.

ORA-01536 was clear enough. And it works now.

Thx again for the help and your time!