oci_execute identifier must be declared ORA-06550

Question

The procedure:

FUNCTION f_exists (
  p_pidm                      gobansr.gobansr_pidm%TYPE,
  p_num                       gobansr.gobansr_num%TYPE,
  p_rowid                     gb_common.internal_record_id_type DEFAULT NULL)
RETURN VARCHAR2 IS
--
  TYPE queryone_ref IS REF CURSOR;
  lv_cursor queryone_ref;
  lv_tempout VARCHAR2(1) := 'N';
BEGIN
--
-- Assign the cursor variable to the appropriate cursor
--
  IF p_rowid IS NOT NULL THEN
    OPEN lv_cursor for
    SELECT 'Y'
      FROM gobansr
     WHERE ROWID = p_rowid;
  ELSE
    OPEN lv_cursor for
    SELECT 'Y'
      FROM gobansr
     WHERE gobansr_pidm = p_pidm
       AND gobansr_num = p_num;
  END IF;
  FETCH lv_cursor INTO lv_tempout;
  CLOSE lv_cursor;
  RETURN lv_tempout;
END f_exists;

PHP Code to run/query the stored procedure:

$conn = oci_connect($username, $password, $db);
$sql = 'BEGIN "gb_pin_answer"."f_exists"(:p_pidm, :p_num); END;';
$stmt = oci_parse($conn, $sql);

if (!$conn)
  exit("DB did not connect.");

$p_pidm = 36706;
$p_num = 1;

oci_bind_by_name($stmt,':p_pidm',$p_pidm,5);
oci_bind_by_name($stmt,':p_num',$p_num,1);

if (!oci_execute($stmt))
  exit("Procedure Failed.");

oci_commit($conn);
oci_close($conn);

I have verified with the DBA that I have execute privileges to the stored procedure and the PHP successfully connects to the database server.

It returns this error:

Message: oci_execute(): ORA-06550: line 1, column 8: PLS-00201: identifier 'gb_pin_answer.f_exists' must be declared ORA-06550: line 1, column 8: PL/SQL: Statement ignored

UPDATE:

I was able to run: select gb_pin_answer.f_exists('36706','2') as RESULT from dual and it returned Y

Now I need to figure out how to go about using this stored procedure in my php:

PROCEDURE p_create(
  p_pidm                      gobansr.gobansr_pidm%TYPE,
  p_num                       gobansr.gobansr_num%TYPE,
  p_gobqstn_id                gobansr.gobansr_gobqstn_id%TYPE DEFAULT NULL,
  p_qstn_desc                 gobansr.gobansr_qstn_desc%TYPE DEFAULT NULL,
  p_ansr_desc                 gobansr.gobansr_ansr_desc%TYPE,
  p_ansr_salt                 gobansr.gobansr_ansr_salt%TYPE,
  p_user_id                   gobansr.gobansr_user_id%TYPE DEFAULT gb_common.f_sct_user,
  p_data_origin               gobansr.gobansr_data_origin%TYPE,
  p_rowid_out             OUT gb_common.internal_record_id_type) IS
--
  lv_gobansr_rec    gobansr%ROWTYPE;
  error_message     gb_common_strings.err_type;

  lv_hashed_answer  gobansr.gobansr_ansr_Desc%type;
  lv_salt           gobansr.gobansr_ansr_salt%type;
BEGIN
--
-- Make sure the record doesn't already exist
--
  IF (gb_pin_answer.f_exists(p_pidm,p_num)='Y') THEN
    error_message :=  gb_common_strings.f_append_error(error_message,
                        gb_pin_answer_strings.f_get_error('RECORD_EXISTS'));
  END IF;
--
  IF (error_message IS NOT NULL) THEN
    RAISE_APPLICATION_ERROR(gb_common_strings.ERR_CODE, error_message);
  END IF;

  lv_salt := gspcrpt.F_GET_SALT(length(replace(p_ansr_desc,' ')));
  gspcrpt.p_saltedhash(lower(replace(p_ansr_desc,' ')),lv_salt,lv_hashed_answer);

--
-- Execute business rules
--
  gb_pin_answer_rules.p_validate(
    p_pidm               => p_pidm,
    p_num                => p_num,
    p_gobqstn_id         => p_gobqstn_id,
    p_qstn_desc          => p_qstn_desc,
    p_ansr_desc          => p_ansr_desc,
    p_ansr_salt          => lv_salt,
    p_user_id            => p_user_id,
    p_data_origin        => p_data_origin);
--
-- This is where the local user exit call, if defined, will occur.
--
  /* -- No local call
    p_pidm                     => p_pidm,
    p_num                      => p_num,
    p_gobqstn_id               => p_gobqstn_id,
    p_qstn_desc                => p_qstn_desc,
    p_ansr_desc                => p_ansr_desc,
    p_ansr_salt                => lv_salt,
    p_user_id                  => p_user_id,
    p_data_origin              => p_data_origin);
*/
--
-- Build the DML record
--
  lv_gobansr_rec := f_build_gobansr_rec(
    p_pidm               => p_pidm,
    p_num                => p_num,
    p_gobqstn_id         => p_gobqstn_id,
    p_qstn_desc          => p_qstn_desc,
    p_ansr_desc          => lv_hashed_answer,
    p_ansr_salt          => lv_salt,
    p_user_id            => p_user_id,
    p_data_origin        => p_data_origin);
--
-- Set the activity date
--
  lv_gobansr_rec.gobansr_activity_date := SYSDATE;
--
-- Delegate to the DML layer
--

  dml_gobansr.p_insert(lv_gobansr_rec, p_rowid_out);
--
-- NOTE:  Banner Messaging Support logic is only called after a
--        successful DML operation
-- Check if messaging is enabled/licensed for this business entity.
--
  IF (gb_event.f_entity_publishable(gb_event.baseline_ind,
                                        M_ENTITY_NAME)) THEN
--    Register this business entity with messaging support.
    gb_pin_answer_rules.p_register_entity(
      p_operation_type     => gb_event.CREATE_OPERATION,
      p_pidm               => p_pidm,
      p_num                => p_num,
      p_gobqstn_id         => p_gobqstn_id,
      p_qstn_desc          => p_qstn_desc,
      p_ansr_desc          => p_ansr_desc,
      p_ansr_salt          => p_ansr_salt,
      p_user_id            => p_user_id,
      p_data_origin        => p_data_origin,
      p_internal_record_id => p_rowid_out);
  END IF;
END p_create;

Are you able to call the function from SQLPlus when logged in with the same credentials? Also, if you're calling the function from SQLPlus you need to do it something like this: SELECT f_exists(arg, arg) FROM DUAL. That may be worth trying in your PHP code, instead of the anonymous block, but that's just a guess. — Ed Gibbs
I ran it in Oracle SQL Developer as the same user, (on a mac) and the select works. I was originally trying to use gb_pin_answer.p_create, then decided to take baby steps and started with f_exists. I tried running it as select from dual with no luck either. — Brad
in your sql, you call f_exists with 2 varchar2 parameters, while you use numerical arguments in your php/oci code - maybe the auto-conversion fails. also try to call f_exists from sqlplus with the same parameters as you do inside your code (( '36706','1' ) instead of ( '36706','2' )). — collapsar
I was able to get the f_exists to work, using: select gb_pin_answer.f_exists(36706, 2) from dual; — Brad

HAL 9000 HAL 9000 · Accepted Answer · 2013-05-02T16:24:08

You've defined a function but the PL/SQL you're using implies you're calling a procedure.

The PL/SQL should look like this:

$sql = 'BEGIN :f := "gb_pin_answer"."f_exists"(:p_pidm, :p_num); END;';

oci_execute identifier must be declared ORA-06550

1 Answers