Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Aug 2003
    Posts
    7

    Unanswered: how to run function or procedure in sqlplus?

    how to run function or procedure in sqlplus?
    and how to run the procedure of a package???

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    execute procedure_name

    execute procedure_name('in_param','in_param');
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    May 2003
    Location
    Dublin, Ireland
    Posts
    44
    And

    execute package_name.procedure_name (....

    Originally posted by The_Duck
    execute procedure_name

    execute procedure_name('in_param','in_param');

  4. #4
    Join Date
    Aug 2003
    Posts
    40
    a) and, for your function(s) you can...

    select myPackage.myFunction(argValue) from dual;

    b) or, if you want to pass values from a table in your
    SQL statement...

    select myPackage.myFunction(rec_id) from myTable;

    This assumes:
    - A rec_id column exists in the table
    - The function could return another column value from the
    table based upon the "key" value being passed.

  5. #5
    Join Date
    Feb 2003
    Location
    Indian Head, MD
    Posts
    30

    Unhappy

    No it doesn't. Declaring the var iResult or declare iResult causes SP2 0552 Bind variable iResult not declared then I am not given an opportunity to enter the parameter values as the procedure tried to run as soon as I hit enter without giving a chance to enter the 27 parameters.

  6. #6
    Join Date
    Feb 2003
    Location
    Indian Head, MD
    Posts
    30

    Red face

    SQL> var iResult
    SP2-0552: Bind variable "iresult" not declared.
    SQL> EXEC ERFOAPPLICANT.INSERT_INOI_SP( 'sss' , 'sss' , 'sss' , 'sss' , 'sss' ,
    'sss' , 'sss' , 222, 'sss' , 'sss' , 'sss' , 'sss' , 'sss' , 546, 'sss' , 'sss'
    , 'sss' , 'sss' , 'sss' , 65 , 175, 'sss' , 'sss' , 'sss' , 'sss' , 'sss' ,
    'sss' , 'sss' , 'sss' , :iResult);
    SP2-0552: Bind variable "IRESULT" not declared.
    SQL> Print iResult;

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    what datatype is iresult supposed to be?

    Code:
    SQL> var
    SP2-0568: No bind variables declared.
    SQL> var iresult
    SP2-0552: Bind variable "iresult" not declared.
    SQL> var iresult number
    SQL> var 
    variable   iresult
    datatype   NUMBER
    SQL> var iresult
    variable   iresult
    datatype   NUMBER
    Last edited by anacedent; 11-24-09 at 16:04.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Feb 2003
    Location
    Indian Head, MD
    Posts
    30
    It is an int I have done Declare iResult int; and var iResult int; and var iResult; each entry results in same error message. I tried to paste entire execute statment in with line breaks between each parameter reference and and then reformating it to exclude crlf's between params. using the first method returned different errors SQL> Declare iResult int;
    2 EXEC ERFOAPPLICANT.INSERT_INOI_SP
    3 (
    4 'sss' ,
    5 'sss' ,
    6 'sss' ,
    7 'sss' ,
    8 'sss' ,
    9 'sss' ,
    10 'sss' ,
    11 222,
    12 'sss' ,
    13 'sss' ,
    14 'sss' ,
    15 'sss' ,
    16 'sss' ,
    17 546,
    18 'sss' ,
    19 'sss' ,
    20 'sss' ,
    21 'sss' ,
    22 'sss' ,
    23 65 ,
    24 175,
    25 'sss' ,
    26 'sss' ,
    27 'sss' ,
    28 'sss' ,
    29 'sss' ,
    30 'sss' ,
    31 'sss' ,
    32 'sss' ,
    33 :iResult
    34 );
    35
    36 Print iResult;
    37 /
    SP2-0552: Bind variable "IRESULT" not declared.
    SQL>
    With var this returned error after firstline and then another error after "EXEC ERFOAPPLICANT.INSERT_INOI_SP" then tried to process
    3 (
    4 'sss' ,
    5 'sss' ,
    6 'sss' ,
    7 'sss' ,
    8 'sss' ,
    9 'sss' ,
    10 'sss' ,
    11 222,
    12 'sss' ,
    13 'sss' ,
    14 'sss' ,
    15 'sss' ,
    16 'sss' ,
    17 546,
    18 'sss' ,
    19 'sss' ,
    20 'sss' ,
    21 'sss' ,
    22 'sss' ,
    23 65 ,
    24 175,
    25 'sss' ,
    26 'sss' ,
    27 'sss' ,
    28 'sss' ,
    29 'sss' ,
    30 'sss' ,
    31 'sss' ,
    32 'sss' ,
    33 :iResult
    34 );
    35
    36 Print iResult;

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    try

    SQL> HELP VARIABLE

    Read The Fine Help message!
    The post what new fact you learned.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Feb 2003
    Location
    Indian Head, MD
    Posts
    30
    says a variable can be declare with anything var iResult to variable iResult Number. which I did in every permutation and combination of the above Still same messge.

  11. #11
    Join Date
    Feb 2003
    Location
    Indian Head, MD
    Posts
    30
    Which you would have known if you had read the post.

  12. #12
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Code:
    var foo number  -- no int type unless in pl/sql
    
    BEGIN
      :foo := 100;
      
      SELECT &bar FROM DUAL; -- You will be prompted for bar
    END;

  13. #13
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by Joeller View Post
    Which you would have known if you had read the post.
    I guess my eyes are failing me.
    What is the timestamp of your post where IRESULT was declared a NUMBER?

    >which I did in every permutation and combination of the above Still same messge.
    Problem exists between keyboard and chair (PEBKAC)!

    Code:
    SQL> @pass2proc
    SQL> CREATE OR REPLACE PROCEDURE PASS2PROC(VAL1 IN NUMBER, VAL2 OUT NUMBER)
      2  AS
      3  BEGIN
      4  	VAL2 := VAL1*2;
      5  END PASS2PROC;
      6  /
    
    Procedure created.
    
    SQL> var ret1 number
    SQL> exec pass2proc(1,:ret1);
    
    PL/SQL procedure successfully completed.
    
    SQL> print ret1
    
          RET1
    ----------
    	 2
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  14. #14
    Join Date
    Feb 2003
    Location
    Indian Head, MD
    Posts
    30
    I still cannot figure how to enter code block so here are the results of three runs in SQLPlus
    This is what I am trying to run:
    variable iResult int;
    EXEC ERFOAPPLICANT.INSERT_INOI_SP(
    'sss' ,
    'sss' ,
    'sss' ,
    'sss' ,
    'sss' ,
    'sss' ,
    'sss' ,
    222,
    'sss' ,
    'sss' ,
    'sss' ,
    'sss' ,
    'sss' ,
    546,
    'sss' ,
    'sss' ,
    'sss' ,
    'sss' ,
    'sss' ,
    65 ,
    175,
    'details',
    500.23,
    '24-01-2009' ,
    '22-02-2009',
    2,
    'storm'
    'stdet'
    '6666');
    print iresult;

    This is what I get when i run it: without a begin or declare

    SQL> variable iResult int;
    Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
    VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
    NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
    REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]
    SQL> EXEC ERFOAPPLICANT.INSERT_INOI_SP
    BEGIN ERFOAPPLICANT.INSERT_INOI_SP; END;

    *
    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'INSERT_INOI_SP'
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored


    SQL> ( 'sss' ,
    2 'sss' ,
    3 'sss' ,
    4 'sss' ,
    5 'sss' ,
    6 'sss' ,
    7 'sss' ,
    8 222,
    9 'sss' ,
    10 'sss' ,
    11 'sss' ,
    12 'sss' ,
    13 'sss' ,
    14 546,
    15 'sss' ,
    16 'sss' ,
    17 'sss' ,
    18 'sss' ,
    19 'sss' ,
    20 65 ,
    21 175,
    22 'details',
    23 500.23,
    24 '24-01-2009' ,
    25 '22-02-2009',
    26 2,
    27 'storm'
    28 'stdet'
    29 '6666');
    ( 'sss' ,
    *
    ERROR at line 1:
    ORA-00928: missing SELECT keyword


    SQL> print iresult;
    SP2-0552: Bind variable "IRESULT" not declared.
    SQL>

    When I commence the block with a Declare or Begin I get

    SQL> BEGIN
    2 variable iResult int;
    3 EXEC ERFOAPPLICANT.INSERT_INOI_SP(
    4 'sss' ,
    5 'sss' ,
    6 'sss' ,
    7 'sss' ,
    8 'sss' ,
    9 'sss' ,
    10 'sss' ,
    11 222,
    12 'sss' ,
    13 'sss' ,
    14 'sss' ,
    15 'sss' ,
    16 'sss' ,
    17 546,
    18 'sss' ,
    19 'sss' ,
    20 'sss' ,
    21 'sss' ,
    22 'sss' ,
    23 65 ,
    24 175,
    25 'details',
    26 500.23,
    27 '24-01-2009' ,
    28 '22-02-2009',
    29 2,
    30 'storm'
    31 'stdet'
    32 '6666');
    33 END;
    34 /
    variable iResult int;
    *
    ERROR at line 2:
    ORA-06550: line 2, column 10:
    PLS-00103: Encountered the symbol "IRESULT" when expecting one of the
    following:
    := . ( @ &#37; ;

    Or

    SQL> Declare
    2 variable iResult int;
    3 EXEC ERFOAPPLICANT.INSERT_INOI_SP
    4 ( 'sss' ,
    5 'sss' ,
    6 'sss' ,
    7 'sss' ,
    8 'sss' ,
    9 'sss' ,
    10 'sss' ,
    11 222,
    12 'sss' ,
    13 'sss' ,
    14 'sss' ,
    15 'sss' ,
    16 'sss' ,
    17 546,
    18 'sss' ,
    19 'sss' ,
    20 'sss' ,
    21 'sss' ,
    22 'sss' ,
    23 65 ,
    24 175,
    25 'details',
    26 500.23,
    27 '24-01-2009' ,
    28 '22-02-2009',
    29 2,
    30 'storm'
    31 'stdet'
    32 '6666');
    33 print iresult;
    34 End;
    35 /
    variable iResult int;
    *
    ERROR at line 2:
    ORA-06550: line 2, column 18:
    PLS-00103: Encountered the symbol "INT" when expecting one of the following:
    := . ( @ % ; not null range default character
    The symbol ":=" was substituted for "INT" to continue.
    ORA-06550: line 31, column 1:
    PLS-00103: Encountered the symbol "stdet" when expecting one of the following:
    ) , * & = - + < / > at in is mod remainder not rem =>
    <an exponent (**)> <> or != or ~= >= <= <> and or like like2
    like4 likec between || multiset me
    ORA-06550: line 34, column 1:
    PLS-00103: Encountered the symbol "END" when expecting one of the following:
    begin function pragma procedure subtype type <an identifier>
    <a double-quoted delimited-identifier> current cursor delete
    exists prior

    Or

    SQL> Declare
    2 variable iResult int;
    3 EXEC ERFOAPPLICANT.INSERT_INOI_SP
    4 ( 'sss' ,
    5 'sss' ,
    6 'sss' ,
    7 'sss' ,
    8 'sss' ,
    9 'sss' ,
    10 'sss' ,
    11 222,
    12 'sss' ,
    13 'sss' ,
    14 'sss' ,
    15 'sss' ,
    16 'sss' ,
    17 546,
    18 'sss' ,
    19 'sss' ,
    20 'sss' ,
    21 'sss' ,
    22 'sss' ,
    23 65 ,
    24 175,
    25 'details',
    26 500.23,
    27 '24-01-2009' ,
    28 '22-02-2009',
    29 2,
    30 'storm'
    31 'stdet'
    32 '6666');
    33 print iresult;
    34 /
    variable iResult int;
    *
    ERROR at line 2:
    ORA-06550: line 2, column 18:
    PLS-00103: Encountered the symbol "INT" when expecting one of the following:
    := . ( @ % ; not null range default character
    The symbol ":=" was substituted for "INT" to continue.
    ORA-06550: line 31, column 1:
    PLS-00103: Encountered the symbol "stdet" when expecting one of the following:
    ) , * & = - + < / > at in is mod remainder not rem =>
    <an exponent (**)> <> or != or ~= >= <= <> and or like like2
    like4 likec between || multiset me
    ORA-06550: line 33, column 14:
    PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
    following:
    begin function pragma procedure subtype type <an identifier>
    <a double-quoted delimited-identifier> current cursor delete
    exists prior

    Far as I can determine, the only difference between the examples given, the documentation is the entering of the parameters on separate lines.

  15. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SQL> variable iResult int;
    >Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
    >VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
    >NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
    >REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]

    What part of the above do you NOT understand?
    Where do you see "int" as a valid datatype?

    Please continue to poke yourself in the eye & wonder why it hurts.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •