sqlcode

==================================================#!/bin/bashsqlplus=sqlplus64username=$1psswd=$2filedir=$3[[$ Filedir] | | Filedir= "/sandbox/edf_api_repo_dir" [[-D $filedir]] | | {echo "mkdir $filedir"; mkdir $filedir; chmod 777 $filedir;} [-D $filedir] | | {echo "Cannot create Oracle directory"; exit 1;} [[$username] | | {echo "Where is user name?"; exit 1;} [[$PSSWD] | | Psswd= $username echo "Create user $username identified by $psswd"; Sqlcode

', fun_get_md5 (' zhangwz ', ' 123456 ')); Sql> commit; Remove password when user logs on: Create or Replace procedure P_login (I_uname varchar2 , i_passwd varchar2) is V_ID number; V_error_text VARCHAR2 (200); Begin Select ID Into v_id From Sys_user where username = I_uname and password = FUN_GET_MD5 (I_uname, I_PASSWD); exception When others then V_error_text: = ' username or password is incorrect! ' | | ', Sqlcode: ' | |

SQLCA variable, which can be used for error checking * * EXEC SQL INCLUDE SQLCA; Main () { /* The following connection database * * EXEC SQL CONNECT Userid/password; /* The following is a connection to the database and check for errors generated t/if (Sqlca.sqlcode) { printf (Printer, "Error Connecting to Database server.\n"); Exit (); } printf ("Connected to Database server.\n"); /* Declare a "Cursor" below. It will be used when the query results are more than one line. EXEC SQL DECLARE itemcu

When you write a stored procedure, you use the following SQL statement Copy Code code as follows: SELECT MAX (re_date) into v_re_date From t_recording WHERE ID = ' 100010010102 ' Logically, the result of a query based on this condition should be sqlcode=1403, but strangely, the following SQL exception is not captured: Copy Code code as follows: EXCEPTION When No_data_found THEN NULL; When others THEN Err_code: =-2000; E

); Begin Select COUNT (*) into vn_ctn from User_all_tables a WHERE a.table_name = Upper (tablename); If vn_ctn > 0 Then --execute Immediate compiles or executes execute immediate ' drop table ', regardless of whether the table is in or out TableName; End If; EXCEPTION when others THEN dbms_output.put_line (SQLCODE | | '::'|| SUBSTR (SQLERRM, 1)); End drop_table; Delete a sequence Create or Replace procedure dro

); dbms_output.put_line (v_string | | '--' || V_n); End; You can also write a function and call implement MD5 encryption:Create or Replace function fun_get_md5 (i_username in Varchar2, I_password in varchar2)Return VARCHAR2 isBeginReturn Utl_raw.cast_to_raw (dbms_obfuscation_toolkit.md5 input_string => (i_username| | I_password)));End FUN_GET_MD5; Sql> Select FUN_GET_MD5 (' Zhangwz ', ' 123456 ') from dual;FUN_GET_MD5 (' zhangwz ', ' 123456 ')-------------------------------------------------

How to invoke a stored procedure in 1.JAVA CallableStatement: interfaces for executing SQL stored procedures and functions Simple steps: A. Write your own stored procedures (of course, you'd better pass the test first) B. Written Java invocation classes C. Get the desired output A: Declaring stored procedures: CREATE OR REPLACE PROCEDURE pro_query_dept ( P_deptno in DEPT. Deptno%type, P_dname out DEPT. Dname%type ) As BEGIN SELECT dname into P_dname from DEPT WHERE DEPTNO = P_deptno; Dbms_output

The DB2 tutorial being looked at is: DB2 programming skills (VI). 2.2 SQL statements as much as possible to write complex SQL By using large, complex SQL statements, you can improve performance by combining multiple, simple statements into large SQL statements. DB2 's SQL Engieer has a stronger ability to optimize complex statements, and basically does not have to be careful about the performance of statements. Oracle, on the contrary, recommends simplifying complex statements, and SQL Engieer o

Label:Sql0973n There is not enough memory available in the Explain:All available memory for this heap has been used. The statement cannot be processed.User response:Terminates the application after receiving this message (SQLCODE). Modify "Configure the parameters to increase the heap size.For example, to update the database configuration parameters, issue the following command:DB2 Update DB CFGFor "Using "To view a list of the database configuration

reverse 1..10 loop; end Loop;end; --The reverse output loop is completed. of which 1.. 10 represents 1 to 10, enhanced for loop.Error Handling: Exception when error 1 then; When others then; Common 2 kinds: too_many_rows no_data_found//Check ManualComplete Example:--Create a errorlog table: create tables errorlog ( id Number primary key, errcode number, errmsg varchar2 (1024x768), Errdate date); create sequence seq_errorlog_id start with 1 increment by 1; declare v_depno dept.depn

rows in the output is too many ');When Delete_mgr_excep then Dbms_output.put_line (' manager cannot be deleted directly ');--Handling exceptionsWhen Too_high_sal and then Dbms_output.put_line (' pay too much ');End⑥ using sqlcode in PL/SQL, SQLERRMSQLCODE return error code numberSQLERRM returns an error message.EXCEPTIONWhen OTHERS ThenDbms_output. Put_Line (sqlcode| | ' ---' | | SQLERRM);⑦ Exception Progr

When your application has called the stored procedure, and the node has dozens of or hundreds, the wrong is not a very troublesome thing, this time, I suggest to write to the database, below is a demo I do.1. Create an error log record tabledrop table Pub_proc_err_log Purge;CREATE TABLE Pub_proc_err_log(LOG_ID number,Module_name VARCHAR2 (100),Proc_name VARCHAR2 (100),Err_time DATE,Sql_code VARCHAR2 (50),SQL_ERRM VARCHAR2 (100),Err_content VARCHAR2 (500));Comment on column pub_proc_err_log. LOG_

_editor =/usr/local/bin/vimREM Format misc Columns commonly retrieved from data dictionaryCOLUMN segment_name FORMAT A30 word_wrapCOLUMN object_name FORMAT A30 word_wrapREM Set the prompt (works in Sql*plus from Oracle9i Database or Later0SET sqlprompt "_user" @ ' _connect_identifier > 'Error handling in 2.3.7 Sql*plus when you run a script, you want to stop running when you encounter an error. Use the following command to achieve the purpose. Sql>whenever SQLERROR EXIT SQL.

Exception handlingCreate or Replace procedurePr_test3 (V_BHinch varchar2, v_xx out T_hq_ryxx%RowType isbegin Select * intoV_xx fromT_hq_ryxxwhereBianh=V_bh;ifSql%Found ThenDbms_output.put_line ('find the data.');ElseDbms_output.put_line ('No data found');End if; exception whenNo_data_found Then --dbms_output.put_line (' No data found ');Dbms_output.put_line ('SQLCODE =' ||SQLCODE);--Error codeDbms_output

, Visittime, CountType)Values(Seq_cn_visitcountid.nextval,v_cityname, V_visitcount, V_visittime, V_counttype); EndLoop; --back up the daily phone number Delete fromCn_visitmobilewhereVisittime=t_date; Insert intoCn_visitmobileSelectSeq_cn_visitmobileid.nextval,mobile,cityid,visittime from ( SelectMobile,cityid,to_number (To_char (Visittime,'YYYYMMDD')) Visittime fromcn_visitanalysiswhereTo_number (To_char (Visittime,'YYYYMMDD'))=t_date and(Cityid= the orCityid= About) Group byCity

');When Delete_mgr_excep then Dbms_output.put_line (' manager cannot be deleted directly ');--Handling exceptionsWhen Too_high_sal and then Dbms_output.put_line (' pay too much ');End⑥ using sqlcode in PL/SQL, SQLERRMSQLCODE return error code numberSQLERRM returns an error message.EXCEPTIONWhen OTHERS ThenDbms_output. Put_Line (sqlcode| | ' ---' | | SQLERRM);⑦ Exception Program:1. By select ... into ... Qu

MySQL inserts the data queried in Table a into Table B if Table B has 1codeclasssqlcodeclasssqlcodeclasssqlinsertintobselect * froma; codecodecode if Table B does not have 1 partition MySQL inserts the data queried in Table a into Table B. If table B has 1 code class = sqlcode class = sqlinsert into B select * from; /code if Table B does not exist 1 code class = sqlcode class = sqlc MySQL inserts the dat

; select count (*) into: dbstate from v $ parameter where lower (name) = '_ dummy_instance' and upper (value) = 'true'DBGSQL: sqlcode = 1008RMAN-00571: ========================================================== ========================RMAN-00569: ============== error message stack follows ======================RMAN-00571: ========================================================== ========================ORA-01008: not all variables boundOrRMAN-00571: