how can see out put

Toad for Oracle
1

Dear all

i am new as oracle DBA and i had write procedure using Toad and i had compile successfully. now i want see the out put of this procedure. in this procedure taking 3 IN parameter and one syscursor OUT parameter.

to see output i had write a simple pice of code.

DECLARE
PTABLENAME VARCHAR2(200);
PCOLUMNID VARCHAR2(200);
PCOLUMNNAME VARCHAR2(200);
PWHERECLAUSE VARCHAR2(200);
CUR_REC SYS_REFCURSOR;
CUR_REC_row CUR_REC%ROWTYPE;

BEGIN
PTABLENAME := ‘TBL_MEETINGVENUE’;
PCOLUMNID := ‘MEETINGVENUEID’;
PCOLUMNNAME := ‘VENUENAME’;
PWHERECLAUSE := NULL;
– CUR_REC := NULL; Modify the code to initialize this parameter

HR.USP_COMBOFILL ( PTABLENAME, PCOLUMNID, PCOLUMNNAME, PWHERECLAUSE, CUR_REC );

OPEN CUR_REC;

LOOP

FETCH CUR_REC INTO CUR_REC_row;
EXIT WHEN CUR_REC%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(CUR_REC_row);

END LOOP;
CLOSE CUR_REC;

– Unable to resolve fields for REF CURSOR CUR_REC

COMMIT;
END;

but i am getting toad error:

ORA-06550: line 7, column 15:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 7, column 15:
PL/SQL: Item ignored
ORA-06550: line 19, column 3:
PLS-00382: expression is of wrong type
ORA-06550: line 19, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 23, column 23:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 23, column 4:
PL/SQL: SQL Statement ignored
ORA-06550: line 26, column 25:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 26, column 4:
PL/SQL: Statement ignored

please let me know how i see procedure output

thanks in advance…

2

Morning "Technology",

Message from: Technology

I don't know why, but your email came through in one large single chunk
of text. Very difficult to read.

Anyway, I suspect that this is the cause of your problem:

DBMS_OUTPUT.PUT_LINE(CUR_REC_row);

You need to output each individual column in the cur_rec_row.

So, if you have two columns, A and B, you need to do this:

DBMS_OUTPUT.PUT_LINE(CUR_REC_row.A);
DBMS_OUTPUT.PUT_LINE(CUR_REC_row.B);

Also, this thread on Yahoo might be of some help:

http://tech.groups.yahoo.com/group/toad/message/35368

Cheers,
Norm.

--

Cheers,
Norm. [TeamT]

3

Ok, here’s some more information.

If you write a function returning a sys_refcursor, like this:

CREATE OR REPLACE FUNCTION test_reff
RETURN sys_refcursor
AS
vRef sys_refcursor;
BEGIN
OPEN vRef FOR ‘select object_id, object_name from user_objects order
by object_name’;
RETURN vRef;
END;
/

Then running “SELECT test_reff from dual;” using F9 shows “Cursor” in
the results grid. If you double click on that cell of the grid, a new
window opens showing the contents of the cursor.

If you run the statement with F5 instead, the results of the SELECT will
be displayed in the output.

If, on the other hand, you write a procedure as follows:

CREATE OR REPLACE PROCEDURE test_ref(pRef OUT sys_refcursor)
AS
BEGIN
IF (pRef%isopen) THEN
CLOSE pRef;
END IF;

OPEN pRef FOR ‘select object_id, object_name from user_objects order
by object_name’;
RETURN;
END;
/

Then you need to write a bit of code to execute the procedure passing in
a cursor and then, print out the results in a loop:

DECLARE
fred sys_refcursor;
BEGIN
test_ref(fred);
– Loop here to dbms_output each column of the cursor.
END;

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk