Re: EXEC SQL <action> and EXEC SQL EXECUTE IMMEDIATE :<host of action>

A copy of this was sent to Alex Vinokur <alexander.vinokur_at_telrad.co.il> (if that email address didn't require changing) On Mon, 25 Oct 1999 05:48:37 GMT, you wrote:

>In article <pRATOASEIFsGIPvoGDsKuv9w50Bs_at_4ax.com>,
> tkyte_at_us.oracle.com wrote:
>> A copy of this was sent to Alex Vinokur
><alexander.vinokur_at_telrad.co.il>
>> (if that email address didn't require changing)
>> On Sun, 24 Oct 1999 10:32:21 GMT, you wrote:
>>
>> >Hi,
>> >
>> >I have got the different results of the running
>> >when using
>> > 1. EXEC SQL <action>
>> > (see test1)
>> >
>> > and
>> >
>> > 2. EXEC SQL BEGIN DECLARE SECTION;
>> > const char *host_action_line = "<action>"
>> > EXEC SQL END DECLARE SECTION;
>> >
>> > EXEC SQL EXECUTE IMMEDIATE :host_action_line;
>> >
>> >
>> > Is anything wrong?
>> >
>> > Do both EXEC SQL <action> EXEC SQL and EXECUTE IMMEDIATE
>> >:host_action_line
>> > have to produce the same result?
>> >
>>
>> They are different AND you are using them to do a select which does
>not make any
>> sense.
>>
>> You would "EXEC SQL INSERT INTO T values ...." and that would make
>sense because
>> an insert doesn't 'return' anything.
>>
>> You could:
>> strcpy( host_var, "creat table t ( x int )" );
>> exec sql execute immediate :host_var;
>>
>> and that would make sense for the same reason.
>>
>> It doesn't make 'sense' to "EXEC SQL SELECT * FROM T" -- where does
>the output
>> go?
>>
>> you would:
>>
>> EXEC SQL SELECT X INTO :my_var FROM T;
>>
>> and that would make sense.
>>
>
>
>[snip]
>===================================================================
>I dont't need any return value im my example.
>I would like only to check sqlca.sqlcode
> to know if my database contains table "KKK123".
>I don't neeed any return value except sqlca.sqlcode.
>However behavior of my function (test1 and test2) is different:
> EXEC SQL SELECT <action> causes sqlca.sqlcode to be 1403 (not found)
> EXEC SQL EXECUTE IMMEDIATE <the same action> causes sqlca.sqlcode to
>be 0 (no errors).
>
>How can we get the same result using
> EXEC SQL SELECT <action> and
> EXEC SQL EXECUTE IMMEDIATE <the same action>
>
>

As i said -- you cannot they are different. Also as I quoted:

From the Pro*C guide as well:

<quote>
Method 1

This method lets your program accept or build a dynamic SQL statement, then immediately execute it using the EXECUTE IMMEDIATE command. The SQL statement must not be a query (SELECT statement) and must not contain any placeholders for input host variables. For example, the following host strings qualify:

’DELETE FROM EMP WHERE DEPTNO = 20’ ’GRANT SELECT ON EMP TO scott’

With Method 1, the SQL statement is parsed every time it is executed. </quote>

the SQL statement must not be a query -- you do not use SELECTs with EXECUTE immediate.

Given that your goal is to discover the existence (or not) of a table, you could either code:

static int process( char * tname )
{
EXEC SQL BEGIN DECLARE SECTION;
    VARCHAR stmt[255];
EXEC SQL END DECLARE SECTION;
    int rc;

    EXEC SQL WHENEVER SQLERROR CONTINUE;     sprintf( stmt.arr, "select * from %s", tname );     stmt.len = strlen(stmt.arr);

    EXEC SQL PREPARE S FROM :stmt;
    rc = sqlca.sqlcode;

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

    return !(rc == 0);
}

main( argc, argv )
int argc;
char * argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR oracleid[50];
EXEC SQL END DECLARE SECTION;     strcpy( oracleid.arr, USERID );
    oracleid.len = strlen( oracleid.arr );     EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();     EXEC SQL CONNECT :oracleid;
    printf("\nConnected to ORACLE as user: %s\n\n", oracleid.arr);

    printf( "Emp %s exist\n", process("emp")?"does not":"does" );
    printf( "Dept %s exist\n", process("dept")?"does not":"does" );
    printf( "Foobar %s exist\n", process("Foobar")?"does not":"does" );

    /* Disconnect from ORACLE. */
    EXEC SQL COMMIT WORK RELEASE;
    exit(0);
}

which would let you use synonyms and all without lots of hassles OR if you know the owner of the table, you could:

   SELECT COUNT(*) into :mycnt FROM ALL_OBJECTS WHERE OWNER = :o and OBJECT_NAME = :n AND type = 'TABLE';

and then just see if mycnt was >0

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Oct 25 1999 - 11:52:47 CDT