ProC (or ProC/C++) is Oracle's embedded SQL programming language that
allows you to write SQL queries inside C or C++ programs. Here's a
structured learning path from basic to advanced topics:
1.Basic SQL Operations in Pro*C
SELECT, INSERT, UPDATE, DELETE in Pro*C
Using host variables,Handling NULL values
2. Advanced SQL Features in Pro*C
Cursors in Pro*C,Implicit vs. Explicit cursors,Cursor loops,Fetching multiple
rows
3.Dynamic SQL in Pro*C
EXECUTE IMMEDIATE
Using PREPARE and EXECUTE
Bind variables
4.Error Handling
SQLCA (SQL Communication Area)
SQLCODE & SQLSTATE
WHENEVER SQLERROR handling
5.PL/SQL Blocks in Pro*C
Calling stored procedures & functions
Using PL/SQL anonymous blocks
6. Performance Optimization
Bulk Processing
Bulk Fetch & Bulk Insert
Array Processing
Using Bind Variables Efficiently
Optimizing Cursor Usage
7. Advanced Pro*C Concepts
Multi-threading with Pro*C
Using File I/O in Pro*C
Using Pipes & Interprocess Communication (IPC)
8.Working with Large Objects (LOBs)
9.Handling Transactions
COMMIT and ROLLBACK
Savepoints
---
5. Debugging & Troubleshooting
Using Debug Mode in Pro*C
Logging SQL Execution
Common Errors & Fixes
---
Next Steps
If you're serious about mastering Pro*C, consider working on real-world
projects like:
Writing a Pro*C program to fetch & update bulk data
Developing a report generation tool using Pro*C
Creating a Pro*C-based ETL job
I'll break down each section with a detailed explanation and examples to
ensure clarity.
---
1. Basics of Pro*C
1.1 Writing a Simple Pro*C Program
This program connects to an Oracle database, retrieves a single record, and
displays it.
#include <stdio.h>
#include <stdlib.h>
#include <sqlca.h>
EXEC SQL BEGIN DECLARE SECTION;
char username[20] = "scott";
char password[20] = "tiger";
char emp_name[50];
int emp_id;
EXEC SQL END DECLARE SECTION;
int main() {
// Connect to Oracle database
EXEC SQL CONNECT :username IDENTIFIED BY :password;
if (sqlca.sqlcode < 0) {
printf("Connection failed: %d\n", sqlca.sqlcode);
exit(1);
printf("Connected successfully!\n");
// Fetch a single employee record
EXEC SQL SELECT empno, ename INTO :emp_id, :emp_name FROM emp
WHERE ROWNUM = 1;
if (sqlca.sqlcode == 0)
printf("Employee ID: %d, Name: %s\n", emp_id, emp_name);
else
printf("No record found!\n");
// Commit and disconnect
EXEC SQL COMMIT WORK;
EXEC SQL DISCONNECT;
return 0;
Explanation:
1. Declares host variables for username, password, and employee details.
2. Connects to the Oracle database using EXEC SQL CONNECT.
3. Runs an SQL query to fetch an employee record into host variables.
4. Checks for SQL errors using sqlca.sqlcode.
5. Commits the transaction and disconnects.
---
2. Advanced SQL Features in Pro*C
2.1 Using Cursors
Cursors help retrieve multiple rows from a query.
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT empno, ename FROM emp WHERE deptno = 10;
EXEC SQL OPEN emp_cursor;
while (1) {
EXEC SQL FETCH emp_cursor INTO :emp_id, :emp_name;
if (sqlca.sqlcode != 0) break;
printf("Employee ID: %d, Name: %s\n", emp_id, emp_name);
EXEC SQL CLOSE emp_cursor;
Explanation:
1. Declares a cursor to select employees from department 10.
2. Opens the cursor and fetches records in a loop.
3. Closes the cursor after retrieving all records.
---
2.2 Dynamic SQL Execution
Use dynamic SQL when the query structure changes at runtime.
char sql_stmt[100] = "SELECT ename FROM emp WHERE empno = :emp_id";
EXEC SQL PREPARE stmt FROM :sql_stmt;
EXEC SQL EXECUTE stmt USING :emp_id;
Explanation:
1. Stores the SQL query in a string.
2. Prepares the SQL statement dynamically.
3. Executes the statement using host variables.
---
2.3 Error Handling
Pro*C provides sqlca for error handling.
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL INSERT INTO emp (empno, ename) VALUES (:emp_id, :emp_name);
if (sqlca.sqlcode < 0) {
printf("Error Code: %d\n", sqlca.sqlcode);
}
Explanation:
1. WHENEVER SQLERROR CONTINUE ensures the program does not exit on
errors.
2. Inserts a record and checks for SQL errors.
Hhhh
I'll go in-depth into each Pro*C topic, providing explanations and complete
code examples.
---
1. Basics of Pro*C
1.1 Writing a Simple Pro*C Program
Program: Connect to Oracle & Retrieve Data
#include <stdio.h>
#include <stdlib.h>
#include <sqlca.h>
EXEC SQL BEGIN DECLARE SECTION;
char username[20] = "scott";
char password[20] = "tiger";
char emp_name[50];
int emp_id;
EXEC SQL END DECLARE SECTION;
int main() {
EXEC SQL CONNECT :username IDENTIFIED BY :password;
if (sqlca.sqlcode < 0) {
printf("Connection failed: %d\n", sqlca.sqlcode);
exit(1);
printf("Connected successfully!\n");
EXEC SQL SELECT empno, ename INTO :emp_id, :emp_name FROM emp
WHERE ROWNUM = 1;
if (sqlca.sqlcode == 0)
printf("Employee ID: %d, Name: %s\n", emp_id, emp_name);
else
printf("No record found!\n");
EXEC SQL COMMIT WORK;
EXEC SQL DISCONNECT;
return 0;
Explanation:
Declares variables to store the username, password, and retrieved employee
details.
Connects to Oracle using EXEC SQL CONNECT.
Runs a SQL query to fetch an employee record.
Checks sqlca.sqlcode for errors.
Commits and disconnects.
---
2. Advanced SQL Features in Pro*C
2.1 Using Cursors
Program: Fetch Multiple Records Using Cursors
#include <stdio.h>
#include <stdlib.h>
#include <sqlca.h>
EXEC SQL BEGIN DECLARE SECTION;
char username[20] = "scott";
char password[20] = "tiger";
char emp_name[50];
int emp_id;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT empno, ename FROM emp WHERE deptno = 10;
int main() {
EXEC SQL CONNECT :username IDENTIFIED BY :password;
EXEC SQL OPEN emp_cursor;
while (1) {
EXEC SQL FETCH emp_cursor INTO :emp_id, :emp_name;
if (sqlca.sqlcode != 0) break;
printf("Employee ID: %d, Name: %s\n", emp_id, emp_name);
EXEC SQL CLOSE emp_cursor;
EXEC SQL COMMIT;
EXEC SQL DISCONNECT;
return 0;
Explanation:
Declares and opens a cursor.
Fetches employee records in a loop.
Closes the cursor after fetching all rows.
---
2.2 Dynamic SQL Execution
Program: Execute Dynamic Query
#include <stdio.h>
#include <stdlib.h>
#include <sqlca.h>
EXEC SQL BEGIN DECLARE SECTION;
char sql_stmt[100];
int emp_id;
char emp_name[50];
EXEC SQL END DECLARE SECTION;
int main() {
sprintf(sql_stmt, "SELECT ename FROM emp WHERE empno = :emp_id");
EXEC SQL PREPARE stmt FROM :sql_stmt;
EXEC SQL EXECUTE stmt USING :emp_id;
printf("Employee Name: %s\n", emp_name);
return 0;
Explanation:
Stores an SQL query in a string.
Prepares and executes it dynamically.
---
2.3 Error Handling
Program: Error Handling Using SQLCA
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL INSERT INTO emp (empno, ename) VALUES (:emp_id, :emp_name);
if (sqlca.sqlcode < 0) {
printf("Error Code: %d\n", sqlca.sqlcode);
Explanation:
Uses WHENEVER SQLERROR CONTINUE to prevent abrupt exits.
Checks sqlca.sqlcode for errors.
---
2.4 Calling PL/SQL Block
Program: Execute PL/SQL in Pro*C
EXEC SQL EXECUTE
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello from PL/SQL in Pro*C!');
END;
END-EXEC;
Explanation:
Executes a PL/SQL block using EXEC SQL EXECUTE.
---
3. Performance Optimization
3.1 Bulk Fetch & Insert
Program: Fetch Multiple Records Using Bulk Processing
EXEC SQL BEGIN DECLARE SECTION;
int empnos[100];
char enames[100][50];
EXEC SQL END DECLARE SECTION;
EXEC SQL FETCH emp_cursor BULK COLLECT INTO :empnos, :enames;
Explanation:
Uses arrays to fetch multiple rows efficiently.
---
4. Advanced Pro*C Concepts
4.1 Working with LOBs (Large Objects)
Program: Fetch a BLOB (Image)
EXEC SQL DECLARE emp_photo BLOB;
EXEC SQL SELECT photo INTO :emp_photo FROM emp WHERE empno
= :emp_id;
Explanation:
Declares a BLOB and fetches an image.
---
4.2 Handling Transactions
Program: COMMIT & ROLLBACK
EXEC SQL UPDATE emp SET sal = sal + 500 WHERE empno = :emp_id;
EXEC SQL COMMIT;
Explanation:
Ensures data consistency by using COMMIT.
---
5. Debugging & Troubleshooting
5.1 Logging SQL Execution
Program: Debugging SQL Execution
printf("Executing SQL: %s\n", sql_stmt);
Explanation:
Logs SQL statements for debugging.
---
Final Summary
Would you like me to explain any topic in more detail?
I'll dive deeper into each Pro*C topic with detailed explanations, examples,
best practices, and real-world scenarios.
---
1. Basics of Pro*C
1.1 What is Pro*C?
Pro*C is a C-based embedded SQL (ESQL) programming language that allows
C programs to interact with Oracle databases efficiently. It is used for:
High-performance database operations
Transactional processing in C applications
Dynamic SQL execution
PL/SQL integration in C
---
1.2 Components of Pro*C
1.2.1 Embedded SQL
SQL statements are written inside C programs using EXEC SQL syntax.
Example:
EXEC SQL SELECT ename INTO :emp_name FROM emp WHERE empno
= :emp_id;
1.2.2 Host Variables
These are C variables used to store query results.
Example:
EXEC SQL BEGIN DECLARE SECTION;
char emp_name[50];
int emp_id;
EXEC SQL END DECLARE SECTION;
---
1.3 Compilation & Execution
To compile a Pro*C program:
1. Precompile: Convert Pro*C to C
proc iname=program.pc oname=program.c sqlcheck=semantics
2. Compile: Compile the C program
gcc program.c -o program -I$ORACLE_HOME/precomp/public -
L$ORACLE_HOME/lib -lclntsh
3. Run: Execute the program
./program
---
2. SQL Features in Pro*C
2.1 Using Cursors
Scenario: Fetch all employees from a department.
#include <stdio.h>
#include <sqlca.h>
EXEC SQL BEGIN DECLARE SECTION;
int emp_id;
char emp_name[50];
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT empno, ename FROM emp WHERE deptno = 10;
int main() {
EXEC SQL OPEN emp_cursor;
while (1) {
EXEC SQL FETCH emp_cursor INTO :emp_id, :emp_name;
if (sqlca.sqlcode != 0) break;
printf("Employee ID: %d, Name: %s\n", emp_id, emp_name);
EXEC SQL CLOSE emp_cursor;
return 0;
Best Practices:
✔ Always close the cursor
✔ Handle exceptions properly
✔ Use bulk fetching for performance
---
2.2 Using Host Arrays for Bulk Fetch
For fetching large data efficiently.
EXEC SQL BEGIN DECLARE SECTION;
int emp_ids[100];
char emp_names[100][50];
EXEC SQL END DECLARE SECTION;
EXEC SQL FETCH emp_cursor BULK COLLECT INTO :emp_ids, :emp_names;
✔ Improves performance by reducing round trips to the database.
---
3. Dynamic SQL in Pro*C
When the query structure is unknown at compile time.
3.1 Example: Dynamic SELECT
#include <stdio.h>
#include <sqlca.h>
EXEC SQL BEGIN DECLARE SECTION;
char sql_stmt[100];
int emp_id;
char emp_name[50];
EXEC SQL END DECLARE SECTION;
int main() {
sprintf(sql_stmt, "SELECT ename FROM emp WHERE empno = :emp_id");
EXEC SQL PREPARE stmt FROM :sql_stmt;
EXEC SQL EXECUTE stmt USING :emp_id;
printf("Employee Name: %s\n", emp_name);
return 0;
✔ Useful for query-driven applications where conditions change dynamically.
---
4. Error Handling in Pro*C
4.1 SQLCA Structure
sqlca is a built-in struct that captures SQL execution status.
Example:
if (sqlca.sqlcode < 0) {
printf("Error Code: %d, Message: %s\n", sqlca.sqlcode,
sqlca.sqlerrm.sqlerrmc);
✔ Always check sqlca.sqlcode for error handling.
✔ Use WHENEVER SQLERROR to define global error-handling logic.
---
4.2 Exception Handling Using WHENEVER
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL INSERT INTO emp (empno, ename) VALUES (:emp_id, :emp_name);
if (sqlca.sqlcode < 0) {
printf("Insertion Failed! Error Code: %d\n", sqlca.sqlcode);
}
✔ Use WHENEVER SQLERROR EXIT for critical failures.
---
5. Calling PL/SQL from Pro*C
Pro*C allows direct execution of PL/SQL blocks.
5.1 Example: Calling PL/SQL from Pro*C
EXEC SQL EXECUTE
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello from PL/SQL in Pro*C!');
END;
END-EXEC;
✔ Useful for complex logic requiring stored procedures.
---
6. Transactions in Pro*C
6.1 COMMIT & ROLLBACK
Transactions ensure data consistency.
Example: Updating Employee Salary with COMMIT
EXEC SQL UPDATE emp SET sal = sal + 500 WHERE empno = :emp_id;
EXEC SQL COMMIT;
✔ Ensures data is permanently saved.
---
6.2 Using ROLLBACK
EXEC SQL ROLLBACK;
✔ Used when an error occurs and changes must be undone.
---
7. Working with LOBs (Large Objects)
LOBs store large data like images, PDFs, and documents.
7.1 Fetching a BLOB (Image)
EXEC SQL DECLARE emp_photo BLOB;
EXEC SQL SELECT photo INTO :emp_photo FROM emp WHERE empno
= :emp_id;
✔ Use BLOBs for storing multimedia data.
---
8. Performance Optimization
8.1 Optimizing Queries in Pro*C
✔ Use bind variables to prevent SQL injection
✔ Use bulk processing for large data sets
✔ Use EXPLAIN PLAN to check query performance
---
9. Debugging in Pro*C
9.1 Debugging SQL Execution
printf("Executing SQL: %s\n", sql_stmt);
✔ Logs the executed SQL statement.
---
9.2 Using Pro*C Tracing
Enable tracing for detailed execution logs:
export PROC_TRACE=1
./program
✔ Helps diagnose performance issues.
---
10. Real-World Scenarios
Scenario 1: Batch Processing with Pro*C
Use Pro*C to process millions of records efficiently.
EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT empno, sal FROM
emp;
EXEC SQL OPEN emp_cursor;
while (1) {
EXEC SQL FETCH emp_cursor BULK COLLECT
INTO :emp_ids, :emp_salaries;
if (sqlca.sqlcode != 0) break;
for (int i = 0; i < 100; i++) {
emp_salaries[i] += 500;
EXEC SQL UPDATE emp SET sal = :emp_salaries WHERE empno
= :emp_ids;
EXEC SQL COMMIT;
EXEC SQL CLOSE emp_cursor;
✔ Optimized for large-scale data updates.
---
Final Summary
---
What Next?
✅ Try these examples on an Oracle database
✅ Experiment with performance tuning techniques
✅ Learn advanced Pro*C tuning for enterprise applications
Would you like additional explanations on any topic?
---
2.4 Calling PL/SQL Block
Pro*C allows executing PL/SQL code.
EXEC SQL EXECUTE
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello from PL/SQL in Pro*C!');
END;
END-EXEC;
Explanation:
1. Executes a PL/SQL block from Pro*C.
2. Uses DBMS_OUTPUT.PUT_LINE to print a message.
---
3. Performance Optimization
3.1 Bulk Fetch & Insert
Fetching multiple rows at once improves performance.
EXEC SQL BEGIN DECLARE SECTION;
int empnos[100];
char enames[100][50];
EXEC SQL END DECLARE SECTION;
EXEC SQL FETCH emp_cursor BULK COLLECT INTO :empnos, :enames;
Explanation:
1. Uses arrays to fetch multiple rows at once.
2. Improves performance compared to row-by-row fetching.
---
4. Advanced Pro*C Concepts
4.1 Working with LOBs (Large Objects)
LOBs handle large data such as images.
EXEC SQL DECLARE emp_photo BLOB;
EXEC SQL SELECT photo INTO :emp_photo FROM emp WHERE empno
= :emp_id;
Explanation:
1. Declares a BLOB (Binary Large Object).
2. Fetches an employee's photo from the database.
---
4.2 Handling Transactions
Transactions ensure data consistency.
EXEC SQL UPDATE emp SET sal = sal + 500 WHERE empno = :emp_id;
EXEC SQL COMMIT;
Explanation:
1. Updates an employee's salary.
2. Commits the transaction.
---
5. Debugging & Troubleshooting
5.1 Logging SQL Execution
Prints SQL statements for debugging.
printf("Executing SQL: %s\n", sql_stmt);
---
Summary of Topics Covered:
✅ Basic Pro*C program
✅ Cursors
✅ Dynamic SQL
✅ Error Handling
✅ PL/SQL Execution
✅ Bulk Processing
✅ Working with LOBs
✅ Transactions
✅ Debugging
Would you like any specific topic explained further?
Here are Pro*C interview questions categorized by difficulty level, along with
sample answers.
---
Basic Pro*C Interview Questions
1. What is Pro*C?
Answer:
Pro*C is an embedded SQL programming language that allows C programs to
interact with Oracle databases. It enables executing SQL queries, handling
transactions, and calling PL/SQL procedures within a C program.
---
2. What are the key components of a Pro*C program?
Answer:
1. Embedded SQL Statements – SQL inside C using EXEC SQL.
2. Host Variables – C variables that store query results.
3. Cursors – Used for fetching multiple rows.
4. Error Handling – Using sqlca or WHENEVER SQLERROR.
5. Transaction Control – COMMIT and ROLLBACK.
---
3. How do you declare host variables in Pro*C?
Answer:
Host variables are declared inside a DECLARE SECTION block.
EXEC SQL BEGIN DECLARE SECTION;
char emp_name[50];
int emp_id;
EXEC SQL END DECLARE SECTION;
These variables are used to store query results.
---
4. How do you connect to an Oracle database in Pro*C?
Answer:
Using the CONNECT statement:
EXEC SQL CONNECT :username IDENTIFIED BY :password;
If sqlca.sqlcode is negative, the connection failed.
---
5. How do you compile a Pro*C program?
Answer:
1. Precompile Pro*C code to C:
proc iname=program.pc oname=program.c sqlcheck=semantics
2. Compile using GCC:
gcc program.c -o program -I$ORACLE_HOME/precomp/public -
L$ORACLE_HOME/lib -lclntsh
3. Run:
./program
---
Intermediate Pro*C Interview Questions
6. What is a cursor in Pro*C?
Answer:
A cursor is a pointer to a result set, allowing row-by-row processing.
Example:
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT empno, ename FROM emp WHERE deptno = 10;
EXEC SQL OPEN emp_cursor;
EXEC SQL FETCH emp_cursor INTO :emp_id, :emp_name;
EXEC SQL CLOSE emp_cursor;
---
7. How do you fetch multiple rows in Pro*C?
Answer:
Using arrays (bulk fetching):
EXEC SQL BEGIN DECLARE SECTION;
int emp_ids[100];
char emp_names[100][50];
EXEC SQL END DECLARE SECTION;
EXEC SQL FETCH emp_cursor BULK COLLECT INTO :emp_ids, :emp_names;
This reduces round trips and improves performance.
---
8. What is dynamic SQL in Pro*C?
Answer:
Dynamic SQL allows execution of queries built at runtime.
Example:
EXEC SQL PREPARE stmt FROM 'SELECT ename FROM emp WHERE empno
= :emp_id';
EXEC SQL EXECUTE stmt USING :emp_id;
This is useful when the query structure is unknown at compile time.
---
9. How do you handle errors in Pro*C?
Answer:
1. Using sqlca:
if (sqlca.sqlcode < 0) {
printf("Error Code: %d\n", sqlca.sqlcode);
2. Using WHENEVER SQLERROR:
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL INSERT INTO emp (empno, ename) VALUES (:emp_id, :emp_name);
---
10. How do you execute a PL/SQL block in Pro*C?
Answer:
Using EXEC SQL EXECUTE:
EXEC SQL EXECUTE
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello from PL/SQL in Pro*C!');
END;
END-EXEC;
---
Advanced Pro*C Interview Questions
11. How do you handle transactions in Pro*C?
Answer:
Transactions ensure data consistency.
COMMIT to save changes:
EXEC SQL COMMIT;
ROLLBACK to undo changes:
EXEC SQL ROLLBACK;
---
12. What are host arrays in Pro*C?
Answer:
Host arrays allow bulk processing to improve performance.
Example (Bulk Insert):
EXEC SQL FOR :n INSERT INTO emp (empno, ename) VALUES
(:emp_ids, :emp_names);
---
13. What is the difference between static and dynamic SQL in Pro*C?
Example of static SQL:
EXEC SQL SELECT ename INTO :emp_name FROM emp WHERE empno
= :emp_id;
Example of dynamic SQL:
EXEC SQL PREPARE stmt FROM :sql_query;
EXEC SQL EXECUTE stmt;
---
14. How do you debug a Pro*C program?
Answer:
1. Enable tracing:
export PROC_TRACE=1
./program
2. Print executed SQL:
printf("Executing SQL: %s\n", sql_stmt);
3. Use sqlca to check errors.
---
15. What is the use of sqlcode and sqlerrm in Pro*C?
Answer:
sqlca.sqlcode stores the execution status.
sqlca.sqlerrm.sqlerrmc provides the error message.
Example:
if (sqlca.sqlcode < 0) {
printf("Error: %s\n", sqlca.sqlerrm.sqlerrmc);
---
Scenario-Based Pro*C Interview Questions
16. How do you handle NULL values in Pro*C?
Answer:
Using indicator variables:
EXEC SQL BEGIN DECLARE SECTION;
char emp_name[50];
short emp_name_ind;
EXEC SQL END DECLARE SECTION;
EXEC SQL SELECT ename INTO :emp_name :emp_name_ind FROM emp
WHERE empno = :emp_id;
if (emp_name_ind == -1) {
printf("Employee Name is NULL\n");
}
✔ Indicator = -1 means NULL value.
---
17. How do you read and write LOBs in Pro*C?
Answer:
To read a BLOB (image):
EXEC SQL SELECT photo INTO :emp_photo FROM emp WHERE empno
= :emp_id;
To write a CLOB (text file):
EXEC SQL UPDATE emp SET resume = :resume_text WHERE empno
= :emp_id;
---
18. How would you process a million records in Pro*C efficiently?
Answer:
✔ Use Bulk Processing to reduce database round trips.
✔ Commit in batches to prevent rollback segment overflow.
Example:
EXEC SQL FETCH emp_cursor BULK COLLECT INTO :emp_ids, :emp_names
LIMIT 1000;
---
19. What are some best practices for Pro*C performance tuning?
✔ Use bind variables to avoid hard parsing.
✔ Use bulk fetching for large datasets.
✔ Close unused cursors to free resources.
✔ Use EXPLAIN PLAN to analyze query performance.
---
20. What are the limitations of Pro*C?
Answer:
❌ Complex debugging compared to Java or Python.
❌ Limited portability (depends on Oracle).
❌ Performance bottlenecks if not optimized properly.
---
Final Thoughts
These 20+ Pro*C interview questions cover the most important concepts,
best practices, and real-world scenarios.
Do you need more in-depth explanations or coding examples?
Here are more advanced Pro*C interview questions along with practical
scenarios and performance optimization techniques.
---
Advanced Pro*C Interview Questions
21. What is the difference between Explicit and Implicit Cursors in Pro*C?
Example of Explicit Cursor:
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT empno, ename FROM emp WHERE deptno = 10;
EXEC SQL OPEN emp_cursor;
EXEC SQL FETCH emp_cursor INTO :emp_id, :emp_name;
EXEC SQL CLOSE emp_cursor;
---
22. What are the different types of Dynamic SQL in Pro*C?
Answer:
1. Simple Dynamic SQL – Uses PREPARE and EXECUTE
2. Dynamic SQL with Input Parameters – Uses USING
3. Dynamic SQL with Output Parameters – Uses INTO
4. Fully Dynamic SQL – Uses EXECUTE IMMEDIATE
Example:
EXEC SQL PREPARE stmt FROM 'SELECT ename FROM emp WHERE empno
= :emp_id';
EXEC SQL EXECUTE stmt USING :emp_id;
---
23. How do you use Indicator Variables in Pro*C?
Answer:
Indicator variables handle NULL values.
Example:
EXEC SQL BEGIN DECLARE SECTION;
char emp_name[50];
short emp_name_ind; // Indicator variable
EXEC SQL END DECLARE SECTION;
EXEC SQL SELECT ename INTO :emp_name :emp_name_ind FROM emp
WHERE empno = :emp_id;
if (emp_name_ind == -1) {
printf("Employee Name is NULL\n");
✔ Indicator = -1 means NULL value
✔ Indicator > 0 means data was truncated
---
24. How do you handle SELECT statements returning multiple rows?
Answer:
Use Cursors or Host Arrays.
Example using a Cursor:
EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT empno, ename FROM
emp;
EXEC SQL OPEN emp_cursor;
while (1) {
EXEC SQL FETCH emp_cursor INTO :emp_id, :emp_name;
if (sqlca.sqlcode != 0) break;
printf("Emp ID: %d, Name: %s\n", emp_id, emp_name);
EXEC SQL CLOSE emp_cursor;
Example using Host Arrays (Bulk Fetch):
EXEC SQL FETCH emp_cursor BULK COLLECT INTO :emp_ids, :emp_names;
✔ Bulk fetching improves performance significantly.
---
25. How do you call a Stored Procedure from Pro*C?
Answer:
Stored procedures can be called using EXEC SQL EXECUTE.
Example:
EXEC SQL EXECUTE
BEGIN
my_procedure(:emp_id, :emp_name);
END;
END-EXEC;
✔ Pro*C can call procedures with input/output parameters.
✔ Best for encapsulating business logic.
---
26. How do you handle concurrent transactions in Pro*C?
Answer:
Use locking mechanisms:
1. SELECT FOR UPDATE – Locks a row
2. COMMIT – Releases locks
3. ROLLBACK – Reverts changes
Example:
EXEC SQL SELECT empno, sal INTO :emp_id, :emp_sal FROM emp
WHERE empno = :input_id FOR UPDATE;
✔ Prevents dirty reads and ensures data integrity.
---
27. What are the performance bottlenecks in Pro*C, and how do you fix
them?
Answer:
| Bottleneck | Solution | |------------|----------| | Too many database calls | Use
bulk processing | | Slow queries | Use EXPLAIN PLAN | | High memory usage |
Optimize cursor handling | | Locking issues | Use ROWLOCK hints |
Example: Optimizing a slow Pro*C query
EXEC SQL EXPLAIN PLAN FOR
SELECT ename FROM emp WHERE empno = :emp_id;
Then analyze the execution plan for index usage.
---
28. What is Array Processing in Pro*C?
Answer:
✔ Used for batch inserts, updates, and deletes.
✔ Reduces database round trips.
Example:
EXEC SQL FOR :n INSERT INTO emp (empno, ename) VALUES
(:emp_ids, :emp_names);
✔ Improves performance by processing multiple rows in one SQL statement.
---
29. How do you handle Long Data Types (BLOB/CLOB) in Pro*C?
Answer:
1. Read BLOB (Image):
EXEC SQL SELECT photo INTO :emp_photo FROM emp WHERE empno
= :emp_id;
2. Write CLOB (Large Text):
EXEC SQL UPDATE emp SET resume = :resume_text WHERE empno
= :emp_id;
✔ LOBs require special handling in Pro*C.
---
30. What are the differences between Pro*C and JDBC?
✔ Pro*C is faster than JDBC for batch processing and system-level
applications.
---
Scenario-Based Pro*C Interview Questions
31. How would you process a large file (millions of records) in Pro*C?
✔ Use Bulk Inserts
✔ Use Parallel Processing
✔ Commit in Batches
Example:
while (fgets(buffer, sizeof(buffer), file)) {
EXEC SQL FOR :batch_size INSERT INTO emp (empno, ename) VALUES
(:emp_ids, :emp_names);
EXEC SQL COMMIT;
✔ Reduces rollback segment usage and improves performance.
---
32. How do you debug a Pro*C program that is failing intermittently?
✔ Enable Pro*C tracing:
export PROC_TRACE=1
./program
✔ Use SQLCA error handling
✔ Check deadlocks and locks using V$LOCK
Example:
SELECT * FROM V$LOCK WHERE block = 1;
✔ Identifies lock contention issues.
---
33. How would you optimize a Pro*C program for high concurrency?
✔ Use Bind Variables
✔ Use Connection Pooling
✔ Reduce Lock Contention
Example:
EXEC SQL SELECT empno, ename FROM emp WHERE empno = :emp_id;
✔ Prevents SQL Injection & Improves Performance.
---
34. How do you implement Error Logging in Pro*C?
✔ Log errors to a table
✔ Use sqlca.sqlcode and sqlca.sqlerrm.sqlerrmc
Example:
if (sqlca.sqlcode < 0) {
EXEC SQL INSERT INTO error_log (err_code, err_msg)
VALUES (:sqlca.sqlcode, :sqlca.sqlerrm.sqlerrmc);
✔ Captures error messages for debugging.
---
35. How do you integrate Pro*C with multithreading?
✔ Use separate database connections for each thread
✔ Avoid shared cursors between threads
Example:
pthread_create(&thread_id, NULL, process_data, (void *)thread_param);
✔ Ensures Pro*C handles concurrency safely.
---
Final Summary
Would you like more real-world scenarios or specific coding examples?
