Q
Problem solve Get help with specific problems with your technologies, process and projects.

PL/SQL program for complex 8i output

I'm writing a PL/SQL program, which I managed to upload to the Oracle8i server. The Oracle DBA Studio program shows that the both packages are valid. So there should not be anything wrong with the packages. I tested the select statement in SQL*Plus program, which returned the results without problem. But when I execute the following command: exec pdm.tuoterakenne_pkg.tuoterakenne(42391); I get following error message:

SQL> exec pdm.tuoterakenne_pkg.tuoterakenne(42391);
BEGIN pdm.tuoterakenne_pkg.tuoterakenne(42391); END;
*

ERROR at line 1
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TUOTERAKENNE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I put the schema name before the package name, because I log in as SYSTEM user. And the actual PDM database situated in the PDM schema. The package I situated also in the PDM schema so that I don't have to make any synonyms for the tables.

My packages look like:

CREATE OR REPLACE PACKAGE pdm.tuoterakenne_pkg AS
TYPE tblPivId IS TABLE OF NUMBER(6) INDEX BY BINARY_INTEGER;
TYPE tblPivFname IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;

PROCEDURE tuoterakenne (i_PiId IN NUMBER, o_PivId OUT tblPivId, o_PivFname
OUT tblPivFname);
END tuoterakenne_pkg; /

CREATE OR REPLACE PACKAGE BODY pdm.tuoterakenne_pkg
AS
PROCEDURE tuoterakenne
(i_PiId IN NUMBER,
o_PivId OUT tblPivId,
o_PivFname OUT tblPivFname)
IS
CURSOR cur_tuoterakenne (curPiId VARCHAR2) IS
SELECT PRODUCTITEMVERFILE.PIVID,
PRODUCTITEMVERFILE.PIVFNAME
FROM PDM.PDM_PRODUCTITEM PRODUCTITEM, PDM.PDM_PRODUCTITEMVERFILE
PRODUCTITEMVERFILE WHERE (PRODUCTITEM.PIID=(curPiId)) AND
(PRODUCTITEMVERFILE.PIVFNAME=PRODUCTITEM.PINAME);
RecordCount NUMBER DEFAULT 0;
BEGIN
FOR curRecTuoterakenne IN cur_tuoterakenne(i_PiId) LOOP RecordCount:=
RecordCount + 1; o_PivId(RecordCount):= curRecTuoterakenne.pivId; o_PivFname
(RecordCount):= curRecTuoterakenne.pivfname; END LOOP; END
tuoterakenne; END tuoterakenne_pkg; /

I tried to look for an answer on the Internet. There are some explanations but it doesn't help me. If I get this package to work, I will modify it to get more complex output.
The problem is that you are not providing any OUT variables in the call to the package. All you did was this:

SQL> exec pdm.tuoterakenne_pkg.tuoterakenne(42391);

Your procedure takes three parameters: 1 IN parameter (i_PiId) and 2 OUT parameters (o_PivId and o_PivFname). You must provide two variables to capture the OUT parameters. SQL*Plus doesn't provide a variable type to capture an output PL/SQL table so you'd have to write a short anonymous PL/SQL block to test it as follows:

declare
  v_out1    pdm.tuoterakenne_pkg.tuoterakenne.tblPivID%TYPE ;
  v_out2    pdm.tuoterakenne_pkg.tuoterakenne.tblPivFname%TYPE ;
begin
  pdm.tuoterakenne_pkg.tuoterakenne(42391, v_out1, v_out2);
  . . . . .
  . . . . .
end ;
/

You could also use dbms_output to display the results that come back, but you'd have to create a little loop to read all the "rows" from the PL/SQL table variable and display them one at a time.

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close