Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Calling a stored procedure from Pro*C using ANSI Dynamic SQL
Hi All!
Could anybody help me with calling stored procedure with cursor as OUT parameter using ANSI Dynamic SQL (not Oracle Dynamic SQL). By other words, can I use statement EXECUTE stmt USING DESCRIPTOR :dsc; (not a EXECUTE stmt USING :curs)?
Stored procedure is simple:
CREATE OR REPLACE PACKAGE pkg AS
TYPE cursorType IS RECORD( col1 CHAR(10) );
TYPE cursorT IS REF CURSOR RETURN cursorType;
END pkg;
/
CREATE OR REPLACE PROCEDURE p (
curs IN OUT pkg.cursorT
)
AS
BEGIN
OPEN curs FOR SELECT char_1 FROM table1;
END p;
/
Pro*C code is like:
EXEC SQL BEGIN DECLARE SECTION;
char s[256];
SQL_CURSOR curs;
char str[256];
int char_type=1;
int char_len=256;
EXEC SQL END DECLARE SECTION;
strcpy(s,"begin p(:v); end;"
EXEC SQL PREPARE stmt FROM :s;
EXEC SQL ALLOCATE DESCRIPTOR 'in';
EXEC SQL ALLOCATE DESCRIPTOR 'out';
EXEC SQL ALLOCATE :curs;
EXEC SQL DESCRIBE INPUT stmt USING DESCRIPTOR 'in';
EXEC SQL SET DESCRIPTOR 'in' VALUE 1 DATA=:curs, TYPE= ??????? ; EXEC SQL SET DESCRIPTOR 'out' VALUE 1 DATA=:str, TYPE=:char_type, LENGTH=:char_len;
EXEC SQL EXECUTE stmt_id USING DESCRIPTOR 'in';
while(1){
EXEC SQL FETCH curs INTO DESCRIPTOR 'out';
EXEC SQL GET DESCRIPTOR 'out' VALUE 1 :str = DATA;
}
What will be the SQL_CURSOR's TYPE number? Is it doable at all?
Stas Received on Fri Oct 19 2001 - 16:47:16 CDT