Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to use stored procedure with many return results and variable with perl

689502Mar 24 2009
Hi everybody,
i´m writtting now a Perl programm, wich use a oracle stored procedure with more than 1 result and 1 variable(I have to return 2 variable fpr each result). I don´t now how I can get it.I already search the web but I didn´t find.
My example:

PROCEDURE get_projects_and_sub_projects (
v_project_id IN INTEGER,
v_project_c_id OUT INTEGER,
v_project_id_find OUT VARCHAR2
)
IS
BEGIN

SELECT c_id, proj_id
INTO
v_project_c_id,
v_project_id_find
FROM t_projet
WHERE t_projet .ksa_pro_art_kbz = 'KU'
AND t_projet.proj_id LIKE v_project_id || '%';



EXCEPTION
WHEN NO_DATA_FOUND
THEN

v_project_c_id := NULL;
v_project_id_find := NULL;
WHEN OTHERS
THEN
kmessages.error (NULL,
'get_projects_and_sub_projects',
'Project-Name: ' || v_project_id,
'Errornumber: '
|| SQLCODE
|| ' Error: '
|| SQLERRM,
TRUE,
TRUE
);
raise_application_error (-20001,
'Error '
|| SQLCODE
|| ' get_projects_and_sub_projects: '
|| SQLERRM,
TRUE
);

END get_projects_and_sub_projects;


in Perl Program:

sub get_projects_unterprojects_name($$){
my ($db_handle, $proj_name_id) = @_; #$db_handle ist the DB Connection return value

my $db_proj_c_id;
my $db_proj_name;


eval{ my $csr = $db_handle->prepare(q{
BEGIN
pro_doc_ber.get_projects_and_sub_projects(:proj_name_id, :db_proj_c_id, :db_proj_name);
END;
});

# parameter value
$csr->bind_param(":proj_name_id", $proj_name_id);

# return values
$csr->bind_param_inout(":db_proj_c_id", \$db_proj_c_id, 11);
$csr->bind_param_inout(":db_proj_name", \$db_proj_name, 20);

$csr->execute(); };

But this didn´t work. Could somebody give me some idea?
Thank you
Felx
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 21 2009
Added on Mar 24 2009
0 comments
277 views