PL/SQL Block to show the table details

Question

I am new to PL/SQL and I need your help..

I was created student table named(students) and inserted 10 rows successfully. I need to create a block when the user enter the student ID should be show the all details which match with the entered student ID.

SET SERVEROUTPUT ON;
ACCEPT inputID PROMPT 'Please enter the student ID to show the details'
DECLARE
B_students students%ROWTYPE;
BEGIN 
  SELECT *
  INTO B_students
  FROM students
  WHERE B_students.studentID= inputID;

dbms_output.put_line('Student details : ID:'
                     ||B_students.studentID
                     ||'Student Name'
                     ||B_students.studentName
                     ||'Student GPA'
                     ||B_students.studentGPA
                     ||'Student AGE'
                     ||B_students.studentAGE
                     ||'Student Gender'
                     ||B_students.studentGender);

END;

The error report : "inputID": invalid identifire PL/SQL: SQL Statment ignored 06550.00000 -"line %s, column %s:\n%s" *cause: usually a PL/SQL compilation error

Where are you running this? `ACCEPT` is supported in`SQL*Plus` only. — GMB, Sep 19 '20 at 17:29
Yes, that's the problem. This syntax is specific to SQLPlus. — GMB, Sep 19 '20 at 17:32
How I can create a Block to show the student details in the SQL developer? — Shrooq Awadh, Sep 19 '20 at 17:34
If you are running this in SQL Developer, why have you tagged PL/SQL Developer? — William Robertson, Sep 19 '20 at 22:16
@GMB - incorrect. The ACCEPT command, just like most if not all sqlplus commands, is fully implemented in SQL Developer as well. Try it for yourself and see. The OP's problem is not the ACCEPT command in SQL Dev, but rather his improper reference to the input variable. — EdStevens, Sep 20 '20 at 13:42

score 1 · Answer 1 · answered Sep 19 '20 at 23:34

The problem is that the identifier inputID is declared in SQL*Plus and your PL/SQL block cannot directly see it. To use it you need to precede it with an & character:

SET SERVEROUTPUT ON;
ACCEPT inputID PROMPT 'Please enter the student ID to show the details'

DECLARE
  B_students students%ROWTYPE;
BEGIN 
  SELECT *
    INTO B_students
    FROM students
    WHERE B_students.studentID = &inputID;

  dbms_output.put_line('Student details : ID:'
                       ||B_students.studentID
                       ||'Student Name'
                       ||B_students.studentName
                       ||'Student GPA'
                       ||B_students.studentGPA
                       ||'Student AGE'
                       ||B_students.studentAGE
                       ||'Student Gender'
                       ||B_students.studentGender);

END;
/

Note the use of &inputID instead of inputID in the WHERE clause of the SELECT statement.

Barbaros Özhan · Answer 2 · 2020-09-19T19:44:32.017

A function containing SYS_REFCURSOR might be created :

CREATE OR REPLACE FUNCTION get_std_rs( i_ID students.studentID%TYPE ) 
                    RETURN SYS_REFCURSOR IS
  v_recordset SYS_REFCURSOR;
  v_sql       VARCHAR2(32767);
BEGIN
  v_sql := 'SELECT *
              FROM students
             WHERE studentID = :inputID';

  OPEN v_recordset FOR v_sql USING i_ID;
  RETURN v_recordset;
END;
/

and then invoked from SQL Developer's Command Line :

VAR rc REFCURSOR
EXEC :rc := get_std_rs(123);
PRINT rc;

INTO Clause cannot be used for multiple returned rows, but one row only
Through the above method, no need to specify all returning columns individually

PL/SQL Block to show the table details

2 Answers2