Skip to main content

[SOLVED] Calling Oracle Stored Procedure

Author: kevin_darbin@hargreaveslansdown.co.uk (kevindarbin)

Hello, I'm trying to call a Stored Procedure in Oracle using a Stored Procedure Component, and am getting an error. The following should describe the steps that I have taken:- - Created a Signature called "SP_GENERATE_SEQUENCE" as a Stored Procedure Component and marked the component as "Stateless" - Created an Operation in the Signature called "SP_GENERATE_SEQUENCE", with an in parameter called "SEQUENCE_NAME" of type string, and an out parameter called "SEQUENCE_NUMBER" of type numeric - Created an entry in the [SERVICES_EXEC] of my asn file which is "SP_GENERATE_SEQUENCE $UD3:SP_GENERATE_SEQUENCE".  $UD3 points to my Oracle database, and logs into the schema containing the Stored Procedure - The call I am making in Uniface is: activate "SP_GENERATE_SEQUENCE".SP_GENERATE_SEQUENCE("ID_ADOBE_ERROR_ID_SEQ", v_sequence) When the above is executed, I get the following Oracle error: ICCDRV-ORA-ERR Call ICC component failed, ORA-06550: line 1, column 28: PLS-00225: subprogram or cursor 'SP_GENERATE_SEQUENCE' reference is out of scope ORA-06550: line 1, column 7: PL/SQL: Statement ignored   And just to complete the information, the SQL to generate the Stored Procedure is: create or replace PROCEDURE sp_generate_sequence (i_sequence_name IN VARCHAR2, o_sequence_number OUT NUMBER) AS   v_sql_statement VARCHAR2(1000); BEGIN   v_sql_statement := 'select ' || i_sequence_name ||'.NEXTVAL from dual';                 EXECUTE IMMEDIATE v_sql_statement INTO o_sequence_number; END;   I haven't tried to do this before, so there is a chance that I have missed something out somewhere.  Any help given would be much appreciated.   Thanks, Kevin

[SOLVED] Calling Oracle Stored Procedure

Author: kevin_darbin@hargreaveslansdown.co.uk (kevindarbin)

Hello, I'm trying to call a Stored Procedure in Oracle using a Stored Procedure Component, and am getting an error. The following should describe the steps that I have taken:- - Created a Signature called "SP_GENERATE_SEQUENCE" as a Stored Procedure Component and marked the component as "Stateless" - Created an Operation in the Signature called "SP_GENERATE_SEQUENCE", with an in parameter called "SEQUENCE_NAME" of type string, and an out parameter called "SEQUENCE_NUMBER" of type numeric - Created an entry in the [SERVICES_EXEC] of my asn file which is "SP_GENERATE_SEQUENCE $UD3:SP_GENERATE_SEQUENCE".  $UD3 points to my Oracle database, and logs into the schema containing the Stored Procedure - The call I am making in Uniface is: activate "SP_GENERATE_SEQUENCE".SP_GENERATE_SEQUENCE("ID_ADOBE_ERROR_ID_SEQ", v_sequence) When the above is executed, I get the following Oracle error: ICCDRV-ORA-ERR Call ICC component failed, ORA-06550: line 1, column 28: PLS-00225: subprogram or cursor 'SP_GENERATE_SEQUENCE' reference is out of scope ORA-06550: line 1, column 7: PL/SQL: Statement ignored   And just to complete the information, the SQL to generate the Stored Procedure is: create or replace PROCEDURE sp_generate_sequence (i_sequence_name IN VARCHAR2, o_sequence_number OUT NUMBER) AS   v_sql_statement VARCHAR2(1000); BEGIN   v_sql_statement := 'select ' || i_sequence_name ||'.NEXTVAL from dual';                 EXECUTE IMMEDIATE v_sql_statement INTO o_sequence_number; END;   I haven't tried to do this before, so there is a chance that I have missed something out somewhere.  Any help given would be much appreciated.   Thanks, Kevin

In an update to my issue..... I managed to get it working by creating the signature with the same name as my database schema.  So instead of "SP_GENERATE_SEQUENCE" I called it "ADOBE_SMSTAGE".  When I then do the following:- activate “ADOBE_SMSTAGE”.SP_GENERATE_SEQUENCE(“ID_ADOBE_ERROR_ID_SEQ”, v_sequence) It works. The only problem then is that if I am working with multiple schemas for multiple environments, I then need to create a signature for each one.  As we have over 30 test environments, this will become slightly unmanageable.  Anyone have any ideas on how I can get around that?   Thanks, Kevin


Author: kevindarbin (kevin_darbin@hargreaveslansdown.co.uk)

[SOLVED] Calling Oracle Stored Procedure

Author: kevin_darbin@hargreaveslansdown.co.uk (kevindarbin)

Hello, I'm trying to call a Stored Procedure in Oracle using a Stored Procedure Component, and am getting an error. The following should describe the steps that I have taken:- - Created a Signature called "SP_GENERATE_SEQUENCE" as a Stored Procedure Component and marked the component as "Stateless" - Created an Operation in the Signature called "SP_GENERATE_SEQUENCE", with an in parameter called "SEQUENCE_NAME" of type string, and an out parameter called "SEQUENCE_NUMBER" of type numeric - Created an entry in the [SERVICES_EXEC] of my asn file which is "SP_GENERATE_SEQUENCE $UD3:SP_GENERATE_SEQUENCE".  $UD3 points to my Oracle database, and logs into the schema containing the Stored Procedure - The call I am making in Uniface is: activate "SP_GENERATE_SEQUENCE".SP_GENERATE_SEQUENCE("ID_ADOBE_ERROR_ID_SEQ", v_sequence) When the above is executed, I get the following Oracle error: ICCDRV-ORA-ERR Call ICC component failed, ORA-06550: line 1, column 28: PLS-00225: subprogram or cursor 'SP_GENERATE_SEQUENCE' reference is out of scope ORA-06550: line 1, column 7: PL/SQL: Statement ignored   And just to complete the information, the SQL to generate the Stored Procedure is: create or replace PROCEDURE sp_generate_sequence (i_sequence_name IN VARCHAR2, o_sequence_number OUT NUMBER) AS   v_sql_statement VARCHAR2(1000); BEGIN   v_sql_statement := 'select ' || i_sequence_name ||'.NEXTVAL from dual';                 EXECUTE IMMEDIATE v_sql_statement INTO o_sequence_number; END;   I haven't tried to do this before, so there is a chance that I have missed something out somewhere.  Any help given would be much appreciated.   Thanks, Kevin

Hi Kevin, Thanks for your update. It would be possible to set the literal name of a operation of a SSP (Service Stored Procedure) signature to "SchemaName.ProcedureName". This, however, does not work at this moment (when using version 9.5.01 + E114 and higher, or version 9.6.01 + X104 and higher) because of BUG 30984. We will fix this issue in one of our future patches. But in case this problem is time critical for you then please log a call with support. Hope this helps. Kind regards, Daniel


Author: diseli (daniel.iseli@uniface.com)

[SOLVED] Calling Oracle Stored Procedure

Author: kevin_darbin@hargreaveslansdown.co.uk (kevindarbin)

Hello, I'm trying to call a Stored Procedure in Oracle using a Stored Procedure Component, and am getting an error. The following should describe the steps that I have taken:- - Created a Signature called "SP_GENERATE_SEQUENCE" as a Stored Procedure Component and marked the component as "Stateless" - Created an Operation in the Signature called "SP_GENERATE_SEQUENCE", with an in parameter called "SEQUENCE_NAME" of type string, and an out parameter called "SEQUENCE_NUMBER" of type numeric - Created an entry in the [SERVICES_EXEC] of my asn file which is "SP_GENERATE_SEQUENCE $UD3:SP_GENERATE_SEQUENCE".  $UD3 points to my Oracle database, and logs into the schema containing the Stored Procedure - The call I am making in Uniface is: activate "SP_GENERATE_SEQUENCE".SP_GENERATE_SEQUENCE("ID_ADOBE_ERROR_ID_SEQ", v_sequence) When the above is executed, I get the following Oracle error: ICCDRV-ORA-ERR Call ICC component failed, ORA-06550: line 1, column 28: PLS-00225: subprogram or cursor 'SP_GENERATE_SEQUENCE' reference is out of scope ORA-06550: line 1, column 7: PL/SQL: Statement ignored   And just to complete the information, the SQL to generate the Stored Procedure is: create or replace PROCEDURE sp_generate_sequence (i_sequence_name IN VARCHAR2, o_sequence_number OUT NUMBER) AS   v_sql_statement VARCHAR2(1000); BEGIN   v_sql_statement := 'select ' || i_sequence_name ||'.NEXTVAL from dual';                 EXECUTE IMMEDIATE v_sql_statement INTO o_sequence_number; END;   I haven't tried to do this before, so there is a chance that I have missed something out somewhere.  Any help given would be much appreciated.   Thanks, Kevin

Resolution.... I ended up creating a package in Oracle, and had by procedure within that package. The Signature then has the same name as the package, and the Operation has the same name as the procedure.  This appears to work, and solves all of my problems ! Hopefully this post may be of use to someone else at some point :-)   Kevin


Author: kevindarbin (kevin_darbin@hargreaveslansdown.co.uk)

[SOLVED] Calling Oracle Stored Procedure

Author: kevin_darbin@hargreaveslansdown.co.uk (kevindarbin)

Hello, I'm trying to call a Stored Procedure in Oracle using a Stored Procedure Component, and am getting an error. The following should describe the steps that I have taken:- - Created a Signature called "SP_GENERATE_SEQUENCE" as a Stored Procedure Component and marked the component as "Stateless" - Created an Operation in the Signature called "SP_GENERATE_SEQUENCE", with an in parameter called "SEQUENCE_NAME" of type string, and an out parameter called "SEQUENCE_NUMBER" of type numeric - Created an entry in the [SERVICES_EXEC] of my asn file which is "SP_GENERATE_SEQUENCE $UD3:SP_GENERATE_SEQUENCE".  $UD3 points to my Oracle database, and logs into the schema containing the Stored Procedure - The call I am making in Uniface is: activate "SP_GENERATE_SEQUENCE".SP_GENERATE_SEQUENCE("ID_ADOBE_ERROR_ID_SEQ", v_sequence) When the above is executed, I get the following Oracle error: ICCDRV-ORA-ERR Call ICC component failed, ORA-06550: line 1, column 28: PLS-00225: subprogram or cursor 'SP_GENERATE_SEQUENCE' reference is out of scope ORA-06550: line 1, column 7: PL/SQL: Statement ignored   And just to complete the information, the SQL to generate the Stored Procedure is: create or replace PROCEDURE sp_generate_sequence (i_sequence_name IN VARCHAR2, o_sequence_number OUT NUMBER) AS   v_sql_statement VARCHAR2(1000); BEGIN   v_sql_statement := 'select ' || i_sequence_name ||'.NEXTVAL from dual';                 EXECUTE IMMEDIATE v_sql_statement INTO o_sequence_number; END;   I haven't tried to do this before, so there is a chance that I have missed something out somewhere.  Any help given would be much appreciated.   Thanks, Kevin

Hi Kevin, Thanks for sharing your solution - really appreciated. And it's good to hear that you could resolve the problem you were facing.   Daniel


Author: diseli (daniel.iseli@uniface.com)
Terms & ConditionsAccessibility statement