Re: Newbie questions

Peter Morris <morrispj_at_lineone.net> wrote in message news:8caceq$hbo$1_at_taliesin2.netcom.net.uk...
> Hello ORACLE experts.
>
> We are just about to migrate a database from
> Informix to ORACLE, and I need some advice
> on the following points.
>
> Question 1)
> I have installed T.O.A.D. on my PC, and
> tried to run it. Whenever I try to establish a
> connection to a database, I get back the message:
> "could not resolve service name" Nobody here
> knows what that means.
>
> Also, when I click on the SQL*Net Help button
> it displays :
>
> Oracle root
> |
> |---Has no Oracle_Home key
> |---Oracle Root is NOT valid
>
> How should these be set?
>
>
> Question 2)
> Can anyone give me information on Dynamic PL/SQL.
> I have seen only one example, and I don't understand the
> syntax.
>
>
> Question 3)
> I need to migrate some Stored Procedures which select
> multiple rows from the database, and return them to
> the calling program. How do I do this? I have seen
> no examples of such. I have seen examples of multiple
> rows selected an written to the screen using DBMS_OUTPUT.
> I have also seen examples of single values returned as
> the RETURN value of a function, or in OUT parameters,
> but no examples of what I want.
>
>
>
>
>
>
>
>
>
>

Question 1 Your questions make me think you didn't install sqlnet. TOAD needs sqlnet. If sqlnet would have been installed the ORACLE_HOME problem wouldn't have occurred.
As a minimum you need sqlnet and the required support files (will be installed automatically when you select sqlnet), however I advise to install a full Oracle client (about 40 Mb)
The syntax to connect is username/password_at_service. The service designator needs to be present in a sqlnet config file called tnsnames.ora
You can use net easy config to generate a correct tnsnames.ora.

2)
Depends whether you are referring to Oracle 7.3,8, or 8i. In Oracle 7.3 and 8 the dbms_sql package is available for this purpose. A very generic example is
procedure simp is
cur_handle integer;
res integer;
sqlstr varchar2(2000);
begin
sqlstr := 'truncate table emp';
cur_handle := dbms_sql.open_cursor;
dbms_sql.parse(cur_handle, sqlstr, dbms_sql.native); dbms_sql.close_cursor(cur_handle);
end;

Actually I think you should get hold of the book by Steve Feuerstein on PL/SQL programming, this contains many pages about this package

Question 3)
You'll need a REF CURSOR.
Refer to http://osi.oracle.com/~tkyte/ResultSets/index.html

Hth,

Sybrand Bakker, Oracle DBA Received on Mon Apr 03 2000 - 11:41:11 CDT