Home » SQL & PL/SQL » SQL & PL/SQL » Problem in executing sql query
Problem in executing sql query [message #320054] Tue, 13 May 2008 22:46 Go to next message
chandu_v
Messages: 4
Registered: May 2008
Junior Member
I am using the following SQL query to run a stored procedure.


var mystatus number;
var myvirhe number;
call MUIKKARI_NEWARCH.POISTA_MUI('&1','&2','&3','ML',:mystatus,:myvirhe);
PROMPT Status:
PRINT mystatus
PROMPT Virhe:
PRINT myvirhe


After running the above query iam getting the following error:

Error starting at line 3 in command:
call MUIKKARI_NEWARCH.POISTA_MUI('&1','&2','&3','ML',:mystatus,:myvirhe)
Error report:
SQL Error: ORA-01008: not all variables bound
01008. 00000 - "not all variables bound"
*Cause:
*Action:
Status:
mystatus
------

Virhe:
myvirhe
------
I passed three parameters as input. Please give me your suggestions.

[Updated on: Tue, 13 May 2008 22:54]

Report message to a moderator

Re: Problem in executing sql query [message #320060 is a reply to message #320054] Tue, 13 May 2008 23:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Without the code how could we know what is at line 3?

Regards
Michel
Re: Problem in executing sql query [message #320062 is a reply to message #320054] Tue, 13 May 2008 23:43 Go to previous messageGo to next message
chandu_v
Messages: 4
Registered: May 2008
Junior Member
chandu_v wrote on Tue, 13 May 2008 22:46
I am using the following SQL query to run a stored procedure.


var mystatus number;
var myvirhe number;
call MUIKKARI_NEWARCH.POISTA_MUI('&1','&2','&3','ML',:mystatus,:myvirhe);
PROMPT Status:
PRINT mystatus
PROMPT Virhe:
PRINT myvirhe


After running the above query iam getting the following error:

Error starting at line 3 in command:
call MUIKKARI_NEWARCH.POISTA_MUI('&1','&2','&3','ML',:mystatus,:myvirhe)
Error report:
SQL Error: ORA-01008: not all variables bound
01008. 00000 - "not all variables bound"
*Cause:
*Action:
Status:
mystatus
------

Virhe:
myvirhe
------
I passed three parameters as input. Please give me your suggestions.



The following is the code of the procedure which i am calling.

PROCEDURE POISTA_MUI(
TILLITNRO IN VARCHAR2,
SEULITNRO IN VARCHAR2,
ASTUNNUS IN CHAR,
KANAVAKOODI IN CHAR,
--STATUS OUT Type_number_10,
--VIRHE OUT Type_number_10
STATUS OUT number,
VIRHE OUT number)
AS

XMESSAGE VARCHAR2(100);
v_ErrorCode NUMBER;
v_ErrorText VARCHAR2(100);

rowcount NUMBER DEFAULT 1;
counter NUMBER (10);

BEGIN

XMESSAGE := 'Virhe';
--VIRHE(rowcount) := 0;
--STATUS(rowcount) := 0;
VIRHE := 0;
STATUS := 0;


IF SEULITNRO IS NULL OR SEULITNRO = ' ' THEN

INSERT INTO IDB.REMIN_TILAAJAT_POISTETTU
SELECT
TIL_LNRO,
SEU_LNRO,
RAJAMK,
KIELI,
TILAUSAIKA,
SYSDATE,
TARKISTE_ID,
KANAVA
FROM IDB.REMIN_TILAAJAT
WHERE TIL_LNRO = TILLITNRO;

UPDATE REMIN_TILAAJAT_POISTETTU
SET KANAVA = KANAVAKOODI
WHERE TIL_LNRO = TILLITNRO AND
TRUNC(EROAIKA) = TRUNC(SYSDATE);

DELETE from IDB.REMIN_TILAAJAT
WHERE TIL_LNRO = TILLITNRO;

IF KANAVAKOODI = 'UD' THEN

INSERT INTO IDB.REMIN_TILAAJAT_POISTETTU
SELECT
TIL_LNRO,
SEU_LNRO,
RAJAMK,
KIELI,
TILAUSAIKA,
SYSDATE,
TARKISTE_ID,
KANAVA
FROM IDB.REMIN_TILAAJAT
WHERE SEU_LNRO = TILLITNRO;

UPDATE REMIN_TILAAJAT_POISTETTU
SET KANAVA = KANAVAKOODI
WHERE SEU_LNRO = TILLITNRO AND
TRUNC(EROAIKA) = TRUNC(SYSDATE);

DELETE from IDB.REMIN_TILAAJAT
WHERE SEU_LNRO = TILLITNRO;
END IF;

ELSE

INSERT INTO IDB.REMIN_TILAAJAT_POISTETTU
SELECT
TIL_LNRO,
SEU_LNRO,
RAJAMK,
KIELI,
TILAUSAIKA,
SYSDATE,
TARKISTE_ID,
KANAVA
FROM IDB.REMIN_TILAAJAT
WHERE TIL_LNRO = TILLITNRO AND SEU_LNRO = SEULITNRO;

UPDATE REMIN_TILAAJAT_POISTETTU
SET KANAVA = KANAVAKOODI
WHERE TIL_LNRO = TILLITNRO AND SEU_LNRO = SEULITNRO AND
TRUNC(EROAIKA) = TRUNC(SYSDATE);

DELETE from IDB.REMIN_TILAAJAT
WHERE TIL_LNRO = TILLITNRO AND SEU_LNRO = SEULITNRO;

END IF;

EXCEPTION
WHEN OTHERS THEN
v_ErrorCode := SQLCODE;
v_ErrorText := SUBSTR(SQLERRM,1,100);
--VIRHE(rowcount) := v_ErrorCode;
VIRHE := v_ErrorCode;
--STATUS(rowcount) := 1;
STATUS := 1;
rollback;

INSERT INTO ERROR_LOG (ERRTIME, PROG, ERRNUM, ERRMESSAGE, MESSAGE)
VALUES (SYSDATE, 'POISTA_MUI', v_ErrorCode, v_ErrorText, XMESSAGE);
commit;

END;
Re: Problem in executing sql query [message #320063 is a reply to message #320060] Tue, 13 May 2008 23:44 Go to previous messageGo to next message
chandu_v
Messages: 4
Registered: May 2008
Junior Member
Here line 3 is

call MUIKKARI_NEWARCH.POISTA_MUI('&1','&2','&3','ML',:mystatus,:myvirhe);
Re: Problem in executing sql query [message #320064 is a reply to message #320054] Tue, 13 May 2008 23:53 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Yes, the problem is in the CALL statement. You shall know its syntax and meaning before you use it. You can easily find it in the documentation, found eg. online on http://tahiti.oracle.com/.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4008.htm#BABDEHHG (link is for 10gR2 version)
Re: Problem in executing sql query [message #320075 is a reply to message #320063] Wed, 14 May 2008 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Use SQL*Plus, Indent the code (See SQL Formatter) and we need line numbers.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Problem in executing sql query [message #320119 is a reply to message #320075] Wed, 14 May 2008 02:29 Go to previous message
chandu_v
Messages: 4
Registered: May 2008
Junior Member
I successfully executed it by using SQL Plus. Before I used SQL Developer. Thanks every one.
Previous Topic: ROWNUM ??
Next Topic: Printing the spooled file
Goto Forum:
  


Current Time: Thu Dec 08 16:40:46 CST 2016

Total time taken to generate the page: 0.10446 seconds