Ora 06550 ошибка pls 00306 - Ремонт и установка крупной бытовой техники

I’m trying to call an API using the exact procedure signature, but somehow the table of numbers I don’t think is recognize correctly.

API definition:

TYPE NUMLIST IS TABLE OF NUMBER INDEX BY VARCHAR2(50);

PROCEDURE GETSERVICES_API
(
   I_DIMOBJID IN NUMBER, I_OBJECTID IN NUMBER, I_FILTER IN NUMBER, 
   O_ERRORCODE OUT NUMBER, O_ERRORTEXT OUT VARCHAR2, O_SERVICELIST OUT NUMLIST
);

My call of API:

DECLARE

   TYPE NUMLIST IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
   lt_SERVICELIST              NUMLIST;

   ls_errortext             varchar2(100);
   ln_errorcode             number;

BEGIN


    PKGCOMSUPPORT_SERVICE.GETSERVICES_API(I_DIMOBJID => 6,
                                          I_OBJECTID => 5263,
                                          I_FILTER => 3,
                                          O_ERRORCODE => ln_errorcode,
                                          O_ERRORTEXT => ls_errortext,
                                          O_SERVICELIST => lt_SERVICELIST);

END;

When I run my call of API I got: PLS-00306: wrong number of types of arguments in call to ‘GETSERVICE_API

Any idea why? Thanks

Источник

Есть ещё один вопрос.
Как правильно передать переменную Дата.
Пробовал следующие варианты

на эти две
CONCATENATE »» ‘01.05.2010’ »» ‘,’ »» ‘DD.MM.YYYY’ »» into DDATE_FROM.
DDATE_FROM = ‘01.05.2010’,’DD.MM.YYYY’.

Code:

Database error text……..: «ORA-01858: a non-numeric character was found
where a numeric was expected#ORA-06512: at line 1″
Database error code……..: 1858
Triggering SQL statement…: «EXECUTE PROCEDURE UDO_PROV»
Internal call code………: «[DBDS/NEW DSQL]»

На эту попытку
DDATE_FROM = ‘01052010’.
выдает

Code:

Database error text……..: «ORA-01861: literal does not match format
string#ORA-06512: at line 1″
Database error code……..: 1861
Triggering SQL statement…: «EXECUTE PROCEDURE UDO_PROV»
Internal call code………: «[DBDS/NEW DSQL]»

Функцию в Парусе видоизменили

Code:

CREATE OR REPLACE PROCEDURE PARUS.UDO_PROV
(
S IN DATE,
S2 OUT VARCHAR2
)
AS

BEGIN
S2:=S;
END;

Если спросите зачем я это делаю, то отвечу:
Надо запустить процедуру, в Парусе, с 37 переменными, т.к. там 3 вида переменных, VARCHAR2, DATE и number, то глючит у меня Дата.

Получилось вот так

Code:

EXEC SQL.
EXECUTE PROCEDURE UDO_PROV ( IN :’01-APR-10′, OUT :YY )
ENDEXEC.

ну и на вывод получил тоже самое, что и требовалось
01-APR-10
вот теперь думаю что делать , так и использовать или мож кто что посоветует.

Источник

Have been fighting this for two days and am very frustrated but feel like I am making progress. After reviewing Oracle’s online docs I am here. Receiving the following error upon code execution:

ORA-06550: line 1, column 15: PLS-00306: wrong number or types of arguments in call to ‘P_SALTEDHASH’ ORA-06550: line 1, column 7: PL/SQL: Statement ignored

Stored procedure looks like this:

PROCEDURE stored_procedure_name ( p_passwd            IN  VARCHAR2,
                          p_salt              IN  VARCHAR2,
                          p_saltedhash_passwd OUT VARCHAR2
                        )

My code:

        string stored_procedure_name = "stored_procedure_name";

        // create the command object
        OracleCommand cmd = conn.CreateCommand();
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = stored_procedure_name;
        cmd.BindByName = true;

        //Oracle Parameters necessary for the p_saltedhash function          
        cmd.Parameters.Add("p_passwd", p_passwd);
        cmd.Parameters.Add("p_salt", p_salt);

        OracleParameter p_saltedhash_passwd = 
            new OracleParameter("p_saltedhash_passwd", OracleDbType.Varchar2);
        p_saltedhash_passwd.Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add(p_saltedhash_passwd);



        // execute the pl/sql block
        cmd.ExecuteNonQuery();

        Response.Write("Pin hash is: " + p_saltedhash_passwd);`

Источник

Remove From My Forums

Question
```
I'm using Microsoft's Oracle Data Access library (http://www.microsoft.com/downloads/details.aspx?familyid=4f55d429-17dc-45ea-bfb3-076d1c052524&displaylang=en) and I keep getting this error when trying to invoke a function on the oracle server:System.Data.OracleClient.OracleException: ORA-06550: line 1, column 24:
PLS-00306: wrong number or types of arguments in call to 'INSERT_SUBSCRIBER'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored



  


                       
  



I check the parameter spelling and types over 5, times, I have no clue what is going on.  
I tried using the following to manually add the parameters:
```
Code Snippet

command.CommandText = «INSERT_SUBSCRIBER»;
command.CommandType = CommandType.StoredProcedure;
command.Connection = connection;

command.Parameters.Add(«RETURN_VALUE», OracleType.Number).Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(«P_SUB_CAR_ID», OracleType.Number).Value = 0;
command.Parameters.Add(«P_SUB_PHONE_MODEL_ID», OracleType.Number).Value = 0;
command.Parameters.Add(«P_SUB_PHONE_NUMBER», OracleType.Number).Value = 0;
command.Parameters.Add(«P_SUB_FIRST_NAME», OracleType.VarChar, 20).Value = null;
command.Parameters.Add(«P_SUB_LAST_NAME», OracleType.VarChar, 20).Value = null;
command.Parameters.Add(«P_SUB_USERNAME», OracleType.VarChar, 32).Value = userName;
command.Parameters.Add(«P_SUB_PASSWD», OracleType.VarChar, 32).Value = passwd;
command.Parameters.Add(«P_SUB_ENC_PWD», OracleType.VarChar, 100).Value = encPwd;
command.Parameters.Add(«P_SUB_DOB», OracleType.DateTime).Value = DateTime.Now;
command.Parameters.Add(«P_SUB_SEX», OracleType.Char, 1).Value = null;
command.Parameters.Add(«P_SUB_LOC_ID», OracleType.Number).Value = 0;
command.Parameters.Add(«P_SUB_ZIPCODE», OracleType.VarChar, 10).Value = null;
command.Parameters.Add(«P_SUB_AREACODE», OracleType.Number).Value = 0;
command.Parameters.Add(«P_SUB_EMAIL», OracleType.VarChar, 50).Value = null;
command.Parameters.Add(«P_SUB_NEWS», OracleType.Number).Value = 0;
command.Parameters.Add(«P_SUB_DIST_ID», OracleType.Number).Value = 0;

connection.Open();
command.ExecuteNonQuery(); // ERROR HERE
```
I even try letting asp tell me the parameters using DeriveParamters:
```
Code Snippet

command.CommandText = «INSERT_SUBSCRIBER»;
command.CommandType = CommandType.StoredProcedure;
command.Connection = connection;

connection.Open();

OracleCommandBuilder.DeriveParameters(command);

command.Parameters[«P_SUB_USERNAME»].Value = userName;
command.Parameters[«P_SUB_PASSWD»].Value = ComputeMD5Hex(password);
command.Parameters[«P_SUB_ENC_PWD»].Value = password;
command.Parameters[«P_SUB_CAR_ID»].Value = 0;
command.Parameters[«P_SUB_PHONE_MODEL_ID»].Value = 0;
command.Parameters[«P_SUB_PHONE_NUMBER»].Value = 0;
command.Parameters[«P_SUB_LOC_ID»].Value = 0;
command.Parameters[«P_SUB_AREACODE»].Value = 0;
command.Parameters[«P_SUB_NEWS»].Value = 0;
command.Parameters[«P_SUB_DIST_ID»].Value = 0;
command.Parameters[«P_SUB_DOB»].Value = DateTime.Now;

command.ExecuteNonQuery(); // ERROR THROW HERE AGAIN!
```
The following function is on the server, and it looks like this:
```
Code Snippet

SQL> describe insert_subscriber;
FUNCTION insert_subscriber RETURNS NUMBER
Argument Name                  Type                    In/Out Default?
—————————— ———————— —— ———
P_SUB_CAR_ID                   NUMBER(4)               IN
P_SUB_PHONE_MODEL_ID           NUMBER(4)               IN
P_SUB_PHONE_NUMBER             NUMBER(15)              IN
P_SUB_FIRST_NAME               VARCHAR2(20)            IN
P_SUB_LAST_NAME                VARCHAR2(20)            IN
P_SUB_USERNAME                 VARCHAR2(32)            IN
P_SUB_PASSWD                   VARCHAR2(32)            IN
P_SUB_ENC_PWD                  VARCHAR2(100)           IN
P_SUB_DOB                      DATE                    IN
P_SUB_SEX                      CHAR(1)                 IN
P_SUB_LOC_ID                   NUMBER(10)              IN
P_SUB_ZIPCODE                  VARCHAR2(10)            IN
P_SUB_AREACODE                 NUMBER(10)              IN
P_SUB_EMAIL                    VARCHAR2(50)            IN
P_SUB_NEWS                     NUMBER(1)               IN
P_SUB_DIST_ID                  NUMBER(32)              IN

The funny thing is, if I right click on the function in Visual Studio’s Database Explorer, and select «Execute», fill in the parameters, and it works! WHY? What am I doing wrong?

Answers

Thanks for the reply, one of the args is a return value, that is why there are 17 parameters.

I figured out the issue actually. To submit null values to the database, you have to set the OracleParameter.Value property to DbNull.Value instead of regular .net nulls. Are you trying to give me white hairs microsoft? Oh well.

Источник

Hi,
As per mentioned earlier we have created oracle testproc with 2 input parameters ,one is number,second is array of string,so we are passing these 2 inputs to procedure as above along with out param like below

«BEGIN testproc(:no,:inPL,:outPL); END;»,
{
no:
{
type: oracledb.NUMBER,
dir: oracledb.BIND_IN,
val:1
},

      inPL:{ 
        type: oracledb.STRING,
        dir: oracledb.BIND_IN,
        val: ['SI','AB']
    },
    outPL:{ 
      dir: oracledb.BIND_OUT, 
      type: oracledb.STRING,
      maxArraySize: 1000
    }              
    },

here no is number param,inPL is array of string ,after passing these 2 input params we want to fetch it in outPL as out param,so logic of procedure is below

CREATE OR REPLACE PROCEDURE DART_CTL_110.testProc(inputno in number, IN_pl_array IN array_pl,OUT_pl_array OUT array_pl)
IS
no number(4);

BEGIN
—OUT_pl_array := NEW array_pl ();
—IN_pl_array :=NEW array_pl ();

no:= 1;
DBMS_OUTPUT.PUT_LINE(inputno);
OUT_pl_array :=IN_pl_array;

END;
/

so actually passing number and array as input and need to get array as output ,that is our actual requirement,but its throwing error like,

PLS-00306: wrong number or types of arguments in call to ‘TESTPROC’
ORA-06550: line 1, column 7:

I am not understanding ,where I am missing wrong type or number of arguments?

could you please help?

Thanks

Источник

Question

Answers

Возможно, вам также будет интересно: