I am looking forward an example that call a stored function/procedure with a
REF CURSOR output parameter.

In other words, I have a stored function/procedure that execute a SELECT
statement using the OCILIB and then it could get the values of each column and
each row.

I am using Oracle client and server 10g

Regards

algarmon

Hi,

here a complete small program that show how to use ref cursors ...

include "ocilib.h"

int main(void)
{
OCI_Connection cn;
OCI_Statement st;
OCI_Statement st2;
OCI_Resultset rs;
int i, n;

if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
return EXIT_FAILURE;

cn = OCI_ConnectionCreate("db", "usr", "pwd",
OCI_SESSION_DEFAULT);

st = OCI_StatementCreate(cn);
st2 = OCI_StatementCreate(cn);

OCI_Prepare(st, "begin open :c for select sysdate from dual; end;");

OCI_BindStatement(st, ":c", st2);
OCI_Execute(st);

rs = OCI_GetResultset(st2);

n = OCI_GetColumnCount(rs);

while (OCI_FetchNext(rs))
{
for(i = 1; i <= n; i++)
{
printf("col '%s' - value %s\n",
OCI_GetColumnName(OCI_GetColumn(rs, i)),
OCI_GetString(rs, i));
}
}

OCI_Cleanup();

return EXIT_SUCCESS;
}

Regards,

Vincent

Hi Vincent,

I understand the program that show how to use de REF CURSOR, but the statement
OCI_Prepare(st, &quot;begin open :c for select sysdate from dual;
end;&quot;);
Doesn´t call a stored procedure o function.
How do I call a stored procedure? Just change the OCI_Prepare? How?

I add a Oracle script that create the CITIES table and add records to this
table and then it creates the SP_GETCITIES procedure that return the cities
for a specific state.

Could you help me with a example that call a stored procedure like this?

Thanks for your help.

Regards.

Antonio Garcia

CREATE TABLE CITIES (
STATE_CODE VARCHAR2(2) NULL,
CITY_CODE NUMBER(15,5) NULL,
CITY_NAME VARCHAR2(30) NULL
)
/

INSERT INTO CITIES(STATE_CODE, CITY_CODE, CITY_NAME)
VALUES('FL', 20, 'ORLANDO')
/
INSERT INTO CITIES(STATE_CODE, CITY_CODE, CITY_NAME)
VALUES('NY', 10, 'NEW YORK')
/
INSERT INTO CITIES(STATE_CODE, CITY_CODE, CITY_NAME)
VALUES('NY', 20, 'ALBANY')
/
INSERT INTO CITIES(STATE_CODE, CITY_CODE, CITY_NAME)
VALUES('CA', 10, 'LOS ANGELES')
/
INSERT INTO CITIES(STATE_CODE, CITY_CODE, CITY_NAME)
VALUES('CA', 20, 'SAN FRANCISCO')
/

CREATE OR REPLACE PACKAGE globalPkg AUTHID CURRENT_USER AS
/ The following are T/SQL specific global variables. /
TYPE RCT1 IS REF CURSOR;/new weak cursor definition/
END globalPkg;
/

CREATE OR REPLACE PROCEDURE SP_GETCITIES(
STATE_KEY IN VARCHAR,
RC1 IN OUT globalPkg.RCT1)
AS
StoO_error INTEGER;
StoO_selcnt INTEGER;
StoO_rowcnt INTEGER;
StoO_errmsg VARCHAR2(255);
BEGIN
StoO_rowcnt := 0;
StoO_error := 0;
StoO_selcnt := 0;
OPEN RC1 FOR
SELECT STATE_CODE, CITY_CODE, CITY_NAME
FROM CITIES
WHERE STATE_CODE = STATE_KEY
ORDER BY CITY_CODE;
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN OTHERS THEN
StoO_rowcnt := 0;
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
END;
/

Hi,

here is :

-your SQL and PLSQL code corrected (typos)
-the desired OCILIB program
-the output ran on with your test suite

SQL and PLSQL :

CREATE TABLE CITIES
(
STATECODE VARCHAR2(2) NULL,
CITYCODE NUMBER(15,5) NULL,
CITYNAME VARCHAR2(30) NULL
);

INSERT INTO CITIES(STATECODE, CITYCODE, CITYNAME) VALUES('FL', 20, 'ORLANDO');
INSERT INTO CITIES(STATECODE, CITYCODE, CITYNAME) VALUES('NY', 10, 'NEW
YORK');
INSERT INTO CITIES(STATECODE, CITYCODE, CITYNAME) VALUES('NY', 20, 'ALBANY');
INSERT INTO CITIES(STATECODE, CITYCODE, CITYNAME) VALUES('CA', 10, 'LOS
ANGELES');
INSERT INTO CITIES(STATECODE, CITYCODE, CITYNAME) VALUES('CA', 20, 'SAN
FRANCISCO');

CREATE OR REPLACE PACKAGE globalPkg AUTHID CURRENT_USER AS

TYPE RCT1 IS REF CURSOR;

END globalPkg;
/

CREATE OR REPLACE PROCEDURE SPGETCITIES(STATEKEY IN VARCHAR, RC1 IN OUT
globalPkg.RCT1) AS
StoOerror INTEGER;
StoOselcnt INTEGER;
StoOrowcnt INTEGER;
StoOerrmsg VARCHAR2(255);
BEGIN

StoOrowcnt := 0;
StoOerror := 0;
StoOselcnt := 0;

OPEN RC1 FOR
SELECT
STATECODE, CITYCODE, CITYNAME
FROM
CITIES
WHERE
STATECODE = STATEKEY
ORDER BY CITYCODE;

StoOrowcnt := SQL%ROWCOUNT;

EXCEPTION WHEN OTHERS THEN
StoOrowcnt := 0;
StoOerror := SQLCODE;
StoOerrmsg := SQLERRM;
END;
/

OCILIB Program :

include "ocilib.h"

int main(void)
{
OCI_Connection cn;
OCI_Statement st;
OCI_Statement st2;
OCI_Resultset rs;
int i, n;
char statecode;

if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
return EXIT_FAILURE;

cn = OCI_ConnectionCreate("db", "usr", "pwd",
OCI_SESSION_DEFAULT);

st = OCI_StatementCreate(cn);
st2 = OCI_StatementCreate(cn);

OCI_Prepare(st, "begin SPGETCITIES(:s, :c); end;");

OCI_BindString(st, ":s", statecode, sizeof(statecode)-1);
OCI_BindStatement(st, ":c", st2);

strcpy(statecode, "CA");

OCI_Execute(st);

rs = OCI_GetResultset(st2);

n = OCI_GetColumnCount(rs);

while (OCI_FetchNext(rs))
{

printf("ROW #%d\n", OCI_GetCurrentRow(rs));

for(i = 1; i <= n; i++)
{
printf("...COL = '%s' - VALUE = '%s'\n",
OCI_GetColumnName(OCI_GetColumn(rs, i)),
OCI_GetString(rs, i));
}
}

OCI_Cleanup();

return EXIT_SUCCESS;
}

and the output :

ROW #1
...COL = 'STATECODE' - VALUE = 'CA'
...COL = 'CITYCODE' - VALUE = '10'
...COL = 'CITYNAME' - VALUE = 'LOS ANGELES'
ROW #2
...COL = 'STATECODE' - VALUE = 'CA'
...COL = 'CITYCODE' - VALUE = '20'
...COL = 'CITYNAME' - VALUE = 'SAN FRANCISCO'

Hi Vincent.

Thank you very much for your time and help.

Best Regards,

Antonio Garcia (Mexico)

Thanks. That helped me too

1R6mX2 Really appreciate you sharing this blog article. Will read on...

I really like and appreciate your blog article.Thanks Again. Awesome.

Wow, great blog post.Much thanks again. Will read on...

Really informative article post.

Thanks for sharing, this is a fantastic article.Much thanks again. Will read
on...

wow, awesome article.Really thank you! Really Cool.

I appreciate you sharing this article post. Really Cool.

Nice information which is very helpful to all
Thanks for sharing
call driver in chennai

Nice information which is very helpful to all
Thanks for sharing
http://www.royalcalldrivers.com/">
Call driver in chennai

Good info to each and every one in this universe
http://www.svlpackersandmovers.com/">packers and movers kk nagar

xRiYMt Say, you got a nice article.Really looking forward to read more. Really
Cool.