Executing SQL via Oracle Stored Procedure passing variables

Question

I created the following Stored Procedure in Oracle:

create or replace
PROCEDURE APPUSERCT 
(
  PROJNAME IN VARCHAR2  
, WHEREDATE IN VARCHAR2 ,
cnt OUT long
) AS 
BEGIN
  select count(Distinct UPPER(field1)) into cnt from bs_log where application_name=PROJNAME and field1 is not null and log_type='info' || WHEREDATE;
END APPUSERCT;

In my php page WHEREDATE is set as:

$whereDate=" and (TO_DATE(created_on,'mm/dd/yyyy HH24:MI:SS')>= TO_DATE('".$startDate."','mm/dd/yyyy') AND TO_DATE(created_on,'mm/dd/yyyy HH24:MI:SS')<= TO_DATE('".$endDate."','mm/dd/yyyy'))";

I then Bind the parameters and values and call the SP:

$sql = 'BEGIN APPUSERCT(:projName,:whereDate,:cnt); END;';
$result = oci_parse($dbconn, $sql); 

oci_bind_by_name($result,':cnt',$totalRowCount,32);
oci_bind_by_name($result,':projName',$projName,32);
oci_bind_by_name($result,':whereDate',$whereDate,200);
oci_execute($result);

If no date range is entered on the php page by the user, then the WHEREDATE is blank in the SP and the SQL runs without any range or any additional syntax appended to the end of it in the Store Procedure (SP).

When the user enters a date range on the php page then the WHEREDATE param becomes:

and (TO_DATE(created_on,'mm/dd/yyyy HH24:MI:SS')>=TO_DATE('05/01/2015','mm/dd/yyyy') AND TO_DATE(created_on,'mm/dd/yyyy  HH24:MI:SS')<=TO_DATE('05/07/2015','mm/dd/yyyy'))

and it is appended to the end of the SQL in the SP. But whenever this happens my php page always returns 0 count from the SQL executed. No error, just 0 count.

If I try running the SQL directly in Oracle it's:

   select count(Distinct UPPER(field1)) as cnt from bs_log where application_name='Myweather' and field1 is not null and log_type='info' and (TO_DATE(created_on,'mm/dd/yyyy HH24:MI:SS')>= TO_DATE('05/01/2015','mm/dd/yyyy') AND TO_DATE(created_on,'mm/dd/yyyy HH24:MI:SS')<= TO_DATE('05/07/2015','mm/dd/yyyy'))

and I get results. I get a count back. But when calling it through the procedure I get 0. Can anyone see why?

Thanks!

What data type is created_on? I'd guess it's a date, you have different NLS settings from PHP, andyour implicit conversion isn't ending up with the correct value. If it is a date then you should not be calling to_date for it. Please clarify the type, and add sample values. — Alex Poole

Bob Jarvis - Reinstate Monica Bob Jarvis - Reinstate Monica · Accepted Answer · 2015-05-18T23:04:13

Well, a couple of problems show up.

First, I suggest that you change the declaration of the cnt parameter from LONG to NUMBER. LONG is not a numeric type in Oracle; instead, it's a type of LOB whose use is deprecated. If you really want to return a LOB use either BLOB or CLOB, whichever is appropriate.

Second, you can't pass in a character string containing part of a WHERE clause (the WHEREDATE parameter) and concatenate it to the end of an SQL statement. In this case you were contatenating the text in WHEREDATE to the string literal info, which I suspect is not what you had in mind. You should probably be using dynamic SQL, in a manner similar to the following:

create or replace PROCEDURE APPUSERCT(PROJNAME IN VARCHAR2,
                                      WHEREDATE IN VARCHAR2,
                                      cnt OUT NUMBER)
AS 
  strSql_text VARCHAR2(32767) := 'select count(Distinct UPPER(field1)) ' ||
                                 ' from bs_log where application_name=''' || PROJNAME ||
                                 ''' and field1 is not null and ' ||
                                 ' log_type=''info'' ' || WHEREDATE;
  csr SYS_REFCURSOR;
BEGIN
  OPEN csr FOR strSql_text;
  FETCH csr INTO cnt;
  CLOSE csr;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('APPUSERCT - exception : ' || SQLCODE || ' (' ||
                         SQLERRM || ')');
    RAISE;
END APPUSERCT;

So you build up the SQL statement as a text string, including your addition to the WHERE clause, then use the OPEN statement to open a cursor for that statement. Then the cursor is fetched, placing the result into cnt, then the cursor is closed, and finally the routine exits. I've included a default EXCEPTION handler as well - always a good idea in any routine.

Executing SQL via Oracle Stored Procedure passing variables

1 Answers