Cannot bind by name to work with a SQLT_TXT in OCI

Cannot bind by name to work with a SQLT_TXT in OCI

Post by John Savil » Sun, 31 Dec 1899 09:00:00



Hi,

I'm trying to get a OCIBindByName to work with text but it never works. The
following works when I convert the text to a INT first

checkerr(errhp, OCIBindByName(stmthp1, &bndhp, errhp, (text *) ":MMSREF",
strlen(":MMSREF"),
         (dvoid *) &mmsRefNo, (sb4) sizeof(mmsRefNo), SQLT_INT,
         (dvoid *) 0, (ub2 *)0, (ub2 *)0,
         (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT));

But if I try

TEXT mmsRef[32];

checkerr(errhp, OCIBindByName(stmthp1, &bndhp, errhp, (text *) ":MMSREF",
strlen(":MMSREF"),
         (dvoid *) &mmsRef, (sb4) sizeof(mmsRef), SQLT_TXT,
         (dvoid *) 0, (ub2 *)0, (ub2 *)0,
         (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT));

Then there are no errors but no record is found. It should be noted that
sometimes mmsRef may only use 8 characters then a null.

What am I doing wrong?

Thanks,

John

John Savill MCSE MVP

 
 
 

1. OCI: Binding collections (varray), Ora 1008: not all variables bound

Hi,
I,ve got a stored procedure, which takes a varray as parameter:

create or replace type tp_person as object
(
        pid             number,
        name            varchar2(20),
        vorname         varchar2(20),
        strasse         varchar2(20),
        ort             varchar2(20),
);
/
create or replace type va_person AS varray(100) of tp_person;  
/
procedure list(person out va_person) is
begin
        select
                cast(multiset(
                        select value(tb)
                        from ov_person tb)
                        as va_person)
                into
                        person
                from
                        dual;
end;
/ (Thanks to T.Kyte)

Now I want to call this procedure from an OCI program:

static text *listStmtTxt = (text *) "BEGIN\
                                        pkg_person.list(:DATA);\
                                        END;";

OCIStmtPrepare(...)
OCIDefineByPos(...,(ub4)1,...)
OCIDescribeAny(...)
OCIAttrGet(..., (ub4) OCI_HTYPE_DESCRIBE,..., (ub4)OCI_ATTR_PARAM,...)
OCIAttrGet(..., (ub4) OCI_DTYPE_PARAM,... (ub4)OCI_ATTR_REF_TDO,...)
OCIObjectPin(...)

OCIDefineObject(...)
OCIDefineArrayOfStruct(...)

OCIStmtExecute(...) returns "Ora 1008: not all variables bound".

Who knows, what I forgot.
Does anybody have a chunk of sample code for this purpose?

TIA,
Joerg Blaese

2. USA-MD-Data Architecture Manager

3. -25572: Network driver cannot bind a name to the port

4. Query optimization problem

5. -25572: Network driver cannot bind a name to the port -------

6. -25572: Network driver cannot bind a name to the port

7. (error -25572) Network driver cannot bind a name to the

8. Network driver cannot bind a name to the port

9. Cannot bind network name to port

10. cannot get <sql:param name= to work