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!

Having problems returning multiple rows with parameterised CURSOR and IF, ELSE DBMS_OUPUT

2652078May 23 2014 — edited May 23 2014

Hi folks

I'm using 11g Express Edition 11.2.0.2.0.

I'm trying to return the name of a student, all of their tests taken, grade, passing grade and whether this is a pass or fail.

In the code i've 'cobbled' together, if the student has failed it works. However, if the student has passed it just returns 'Result = Pass'  and fails to return their name, tests, grade and passing grade.

A second issue (and please let me know if i need to post this in a separate thread) is if a student has carried out multiple tests, it only returns one.

Thanks for any advice you can give me.

SET SERVEROUTPUT ON

DECLARE

  CURSOR c_pass_fail_cursor

    (p_studentid number) IS

    SELECT students.firstname,

      test_history.score,

      test_id.test_name,

      test_id.passing_grade

    FROM students

    INNER JOIN test_history

      ON students.STUDENT_ID = test_history.student_id

    INNER JOIN test_id

      ON test_id.test_id = test_history.test_id

    WHERE students.student_id = p_studentid

    AND test_history.SCORE < test_id.passing_grade;

   

  v_name students.firstname%type;

  v_score test_history.SCORE%type;

  v_test test_id.test_name%type;

  v_passing test_id.passing_grade%type;

  v_result varchar2(4); 

   

BEGIN

  OPEN c_pass_fail_cursor (1);

  LOOP

    FETCH c_pass_fail_cursor INTO v_name, v_score, v_test, v_passing;

  EXIT WHEN c_pass_fail_cursor%notfound;

  END LOOP;

  CLOSE c_pass_fail_cursor;

 

  IF v_score < v_passing then

    v_result := 'Fail';

    DBMS_OUTPUT.PUT_LINE (v_name || ' ' || v_score || ' ' || v_test || ' ' || v_passing || ' ' || 'Result = ' || v_result); 

  ELSE

    v_result := 'Pass';

    DBMS_OUTPUT.PUT_LINE (v_name || ' ' || v_score || ' ' || v_test || ' ' || v_passing || ' ' || 'Result = ' || v_result);

  END IF;

 

END;

/

This post has been answered by David Berger on May 23 2014
Jump to Answer

Comments

odie_63
PS: I don't want to use an hardcoding
Why?

Do you have such a scenario that you don't know the procedure name you're trying to compile?

Edited by: odie_63 on 11 janv. 2013 10:45
padders
Simple answer is you can't - without generating some additional metadata for the package.

One way to generate the metadata would appear to be PL/Scope in 11g but I haven't actually tried that to see how it pans out.
odie_63
There's $$PLSQL_UNIT compiler directive but it doesn't reference subprograms in a package, only the package itself :
SQL> create or replace package mypck is
  2    procedure myprc;
  3  end;
  4  /
 
Package created
 
SQL> 
SQL> create or replace package body mypck is
  2    procedure myprc is
  3    begin
  4      dbms_output.put_line('I''m in '||$$PLSQL_UNIT);
  5    end;
  6  end;
  7  /
 
Package body created
 
SQL> exec mypck.myprc;
 
I'm in MYPCK
 
PL/SQL procedure successfully completed
 
bencol
This might help you understand the problem:
http://tkyte.blogspot.co.uk/2009/10/httpasktomoraclecomtkytewhocalledme.html

Buit I usually hardcode the proc name into a variable and use that for logging.
unknown-7404
You've posted enough to know that you need to provide your 4 digit Oracle version (result of SELECT * FROM V$VERSION).
>
is it possible to obtain a called procedure/function name within package?

For a measuring and tracing purpose, I would like to store an info at the beginning of each procedure/function in package with timestamp + additional details if needed.
>
I usually use this method

1. Create a SQL type for logging information
2. Put the package name into a constant in the package spec
3. Add a line to each procedure/function for the name.

Sample package spec
	/******************************************************************************
	 * Constants and package variables
	 ******************************************************************************/
		gc_pk_name			CONSTANT VARCHAR2(30) := 'PK_TEST';
Sample procedure code in package
	 PROCEDURE P_TEST_INIT
	 IS
	   c_proc_name CONSTANT VARCHAR2(80)  := 'P_TEST_INIT';
	   v_log_info  TYPE_LOG_INFO := TYPE_LOG_INFO(gc_pk_name, c_proc_name); -- create the log type instance

	 BEGIN
		NULL; -- code goes here
	 EXCEPTION
	 WHEN ??? THEN
		v_log_info.log_code := SQLCODE;  -- add info to the log type
		v_log_info.log_message := SQLERRM;
		v_log_info.log_time    := SYSDATE;
		pk_log.p_log_error(v_log_info);
                                raise;
	 END P_PK_TEST_INIT;
Sample SQL type
DROP TYPE TYPE_LOG_INFO;

CREATE OR REPLACE TYPE TYPE_LOG_INFO AUTHID DEFINER AS OBJECT (
/* *****************************************************************************
 *  NAME:      TYPE_LOG_INFO
 *  PURPOSE:   Holds info used by PK_LOG package to log errors.
 *
 *             Using a TYPE instance keeps the procedures and functions
 *             independent of the logging mechanism.
 *
 *             If new logging features are needed a SUB TYPE can be derived
 *             from this base type to add the new functionality without
 *             breaking any existing code.
 *
 *  REVISIONS:
 *  Ver        Date        Author           Description
 *
 *   1.00      mm/dd/yyyy  me               Initial Version.
 ******************************************************************************/
    PACKAGE_NAME  VARCHAR2(80),
    PROC_NAME     VARCHAR2(80),
    STEP_NUMBER   NUMBER,
    LOG_LEVEL   VARCHAR2(10),
    LOG_CODE    NUMBER,
    LOG_MESSAGE VARCHAR2(1024),
    LOG_TIME    TIMESTAMP,
    CONSTRUCTOR FUNCTION type_log_info (p_package_name IN VARCHAR2 DEFAULT 'Uninitialized',
                                        p_proc_name IN VARCHAR2 DEFAULT 'Uninitialized',
                                        p_step_number IN NUMBER DEFAULT 1,
                                        p_LOG_level IN VARCHAR2 DEFAULT 'Uninit',
                                        p_LOG_code IN NUMBER DEFAULT -1,
                                        p_LOG_message IN VARCHAR2 DEFAULT 'Uninitialized',
                                        p_LOG_time IN DATE DEFAULT SYSDATE)
                RETURN SELF AS RESULT
  ) NOT FINAL;
/

DROP TYPE BODY TYPE_LOG_INFO;

CREATE OR REPLACE TYPE BODY TYPE_LOG_INFO IS
    CONSTRUCTOR FUNCTION type_log_info (p_package_name IN VARCHAR2 DEFAULT 'Uninitialized',
                                        p_proc_name IN VARCHAR2 DEFAULT 'Uninitialized',
                                        p_step_number IN NUMBER DEFAULT 1,
                                        p_LOG_level IN VARCHAR2 DEFAULT 'Uninit',
                                        p_LOG_code IN NUMBER DEFAULT -1,
                                        p_LOG_message IN VARCHAR2 DEFAULT 'Uninitialized',
                                        p_LOG_time IN DATE DEFAULT SYSDATE)
     RETURN SELF AS RESULT IS
    BEGIN
      self.package_name  := p_package_name;
      self.proc_name     := p_proc_name;
      self.step_number   := p_step_number;
      self.LOG_level   := p_LOG_level;
      self.LOG_code    := p_LOG_code;
      self.LOG_message := p_LOG_message;
      self.LOG_time    := p_LOG_time;
      RETURN;
    END;
END;
/
SHO ERR
Edited by: rp0428 on Jan 11, 2013 10:35 AM after 1st cup of coffee ;)
odie_63
rp0428 wrote:
You would want the $$PLSQL_UNIT directive.
And as shown in my post, this doesn't apply to subprograms in a package, which seems to be precisely what OP is after.
unknown-7404
Thanks - brain f*** - didn't even see your post somehow. I update my post with the old method.
1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 20 2014
Added on May 23 2014
3 comments
1,661 views