469,271 Members | 1,322 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

Inserting a record for every value from a query

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:
Expand|Select|Wrap|Line Numbers
  1.  
  2. CURSOR c is SELECT * FROM prig_own.dpic300;
  3.  
  4. BEGIN
  5. FOR r IN c LOOP
  6. INSERT INTO idmdirect.event_process
  7. (
  8. record_id, 
  9. table_key,
  10. event_type,
  11. event_time,
  12. table_name
  13. )
  14. VALUES
  15. (
  16. idmdirect.seq_log_record_id.nextval,
  17. ('pk_DienstverbandID=' || REPLACE(r.object_ID,' ', '')),
  18. 6,
  19. sysdate,
  20. 'view_dienstverband'
  21. );
  22.  
  23. END LOOP;
  24. COMMIT;
  25.  
  26. END;
  27.  
  28.  


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
Mar 14 '08 #1
11 4063
amitpatel66
2,367 Expert 2GB
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:
Expand|Select|Wrap|Line Numbers
  1.  
  2. CURSOR c is SELECT * FROM prig_own.dpic300;
  3.  
  4. BEGIN
  5. FOR r IN c LOOP
  6. INSERT INTO idmdirect.event_process
  7. (
  8. record_id, 
  9. table_key,
  10. event_type,
  11. event_time,
  12. table_name
  13. )
  14. VALUES
  15. (
  16. idmdirect.seq_log_record_id.nextval,
  17. ('pk_DienstverbandID=' || REPLACE(r.object_ID,' ', '')),
  18. 6,
  19. sysdate,
  20. 'view_dienstverband'
  21. );
  22.  
  23. END LOOP;
  24. COMMIT;
  25.  
  26. END;
  27.  
  28.  


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.
Mar 14 '08 #2
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?

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE  PROCEDURE 
  2.     "IDMDIRECT"."PROC_FULLSYNC_DIENSTVERBAND" AS
  3. CURSOR c is SELECT * FROM prig_own.dpic300;
  4.  
  5. BEGIN
  6.   FOR r IN c LOOP
  7.   INSERT INTO idmdirect.event_process
  8.   (
  9.     record_id, 
  10.     table_key,
  11.     event_type,
  12.     event_time,
  13.     table_name
  14.   )
  15.   VALUES
  16.   (
  17.     idmdirect.seq_log_record_id.nextval,
  18.     ('pk_DienstverbandID=' || REPLACE(r.object_ID,' ', '')),
  19.     6,
  20.     sysdate,
  21.     'view_dienstverband'
  22.   );
  23.  
  24.    END LOOP;
  25.    COMMIT;
  26.  
  27. END;
  28.  
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
Mar 14 '08 #3
amitpatel66
2,367 Expert 2GB
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?

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE PROCEDURE 
  2. "IDMDIRECT"."PROC_FULLSYNC_DIENSTVERBAND" AS
  3. CURSOR c is SELECT * FROM prig_own.dpic300;
  4.  
  5. BEGIN
  6. FOR r IN c LOOP
  7. INSERT INTO idmdirect.event_process
  8. (
  9. record_id, 
  10. table_key,
  11. event_type,
  12. event_time,
  13. table_name
  14. )
  15. VALUES
  16. (
  17. idmdirect.seq_log_record_id.nextval,
  18. ('pk_DienstverbandID=' || REPLACE(r.object_ID,' ', '')),
  19. 6,
  20. sysdate,
  21. 'view_dienstverband'
  22. );
  23.  
  24. END LOOP;
  25. COMMIT;
  26.  
  27. END;
  28.  
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?
Mar 14 '08 #4
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.
Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE idmdirect.proc_eventlog
  2. AS
  3.    CURSOR c is SELECT * FROM idmdirect.event_process
  4.                  WHERE status != 'N' FOR UPDATE;
  5. BEGIN 
  6.    FOR r IN c LOOP
  7.     INSERT INTO idmdirect.event_processed
  8.     VALUES
  9.     (
  10.       r.record_id,
  11.       r.table_key,
  12.       r.status,
  13.       r.event_type, 
  14.       r.event_time,
  15.       r.perpetrator,
  16.       r.table_name,
  17.       r.column_name,
  18.       r.old_value,
  19.       r.new_value
  20.     );
  21.     DELETE FROM idmdirect.event_process
  22.       WHERE CURRENT OF c;
  23.    END LOOP;
  24.    COMMIT;
  25.  
  26. --  EXECUTE IMMEDIATE 'ALTER INDEX indirect.idx_indirectlog_1 REBUILD ONLINE';
  27. --  EXECUTE IMMEDIATE 'ALTER INDEX indirect.idx_indirectlog_2 REBUILD ONLINE';
  28. --  EXECUTE IMMEDIATE 'ALTER INDEX indirect.idx_indirectlog_3 REBUILD ONLINE';
  29.  
  30. END proc_eventlog;
  31.  
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.
Mar 14 '08 #5
amitpatel66
2,367 Expert 2GB
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.
Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE idmdirect.proc_eventlog
  2. AS
  3. CURSOR c is SELECT * FROM idmdirect.event_process
  4. WHERE status != 'N' FOR UPDATE;
  5. BEGIN 
  6. FOR r IN c LOOP
  7. INSERT INTO idmdirect.event_processed
  8. VALUES
  9. (
  10. r.record_id,
  11. r.table_key,
  12. r.status,
  13. r.event_type, 
  14. r.event_time,
  15. r.perpetrator,
  16. r.table_name,
  17. r.column_name,
  18. r.old_value,
  19. r.new_value
  20. );
  21. DELETE FROM idmdirect.event_process
  22. WHERE CURRENT OF c;
  23. END LOOP;
  24. COMMIT;
  25.  
  26. -- EXECUTE IMMEDIATE 'ALTER INDEX indirect.idx_indirectlog_1 REBUILD ONLINE';
  27. -- EXECUTE IMMEDIATE 'ALTER INDEX indirect.idx_indirectlog_2 REBUILD ONLINE';
  28. -- EXECUTE IMMEDIATE 'ALTER INDEX indirect.idx_indirectlog_3 REBUILD ONLINE';
  29.  
  30. END proc_eventlog;
  31.  
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 ??
Mar 14 '08 #6
Quite Surprising to why that happened?
Your procedure looks fine.
Anyways, invalid syntax at line no ??
Very awkward.... it really is the CURSOR-line...

Expand|Select|Wrap|Line Numbers
  1. CURSOR cursor1 IS SELECT * FROM prig_own.dpic300;
  2.  
When I run this in toad, I still get a ORA-00900, invalid syntax. Just the SELECT-statement works fine :(
Mar 14 '08 #7
amitpatel66
2,367 Expert 2GB
Very awkward.... it really is the CURSOR-line...

Expand|Select|Wrap|Line Numbers
  1. CURSOR cursor1 IS SELECT * FROM prig_own.dpic300;
  2.  
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?
Mar 14 '08 #8
Which version of oracle yo uare running?
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM v$version
  2. WHERE banner LIKE 'Oracle%';
Oracle9i Release 9.2.0.1.0
Mar 14 '08 #9
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM v$version
  2. 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:
Expand|Select|Wrap|Line Numbers
  1. BEGIN 
  2.   IDMDIRECT.PROC_FULLSYNC;
  3.   COMMIT; 
  4. END; 
  5.  
2) And my user didn't have permissions on the tablespace
Expand|Select|Wrap|Line Numbers
  1. ALTER USER username QUOTA UNLIMITED on tablespace;
  2.  
My eventtable is full and waiting full-sync! I'm happy :-)
Big thx for the help!
Mar 14 '08 #10
amitpatel66
2,367 Expert 2GB
Both are same.:

1. EXECUTE proc_name
2. BEGIN proc_name; END;

Check This:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> select * from emp;
  3. ENAME                     EMPNO
  4. -------------------- ----------
  5. a                             1
  6. am                            2
  7.  
  8. SQL> create or replace procedure a IS
  9.   2  BEGIN
  10.   3  INSERT INTO emp as SELECT * FROM EMP;
  11.   4  COMMIT;
  12.   5  END;
  13.   6  /
  14.  
  15.  
  16. Procedure created.
  17.  
  18. SQL> EXECUTE a
  19.  
  20. PL/SQL procedure successfully completed.
  21.  
  22. SQL> select * from emp;
  23. ENAME                     EMPNO
  24. -------------------- ----------
  25. a                             1
  26. am                            2
  27. a                             1
  28. am                            2
  29.  
  30.  SQL> BEGIN a; END;
  31.   2  /
  32.  
  33. PL/SQL procedure successfully completed.
  34.  
  35. SQL> select * from emp;
  36.  
  37. ENAME                     EMPNO
  38. -------------------- ----------
  39. a                             1
  40. am                            2
  41. a                             1
  42. am                            2
  43. a                             1
  44. am                            2
  45. a                             1
  46. am                            2
  47.  
  48. 8 rows selected.
  49.  
  50. SQL> 
  51.  
  52.  
If you see in above example, I have used both the ways of calling procedure and it works fine.
Mar 14 '08 #11
Both are same.:

1. EXECUTE proc_name
2. BEGIN proc_name; END;

Check This:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> select * from emp;
  3. ENAME                     EMPNO
  4. -------------------- ----------
  5. a                             1
  6. am                            2
  7.  
  8. SQL> create or replace procedure a IS
  9.   2  BEGIN
  10.   3  INSERT INTO emp as SELECT * FROM EMP;
  11.   4  COMMIT;
  12.   5  END;
  13.   6  /
  14.  
  15.  
  16. Procedure created.
  17.  
  18. SQL> EXECUTE a
  19.  
  20. PL/SQL procedure successfully completed.
  21.  
  22. SQL> select * from emp;
  23. ENAME                     EMPNO
  24. -------------------- ----------
  25. a                             1
  26. am                            2
  27. a                             1
  28. am                            2
  29.  
  30.  SQL> BEGIN a; END;
  31.   2  /
  32.  
  33. PL/SQL procedure successfully completed.
  34.  
  35. SQL> select * from emp;
  36.  
  37. ENAME                     EMPNO
  38. -------------------- ----------
  39. a                             1
  40. am                            2
  41. a                             1
  42. am                            2
  43. a                             1
  44. am                            2
  45. a                             1
  46. am                            2
  47.  
  48. 8 rows selected.
  49.  
  50. SQL> 
  51.  
  52.  
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!
Mar 14 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Etayki | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.