Home » Developer & Programmer » Precompilers, OCI & OCCI » Pro*C - Table or view doesn't exist error... (Unix,Oracle 10g)
Pro*C - Table or view doesn't exist error... [message #319958] Tue, 13 May 2008 08:52 Go to next message
xyzt
Messages: 27
Registered: April 2008
Junior Member
Hello,

I'm trying to learn how to use Pro*C so, i try to apply the sample in this page:


I changed the USERNAME and PASSWORD in the code according to my system. I created the executable file successfully. when I run it, it asks me the Employee number, and when I enter it I get this error:
ORACLE error--

ORA-00942: table or view does not exist


I have EMP table in the system, when I run SELECT * FROM EMP in TOAD, I get the results. so , what can be the problem?
thanks in advance.

here's my code, if it helps:
/*sample1.pc
 *
 *  Prompts the user for an employee number,
 *  then queries the emp table for the employee's
 *  name, salary and commission.  Uses indicator
 *  variables (in an indicator struct) to determine
 *  if the commission is NULL.
 *
 */

#include <stdio.h>
#include <string.h>


/* Define constants for VARCHAR lengths. */
#define     UNAME_LEN      20
#define     PWD_LEN        40

/* Declare variables.No declare section is needed if MODE=ORACLE.*/
VARCHAR     username[UNAME_LEN];
/* VARCHAR is an Oracle-supplied struct */
varchar     password[PWD_LEN];
/* varchar can be in lower case also. */
/*
Define a host structure for the output values of a SELECT statement.
*/
struct {
    VARCHAR   emp_name[UNAME_LEN];
    float     salary;
    float     commission;
} emprec;
/*
Define an indicator struct to correspond to the host output struct. */
struct
{
    short     emp_name_ind;
    short     sal_ind;
    short     comm_ind;
} emprec_ind;

/*  Input host variable. */
int         emp_number;
int         total_queried;
/* Include the SQL Communications Area.
   You can use #include or EXEC SQL INCLUDE. */
#include <sqlca.h>

/* Declare error handling function. */
void sql_error();

main()
{
    char temp_char[32];

/* Connect to ORACLE--
 * Copy the username into the VARCHAR.
 */
    strncpy((char *) username.arr, "SYSADM", UNAME_LEN);
/* Set the length component of the VARCHAR. */
    username.len = strlen((char *) username.arr);
/* Copy the password. */
    strncpy((char *) password.arr, "SYSADM", PWD_LEN);
    password.len = strlen((char *) password.arr);
/* Register sql_error() as the error handler. */
    EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");

/* Connect to ORACLE.  Program will call sql_error()
 * if an error occurs when connecting to the default database.
 */
    EXEC SQL CONNECT :username IDENTIFIED BY :password;
    printf("\nConnected to ORACLE as user: %s\n", username.arr);
/* Loop, selecting individual employee's results */
    total_queried = 0;
    for (;;)
    {
/* Break out of the inner loop when a
 * 1403 ("No data found") condition occurs.
 */
        EXEC SQL WHENEVER NOT FOUND DO break;
        for (;;)
        {
            emp_number = 0;
            printf("\nEnter employee number (0 to quit): ");
            gets(temp_char);
            emp_number = atoi(temp_char);
            if (emp_number == 0)
                break;
            printf("\nexecuting the query...\n");
            EXEC SQL SELECT ename, sal, NVL(comm, 0)
                INTO :emprec INDICATOR :emprec_ind
                FROM EMP
                WHERE EMPNO = :emp_number;
/* Print data. */
            printf("\n\nEmployee\tSalary\t\tCommission\n");
            printf("--------\t------\t\t----------\n");
/* Null-terminate the output string data. */
            emprec.emp_name.arr[emprec.emp_name.len] = '\0';
            printf("%-8s\t%6.2f\t\t",
                emprec.emp_name.arr, emprec.salary);
            if (emprec_ind.comm_ind == -1)
                printf("NULL\n");
            else
                printf("%6.2f\n", emprec.commission);

            total_queried++;
        }  /* end inner for (;;) */
        if (emp_number == 0) break;
        printf("\nNot a valid employee number - try again.\n");
    } /* end outer for(;;) */

    printf("\n\nTotal rows returned was %d.\n", total_queried);
    printf("\nG'day.\n\n\n");
/* Disconnect from ORACLE. */
    EXEC SQL COMMIT WORK RELEASE;
    exit(0);
}
void sql_error(msg)
char *msg;
{
    char err_msg[128];
    int buf_len, msg_len;

    EXEC SQL WHENEVER SQLERROR CONTINUE;
    printf("\n%s\n", msg);
    buf_len = sizeof (err_msg);
    sqlglm(err_msg, &buf_len, &msg_len);
    if (msg_len > buf_len)
    msg_len = buf_len;
    printf("%.*s\n", msg_len, err_msg);
    EXEC SQL ROLLBACK RELEASE;
    exit(1);
}
Re: Pro*C - Table or view doesn't exist error... [message #319963 is a reply to message #319958] Tue, 13 May 2008 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
when I run SELECT * FROM EMP in TOAD, I get the result

Are you using the same user to connect?
Does SYSADM have an EMP table?

Regards
Michel
Re: Pro*C - Table or view doesn't exist error... [message #319968 is a reply to message #319963] Tue, 13 May 2008 09:13 Go to previous messageGo to next message
xyzt
Messages: 27
Registered: April 2008
Junior Member
Yes, I connect to the Oracle using TOAD with the same username and password and when I run the query it executes successfully.
Re: Pro*C - Table or view doesn't exist error... [message #319978 is a reply to message #319968] Tue, 13 May 2008 10:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste the whole session including the connection.
Copy and paste the execution of your program as well.

But before read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines do not exceed 80 characters when you format.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Pro*C - Table or view doesn't exist error... [message #320249 is a reply to message #319958] Wed, 14 May 2008 07:27 Go to previous message
xyzt
Messages: 27
Registered: April 2008
Junior Member
it's solved...I changed the ORACLE_SID env variable's value, now it's ok.
Previous Topic: OCI, returning into.
Next Topic: pro*c compile error:(related to JOIN)
Goto Forum:
  


Current Time: Thu Mar 28 04:25:36 CDT 2024