Using PL/SQL in Free Hand SQL

general discussion
1

Has anyone successfully executed a stored procedure that inserted/updated a table and then selected rows from that table “Using Free Hand SQL”

Essentially two commands:

  1. Execute PL/SQL (inserts/updates table) 2. Select from table

As a test we did a

BEGIN

insert into apa_load.pom_mgt
(property_no,doi_no)
(select distinct pr_pr_no,‘00001’
from upstream.rvtpr
where pr_pr_no = ‘01277901’);
commit;

END;

select * from apa_load.pom_mgt;

This gave us an error. When we removed the “select * from apa_load.pom_mgt;”–it Inserted rows successfully, but we got the message “No Data to Fetch”.

Does this mean Oracle’s “Free Hand SQL” can’t handle multiple commands like MS SQL*Server or Sybase.

Any ideas.

Listserv Archives (BOB member since 2002-06-25)

2

Hi, Hector.

Your last statement is on the right track, but not on target. BusinessObjects Free-Hand SQL does not support multiple statements.

When you removed the second select statement, the “No Data to Fetch” window appeared because BusinessObjects looks for results in the database buffer no matter what command you issue from this screen. You got the message because the results of your query are written to a table and not the buffer.

Just separate your commands, issue one at a time, and you should be fine.

Cheers,
Luis Gonzalez

Listserv Archives (BOB member since 2002-06-25)