OraFAQ Forum: Forms » Fetching Value from Dynamic Cursor

Home » Developer & Programmer » Forms » Fetching Value from Dynamic Cursor (Oracle 10g form)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Fetching Value from Dynamic Cursor [message #535385]

Tue, 13 December 2011 22:59

swapnilpimple
Messages: 33
Registered: July 2010
Location: Mumbai

Member

Dear all,

I have used a dynamic cursor for fetching value from different tables hence table name assigned dynamically during run time

but i face an error ORA-06562 type of argument must match type of column and bind variable with error ORA-06212
But i frequently check the table structure and Declared variable
there is no mismatch between them

Here i am Sending my Code for your reference. Please help me to solve this problem

Code----------

Declare

C_YEAR number(38);
C_LOC VARCHAR2(200);
C_INVNO VARCHAR2(200);
C_INVDT DATE;
C_CTRT VARCHAR2(200);
C_GLCD VARCHAR2(200);
C_SLCD VARCHAR2(200);
C_CLNAM VARCHAR2(1200);
C_INVAMT NUMBER (38);
C_PONO VARCHAR2(700);
C_UFLG VARCHAR2(200);
-----

BEGIN

sqlstr := ' SELECT CDT_YEAR C_YEAR, CDT_LOC C_LOC , CDT_INVOICE_NO C_INVNO, CDT_INVOICE_DATE C_INVDT,CDT_CTRCT C_CTRT,
CDT_GL_CD C_GLCD,CDT_SL_CD C_SLCD,CDT_CL_NAME C_CLNAM, CDT_INVOICE_AMT C_INVAMT, CDT_CL_PO_NO C_PONO, CDT_UPLOAD_FLG C_UFLG
FROM '||p_loc||'
WHERE (CDT_YEAR, CDT_LOC , CDT_INVOICE_NO , CDT_INVOICE_DATE,CDT_CTRCT, CDT_GL_CD,CDT_SL_CD ,CDT_CL_NAME, CDT_INVOICE_AMT, CDT_CL_PO_NO )IN(
SELECT CDT_YEAR, CDT_LOC , CDT_INVOICE_NO , CDT_INVOICE_DATE,CDT_CTRCT, CDT_GL_CD,CDT_SL_CD ,CDT_CL_NAME, CDT_INVOICE_AMT, CDT_CL_PO_NO
FROM '||p_loc||'
WHERE CDT_INVOICE_DATE BETWEEN '||''''||:FROM_DATE||''''||'AND '||''''||:TO_DATE||''''||'
MINUS
SELECT CDT_YEAR, CDT_LOC , CDT_INVOICE_NO , CDT_INVOICE_DATE,CDT_CTRCT, CDT_GL_CD,CDT_SL_CD ,CDT_CL_NAME, CDT_INVOICE_AMT, CDT_CL_PO_NO
FROM FAS.FORM_DTL1_TAB
WHERE CDT_INVOICE_DATE BETWEEN '||''''||:FROM_DATE||''''||'AND '||''''||:TO_DATE||''''||
' and cdt_cmpny_cd = '||''''||:global.cmpny_cd||''''||')';

-----------------------------------------------Cursor Declare-------------------------------------------
my_cursor:= DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE (my_cursor, SQLSTR, 2);
NUM_ROWS_PROCESSED := DBMS_SQL.EXECUTE (my_cursor);

--------------------------------------column difine-------------------------------------

DBMS_SQL.DEFINE_COLUMN (my_cursor, 1, C_YEAR, 38 );
DBMS_SQL.DEFINE_COLUMN (my_cursor, 2, C_LOC, 200 );
DBMS_SQL.DEFINE_COLUMN (my_cursor, 3, C_INVNO, 200 );
DBMS_SQL.DEFINE_COLUMN (my_cursor, 4, C_INVDT );
DBMS_SQL.DEFINE_COLUMN (my_cursor, 5, C_CTRT, 200 );
DBMS_SQL.DEFINE_COLUMN (my_cursor, 6, C_GLCD, 200 );
DBMS_SQL.DEFINE_COLUMN (my_cursor, 7, C_SLCD, 200);
DBMS_SQL.DEFINE_COLUMN (my_cursor, 8, C_CLNAM, 1200);
DBMS_SQL.DEFINE_COLUMN (my_cursor, 9, C_INVAMT, 38);
DBMS_SQL.DEFINE_COLUMN (my_cursor, 10, C_PONO, 700);
DBMS_SQL.DEFINE_COLUMN (my_cursor, 11, C_UFLG, 200);

LOOP

IF DBMS_SQL.FETCH_ROWS (my_cursor) > 0 THEN

----------------------------Actual Data Fetching in Variable from Cursor---------------------------
DBMS_SQL.COLUMN_VALUE (my_cursor, 1, C_YEAR);
DBMS_SQL.COLUMN_VALUE (my_cursor, 2, C_LOC);
DBMS_SQL.COLUMN_VALUE (my_cursor, 3, C_INVNO);
DBMS_SQL.COLUMN_VALUE (my_cursor, 4, C_INVDT);
DBMS_SQL.COLUMN_VALUE (my_cursor, 5, C_CTRT);
DBMS_SQL.COLUMN_VALUE (my_cursor, 6, C_GLCD);
DBMS_SQL.COLUMN_VALUE (my_cursor, 7, C_SLCD);
DBMS_SQL.COLUMN_VALUE (my_cursor, 8, C_CLNAM);
DBMS_SQL.COLUMN_VALUE (my_cursor, 9, C_INVAMT);
DBMS_SQL.COLUMN_VALUE (my_cursor, 9, C_PONO);
DBMS_SQL.COLUMN_VALUE (my_cursor, 10, C_UFLG);

ELSE
EXIT;
END IF;

END LOOP;

DBMS_SQL.CLOSE_CURSOR (my_cursor); ----Closing Cursor
END;

Report message to a moderator

Re: Fetching Value from Dynamic Cursor [message #535398 is a reply to message #535385]

Wed, 14 December 2011 00:38

ranamirfan
Messages: 535
Registered: January 2006
Location: Pakistan / Saudi Arabia

Senior Member

ORA-06562:
type of out argument must match type of column or bind variable
Cause:	 Attempting to get the value of a column or a bind variable by calling procedure COLUMN_VALUE or VARIABLE_VALUE of package DBMS_SQL but the type of the given out argument where to place the value is different from the type of the column or bind variable that was previously defined by calling procedure DEFINE_COLUMN (for defining a column) or BIND_VARIABLE (for binding a bind variable) of package DBMS_SQL.
Action:	 Pass in an out argument of the correct type when calling procedure COLUMN_VALUE or VARIABLE_VALUE. The right type is the type that was provided when defining the column or binding the bind variable.

Please also follow the OraFAQ Forum Guide. http://www.orafaq.com/forum/t/88153/0/

Regards,
Irfan

Report message to a moderator

Re: Fetching Value from Dynamic Cursor [message #535402 is a reply to message #535398]

Wed, 14 December 2011 01:01

swapnil_naik
Messages: 269
Registered: December 2009
Location: Mumbai

Senior Member

hi irfan,

I agreed solution u given ...but i didnt understand yet which type of column or bind variable i check...because dbms_sql package is wrapped..so i didnt see code...and when i go to that code..i see same no. of column def. are used..

Report message to a moderator

Previous Topic:	Oracle Global.<variable name> can't hold 4000 characters?
Next Topic:	error 49 bad bind variable

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 03 06:47:25 CST 2025