Python内网爬虫调用PM_USER_LOGIN_SP报PLS-00306错误求助

Hey there, let's break down this frustrating PL/SQL error you're facing with your crawler. Those PLS-00306 (wrong number/type of arguments) and ORA-06550 (statement ignored) errors almost always stem from a mismatch between how your Python code is invoking the stored procedure and what the procedure actually expects—even if your DevTools checks seem to say everything is right. Here are targeted steps to diagnose and fix this:

1. Verify the Exact Parameter Definition of PM_USER_LOGIN_SP #

First, you need to confirm the official parameter specs for the stored procedure, because what you see in DevTools might not tell the full story (e.g., hidden parameters, data type constraints, IN/OUT directions).

• Use an Oracle client tool like SQL Developer to run:

  DESCRIBE PM_USER_LOGIN_SP;
  

• Or query Oracle's system views for granular details:

  SELECT argument_name, data_type, in_out, data_length 
  FROM user_arguments 
  WHERE object_name = 'PM_USER_LOGIN_SP' 
  ORDER BY position;
  

Pay close attention to:

• Number of parameters (don't miss any required ones)
• Data types (e.g., VARCHAR2(50) vs NVARCHAR2, NUMBER vs INTEGER)
• Parameter direction (IN, OUT, INOUT—OUT parameters need special handling in Python)
• Case sensitivity: If parameters were defined with double quotes (e.g., "UserName"), your Python code must match that exact case.

2. Audit Your Python Stored Procedure Call #

If you're using cx_Oracle or oracledb (the modern Oracle Python driver), make sure your call syntax aligns perfectly with the procedure's specs. Common mistakes here include:

• Incorrect parameter order: Oracle relies heavily on positional arguments unless you use named notation.
• Missing OUT parameter handling: If the procedure returns data via OUT parameters, you need to declare Oracle variables for them in Python.
• Mismatched data types: For example, passing a Python int to a VARCHAR2 parameter, or a string that exceeds the procedure's VARCHAR2 length limit.

Example of a correct call for a procedure with 2 IN parameters and 1 OUT parameter:

import oracledb

# Establish connection
with oracledb.connect(user="your_internal_user", password="your_pass", dsn="your_dsn") as conn:
    with conn.cursor() as cur:
        # Declare OUT variable (match the procedure's data type)
        login_result = cur.var(oracledb.NUMBER)
        # Call procedure with positional arguments
        cur.callproc("PM_USER_LOGIN_SP", ["your_username", "encrypted_password", login_result])
        # Retrieve OUT value
        print(f"Login Status: {login_result.getvalue()}")

3. Compare DevTools Request Data vs. Python's Actual Payload #

DevTools shows you what the browser sends, but your Python code might be sending something subtly different. Check these:

• Parameter encoding: If the browser sends URL-encoded form data (e.g., password=abc%21def), ensure your Python code uses requests.post(url, data=payload) (not json=payload) and that special characters are encoded correctly.
• Hidden/derived parameters: Some internal systems add auto-generated parameters (e.g., session tokens, CSRF tokens, client IP hashes) that aren't visible in the form but are required for the stored procedure. Use DevTools' "Copy as cURL" feature, then manually replicate the headers and payload in your Python code.
• Data transformations: The browser might encrypt/hash data (e.g., password) before sending it. If you're sending plaintext in Python but the procedure expects a hashed value, that'll trigger a type/value mismatch.

4. Check Session Context Requirements #

Many internal Oracle stored procedures depend on session-level settings or pre-existing context (e.g., NLS date format, client identifier, prior session initialization calls). For example:

• The browser's session might have already set a client IP via DBMS_SESSION.SET_IDENTIFIER, but your Python crawler's new session doesn't.
• The procedure expects dates in DD-MON-YYYY format, but your Python session uses YYYY-MM-DD.

Try adding session initialization steps before calling the procedure:

with conn.cursor() as cur:
    # Set required session parameters
    cur.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'")
    cur.execute("BEGIN DBMS_SESSION.SET_IDENTIFIER('your_crawler_ip'); END;")
    # Now call the login procedure
    cur.callproc("PM_USER_LOGIN_SP", [...])

5. Capture Full Error Details #

To get more specific info about which parameter is causing the issue, enhance your Python error handling to print the full Oracle error stack:

import oracledb
import traceback

try:
    # Your connection and procedure call code here
except oracledb.DatabaseError as e:
    error_details, = e.args
    print(f"Oracle Error Code: {error_details.code}")
    print(f"Detailed Error Message: {error_details.message}")
    print("\nFull Traceback:")
    traceback.print_exc()

This often reveals exactly which parameter is mismatched (e.g., "expected VARCHAR2 got NUMBER for parameter 2").

Start with verifying the stored procedure's parameter definition—this is the most common root cause. Once you have that, cross-reference every detail with your Python code and payload.

内容的提问来源于stack exchange，提问作者Ired.X