I'm trying to send log to remote oracle db thru PL/SQL. It is sent to procedure RECORD_MANAGEMENT.ADD_INTO (@bottom of the post). This procedure serves sort of as a entry point.
Challenge is in 4 object types that procedure expects.
Two things I've tried:
oci_execute(): ORA-06550: line 1, column 7: PLS-00306:
oci_bind_array_by_name(): ORA-01036: illegal variable name/number
UPDATE #1 3. Similar to second way; this inserts variables as strings in raw PL
//for all 4 object types it reports this err
oci_execute(): ORA-06550: line 2, column 11:
PLS-00201: identifier 'PROJ_DB.EMPLOYEE_TYPE' must be declared
ORA-06550: line 2, column 11:
//on three occasions this is also stated
PL/SQL: Item ignored
ORA-06550: line 19, column 3:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
If anyone has any suggestion, it would be very helpful! Just recently started 'oracling'.
Example for first try;
$var1 = ["1111", null, null, null, null, null, null, null, null, null, null, null];
$var2 = 'String 1';
$var3 = ["String 2", null, null, null, null, null, null, null, null, null];
$var4 = '2020-06-03 07:00:00';
$var5 = ["String 3",null,null];
$var6 = ["String 4",null];
$var7 = '222222';
$var8 = NULL;
$var9 = NULL;
$var10= 'String 5';
$var11= '333333';
$var12= '444444';
$var13= '555555';
$var14= '666666';
$var15= NULL;
$stmt = oci_parse($conn, "BEGIN PROJ_DB.RECORD_MANAGEMENT.ADD_INTO(:val1, :val2, :val3, :val4, :val5, :val6, :val7, :val8, :val9, :val10, :val11, :val12, :val13, :val14, :val15); END;");
oci_bind_array_by_name($stmt,':var1',$var1,count($var1),-1);
oci_bind_by_name($stmt,':var2', $var2);
oci_bind_array_by_name($stmt,':var3', $var3,count($var3),-1);
oci_bind_by_name($stmt,':var4', $var4);
oci_bind_array_by_name($stmt,':var5', $var5,count($var5),-1);
oci_bind_array_by_name($stmt,':var6', $var6,count($var6),-1);
oci_bind_by_name($stmt,':var7', $var7);
oci_bind_by_name($stmt,':var8', $var8);
oci_bind_by_name($stmt,':var9', $var9);
oci_bind_by_name($stmt,':var10', $var10);
oci_bind_by_name($stmt,':var11', $var11);
oci_bind_by_name($stmt,':var12', $var12);
oci_bind_by_name($stmt,':var13', $var13);
oci_bind_by_name($stmt,':var14', $var14);
oci_bind_by_name($stmt,':var15', $var15);
oci_execute($stmt);
Example for second try (Tried different syntax A:a A:=a ).
$proc="
DECLARE
VAR_A PROJ_DB.EMPLOYEE_TYPE;
VAR_B VARCHAR2(200);
VAR_C PROJ_DB.PROJECT_TYPE;
VAR_D DATE;
VAR_E PROJ_DB.SYSTEM_TYPE;
VAR_F PROJ_DB.ACTION_TYPE;
VAR_G NUMBER;
VAR_H VARCHAR2(200);
VAR_I VARCHAR2(200);
VAR_J CLOB;
VAR_K NUMBER;
VAR_L NUMBER;
VAR_M NUMBER;
VAR_N NUMBER;
VAR_O VARCHAR2(200);
BEGIN
VAR_A var1;
VAR_B var2;
VAR_C var3;
VAR_D var4;
VAR_E var5;
VAR_F var6;
VAR_G var7;
VAR_H var8;
VAR_I var9;
VAR_J var10;
VAR_K var11;
VAR_L var12;
VAR_M var13;
VAR_N var14;
VAR_O var15;
RECORD_MANAGEMENT.ADD_INTO(
VAR_A => VAR_A,
VAR_B => VAR_B,
VAR_C => VAR_C,
VAR_D => VAR_D,
VAR_E => VAR_E,
VAR_F => VAR_F,
VAR_G => VAR_G,
VAR_H => VAR_H,
VAR_I => VAR_I,
VAR_J => VAR_J,
VAR_K => VAR_K,
VAR_L => VAR_L,
VAR_M => VAR_M,
VAR_N => VAR_N,
VAR_O => VAR_O
);
COMMIT;
END;
";
//variables same as in first example
$stmt = oci_parse($conn, $proc);
//binding also same
oci_execute($stmt);
Example of third attempt, variables were inserted as string in pl, thus binding was avoided
//$var1-3-5-6 is not anymore array
VAR_A := PROJ_DB.EMPLOYEE_TYPE($var1, null, null, null, null, null, null, null, null, null, null, null);
VAR_B :='".$var2."';
VAR_C := PROJ_DB.PROJECT_TYPE($var3, null, null, null, null, null, null, null, null, null);
VAR_D := to_date('".$var4."', 'dd.mm.yyyy hh24:mi:ss');
VAR_E := PROJ_DB.SYSTEM_TYPE($var5, null, null);
VAR_F := PROJ_DB.ACTION_TYPE('".$var6."', null);
VAR_G :=$var7;
VAR_H :=NULL;
VAR_I :=NULL;
VAR_J :='".$var10."';
VAR_K :=$var11;
VAR_L :=$var12;
VAR_M :=$var13;
VAR_N :=$var14;
VAR_O := NULL;
Example of one entry for procedure (second attempt was based on this example);
DECLARE
VAR_A PROJ_DB.EMPLOYEE_TYPE;
VAR_B VARCHAR2(200);
VAR_C PROJ_DB.PROJECT_TYPE;
VAR_D DATE;
VAR_E PROJ_DB.SYSTEM_TYPE;
VAR_F PROJ_DB.ACTION_TYPE;
VAR_G NUMBER;
VAR_H VARCHAR2(200);
VAR_I VARCHAR2(200);
VAR_J CLOB;
VAR_K NUMBER;
VAR_L NUMBER;
VAR_M NUMBER;
VAR_N NUMBER;
VAR_O VARCHAR2(200);
BEGIN
VAR_A := PROJ_DB.EMPLOYEE_TYPE(11111, null, null, null, null, null, null, null, null, null, null, null);
VAR_B := 'String 1';
VAR_C := PROJ_DB.PROJECT_TYPE('String 2', null, null, null, null, null, null, null, null, null);
VAR_D := to_date('24.02.2020 08:00:00', 'dd.mm.yyyy hh24:mi:ss');
VAR_E := PROJ_DB.SYSTEM_TYPE('String 3', null, null);
VAR_F := PROJ_DB.ACTION_TYPE('String 4', null);
VAR_G := 2222222;
VAR_H := NULL;
VAR_I := NULL;
VAR_J := 'String 5';
VAR_K := 3333333;
VAR_L := 4444444;
VAR_M := 5555555;
VAR_N := 6666666;
VAR_O := NULL;
RECORD_MANAGEMENT.ADD_INTO(
VAR_A => VAR_A,
VAR_B => VAR_B,
VAR_C => VAR_C,
VAR_D => VAR_D,
VAR_E => VAR_E,
VAR_F => VAR_F,
VAR_G => VAR_G,
VAR_H => VAR_H,
VAR_I => VAR_I,
VAR_J => VAR_J,
VAR_K => VAR_K,
VAR_L => VAR_L,
VAR_M => VAR_M,
VAR_N => VAR_N,
VAR_O => VAR_O
);
COMMIT;
END;
Don't try to pass the object types in. Instead, pass the values that you are using to initialise the object types. You can DECLARE the items first in intermediate variables or you can just initialise them directly into the procedure's parameters without intermediate variables:
BEGIN
RECORD_MANAGEMENT.ADD_INTO(
VAR_A => PROJ_DB.EMPLOYEE_TYPE(
:var_a, null, null, null, null, null, null, null, null, null, null, null
),
VAR_B => :var_b,
VAR_C => PROJ_DB.PROJECT_TYPE(
:var_c, null, null, null, null, null, null, null, null, null
),
VAR_D => to_date(:var_d, 'dd.mm.yyyy hh24:mi:ss'),
VAR_E => PROJ_DB.SYSTEM_TYPE(:var_e, null, null),
VAR_F => PROJ_DB.ACTION_TYPE(:var_f, null),
VAR_G => :var_g,
VAR_H => :var_h,
VAR_I => :var_i,
VAR_J => :var_,
VAR_K => :var_k,
VAR_L => :var_l,
VAR_M => :var_m,
VAR_N => :var_n,
VAR_O => :var_o
);
COMMIT;
END;