sql – Ellucian Banner API

sql – Ellucian Banner API

The small code snippet will not produce the error ORA-00907: missing
right parenthesis. That error is either from the context around the
code snippet, or within the code ran by calling f_query_one.

Below, I show

1. That double quotes around the values in the call do not work. In
   Oracle double quotes are a way to specify identifiers that do not
   follow the normal identifier rules. The error in this case is
   ORA-06576: not a valid function or procedure name
2. Using single quote works when calling a procedure.
3. When calling a function, an into clause needs to be specified to
   receive the return value. Otherwise the error ORA-06576: not a valid
   function or procedure name will be raised.

In no case is ORA-00907: missing right parenthesis raised
Please look at the wider context to find the cause of your error.

The following is the interaction with SQL*Plus. SQL> is a prompt for
new commands, with the lines below with numbers for additional lines
for that one command. Everything else is printed by SQL*Plus.

Make output from the dbms_output package visible.

SQL> set serveroutput on size unlimited

Create a procedure f_query_one taking the augments from your
sample. Types are guessed based on the values passed in your
sample. Note, I do not have an SB_COURSE schema. That will be a
difference from my samples below, and your code snippet.

SQL> create or replace procedure f_query_one(p_subj_code in varchar2
  2      , p_crse_numb in varchar2
  3      , p_eff_term in varchar2)
  4  is begin
  5      dbms_output.put_line(Hello World!);
  6  end f_query_one;
  7  /

Procedure created.

Call as posted in your question, and find that double quotes do not
work.

SQL> call f_query_one(
  2              p_subj_code => LMA,
  3              p_crse_numb => 400,
  4              p_eff_term  => 201203
  5              );
            p_subj_code => LMA,
                           *
ERROR at line 2:
ORA-06576: not a valid function or procedure name

Call with single quotes. Works!

SQL> call f_query_one(p_subj_code => LMA
  2      , p_crse_numb => 400
  3      , p_eff_term  => 201203);
Hello World!

Call completed.

Drop the procedure and create a function with the name f_query_one.

SQL> drop procedure f_query_one;

Procedure dropped.

SQL> create or replace function f_query_one(p_subj_code in varchar2
  2      , p_crse_numb in varchar2
  3      , p_eff_term in varchar2) return varchar2
  4  is begin
  5      return Hello World!;
  6  end f_query_one;
  7  /

Function created.

Call the function. But the function does exist, why does it say it does not?

SQL> call f_query_one(p_subj_code => LMA
  2      , p_crse_numb => 400
  3      , p_eff_term  => 201203);
call f_query_one(p_subj_code => LMA
     *
ERROR at line 1:
ORA-06576: not a valid function or procedure name

Create a bind variable. var is a SQL*Plus command. It is part of
neither the SQL or PL/SQL languages.

SQL> var so varchar2(20)

Add an into clause to save the value in the bind variable.

SQL> call f_query_one(p_subj_code => LMA
  2      , p_crse_numb => 400
  3      , p_eff_term  => 201203) into :so;

Call completed.

Print the bind variable. print is a SQL*Plus command. It is part of
neither the SQL or PL/SQL languages.

SQL> print so

SO
--------------------------------
Hello World!

Using single quotes should work. Are you sure the error is happening on this line? What you written in your comment looks good.

Strike that…..

You do not want to use the word call. If you are writing in pl/sql, then drop that word. If you are writing this in sql, then use exec instead.

sql – Ellucian Banner API