calling stored proc. from Pro-C

[mahesh_575]

Hi, I am working in a project of Pro-C. I want to use cursors and stored procedures in the C program. Infact I am using those but I am trying to call a stored procedure from C program and I don`t know how to call it ? Can anyone help me please !

[jim mcnamara]

I code Pro-C almost every day.

Stored procedures in Oracle are in PL/SQL. The only good way to invoke them is to make them a trigger for update of a field, or on Insert, and so on.

There is no EXEC SQL EXECUTE statment in Pro-C that will run PL/SQL code blocks. It is reserved for dynamic SQL - EXEC SQL PREPARE, then EXEC SQL EXCUTE mycmd;

From SQLPLUS you can use the EXECUTE statment to run PL/SQL.
I've tried setting mycmd to "EXECUTE mycode;" in dynamic SQL but it doesn't work.

For cursors:
EXEC SQL DECLARE
CHAR20 szstuff;
CHAR20 szmorestuff;
short ind1;
short ind2;
EXE SQL END DECLARE;
EXEC SQL DECLARE mycursor for
SELECT stuf, morestuff from mytable, yourtable
where mystuff = yourstuff;
EXEC SQL OPEN mycursor;
/* check errors in sqlerr *
EXEC SQL fetch mycursor into :szstuff:ind1, :szmorestuff:ind2;
/* check for rows returned */
EXEC SQL CLOSE mycursor;
/* check sqlerr again */
Process the returned values, if rows returned.

[mahesh_575]

Thank you Jim

I have tried it for cursor, it is working fine but problem is still lying there regarding Stored Procedure. I have been given project specs. and it is clearly mentioned in it, that stored procedure should be called from C program. You have said that EXEC SQL EXECUTE won`t work. isn`t it ? then is there any alternative for it ? if yes then please guide me. Waiting for your reply.

[jim mcnamara]

5 – 9 Using Embedded PL/SQL
main()
{
char trans_type;
/* printf(”Username? ”);
gets(username);
printf(”Password? ”);
gets(password);
*/
strcpy(password, ”TIGER”);
strcpy(username, ”SCOTT”);
EXEC SQL WHENEVER SQLERROR DO sql_error();
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf(”Connected to Oracle\n”);
for (;;)
{
printf(”Account Number (0 to end)? ”);
gets(temp);
acct_num = atoi(temp);
if(acct_num == 0)
{
EXEC SQL COMMIT WORK RELEASE;
printf(”Exiting program\n”);
break;
}
printf(”Transaction Type – D)ebit or C)redit? ”);
gets(temp);
trans_type = temp[0];
printf(”Transaction Amount? ”);
gets(temp);
trans_amt = atof(temp);
/*––––––––––––––––– begin PL/SQL block –––––––––––––––––––*/
EXEC SQL EXECUTE
DECLARE
old_bal NUMBER(9,2);
err_msg CHAR(70);
nonexistent EXCEPTION;
5 – 10 Programmer’s Guide to the Oracle Pro*C/C++ Precompiler
BEGIN
:trans_type := UPPER(:trans_type);
IF :trans_type = ’C’ THEN –– credit the account
UPDATE accts SET bal = bal + :trans_amt
WHERE acctid = :acct_num;
IF SQL%ROWCOUNT = 0 THEN –– no rows affected
RAISE nonexistent;
ELSE
:status := ’Credit applied’;
END IF;
ELSIF :trans_type = ’D’ THEN –– debit the account
SELECT bal INTO old_bal FROM accts
WHERE acctid = :acct_num;
IF old_bal >= :trans_amt THEN –– enough funds
UPDATE accts SET bal = bal – :trans_amt
WHERE acctid = :acct_num;
:status := ’Debit applied’;
ELSE
:status := ’Insufficient funds’;
END IF;
ELSE
:status := ’Invalid type: ’ || :trans_type;
END IF;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND OR nonexistent THEN
:status := ’Nonexistent account’;
WHEN OTHERS THEN
err_msg := SUBSTR(SQLERRM, 1, 70);
:status := ’Error: ’ || err_msg;
END;
END–EXEC;
/*––––––––––––––––– end PL/SQL block ––––––––––––––––––––––– */
printf(”\nStatus: %s\n”, status);
}
exit(0);
}
void
sql_error()
{
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
printf(”Processing error\n”);
exit(1);
}