Precompilers, OCI & OCCI :: PLS-00642 / Local Collection Types Not Allowed In SQL Statements

Oct 2, 2008

I have an anonymous PL/SQL procedure executed from OCCI like:

declare
type temp is table of emp%rowtype;
vemp temp;
begin
select * bulk collect into vemp

[code]...

and a cpp code like:

string stmt = "see above";
stmt->registerOutParam (1, OCCICURSOR);
stmt->execute ();

The "execute" gives me the error (of course): PLS-00642: local collection types not allowed in SQL statements

I cannot select from an PL/SQL table.But how to transfer these PL/SQL table "vemp" into my OCCI program?

Note: the first select is for information only, so I can not select the data in question from that directly.

View 6 Replies


ADVERTISEMENT

PLS-00642 - Local Collection Types Not Allowed In SQL Statements

Feb 20, 2011

I write plsql code as following:

DECLARE
TYPE id_collection IS TABLE OF NUMBER;
TYPE title_collection IS TABLE OF VARCHAR2(60);
id ID_COLLECTION;

[Code]...

I got following error: PLS-00642: local collection types not allowed in SQL statements.

View 3 Replies View Related

Precompilers, OCI & OCCI :: Retrieve Collection Of User-defined Object?

May 9, 2003

I am having a OCCI problem, I try to retrieve a collection of user-define objects.

The major steps I am using are
1. define database object

CREATE OR REPLACE TYPE my_obj_t AS OBJECT
( id number, name varchar2(10) )
CREATE OR REPLACE
TYPE my_obj_tab_t as table of my_obj_t;

2. A stored procedure return a collection as out paramerter
my_stored_procedure(obj_list out my_obj_tab_t);

3. Using OTT to generate the *.cpp and header files.

4. Register out parameter like
vector<my_obj_t> vect;
stmt->registerOutParam(1, OCCIVECTOR,
sizeof (vect),
"MY_OBJ_TAB_T");

5. stmt->execute();

6. Try to get the results by
getVector(stmt, 1, vect);

I am getting compilation error:

Could not find a match for
oracle::occi::getVector(oracle::occi::Statement*,
int, std::vector<my_obj_t, std::allocator<my_obj_t>>).

Am I doing the right things ?What steps and function should I use ?

View 1 Replies View Related

Precompilers, OCI & OCCI :: How To Fetch All Rows In A Cursor In Local Variables

Jul 29, 2008

How to fetch all the rows in a cursor in local variables(host variables) in PRO C . /*For x in cur is not working....*/

View 2 Replies View Related

Precompilers, OCI & OCCI :: PLS-00306 / Wrong Number Or Types Of Arguments In Call To Existing Stored Procedure

Feb 1, 2010

I'm using an existing stored procedure in my C code. The stored procedure in question has been compiled and is proven to work without any errors. However, when I use the same in my C code, its failing with the above error.

The Store procedure definition looks like :

CREATE OR REPLACE FUNCTION SP(
srq_id integer ,
unid IN SPkg.arr_parmid,
parm_typ IN SPkg.arr_parm_typ,

[code].....

Type definitions

TYPE arr_parm_typ IS TABLE OF char INDEX BY BINARY_INTEGER;
TYPE arr_parmid IS TABLE OF tbl_parm.UNID%TYPE INDEX BY BINARY_INTEGER;
TYPE arr_parm_lbl IS TABLE OF tbl_parm.PARM_LBL%TYPE INDEX BY BINARY_INTEGER;
TYPE arr_parm_vlu IS TABLE OF tbl_parm.PARM_VLU%TYPE INDEX BY BINARY_INTEGER;
TYPE arr_vlu_hint IS TABLE OF tbl_parm.VLU_HINT%TYPE INDEX BY BINARY_INTEGER;

My C code looks like :

typedef struct param
{
char lbl[30][81];
char vlu[30][256];
char typ[30];
ub8 seq_no[30];

[code].....

The way I invoke the stored procedure:

char command[250] = "begin
:retval := SSP_srq_parm_all(:srq_id,:unid,:parm_typ,:parm_lbl,:parm_vlu,:commit_flag,:vlu_hint,:create_flag);
end;";
OCIStmtPrepare2((OCISvcCtx *)svchp, (OCIStmt **)&(stmthp),
(OCIError *)errhp, (OraText *)command,

[code].....

OCIStmtExecute() fails with the above error.

View 3 Replies View Related

SQL & PL/SQL :: Various Collection Types Available In Oracle 9i?

Jun 15, 2010

What are the various collection types available in Oracle 9i.

Mention the differences between them.

View 4 Replies View Related

Precompilers, OCI & OCCI :: Connectivity Between C And Oracle?

Aug 12, 2008

Is it possible to connect with Oracle using C?

View 2 Replies View Related

Precompilers, OCI & OCCI :: Keep Getting ORA-22275 From Extproc?

Sep 26, 2012

I am trying to give back data from a stored procedure written in C. I registered the functions as follows:create or replace procedure version(versioninfo OUT clob) as external name "version" library myLib language c with context parameters (context, versioninfo, versioninfo INDICATOR SB4); It compiles fine. The function being called look like this:

extern "C" DLLEXPORT
void version(OCIExtProcContext* context, OCILobLocator **out, sb4 *ind_out){
OCIEnv* envh;
OCISvcCtx* svch;
OCIError* errh;
OCIExtProcGetEnv (context, &envh, &svch, &errh);

std::string versioninfo = "104";
oraub8 maxChar = 10;

[code]...

If I execute the procedure with SQLDeveloper by pressing "play" it is getting executed but there is no result. If I try to execute it from an anonymous block it results in ORA-22275 instead of doing anything.

declare
res clob;
begin
-- the following doesn't work much
--dbms_lob.createtemporary(res,true);
version(res);
dbms_output.put_line(res);
end;

Actually I have to questions:
1.) Why does Oracle give me the error? In my opinion all requirements mentioned by the error description are met.
2.) Why is there no output when executing the function via SQL Developer? Is the usage of OCILobWrite wrong?

View 2 Replies View Related

Precompilers, OCI & OCCI :: Describe STP In Package

Nov 26, 2009

I am trying to describe an STP in a package, but it gives me an error.

e.g. In package ABC suppose there is an STP XYZ, I am trying to describe ABC.XYZ function but it gives me an error code 4043 and error message object XYZ.ABC does not exist.

View 2 Replies View Related

Precompilers, OCI & OCCI :: 11g Pro*C Application Crashes While 10g Is OK

Jan 24, 2009

My 10g pro*c application works perfectly, recently I upgraded it to 11g, but it keep crashing, why?

View 10 Replies View Related

Precompilers, OCI & OCCI :: Proc Compilation?

Mar 31, 2010

i need to compile a proc program, say prog.pc.have oracle 10g in my system. Since i am new to proc programming, me on the steps to compile the proc program in oracle proc compiler.

View 2 Replies View Related

Precompilers, OCI & OCCI :: How To Get Columns As NUMBER(30)

Aug 13, 2008

in my oci applications,if i get a column of number that is in the scope of int,i can use value = *(int *)field.data; get the value,but if the column size is larger than 10,the code can't be available,how can i get the value.

View 6 Replies View Related

Precompilers, OCI & OCCI :: Calling Procedure From PRO C?

Nov 26, 2012

I am trying to call procedure from PRO C Procedure has many parameters and I do not need to put all of them when I call procedure. Is there way to make the same way as in PL/SQL

like this

my_procedure( q=>a1 , b=>'abc )

View 1 Replies View Related

Precompilers, OCI & OCCI :: How To Connect To Two Databases

Aug 25, 2009

My program needs to connect to two databases and make operations in both databases:

Quote:connect to db1;
connect to db2;

select from table in db1;
update table in db2;

i would like to know whether it's possible, and how to do it ? how to specify for each query the database connection to use?

note that I can't use dblinks as i don't have enough access rights and privileges on neither of the databases.

View 2 Replies View Related

Precompilers, OCI & OCCI :: How To Report Oracle Bug

Jan 30, 2009

How to report an Oracle bug? Is there webpage or email?

View 4 Replies View Related

Precompilers, OCI & OCCI :: How To Connect C++ And Oracle

Sep 17, 2008

My OS is Linux and I installed Oracle 10.2. Everything is fine.I can use sqlplus, exp, imp etc with no problem.Now I have created another linux user test in /home/test. I unzipped basic-10.2xxxxxx.zip (/home/test/instantclient_10_2) and exported LD_LIBRARY_PATH.I guess I have installed instant client in this way.

My testOra.cpp:

#include <occi.h>
int main()
{
return 0;
}

This test.cpp would not compile. It cannot find occi.

View 8 Replies View Related

Precompilers, OCI & OCCI :: Using Cursor In Pro Cobol

Oct 7, 2010

I declare a cursor for a table with 8000 records, when I fetch the cursor this message appear ORA-03113: end-of-file on communication channels when the fetch reach the 6500 element, What is the problem here?. All data are ok, not null fields.

other problem

How I can reuse a cursor, I declare a cursor for a table where code_part = 300, then I fetch all elements until end cursor, then I close the cursor, Then I declare the same cursor again for the same table where code_part = 359, but it is not successful, when I tried to fetch the cursor again the cobol program show me the last record for the first code, How I can restart the cursor or delete it or freed the cursor position?

View 1 Replies View Related

Precompilers, OCI & OCCI :: Print Run Time Value?

Apr 26, 2012

I am inserting empno in a table1 and updating another table2 using table1 empno and getting ora-01427 error. I want to print empno for whis this error is coming. How to print that value?

View 1 Replies View Related

Precompilers, OCI & OCCI :: Convert C To PC File?

Dec 11, 2010

Can i convert a .c file to .pc file?The .c file is generated by compiling .pc file. I mistakenly deleted the .pc file. I need the original .pc file.

View 2 Replies View Related

Precompilers, OCI & OCCI :: How To Integrate SQLnet

Apr 17, 2009

how to integrate SQLnet & c and I'm quite losted at the moment.

Searching with google gives some random stuff, which does seems to be irrelevant.some oracle db somewhere and need for good way to use that remote db (one solution seems to be using SQLnet).

View 1 Replies View Related

Precompilers, OCI & OCCI :: Indicator Values For DATES

Jun 1, 2010

If doing an insert into DATE type fields like below... how do I employ null indicator values with the TO_DATE sql to cope with NULL values for the End Date? I can test the NULLness of the pServiceRecord->itemTo value and set the indicator ind_dbToDate to -1 but I don't know how to incorporate this with the to_date syntax (if I can)?

EXEC SQL BEGIN DECLARE SECTION;
char dbFromDate[MAX_DATE_LEN];
char dbToDate[MAX_DATE_LEN];
short ind_dbToDate;
[code]...

View 2 Replies View Related

Precompilers, OCI & OCCI :: Using Distributed Transactions With ProC

Jul 7, 2011

I found this thread, and recently i want to embedded oci in pro*c for distributed transaction reason.

can pro*c deal with distributed transaction? i want to use mts in pro*c .

View 14 Replies View Related

Precompilers, OCI & OCCI :: How To Use Cursor With Dynamic SQL In ProC

May 24, 2005

I am using dynamic Pl/SQL with ProC and having problems with cursor. I've to execute stored procedure dynamically and get the result of select list into the cursor. The EXECUTE command is working fine but the FETCH gets failed with error "ORA-01002: fetch out of sequence". I've read that cursor variable can not be used with dynamic SQL.

Stored Procedure:
------------------
ROCEDURE open_mod_cur (
curs IN OUT cur_type,
module_id IN varchar2)

[Code].....

View 4 Replies View Related

Precompilers, OCI & OCCI :: NULL Values In C Structures?

May 18, 2010

I use a cursor to select records from a database table into a C structure as follows...

{
int iLoop = 0;
int iResult = 0;

[Code]....

The otc_multiplier field is NULL. As is the to_date sometimes. However, when I output the records later, the entries where the to_date is NULL come out fine (no value). But the otc_multiplier is getting output as 0.0 using...

// this is output later in another fuction using the following..
sprintf(newRecord, "%.1f",daServiceRecs->itemMultiplier);

If using c structures in this manner, what is the method for ensuring that numeric values are set to NULL when required?

View 2 Replies View Related

Precompilers, OCI & OCCI :: Oracle Connection Not Getting Closed

Apr 26, 2010

I have Multi-threaded application using pro*c to connect to oracle.

I am using the following piece of code to create a connection to oracle database and preserver the context of created connection.

struct sqlca * tempSqlca = new sqlca;
struct sqlca & sqlca = * tempSqlca;
EXEC SQL BEGIN DECLARE SECTION;
SQL_CONTEXT localContext;

[code].....

I am using the following piece of code to close the connection to the oracle database, I am using the context created in the previous step and passing it to the below code(connection.context)to release the connection.

if (connection.context == 0 || connection.ca == 0)
return true;
// Multithreading: get the communication area of the context
struct sqlca & sqlca = *(connection.ca);
EXEC SQL BEGIN DECLARE SECTION;

[code].....

When the above code is executed there is no exception thrown but the connection is not getting close. I am using the V$SESSION system views to monitor the connection created.

I there anything I am missing in my code for the connection closing to fail.

View 1 Replies View Related

Precompilers, OCI & OCCI :: Errors During Insert Operation In Pro*C?

Apr 13, 2009

I am receiving the following error while inserting records.

"Oracle Error:ORA-12899: value too large for column "MFG_ADMIN"."GAGE_RESULTS"."COMP

whereas I am checking the length of all values before inserting and am sure that none of them are larger than column lengths.I did some research and found this error might be due to character set.

select * from nls_database_parameters where parameter like '%CHARACTERSET';

PARAMETER VALUE
------------------------------
----------------------------------------------------------------------------------------------------
--------------------
NLS_CHARACTERSET UTF8
NLS_NCHAR_CHARACTERSET UTF8

how can this be related to my problem or if something else is causing this error.

View 3 Replies View Related

Precompilers, OCI & OCCI :: Printing Of Sqlstmt During Execution

Aug 23, 2010

I am having a core dump while running exe that uses services in pro c to go to the DBthe sentence that causes the dump is:
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);

I need to print the &sqlctx, &sqlstm, &sqlfpn just before the call.

tried " printf("from select sqlstmt %s
", sqlstmt);

but it only prints the " from select sqlstmt "

View 6 Replies View Related

Precompilers, OCI & OCCI :: Batch Error Mode?

Mar 4, 2010

what is OCI_BATCH_ERRORS and BATCH ERROR Mode ?and how the user can set it ?

View 6 Replies View Related

Precompilers, OCI & OCCI :: ORA-01002 / Fetch Out Of Sequence

Apr 16, 2009

I am back with one more issue. I am fetching records through a cursor from database using Pro*C and inserting some records as shown below:

EXEC SQL DECLARE cur_num CURSOR FOR
SELECT STUDENT_NUMBER
FROM STUDENTS
WHERE STDID = :temp_num
ORDER BY STUDENT_NUMBER;
EXEC SQL OPEN cur_num;
EXEC SQL WHENEVER NOT FOUND DO break;

[code]....

This code works fine sometimes but ends up with this error sometimes: ORA-01002: fetch out of sequence and also do I need to do null termination once i fetch data from the database like this:

Quote:EXEC SQL FETCH cur_num INTO :std_num ;
std_num.arr[std_num.len] = '�';
std_num is declared as varchar datatype.

View 8 Replies View Related

Precompilers, OCI & OCCI :: Reconnect Proc If Disconnect?

Nov 11, 2011

I am running a pro*c its working fine but my connection gets disconnect to my db server so i want to continue my proc if connection between my machine & server reestablish again weather it starts from start or from where it was stooped because of connection break

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved