Oracle native driver 2

[moonknightt]

What is the Oracle Native Driver and how can I connect to it in Crystal 10 and XI.

I am using OleDB for Oracle and it doesn't seen to be working on stored procedures

 

[synapsevampire]

The Native Connection will be displayed as Oracel Server->Create New Connection. You must have the Oracle client on the box, and in Crystal have turned on the File->Options->Database->Show Stored Procedures. This may be your difficulty with OLEDb.

OleDB works for Stored Procedures, and saying it doesn't seem to be working doesn't describe anything, post specifics: Does it crash? The SP isn't listed? etc...

One line posts almost always beget more posts, please take the time to state specifics.

Hopefully the above resolves.

-k

 

[moonknightt]

Thanks for your quick response.

the stored procedure shows and works with Microsoft Oledb provider for Oracle but not the Oracle Provider for Oledb.

The error it gives when using the Oracle Provideer for Oledb is

"Query engine error;'ADO engine error code 0x80040e14
Source: OraOLEDB
Description:Ora-6550:line1 column 7
PLS-0036: wrong number or types of arguments in call to 'Proc _Role_Conflicts' Ora-6550:line1 column 7
PL/SQL: Statement ignored
Native Error:6550

Is there any added benefits of using the Native driver over the Oledb or the Microsoft provider over the Oracle Provider in The report designer and enterprise both for 10 and XI

 

[Turkbear]

Hi,
A 'Native' driver is almost always prefered since no additional layers of code are used to access the data, just the database's own methods..( Some 'wire protocol' ODBC drivers ( like Business Objects newest ones) are close to native in use, but most others are not as efficient or version compliant))


The error you are getting indicates that the parameters required by the SP's IN and/or IN OUT statements are not being met...





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

If the SP is written to be Crystal compliant, and the Oracle OleDB probably doesn't support the cursor properly, or needs an option, set, as with the Crystal supplied Oracle ODBC driver, you need to set the Procedure Returns Results, so maybe there's something similar there.

Go native, it's faster and you'll find more people are using it, hence better documentation/testing/experience from which to call upon.

-k

 

[Turkbear]

Hi,
Your Error message # is incorrect, here is the full text of that error:
(PLS-00306: wrong number or types of arguments in call to 'Proc _Role_Conflicts' Ora-6550:line1 column 7
PL/SQL: Statement ignored

PLS-00306 wrong number or types of arguments in call to 'string'

Cause: This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The subprogram name might be misspelled, a parameter might have the wrong datatype, the declaration might be faulty, or the declaration might be placed incorrectly in the block structure. For example, this error occurs if the built-in square root function SQRT is called with a misspelled name or with a parameter of the wrong datatype.

Action: Check the spelling and declaration of the subprogram name. Also confirm that its call is correct, its parameters are of the right datatype, and, if it is not a built-in function, that its declaration is placed correctly in the block structure.

Look at the lines referenced and see what is incorrect..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[moonknightt]

This is the whole Procedure. i couldn't find anything on line 1 col 7

PROMPT CREATE OR REPLACE PROCEDURE reptowner.proc_role_conflicts

CREATE OR REPLACE PROCEDURE reptowner.PROC_ROLE_CONFLICTS
(P_conflict IN OUT Pkg_Cr.CR_TYPE)
AS



--DECLARATION STARTS----------------
TABSTRcrl Pkg_Cr.tString1;
TABSTRncrl Pkg_Cr.tString2;
V_VALIDATION_STEP VARCHAR2(50) :='';
USER_NAME VARCHAR2(100) := 'TEMP'||'_'||TO_CHAR(SYSDATE,'HH_MI_SS')||'_'||'USER' ;
V_CONFLICT_ROLE VARCHAR2(100):='';
V_CONFLICT_TEXT VARCHAR2(20) :='';
V_VALID_CONFLICT NUMBER := 999999999;
V_PREVIOUS_MATCH CHAR(1):= 'N';
V_COUNTCRL NUMBER := 0;
V_COUNT_NoCRL NUMBER := 0;
INDX NUMBER := 1;

CURSOR CUR_USER_ROLE_LIST IS
SELECT Distinct PRNC.PRIM_SCTY_ORGN_ID AS PRIMARY_ORG,
ROLE_JN.SCTY_ORGN_ID AS SECONDARY_ORG,ROLE_JN.SCTY_PRNC_ID AS USER_ID,
LTRIM(RTRIM(SUBSTR(ROLE_JN.SCTY_ROLE_ID,7,20))) AS USER_ROLE,PRNC.ACTV_FL
FROM
MF_SCTY_PRNC PRNC,MF_PRNC_ROLE_JN ROLE_JN,MF_SCTY_ORGN ORG
WHERE PRNC.UIDY = ROLE_JN.SCTY_PRNC_ID and
PRNC.PRIM_SCTY_ORGN_ID = ORG.UIDY and
PRNC.ACTV_FL = 'T';
--ORDER BY
--PRNC.PRIM_SCTY_ORGN_ID,
--ROLE_JN.SCTY_ORGN_ID,
--ROLE_JN.SCTY_PRNC_ID;

CURSOR CUR_CONFLICT_ROLES (V_USER_ROLE VARCHAR2) IS
SELECT distinct CONFLICT.PH_ROLE_CD,CONFLICT.RPT_CONFLICT_ROLE_CD
FROM
PH_RPT_SCTY_ROLE_CONFLICT CONFLICT
WHERE
CONFLICT.PH_ROLE_CD = LTRIM(RTRIM(V_USER_ROLE));


CURSOR CUR_VALID_CONFLICT(V_PRIMARY_ORG VARCHAR2,V_SECONDARY_ORG VARCHAR2,V_USER_ID VARCHAR2,V_CONFLICT_ROLE VARCHAR2) IS
SELECT COUNT(DISTINCT ROLE_JN.SCTY_PRNC_ID) AS VALID_COUNT
FROM
MF_PRNC_ROLE_JN ROLE_JN,MF_SCTY_PRNC PRNC
WHERE PRNC.UIDY = ROLE_JN.SCTY_PRNC_ID and
PRNC.PRIM_SCTY_ORGN_ID = LTRIM(RTRIM(V_PRIMARY_ORG)) AND
ROLE_JN.SCTY_ORGN_ID = LTRIM(RTRIM(V_SECONDARY_ORG)) AND
ROLE_JN.SCTY_PRNC_ID = LTRIM(RTRIM(V_USER_ID)) AND
LTRIM(RTRIM(SUBSTR(ROLE_JN.SCTY_ROLE_ID,7,20))) = LTRIM(RTRIM(V_CONFLICT_ROLE));


BEGIN

--GET THE ROLES AND THE CONFLICTING ROLES...
FOR USER_LIST IN CUR_USER_ROLE_LIST
LOOP

V_CONFLICT_TEXT := 'No Conflicts';

FOR CONFLICT IN CUR_CONFLICT_ROLES(USER_LIST.USER_ROLE)
LOOP

IF LENGTH(LTRIM(RTRIM(CONFLICT.RPT_CONFLICT_ROLE_CD)))=0 OR CONFLICT.RPT_CONFLICT_ROLE_CD IS NULL THEN
INSERT INTO TEMP_PH_RPT_SCTY_ROLE_CONFLICT
(PRIMARY_SCTY_ORG,
SECONDARY_SCTY_ORG,
USER_ID,
USER_ROLE,
CONFLICTING_ROLE,
ACTIVE_FLAG,
TEMP_USER_ID
)
VALUES
(LTRIM(RTRIM(SUBSTR(USER_LIST.PRIMARY_ORG,7,20))),
LTRIM(RTRIM(SUBSTR(USER_LIST.SECONDARY_ORG,7,20))),
LTRIM(RTRIM(SUBSTR(USER_LIST.USER_ID,7,20))),
USER_LIST.USER_ROLE,
LTRIM(RTRIM(V_CONFLICT_TEXT)),
USER_LIST.ACTV_FL,
USER_NAME
);
ELSE
FOR VALID IN CUR_VALID_CONFLICT(USER_LIST.PRIMARY_ORG,USER_LIST.SECONDARY_ORG,USER_LIST.USER_ID,CONFLICT.RPT_CONFLICT_ROLE_CD)
LOOP
V_VALID_CONFLICT := VALID.VALID_COUNT;
IF VALID.VALID_COUNT = 0 THEN

V_COUNT_NoCRL := V_COUNT_NoCRL + 1;

ELSIF VALID.VALID_COUNT = 1 THEN

V_COUNTCRL := V_COUNTCRL + 1;
TABSTRcrl(V_COUNTCRL) := CONFLICT.RPT_CONFLICT_ROLE_CD;

END IF;
EXIT;
END LOOP;
END IF;
END LOOP;

IF V_COUNTCRL >= 1 THEN

FOR INDX IN 1..V_COUNTCRL
LOOP

INSERT INTO TEMP_PH_RPT_SCTY_ROLE_CONFLICT
(PRIMARY_SCTY_ORG,
SECONDARY_SCTY_ORG,
USER_ID,
USER_ROLE,
CONFLICTING_ROLE,
ACTIVE_FLAG,
TEMP_USER_ID
)
VALUES
(LTRIM(RTRIM(SUBSTR(USER_LIST.PRIMARY_ORG,7,20))),
LTRIM(RTRIM(SUBSTR(USER_LIST.SECONDARY_ORG,7,20))),
LTRIM(RTRIM(SUBSTR(USER_LIST.USER_ID,7,20))),
USER_LIST.USER_ROLE,
TABSTRcrl(INDX),
USER_LIST.ACTV_FL,
USER_NAME
);

TABSTRcrl(INDX) := '';

END LOOP;

ELSIF V_COUNTCRL = 0 AND V_COUNT_NoCRL >=1 THEN

INSERT INTO TEMP_PH_RPT_SCTY_ROLE_CONFLICT
(PRIMARY_SCTY_ORG,
SECONDARY_SCTY_ORG,
USER_ID,
USER_ROLE,
CONFLICTING_ROLE,
ACTIVE_FLAG,
TEMP_USER_ID
)
VALUES
(LTRIM(RTRIM(SUBSTR(USER_LIST.PRIMARY_ORG,7,20))),
LTRIM(RTRIM(SUBSTR(USER_LIST.SECONDARY_ORG,7,20))),
LTRIM(RTRIM(SUBSTR(USER_LIST.USER_ID,7,20))),
USER_LIST.USER_ROLE,
V_CONFLICT_TEXT,
USER_LIST.ACTV_FL,
USER_NAME
);
END IF;

V_COUNT_NoCRL := 0;
V_COUNTCRL := 0;

END LOOP;
COMMIT;

OPEN P_CONFLICT FOR
SELECT * FROM TEMP_PH_RPT_SCTY_ROLE_CONFLICT WHERE
TEMP_USER_ID = USER_NAME;

DELETE FROM TEMP_PH_RPT_SCTY_ROLE_CONFLICT WHERE
TEMP_USER_ID = USER_NAME;
COMMIT;

EXCEPTION

WHEN OTHERS THEN

IF CUR_USER_ROLE_LIST%ISOPEN THEN
CLOSE CUR_USER_ROLE_LIST;
ELSIF CUR_CONFLICT_ROLES%ISOPEN THEN
CLOSE CUR_CONFLICT_ROLES;
ELSIF CUR_VALID_CONFLICT%ISOPEN THEN
CLOSE CUR_VALID_CONFLICT;
END IF;

END PROC_ROLE_CONFLICTS;
/

 

[synapsevampire]

Since it works for one connectivity and not the other, I'd guess that the driver is the issue, did you try native connectivity instead?

Go to Create New Connection->Oracle Server.

-k

 

[moonknightt]

Yes.. It works witht he Native driver. but the problem is that the company has ben going with Oracle provider for OLEDB and in the past Microsoft Oledb Provider for Oracle and in other to get them to change that i have to give them definite reasons why Oracle server might be the way to go.

 

[synapsevampire]

Because it's faster.

With SPs it's not as noticable because the SP will run in close to the same amount of time.

Not sure what the dilema is, could even be an unsupported data type.

I use Native connectivity or ODBC, not OleDB.

-k

 

[Turkbear]

Hi,
I suspect that the OleDb provider ( unless a very recent version) is having problems with the Pkg_Cr.CR_TYPE OUT parameter ( it is in line 1 and could easily be column 7)....

You can use the fact that it works ( usually a requirement) , uses existing software that is already installed and being used by the OleDb stuff, and is what Oracle recommends when accessing Stored Procedures as a reason to use it instead..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[moonknightt]

I really appreciate all your help. Keep up the Good work