Guest_imported
New member
- Jan 1, 1970
- 0
hi,
hi have a big problem with the new odbc driver for
Dynamic SQL, its very important to use package to return
my result set,its the new way and to call a store procedure
with sql server syntax, is very good. and the migration tools use
the package way to convert store procedure. GOOD JOB ORACLE.
the problem is if i use dbms_output.put_line in sql plus
every thing work fine, but by odbc the driver don't support
this kind of result set.
i'm trying to replace this syntax with package syntax.
is working , i would like you try it, the only problem is
if i have 5 rows in my table, i got the first one only.
i fetch each rows but the result set i got only one.
can you help me to convert this syntax, or maybe you have
a other method, but its very important to use my package
because the new driver odbc in sql migration (enable exec syntax) is on. I PUT SOME COMMENT WHERE THE SYNTAX IS NOT SUPPORTED IN
MY STORE PROCEDURE.
CREATE OR REPLACE PACKAGE TESTPKG AS
TYPE RT1 IS RECORD (
o_oval1 VARCHAR2(255),
o_oval12 VARCHAR2(255)
);
TYPE RCT1 IS REF CURSOR RETURN RT1;
END;
-------------
CREATE OR REPLACE PROCEDURE "KARATB2"."DYNTEST" (
RC2 IN OUT testpkg.RCT1
)
AS
type my_curs_type is ref cursor;
rc1 my_curs_type;
v_Query VARCHAR2(255);
v_ExecQuery VARCHAR2(255);
v_ExecQuery2 VARCHAR2(255);
ProcessusInfoExists NUMBER(1,0);
ProcessusInfocount NUMBER(1,0);
BEGIN
BEGIN
ProcessusInfoExists := 1;
IF ProcessusInfoExists = 1 THEN
BEGIN
v_Query := 'select filkey,filname from fileheader';
open rc1 for v_Query;
loop
FETCH rc1 into v_ExecQuery,v_ExecQuery2;
exit when rc1%notfound;
--return only one row????
open rc2 for select v_ExecQuery,v_ExecQuery2 from dual ;
--work fine with sql plus, but odbc driver don't support it
-- dbms_output.put_line(v_ExecQuery|| ' '|| v_ExecQuery2);
end loop;
Close rc1;
END;
ELSE
BEGIN
NULL;
OPEN RC2 FOR select 0,1 "ProcessusInfoExists" from dual;
END;
END IF;
end;
RETURN ;
END;
------------
hi have a big problem with the new odbc driver for
Dynamic SQL, its very important to use package to return
my result set,its the new way and to call a store procedure
with sql server syntax, is very good. and the migration tools use
the package way to convert store procedure. GOOD JOB ORACLE.
the problem is if i use dbms_output.put_line in sql plus
every thing work fine, but by odbc the driver don't support
this kind of result set.
i'm trying to replace this syntax with package syntax.
is working , i would like you try it, the only problem is
if i have 5 rows in my table, i got the first one only.
i fetch each rows but the result set i got only one.
can you help me to convert this syntax, or maybe you have
a other method, but its very important to use my package
because the new driver odbc in sql migration (enable exec syntax) is on. I PUT SOME COMMENT WHERE THE SYNTAX IS NOT SUPPORTED IN
MY STORE PROCEDURE.
CREATE OR REPLACE PACKAGE TESTPKG AS
TYPE RT1 IS RECORD (
o_oval1 VARCHAR2(255),
o_oval12 VARCHAR2(255)
);
TYPE RCT1 IS REF CURSOR RETURN RT1;
END;
-------------
CREATE OR REPLACE PROCEDURE "KARATB2"."DYNTEST" (
RC2 IN OUT testpkg.RCT1
)
AS
type my_curs_type is ref cursor;
rc1 my_curs_type;
v_Query VARCHAR2(255);
v_ExecQuery VARCHAR2(255);
v_ExecQuery2 VARCHAR2(255);
ProcessusInfoExists NUMBER(1,0);
ProcessusInfocount NUMBER(1,0);
BEGIN
BEGIN
ProcessusInfoExists := 1;
IF ProcessusInfoExists = 1 THEN
BEGIN
v_Query := 'select filkey,filname from fileheader';
open rc1 for v_Query;
loop
FETCH rc1 into v_ExecQuery,v_ExecQuery2;
exit when rc1%notfound;
--return only one row????
open rc2 for select v_ExecQuery,v_ExecQuery2 from dual ;
--work fine with sql plus, but odbc driver don't support it
-- dbms_output.put_line(v_ExecQuery|| ' '|| v_ExecQuery2);
end loop;
Close rc1;
END;
ELSE
BEGIN
NULL;
OPEN RC2 FOR select 0,1 "ProcessusInfoExists" from dual;
END;
END IF;
end;
RETURN ;
END;
------------