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

Hi,

Thank you very much for your attention. :-)

But, orcle and ProC*C++ still very obscure to me. There are a bunch of question on me...See below...(Comments are embedded..)

> > ////
> >
> > 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.

Maybe you're right but this way looks very easy for me. So I still insist on using that form. I guess maybe the communication betwenn pl/sql and C++ application is very limited. I want to know if this is possible or not. If possible how?...:-)

> >
> > 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)

Thanks for the pointing. I've read it carefully but it's not what I want. That's exactly the same way I do to use oracle now. But I want to use pl/sql more for advanced feature and portability in future.

> >
> > 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.

Sorry but I can't catch what you mean. :-(  

> Regards,
> 
> Sybrand Bakker, Senior Oracle DBA

So I'm filling that still the 4 problem didn't solved or answered yet.

Can you give me a little more specific and detailed information?

Thanks in advance.

Regards,
 Ryan Received on Thu Sep 06 2001 - 03:25:28 CEST