Re: [PL/SQL Question]How can I do this?

Comments and answers embedded

bajy_at_intizen.com (Ryan Park) wrote in message news:<5016f0aa.0109041935.433b15c5_at_posting.google.com>...
> Hi,
>
> (NOTE: I'm totally a newbie to Pro*C/C++. This is my first 4th day of
> working on oracle. So if you have any advice to me don't hesitat to
> reply to this posting. Any words would be grateful. :-) )
>
> See below pseudo-code first.
>
> ////
>
> sprintf(l_szCmd, "Select.....where a.pk = '%s'....", input_parameter);
>
> EXEC SQL EXECUTE
> DECLARE CMD1 STATEMENT;
> PREPARE CMD1 FROM :l_szCmd;
> DECLARE csFMLY CURSOR FOR CMD1;
> DECLARE ttt csFMLY%ROWTYPE;
> begin
> OPEN csFMLY;
> FETCH csFMLY into ttt;
> for a in csFMLY LOOP
> ttt := a;
> dbms_output.put_line(ttt.a);
> END LOOP;
> close csFMLY;
> end;
> end-EXEC;
> ////
>
> This generates compile errors.
>
> It goes:"
> REPARE CMD1 FROM :l_szCmd;
> .....................1
> PLS-S-00103, Encountered the symbol "FROM" when expecting one of the
> following:
>
> := . ( _at_ % ; not null range renames default character
>
> Semantic error at line 243, column 3, file InsaKirok_publish.pc:
> DECLARE CMD1 STATEMENT;
> "
>
> What I want to do is selecting..fetching selected data...retrive data
> to C++ appl and return to client.

You can do that without using PL/SQL blocks. PL/SQL is the procedural extension to SQL. There is no need to use PL/SQL blocks for every select.

>
> And my questions are ..
>
> 1. Why I can not use "PREPARE CMD1 FROM :l_szCmd;" (for dynamical sql
> statement generation ) in EXEC SQL EXECUTE ~ END-EXEC phrase? Why
> compile errors occur?

That's just the way it works. You are trying to mix pl/sql and sql in pro*c.

At this link you can find how it works.
http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/appdev.817/a76942/pc_03dbc.htm#6172

An example is pasted below

The following example shows Method 3:

EXEC SQL AT :db_name DECLARE sql_stmt STATEMENT; EXEC SQL PREPARE sql_stmt FROM :sql_string; EXEC SQL DECLARE emp_cursor CURSOR FOR sql_stmt; EXEC SQL OPEN emp_cursor ...
EXEC SQL FETCH emp_cursor INTO ...
EXEC SQL CLOSE emp_cursor;

(The at :db_name is optional)

>
> 2. If this error can be fixed, how can I do that?
>
> 3. I want to refer the fetched data (in this cod:ttt) in my C++
> application.
>
> How can I do that?
>
> 4. I've saw dbms_output() API from other's posting. But this prints
> nothing to my console. How can I use this?
>

Did you enable serveroutput.
Call dbms_output.enable(n) where is the maximum number of bytes, before using a put_line call.

> Plz help. Thank a million.
>
> Regards,
> Ryan

Regards,

[Quoted] Sybrand Bakker, Senior Oracle DBA Received on Wed Sep 05 2001 - 14:47:48 CEST