Re: A stored procedure call in a C++ Program ?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 08 Sep 1998 16:27:27 GMT
Message-ID: <35f6591b.12461969@192.86.155.100>

A copy of this was sent to "BEY Pascal" <beyp_at_mygale.org> (if that email address didn't require changing) On Tue, 8 Sep 1998 17:32:44 +0200, you wrote:

>Hello,
>I just want to know how is the right way to call a Stored Procedure from a
>C++ Programm.
>
>I'm on an UNIX system on a SUN Machine.
>Could anyone help me to see where is the problem ?
>
>Thanks in advance !
>
>-----------------------------------
>
>Here is my syntax :
> EXEC SQL EXECUTE
> BEGIN
> www_ep_fin_sta.fs_tg_ins
> (:user,:ora_user,:shop_id, :datum_von, :datum_bis, :seq);
> END;
> END-EXEC;
>

In order to precompile STATIC references to pl/sql as you have above, you must use

userid=user/pass sqlcheck=semantics

on the pro*c precompiler line so it can look up the procedure in the database.

If you want to avoid using userid=user/pass sqlcheck=semantics (it will take longer to precompile that way) you can code instead something like the following:

void process(void )
{
EXEC SQL BEGIN DECLARE SECTION;

VARCHAR     str[512];
VARCHAR     something[512];

EXEC SQL END DECLARE SECTION;     strcpy( something.arr, "Some Data Here" );     something.len = strlen( something.arr );

    strcpy( str.arr, "begin dbms_output.put_line( :something ); end;" );     str.len = strlen( str.arr );

    EXEC SQL PREPARE S FROM :str;
    EXEC SQL EXECUTE S USING :something; }

Since it uses dynamic sql, the precompiler won't want to check the pl/sql routine in the database...

Alternatively, if you plan on calling this routine many times and want to avoid the parse/reparse/reparse it would be doing, you could code:

void process(void )
{
EXEC SQL BEGIN DECLARE SECTION;

VARCHAR        str[512];
VARCHAR        something[512];

EXEC SQL END DECLARE SECTION;
static int firstTime = 1;

    if ( firstTime )
    {

        strcpy( str.arr, "begin dbms_output.put_line( :something ); \
                                :something := 'Ok, did it'; \
                            end;" );
        str.len = strlen( str.arr );

        EXEC SQL PREPARE S FROM :str;
        EXEC SQL DECLARE C CURSOR FOR S;
        firstTime = 0;

    }

    strcpy( something.arr, "Some Data Here" );     something.len = strlen( something.arr );

    EXEC SQL OPEN C USING :something;
    printf( "%.*s\n", something.len, something.arr ); }

The cursor will be bound to the parsed statement once this way and you would just be executing the pl/sql block over and over again by 'opening' the cursor...

>but here is the pre-compiler PRO*C error !
>
> www_ep_fin_sta.fs_tg_ins
>.....1
>(1) PCC-S-02201, identifier 'WWW_EP_FIN_STA.FS_TG_INS' must be declared
>
> www_ep_fin_sta.fs_tg_ins
>.....1
>(1) PCC-S-02000, Statement ignored
>
>Semantic error at line 281, column 5, file tagestot.pc:
> BEGIN
>....1
>(1) PCC-S-02346, PL/SQL found semantic errors
>
>
>
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Sep 08 1998 - 11:27:27 CDT